February 8, 2010 at 12:09 pm
I am trying to query an XML: document to produce a particular output string. Here is an example of what I am trying to do:
Consider this XML document
<strinfo>
<choices>
<choice id="111" >
<Labels>
<Label>
<LabelType id="1" />
<Label id="11" />
</Label>
<Label>
<LabelType id="2" />
<Label id="22" />
</Label>
</Labels>
</choice>
<choice id="222" />
<choice id="333" />
</choices>
<miscellaneous>
<misc id="2001" />
<misc id="2003" />
<misc id="2005" />
</miscellaneous>
</strinfo>
I am trying to build a string which is the concatenation of the choice ids and the label types and Id's. So for the data above, what I'd really like to get is:
111-1-11-2-22
(choices 222 and 333 don't have label info so I don't want them in my string)
I could live with 111-1-11-111-2-222 if that was a lot eaiser/faster....
I know how to query the xml to get two rows (111, 1, 11 and 111,2,22) and I could go from there to building a string, but I can't help but think there is a better way to do what I want. Any suggestions?
Thanks!
February 8, 2010 at 1:15 pm
Assuming you have your xml data stored in an xml variable named @xml, I'd use the following code:
;WITH cte AS
(
SELECT
c.value('@id[1]', 'varchar(10)') +'-'+
y.value('LabelType[1]/@id[1]', 'varchar(10)')+'-'+
y.value('Label[1]/@id[1]', 'varchar(10)') a
FROM @xml.nodes('strinfo/choices/choice') t(c)
CROSS APPLY
t.c.nodes('Labels/Label') x(y)
)
SELECT STUFF((SELECT '-' + a FROM cte FOR XML PATH('')),1,1,'')
-- result: 111-1-11-111-2-22
February 8, 2010 at 3:01 pm
Wow - that works perfectly!! Thanks VERY much - Jeff
February 8, 2010 at 4:17 pm
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply