Hi, what would the correct way be to import an XML document from a URL? The document is generated on the fly and passed back as the request body - ie we call a remote service like https://www.server.co.uk/api/?apikey=12344&service=failures
which passes back a valid XML document in the response.
the XML document is not actually stored anywhere.
I would like to save this into SQL Server for processing. it is an XML document with about 7 levels of hierarchy.
I am happy loading the XML into an XML field in SQL for further querying.
i was looking at using openrecordset but this does not support none physical files and this approach is will not be security best practice
i am happy using SSIS. The XML data source will support a URL, but then the XML data source splits the hierarchy into tables, which i don't really want. i want to pass the xml as it is straight to an XML field.
i could uses SSIS to save the XML to a physical file, but this seems like an unnecessary step.
which approach is best? We are using SQL Server 2012