How to shred xml column into multiple rows of data

• bexstarr

  SSC Veteran

  Points: 276

  More actions

  June 3, 2013 at 7:38 am

  #275898

  I have a table with thousands of rows and a large xml blob containing lots of elements. I need to shred all the elements into a table. However, I have multiple elements in the same root like so:

  ID xml

  1 <root>

  <name1 row = 1 value ="1" />

  <name2 row = 1 value ="2" />

  <name1 row = 2 value ="3" />

  <name2 row = 2 value ="4" />

  <name1 row = 3 value ="5" />

  <name2 row = 3 value ="6" />

  <name1 row = 4 value ="7" />

  <name2 row = 4 value ="8"/>

  <name1 row = 5 value ="9"/>

  <name2 row = 5 value ="10"/>

  </root>

  So I want to shred all the rows like so:

  ID Row Name1 Name2

  1 1 1 2

  1 2 3 4

  1 3 5 6

  1 4 7 8

  1 5 9 10

  CREATE TABLE #TEMP (ID int, XMLStuff xml)

  INSERT INTO #TEMP

  SELECT 1, '<dynoformdata><incidentdate row="0" value="2013/01/13" /><totalretail row="0" value="27.25"/><incidentdate row="1" value="2013/01/14" /><totalretail row="1" value="10.75"/></dynoformdata>'

  UNION

  SELECT 2, '<dynoformdata><incidentdate row="0" value="2013/01/14" /><totalretail row="0" value="17.05"/><incidentdate row="1" value="2013/02/15" /><totalretail row="1" value="11.75"/></dynoformdata>'

  UNION

  SELECT 3,'<dynoformdata><incidentdate row="0" value="2013/01/15" /><totalretail row="0" value="17.05"/><incidentdate row="1" value="2013/02/16" /><totalretail row="1" value="8.75"/></dynoformdata>'

  SELECT *

  FROM #TEMP

  I want the results to look something like this:

• Mark Cowne

  One Orange Chip

  Points: 26944

  More actions

  June 3, 2013 at 7:48 am

  #1620550

  Try this

  DECLARE @t TABLE(ID INT, X XML)

  INSERT INTO @T(ID,X)

  VALUES(1,'<root>

  <name1 row = "1" value ="1" />

  <name2 row = "1" value ="2" />

  <name1 row = "2" value ="3" />

  <name2 row = "2" value ="4" />

  <name1 row = "3" value ="5" />

  <name2 row = "3" value ="6" />

  <name1 row = "4" value ="7" />

  <name2 row = "4" value ="8"/>

  <name1 row = "5" value ="9"/>

  <name2 row = "5" value ="10"/>

  </root>');

  WITH CTE AS (

  SELECT ID,

  x.r.value('local-name(.)','VARCHAR(10)') AS name,

  x.r.value('@row','INT') AS Row,

  x.r.value('@value','INT') AS value

  FROM @t

  CROSS APPLY X.nodes('/root/*') AS x(r))

  SELECT ID,

  Row,

  MAX(CASE WHEN name='name1' THEN value END) AS Name1,

  MAX(CASE WHEN name='name2' THEN value END) AS Name2

  FROM CTE

  GROUP BY ID,Row

  ORDER BY ID,Row;

  ____________________________________________________

  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

• bexstarr

  SSC Veteran

  Points: 276

  More actions

  June 3, 2013 at 8:34 am

  #1620591

  Thank you very much