import XML into SQL Server XML field direct from URL
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have ended up doing this in SSIS with a System.Net.WebClient, reading the respnnse to a variable and using that in an insert statement
Glad you got it sorted!
ASKER
so is the best approach to get the response data into a string and just use a straight insert command then?
I can write the code for this and have done similar http response grabbers before.
thinking about it - it's just a string of text isn't it. if I totally ignore the XML being XML, all I want to do is take a string of text from the internet and put in into an SQL Server field.