sql-server - 选择多个节点xml

Question

我正在从 ntext 字段中检索 xml 格式的文本(下面一行的示例格式):

<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)

这适用于单节点值(公司名称、网站网址)。但是，它没有正确插入具有多个值的节点(例如 PrimaryServices 和 SecondaryServices - 每个节点可能有 0 到 16 个节点)。如何将这些可变长度的多节点值放入这些列中？

感谢您的帮助

Answer 1

要将多个节点作为逗号分隔值，您可以使用 for xml path('') 的变体把戏。使用切碎的 XML (T.N) 作为子查询中的源来获取您感兴趣的节点。 xQuery ... substring(text()[1]) ...部分只是为了删除额外的逗号并从 for xml 创建的 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)