Блог

Главная - Вопросы по программированию - Как я могу вернуть содержимое поля XML в виде набора записей?

Как я могу вернуть содержимое поля XML в виде набора записей?

#sql #xml

Вопрос:

Допустим, у меня есть эта таблица (SQL Server 2005).:

 Id => integer  
MyField => XML

Удостоверение личности МаЙфилд

 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.

Комментарии:

1. Обратите внимание, что я не буду заранее знать, сколько «Типов» (например,AAA, BBB, CCC, DDD и т. Д.) Будет в строке xml.

2. тогда, скорее всего, вам придется использовать динамический sql и курсоры. я не думаю, что есть какое-то одно утверждение, чтобы получить то, что вы хотите.

Ответ №1:

Для этого вам потребуется использовать запрос XML в sql server.

что-то вроде

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

не уверен, что это на 100% правильный синтаксис xquery, но это будет что-то вроде этого.

Ответ №2:

Одним из возможных вариантов является использование XMLDataDocument. Используя этот класс, вы можете извлечь данные в формате XML, загрузить их в XmlDataDocument, а затем использовать свойство Dataset для доступа к ним, как если бы это был стандартный набор данных.

Ответ №3:

Вам нужно будет использовать 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 .

Метки: