выбрать несколько узлов xml

Я получаю текст в формате xml из полей ntext (пример формата строки ниже):

<root>
  <DocInfo>
    <CompanyName>Some Company</CompanyName>
    <WebsiteUrl>http://www.someurl.com</WebsiteUrl>
    <PrimaryServices>Benefits Administration</PrimaryServices>
    <PrimaryServices>Payroll Processing</PrimaryServices>
    <SecondaryServices>Background Checking</SecondaryServices>
    <SecondaryServices>HR Outsourcing</SecondaryServices>
    <SecondaryServices>Comp & Benefits</SecondaryServices>
    <SecondaryServices>Administration</SecondaryServices>
  </DocInfo>
</root>

Используя этот sql, я получаю значения одного узла:

select  @xmlString = COALESCE(@xmlString + '', '') + cast(content_html as nvarchar(max)) FROM  content where folder_id = 18
set @xmlString = replace(@xmlString,'<?xml version="1.0" encoding="UTF-16" standalone="yes"?>','')
set @XML = cast(@xmlString as xml)

Select
T.N.value('CompanyName[1]', 'varchar(250)') as CompanyName,
T.N.value('WebsiteUrl[1]', 'varchar(250)') as WebsiteUrl,
T.N.value('PrimaryServices[1]', 'varchar(250)') as PrimaryServices,
T.N.value('SecondaryServices[1]', 'varchar(250)') as SecondaryServices,
T.N.value('Description[1]', 'varchar(max)') as Description
from @XML.nodes('/root/DocInfo') as T(N)

Это отлично работает для значений одного узла (CompanyName, WebsiteUrl). Однако он неправильно вставляет узлы с несколькими значениями (например, PrimaryServices и SecondaryServices, каждый из которых может иметь от 0 до 16 узлов). Как мне получить эти значения нескольких узлов переменной длины в эти столбцы?

Спасибо за любую помощь


xml sql-server sql-server-2008 xquery-sql
person lance-p    schedule 13.03.2013    source источник

Ответы (1)


arrow_upward
3
arrow_downward

Чтобы получить несколько узлов в виде значения, разделенного запятыми, вы можете использовать вариант трюка for xml path(''). Используйте измельченный XML (T.N) в качестве источника в подзапросе, чтобы получить интересующие вас узлы. Часть xQuery ... substring(text()[1]) ... предназначена только для удаления лишней запятой и получения значения, разделенного запятыми, из созданного XML. от for xml.

select
  T.N.value('(CompanyName/text())[1]', 'varchar(250)') as CompanyName,
  T.N.value('(WebsiteUrl/text())[1]', 'varchar(250)') as WebsiteUrl,
  (
    select ', '+P.N.value('text()[1]', 'varchar(max)')
    from T.N.nodes('PrimaryServices') as P(N)
    for xml path(''), type
  ).value('substring(text()[1], 2)', 'varchar(max)') as PrimaryServices,
  (
    select ', '+S.N.value('text()[1]', 'varchar(max)')
    from T.N.nodes('SecondaryServices') as S(N)
    for xml path(''), type
  ).value('substring(text()[1], 2)', 'varchar(max)') as SecondaryServices,
  T.N.value('(Description/text())[1]', 'varchar(max)') as Description
from @XML.nodes('/root/DocInfo') as T(N)

Если вы хотите, чтобы все службы были в одном столбце, вы можете использовать другой xPath в части узлов в подзапросе.

select
  T.N.value('(CompanyName/text())[1]', 'varchar(250)') as CompanyName,
  T.N.value('(WebsiteUrl/text())[1]', 'varchar(250)') as WebsiteUrl,
  (
    select ', '+P.N.value('text()[1]', 'varchar(max)')
    from T.N.nodes('PrimaryServices,SecondaryServices') as P(N)
    for xml path(''), type
  ).value('substring(text()[1], 2)', 'varchar(max)') as Services,
  T.N.value('(Description/text())[1]', 'varchar(max)') as Description
from @XML.nodes('/root/DocInfo') as T(N)
person Mikael Eriksson    schedule 13.03.2013
comment
Спасибо. Это получает как xml, так и значение. Как получить только значение? Т - person lance-p; 13.03.2013
comment
@ user971823 Как вам нужны значения? В один столбец, отдельные столбцы, отдельные строки что-то еще или ....? - person Mikael Eriksson; 13.03.2013
comment
Один столбец, разделенный запятой, был бы идеальным. - person lance-p; 14.03.2013
comment
Большой. Это было именно то, что мне было нужно. Спасибо. - person lance-p; 16.03.2013