Expanding list from SQL column with XML datatype

Question

Given a table with an XML column, how can I expand a list stored therein, into a relational rowset?

Here is an example of a similar table.

DECLARE @test TABLE
(
  id int,
  strings xml
)

insert into @test (id, strings)
select 1, '<ArrayOfString><string>Alpha</string><string>Bravo</string><string>Charlie</string></ArrayOfString>' union
select 2, '<ArrayOfString><string>Bravo</string><string>Delta</string></ArrayOfString>'

I would like to obtain a rowset like this.

id  string  
--  ------  
1   Alpha  
1   Bravo  
1   Charlie  
2   Bravo  
2   Delta  

I have figured out that I can use the nodes method to output the XML value for each id like this

select id, R.strings.query('/ArrayOfString/string') as string
from @test cross apply strings.nodes('/ArrayOfString/string') as R(strings)

returning

id  string
--  ------
1   <string>Alpha</string><string>Bravo</string><string>Charlie</string>
1   <string>Alpha</string><string>Bravo</string><string>Charlie</string>
1   <string>Alpha</string><string>Bravo</string><string>Charlie</string>
2   <string>Bravo</string><string>Delta</string>
2   <string>Bravo</string><string>Delta</string>

and that I can append [1] to the query to get only the first string

select id, R.strings.query('/ArrayOfString/string[1]') as string
from @test cross apply strings.nodes('/ArrayOfString/string') as R(strings)

but that still leaves me with this

id  string
--  ------
1   <string>Alpha</string>
1   <string>Alpha</string>
1   <string>Alpha</string>
2   <string>Bravo</string>
2   <string>Bravo</string>

Additionally, if I change the R.strings.query to R.strings.value to get rid of the tags, I get an error; XQuery [@test.strings.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'.

Can anyone point out what I'm missing in order to get each subsequent string value for each id on subsequent rows, and why the value() method is giving me that error?

Answer 1

Maybe you could try using the string() function instead of the value() function? This should return the actual value of the node without the xml.

Here is the msdn page - http://msdn.microsoft.com/en-us/library/ms188692.aspx

Really hope that helps. :)

Answer 2

Here is how you can do it using a cross apply and the xml methods value and nodes:

select 
    id,
    s2.value('.', 'varchar(max)') as string
from @test
cross apply strings.nodes('/*/string') t(s2)

You were getting the error because you were missing the () around the xPath:

select id, 
    R.strings.value('(/ArrayOfString/string)[1]','varchar(max)') as string
from @test 
    cross apply strings.nodes('/ArrayOfString/string') as R(strings)

The problem with your script is that you are always selecting the first string. Using the '.' you will get the contents of the XML element string.