Работа с XML в MS SQL?

#sql #xml #sql_server #xquery

Допустим, есть XML такого вида:















Можно ли получить выборку такого вида?

value
---------------
значение field1
значение field2
значение field3
значение field1
значение field2
значение field3


Можно ли это сделать без явного перечисления названия узлов? Допустим, известно,
что все узлы field+цифра. Можно ли сделать запрос по маске?
    

Ответы

Ответ 1

Вы можете отфильтровать узлы с нужными именами с помощью функций local-name и substring:

declare @xml xml = N'

  
    value 1
    value 2
    value 3
    AA
  
  
    value 4
    value 5
    value 6
    BB
  
';

select x.c.value('text()[1]', 'varchar(20)') value
from @xml.nodes('/root[1]/TableRow/*[substring(local-name(), 0, 6)="field"]') x(c);


Результат:

value
--------
value 1
value 2
value 3
value 4
value 5
value 6


В XQuery запросах использовать * в XPath (также как и выражения наподобие '//field'),
если без них можно обойтись, не рекомендуется. Если выбираете узлы от корня, то лучше
указать '/root[1]' в начале XPath. Если точно известен путь до узлов - указывайте его
явно. Если берёте значение из элемента, то лучше взять его не через ., а через text()[1].
Чем больше информации об узлах вы дадите XQuery процессору, тем проще будет ему распарсить
xml и тем ниже будет стоимость запроса.

В данном случае, например, оценочная стоимость такого запроса

select x.c.value('text()[1]', 'varchar(20)') value
from @xml.nodes('/root[1]/TableRow/*[substring(local-name(), 0, 6)="field"]') x(c);


и такого

select x.c.value('.', 'varchar(20)') value
from @xml.nodes('*/*/*[substring(local-name(), 0, 6)="field"]') x(c);


соотносятся примерно как 2 к 136.


Ответ 2

Должен сработать такой запрос:

select x.value('.', 'nvarchar(max)')
from [TableName]
cross apply [XmlField].nodes('*/*/*') t(x);


В выражении */*/* каждая звездочка соответствует любому имени на данном уровне вложенности.