Help with shredding xml using nodes()

• Johnny-854353

  SSC Rookie

  Points: 40

  More actions

  October 22, 2008 at 7:45 am

  #195043

  I am new to the world of xml and sql 2005. I have a xml file which contains both elements and attributes. The xml file needs to be shredded into a sql table. A row needs to be built per /root tag found in the xml file. To accomplish this I have been trying to use the XQuery.nodes() method. There are four separate levels from which I need to extract data. These include /root, /root/tbl_MFC, /root/tbl_Component, and /root/tbl_Insert. I have been able to shred the file based on four separate select statements. I should be able to insert these four select statements into four tables and then join them together to one table by using identity fields set in each of the four tables. However, it would be more efficient to shred the file using one select statement. Is this possible? When I have tried to put them into one select statement, I receive numerous errors. I am hoping I am overlooking the obvious. Please see attachment to view sample xml and code I have working.

  Any help would be greatly appreciated.

• Mark Cowne

  One Orange Chip

  Points: 26984

  More actions

  October 22, 2008 at 8:03 am

  #888123

  Not sure how you can get one row per /root tag where there are

  multiple tbl_Component and tbl_Insert child elements, however this may help

  SELECT XMLTable.XMLColumn.value('tbl_Site[1]', 'VARCHAR(25)') AS tbl_Site,

  XMLTable.XMLColumn.value('tbl_Applications[1]', 'VARCHAR(25)') AS tbl_Applications,

  XMLTable.XMLColumn.value('tbl_Language[1]', 'VARCHAR(25)') AS tbl_Language,

  XMLTable2.XMLColumn2.value('@Cycle_Time_Stamp', 'VARCHAR(25)') AS Cycle_Time_Stamp,

  XMLTable2.XMLColumn2.value('@MailDate', 'datetime') AS MailDate,

  XMLTable2.XMLColumn2.value('@Tot_Bills', 'VARCHAR(25)') AS Tot_Bills,

  XMLTable3.XMLColumn3.value('@Data_Load_Name', 'VARCHAR(25)') AS Data_Load_Name,

  XMLTable3.XMLColumn3.value('@Qty', 'VARCHAR(25)') AS Qty,

  XMLTable4.XMLColumn4.value('@Insert_name', 'VARCHAR(25)') AS Insert_name,

  XMLTable4.XMLColumn4.value('@Weight', 'VARCHAR(25)') AS Weight,

  XMLTable4.XMLColumn4.value('@Qty', 'VARCHAR(25)') AS Qty

  FROM @xml.nodes('/root') AS XMLTable(XMLColumn)

  CROSS APPLY XMLTable.XMLColumn.nodes('tbl_MFC') AS XMLTable2(XMLColumn2)

  CROSS APPLY XMLTable.XMLColumn.nodes('tbl_Component') AS XMLTable3(XMLColumn3)

  CROSS APPLY XMLTable.XMLColumn.nodes('tbl_Insert') AS XMLTable4(XMLColumn4)

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537