3

I have a value in my MS SQL table column 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 get the value of the row back 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 am getting 123 back. Not three rows with two columns. How do I get three rows back, each row with two columns:

KEY, VALUE
    1,A
    2,B
    3,C

Thanks

InfoLearner
  • 14,952
  • 20
  • 76
  • 124

1 Answers1

3

Use nodes() to shred the XML to rows and use value() to get a value.

select 
  T.N.value('Key[1]', 'int') as [Key],
  T.N.value('Value[1]', 'varchar(10)') as Value
from @x.nodes('/root/KV') as T(N)
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • great. what if instead of having @x, i wanted column of a table e.g. Data column of table Information? – InfoLearner Mar 28 '12 at 16:41
  • 1
    @KnowledgeSeeker - You put the table in the `from`clause and do a `cross apply` against the XML column. Have a look at [this answer](http://stackoverflow.com/a/9600806/569436) – Mikael Eriksson Mar 28 '12 at 17:02