Insert XML Data

• venkat84.tnj

  Newbie

  Points: 9

  More actions

  June 3, 2008 at 3:31 am

  #187339

  How to insert XML data into Table data in SQL Server

  Thanx in advance...

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  June 3, 2008 at 5:55 pm

  #823922

  This works just fine:

  INSERT into TestTable( xmlData )

  Values ( N'<root>text</root>')

  [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]

• Ken Simmons

  SSCertifiable

  Points: 7822

  More actions

  June 3, 2008 at 7:00 pm

  #823941

  It depends on your source. If you import from a file you can use something like...

  INSERT INTO InventoryFeeds (InventoryXML)

  SELECT InventoryXML

  FROM ( SELECT * FROM OPENROWSET

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

  To import the data to an xml column. If you want to get it into other tables from there, you could query the xml column to insert the data.

  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