Import XML into SQL datatypes and XML datatype

Question

Post reply

Import XML into SQL datatypes and XML datatype

mstevens92

Old Hand

Points: 320
More actions
September 27, 2010 at 2:43 pm

#73695

I have the following XML structure:
<FORM NAME="LA NOTICE WD (01-10)" OPTIONS="N">
<DESCRIPTION>LA NOTICE W/WIND NOT EXCLUDED</DESCRIPTION>
<RECIPIENT NAME="Company" COPYCOUNT="1"/>
<RECIPIENT NAME="General Agent" COPYCOUNT="1"/>
<RECIPIENT NAME="Insured" COPYCOUNT="1"/>
<RECIPIENT NAME="Memo" COPYCOUNT="0"/>
<RECIPIENT NAME="Retail Agent" COPYCOUNT="1"/>
<RECIPIENT NAME="Stamping" COPYCOUNT="0"/>
<SHEET>
<PAGE NUMBER="1">
<SECTION NAME="LANOTWD-10" SRCNAME="LANOTWD-10" OPTIONS="FDM">
<DAPINSTANCE VALUE="1"/>
<DAPOPTIONS VALUE="M"/>
</SECTION>
</PAGE>
<PAGE NUMBER="2">
<SECTION NAME="LANOTWD-10" SRCNAME="LANOTWD-10_2" OPTIONS="RDM">
<DAPINSTANCE VALUE="2"/>
<DAPOPTIONS VALUE="M"/>
</SECTION>
</PAGE>
<PAGE NUMBER="3">
<SECTION NAME="LANOTWD-10" SRCNAME="LANOTWD-10_3" OPTIONS="RDM">
<DAPINSTANCE VALUE="3"/>
<DAPOPTIONS VALUE="M"/>
</SECTION>
</PAGE>
</SHEET>
</FORM>
I would like to import this into the following table:
FormName varchar(255),
FormDescription varchar(255),
FormOptions varchar(50),
Recipients xml,
Sheets xml
I'm basically stuck trying to figure out how to insert the XML portions. I don't want to the Recipient and Sheet portions as SQL data, I want the raw XML for those two columns. Thanks in advance.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Something like this?

SELECT

c.value('@NAME[1]','varchar(50)') AS a,

c.value('@OPTIONS[1]','varchar(10)') AS b,

c.value('DESCRIPTION[1]','varchar(50)') AS c,

c.query('SHEET'),

c.query('RECIPIENT')

FROM @xml.nodes('FORM') T(c)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

mstevens92 Old Hand Points: 320 More actions · Answer 2

This looks perfect. However, my SQL ignorance forces me to ask how to read this from an XML file. I'm googling away at that question now, but not finding many good answers...

Thanks Lutz

LutzM SSC Guru Points: 107049 More actions · Answer 3

I know, the XML stuff looks weird compared to what we're used to... 😉

OPENROWSET is a lot closer...

DECLARE @xml xml

SET @xml =(

SELECT * FROM OPENROWSET(

BULK 'C:\YourFolder\YourFileName',

SINGLE_BLOB

) AS x

)

select @xml

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

mstevens92 Old Hand Points: 320 More actions · Answer 4

LutzM (9/27/2010)I know, the XML stuff looks weird compared to what we're used to... 😉

Probably doesn't help that I haven't touched SQL in about 6 months, been up to my eyeballs in C# and Entity Framework. Going back and forth from T-SQL to LINQtoEntities is giving me head trauma. Plus I kept hovering the mouse over '@xml" variable trying to determine its type......

Thanks for your help Lutz, saved me countless hours of google-ized fun.

LutzM SSC Guru Points: 107049 More actions · Answer 5

mstevens92 (9/28/2010)
LutzM (9/27/2010)I know, the XML stuff looks weird compared to what we're used to... 😉
Probably doesn't help that I haven't touched SQL in about 6 months, been up to my eyeballs in C# and Entity Framework. Going back and forth from T-SQL to LINQtoEntities is giving me head trauma. Plus I kept hovering the mouse over '@xml" variable trying to determine its type......
Thanks for your help Lutz, saved me countless hours of google-ized fun.

Glad I could help 😀

Like I said: the XQuery syntax isn't something that'll fit into the "usual SQL language".

It's like any kind of language (either programming or foreign language): If you don't use it, you'll lose it.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]