XML to SQL Insert Script

[dubious]

Hi all

I have an XML file that has a bunch of data in that I need to insert into a MS SQL database. I've tried looking around online but everthing I have had a look at seems to be written for people beyond my skill level (which is very basic). Does anyone have a "XML to SQL for Dummies" kind of method to do this?

Thanks in advance

 

[guest2013-1]

Perfect learning opportunity for you. I've recently done more or less what you want, not sure about how you'd read the file in T-SQL

Here's a hint:

SELECT
Tbl.Col.value(@column_name_in_xml,varchar(10)) as column1
FROM @xml.nodes(//root/rows/data/wherever) Tbl(Col)

Enjoy

(I used the above in conjunction with a user-defined table type to store the structure of the xml for me and then build the select list dynamically)

 

[dadecoza]

I do this all the time with perl and XML::Simple. But I guess you wouldnt want to go that route using MS SQL and all.

 

[ToxicBunny]

Acid pretty much has it spot on

cracking the xml is nasty, but sql can do it with a bit of patience.

 

[semaphore]

http://www.codeproject.com/Tips/70586/T-SQL-Cursor-and-XML-Basic-Example