XML file import to sql server 2005

Question

Post reply

XML file import to sql server 2005

balbirsinghsodhi

SSCrazy Eights

Points: 9210
More actions
June 11, 2008 at 11:36 am

#91980

Can somebody help me importing XML file into sql 2005 table.
Thanks a lot.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 1

Here is a sample that loads an xml file to a table with an xml column.

INSERT INTO InventoryFeeds (InventoryXML)

SELECT InventoryXML

FROM ( SELECT * FROM OPENROWSET

(BULK 'C:\Test\Inventory.xml', SINGLE_CLOB) AS xmlData) AS feed (InventoryXML)

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 2

balbirsinghsodhi

SSCrazy Eights

Points: 9210

June 11, 2008 at 12:25 pm

#827464

Thanks a lot but how to parse into columns.

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 3

Here is a sample of inserting from the table with the xml datatype to a parsed table.

INSERT INTO SERVERS(SerialNumber,Manufacturer,Model,SystemType)

Select

InventoryXML.value('(/Root/Server/SerialNumber)[1]','varchar(50)') SerialNumber,

InventoryXML.value('(/Root/Server/Manufacturer)[1]','varchar(50)') Manufacturer,

InventoryXML.value('(/Root/Server/Model)[1]','varchar(50)') Model,

InventoryXML.value('(/Root/Server/SystemType)[1]','varchar(50)') SystemType

FROM InventoryFeeds where InventoryFeedID =@InventoryFeedID

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 4

I new to xml. Here is my xml file. How can I use your insert function to parse into columns.

-

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 5

Here is the XML file. How can I use insert statement to parse into column

-

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

Just attach it as a text file.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 7

I am able to extract data from XML files using SSIS package through XML Source component. It looks ok but there is little problem about datatype. Default datatype for one of the xml column is unsigned integer (DT_UI8) but in sql server there is no unsigned integer type, so I have to change the datatype from DT_UI8 to DT_I4 (integer)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

balbirsinghsodhi (6/12/2008)
I am able to extract data from XML files using SSIS package through XML Source component. It looks ok but there is little problem about datatype. Default datatype for one of the xml column is unsigned integer (DT_UI8) but in sql server there is no unsigned integer type, so I have to change the datatype from DT_UI8 to DT_I4 (integer)

No, I mean attach your example as a text file to this discussion. We cannot see your previous two attempts, because the forum software strips out anything that might be html or xml tags.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]