Как я могу вернуть содержимое поля XML в виде набора записей?
Скажем, у меня есть эта таблица (SQL Server 2005):
Id => integer
MyField => XML
Id MyField
1 < Object>< Type>AAA< /Type>< Value>10< /Value>< /Object>< Object>< Type>BBB< /Type><Value>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>
Мне нужен запрос TSQL, который вернет что-то вроде этого:
Id AAA BBB
1 10 20
2 15 NULL
3 20 30
Обратите внимание, что я не буду знать заранее, сколько 'Type' (например, AAA, BBB, CCC, DDD и т. д.) Будет в строке xml.
вам, скорее всего, придется использовать динамический sql и курсоры. нет единого заявления, чтобы получить то, что вы хотите, я не думаю.
Ответы 3
Для этого вам нужно будет использовать Запрос XML на сервере sql.
что-то вроде
select id, MyField.query('/Object/Type[. = "AAA"]/Value') as AAA, MyField.query('/Object/Type[. = "BBB"]/Value) AS BBB
не уверен, что это на 100% правильный синтаксис xquery, но это будет что-то в этом роде.
Один из возможных вариантов - использовать XMLDataDocument. Используя этот класс, вы можете извлекать данные, когда XML загружает их в XmlDataDocument, а затем использовать свойство Dataset для доступа к ним, как если бы это был стандартный набор данных.
Вам нужно будет использовать CROSS APPLY. Вот пример, основанный на вашем запросе:
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)
О, и ваш пример XML недействителен, в первой строке отсутствует открывающий элемент Value для Type BBB.
Обратите внимание, что я не буду знать заранее, сколько «Типов» (например, AAA, BBB, CCC, DDD и т. д.) Будет в строке xml.