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
LVL 1
PatrickK_WAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MlandaTCommented:
you could do this all from within SQL Server itself without having to use SSIS. To talk to the internet (well... an HTTP resource), you will have to create a C# SQL CLR procedure. In the C# code, you would use the HttpWebRequest or HttpWebClient to download the data as a string and write it to your table. An example of this can be found here: http://sqlsunday.com/2013/03/03/web-requests-using-clr-proc/ (I think the example perhaps splits up the string but you won't have to do that step), Take note of the security requirements for this approach (PERMISSION_SET = UNSAFE, enable CLR objects on your SQL Server instance etc). You can then call this stored procedure from SSIS
0
PatrickK_WAuthor Commented:
Hi, thanks for this.

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.
0
MlandaTCommented:
YUP! It's just a string! :)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatrickK_WAuthor Commented:
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
0
MlandaTCommented:
Glad you got it sorted!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.