MS SQL 2008 - How to convert xml value to row collection from sql sqs table?
I have a value in a column of MS SQL table called "XDATA" like this:
<root><KV><Key>1</Key><Value>A</Value></KV>
<KV><Key>2</Key><Value>B</Value></KV>
<KV><Key>3</Key><Value>C</Value></KV></root>
I want to be able to return a string value like this:
KEY, VALUE 1,A 2,B 3,C
So far I have written this:
DECLARE @a1 xml
declare @x xml;
set @x = '<root><KV><Key>1</Key><Value>A</Value></KV>
<KV><Key>2</Key><Value>B</Value></KV>
<KV><Key>3</Key><Value>C</Value></KV></root>';
SELECT @a1 = @x.query('(/root/KV/Key/text())')
select @a1
But I'm going back. Not three rows with two columns. How to get three rows back, each row with two columns:
KEY, VALUE 1,A 2,B 3,C
thank
+3
source to share