How can I return the content of an XML field as a recordset?

Question

Say I've got this table (SQL Server 2005):

Id => integer
MyField => XML

Id MyField
1 < Object>< Type>AAA< /Type>< Value>10< /Value>< /Object>< Object>< Type>BBB< /Type>20< /Value>< /Object>
2 < Object>< Type>AAA< /Type>< Value>15< /Value>< /Object>
3 < Object>< Type>AAA< /Type>< Value>20< /Value>< /Object>< Object>< Type>BBB< /Type>< Value>30< /Value>< /Object>

I need a TSQL query which would return something like this:

Id AAA BBB
1 10 20
2 15 NULL
3 20 30

Note that I won't know if advance how many 'Type' (eg AAA, BBB, CCC,DDD, etc.) there will be in the xml string.

Answer 1

You will need to use the XML querying in sql server to do that.

somethings like

select id, MyField.query('/Object/Type[.="AAA"]/Value') as AAA, MyField.query('/Object/Type[.="BBB"]/Value) AS BBB

not sure if that's 100% correct xquery syntax, but it's going to be something like that.

Answer 2

One possible option is to use the XMLDataDocument. Using this class you can retrieve the data as XML load it into the XmlDataDocument and then use the Dataset property to access it as if it were a standard dataset.

Answer 3

You'll need to use CROSS APPLY. Here's an example based on your request:

declare @y table (rowid int, xmlblock xml)
insert into @y values(1,'<Object><Type>AAA</Type><Value>10</Value></Object><Object><Type>BBB</Type><Value>20</Value></Object>')
insert into @y values(2,'<Object><Type>AAA</Type><Value>15</Value></Object>')
insert into @y values(3,'<Object><Type>AAA</Type><Value>20</Value></Object><Object><Type>BBB</Type><Value>30</Value></Object>')

select y.rowid, t.b.value('Type[1]', 'nvarchar(5)'), t.b.value('Value[1]', 'int')
from @y y  CROSS APPLY  XmlBlock.nodes('//Object') t(b)

Oh, and your example XML is invalid, the first row is missing the opening Value element for Type BBB.