June 3, 2013 at 7:38 am
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:
June 3, 2013 at 7:48 am
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/61537June 3, 2013 at 8:34 am
Thank you very much
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply