I have a set of XML as follows in a column "test" of type xml. The table is "cow".
<p>
<k>query</k>
<v>
<w id="a" name="b">
<p>
<op>IN</op>
<v name="apple">1</v>
<v name="pear">2</v>
<v name="kiwi">3</v>
<v name="carrot">4</v>
</p>
</w>
</v>
</p>
I want to write a select statement that returns 1 row and 1 column with a value of "apple, pear, kiwi, carrot". Is this even possible?
If there's no other criteria and all you need to do is retrieve those values as a single thing, you can do it something like this:
SELECT STUFF((SELECT ', ' + A.B.value('@name', 'NVARCHAR(255)')
FROM cow
CROSS APPLY test.nodes('p/v/w/p/v') A(B)
FOR XML PATH('')), 1, 2, '')
Ironically, the FOR XML PATH has no bearing on the fact that it's an XML column to begin with.
Consider using XQuery's data() function which takes a sequence of items and atomizes them:
CREATE TABLE #cow(cow XML);
INSERT INTO #cow
SELECT (N'<p>
<k>query</k>
<v>
<w id="a" name="b">
<p>
<op>IN</op>
<v name="apple">1</v>
<v name="pear">2</v>
<v name="kiwi">3</v>
<v name="carrot">4</v>
</p>
</w>
</v>
</p>');
select cow.query('data(/p/v/w/p/v/@name)') as example from #cow
# example
#1 apple pear kiwi carrot
For comma-separated values:
select REPLACE(CONVERT(nvarchar(255), cow.query('data(/p/v/w/p/v/@name)')),
' ', ', ') as example from #cow
# example
#1 apple, pear, kiwi, carrot
