xml Question
-
Using SQL Server 2005 sp3
I have a query.
SELECT attribute_name, attribute_value
FROM dbo.extraAttributes AS mea
INNER JOIN dbo.testXml AS ma
ON mea.option_id = ma.option_id
WHERE mea.option_id = 7584497 OR mea.option_id = 7584445
Which gives me the following 3 rows.
attribute_nameattribute_value
SIZEL
SIZES
COLORBlack
XML QUERY:
SELECT attribute_name, attribute_value
FROM dbo.extraAttributes AS mea
INNER JOIN dbo.testXml AS ma
ON mea.option_id = ma.option_id
WHERE mea.option_id = 7584497 OR mea.option_id = 7584445
FOR XML PATH('item'), TYPE
Output as xml it looks like
<item>
<attribute_name>COLOR</attribute_name>
<attribute_value>Black</attribute_value>
</item>
<item>
<attribute_name>SIZE</attribute_name>
<attribute_value>S</attribute_value>
</item>
<item>
<attribute_name>SIZE</attribute_name>
<attribute_value>L</attribute_value>
</item>
Is there anyway to use the data values from attribute_name(('SIZE'/'COLOR') to change the xml output to
<item>
<SIZE>L</SIZE>
<SIZE>S</SIZE>
<COLOR>Black</COLOR>
</item>
and do this at run time for every row?
-
Yup.
DECLARE @MyXML XML
SET @MyXML = '
<item>
<attribute_name>COLOR</attribute_name>
<attribute_value>Black</attribute_value>
</item><item>
<attribute_name>SIZE</attribute_name>
<attribute_value>S</attribute_value>
</item><item>
<attribute_name>SIZE</attribute_name>
<attribute_value>L</attribute_value>
</item>'
SELECT * FROM (
SELECT CASE WHEN Attribute_name = 'COLOR' THEN Attribute_value ELSE NULL END AS COLOR
,CASE WHEN Attribute_name = 'SIZE' THEN Attribute_value ELSE NULL END AS SIZE
FROM (
SELECT x.value('attribute_name[1]', 'VARCHAR(10)') AS Attribute_name
,x.value('attribute_value[1]', 'VARCHAR(10)') AS Attribute_value
FROM @MyXML.nodes('item') x(x)) a) b
FOR XML PATH(''), ROOT('item')
To do this across all rows in a table, you'll need to CROSS APPLY the nodes method.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
My bad I didn't make myself clear enough. In the extraAttributes table I have over 130 distinct attribute_names (I just used 'COLOR' and 'SIZE' as examples) and more will be added. So at run time I don't know what the values are going to be. In your example building a CASE for all the values isn't practical as I would be modifying it every time a new attribute_name value was added.
Otherwise pretty cool script!
-
Michael Kaluza (6/8/2012)
My bad I didn't make myself clear enough. In the extraAttributes table I have over 130 distinct attribute_names (I just used 'COLOR' and 'SIZE' as examples) and more will be added. So at run time I don't know what the values are going to be. In your example building a CASE for all the values isn't practical as I would be modifying it every time a new attribute_name value was added.Otherwise pretty cool script!
That could be quite challenging because I've always seen the attributes hard-coded within the value function's parameters. I'm not even sure you can pass in local parameters.
Of course, there may be another way with some more advanced shredding techniques that I'm unaware of. I am but a Padawan at this stuff. I will think about it a little more and see if something comes to me though.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
Some Dynamic SQL may be your friend here. Notice that I've made this work for the column of a table.
CREATE TABLE #t (MyXML XML)
INSERT INTO #t
SELECT '<item>
<attribute_name>COLOR</attribute_name>
<attribute_value>Black</attribute_value>
</item><item>
<attribute_name>SIZE</attribute_name>
<attribute_value>S</attribute_value>
</item><item>
<attribute_name>SIZE</attribute_name>
<attribute_value>L</attribute_value>
</item><item>
<attribute_name>MATERIAL</attribute_name>
<attribute_value>Lace</attribute_value>
</item><item>
<attribute_name>MATERIAL</attribute_name>
<attribute_value>Felt</attribute_value>
</item>'
UNION ALL SELECT '<item>
<attribute_name>COLOR</attribute_name>
<attribute_value>Black</attribute_value>
</item><item>
<attribute_name>SIZE</attribute_name>
<attribute_value>S</attribute_value>
</item><item>
<attribute_name>SIZE</attribute_name>
<attribute_value>L</attribute_value>
</item><item>
<attribute_name>MATERIAL</attribute_name>
<attribute_value>Lace</attribute_value>
</item><item>
<attribute_name>MATERIAL</attribute_name>
<attribute_value>Felt</attribute_value>
</item>'
SELECT * FROM #t
DECLARE @sql VARCHAR(MAX) = 'SELECT * FROM ( SELECT '
(SELECT ',' + ' CASE WHEN Attribute_name = ''' + attribute_name +
''' THEN Attribute_value ELSE NULL END AS ' + attribute_name + ' '
FROM (
SELECT DISTINCT str.value('attribute_name[1]', 'VARCHAR(10)') AS Attribute_name
FROM #t CROSS APPLY MyXML.nodes('item') x(str) ) y
FOR XML PATH(''), root('MyString'), type).value('/MyString[1]','varchar(max)' )
, 1, 1, '')
' FROM (
SELECT x.value(''attribute_name[1]'', ''VARCHAR(10)'') AS Attribute_name
,x.value(''attribute_value[1]'', ''VARCHAR(10)'') AS Attribute_value
FROM #t CROSS APPLY MyXML.nodes(''item'') x(x)) a) b
FOR XML PATH(''''), ROOT(''item'')'
SELECT @sql
EXEC (@SQL)
DROP TABLE #t
The XML that results from this is:
<item>
<COLOR>Black</COLOR>
<SIZE>S</SIZE>
<SIZE>L</SIZE>
<MATERIAL>Lace</MATERIAL>
<MATERIAL>Felt</MATERIAL>
<COLOR>Black</COLOR>
<SIZE>S</SIZE>
<SIZE>L</SIZE>
<MATERIAL>Lace</MATERIAL>
<MATERIAL>Felt</MATERIAL>
</item>
That's one ugly, nasty piece of work there. You may need to massage it if the final XML is not exactly to your liking, e.g., if you need tags between each row of the table.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
You've definitely given me some food for thought here. I actually may try to dynamically create the attributes but like you said it would probably get ugly. I might have to rethink the way we want to process our xml.
As always thanks for the help!
-
Here's another to try
DECLARE @t TABLE(attribute_nameVARCHAR(10),attribute_value VARCHAR(10))INSERT INTO @T(attribute_name,attribute_value)
SELECT 'SIZE','L' UNION ALL
SELECT 'SIZE','S' UNION ALL
SELECT 'COLOR','Black';
SELECT CAST('<' + attribute_name + '>' + attribute_value + '</' + attribute_name + '>' AS XML)
FROM @t
FOR XML PATH(''), ROOT('item');
____________________________________________________
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply