时间戳

首页 > 解决方案 > SQL - 如何从表中的 XML 字段中提取多个属性

sql - SQL - 如何从表中的 XML 字段中提取多个属性

问题描述

我正在尝试对一个表运行 SQL 查询，该表具有一个包含 XML 数据的字段，但该 XML 包含多个需要转换为一个字段的值。请注意，该字段是 XML 内容，但实际字段类型设置为 nvarchar(max)，而不是 xml。

编辑：版本是SQL Server 2014 Express Edition

我有一张这样的表：[有市场的客户列表]

在此处输入图像描述

我想在同一行中提取“marketCode”值（逗号分隔）：

|CompanyCode|CompanyName|MarketCode,MarketCode,MarketCode,etc.|Phone|

示例的预期输出（见屏幕截图）：

|ABC123|JOHN DEERE|AA,BB,CC,DD|555-123-000|
|DEF456|NEW HOLLLAND|AA,FF,GG,HH,KK|555-456-0000|

标签： sqlsql-serverxmltsqlpivot

解决方案

样本数据

下次请提供文字而不是图片；-)

列Markets定义为nvarchar(max)。数据以 unicode（带N前缀）的形式插入。

create table Company2
(
    Code nvarchar(6),
    Name nvarchar(11),
    Markets nvarchar(max),
    Phone nvarchar(12)
);

insert into Company2 (Code, Name, Markets, Phone) values
(N'ABC123',
 N'JOHN DEERE',
 N'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
  <license>
    <company companyCode="ABC123">
      <markets>
        <market marketCode="AA"/>
        <market marketCode="BB"/>
        <market marketCode="CC"/>
        <market marketCode="DD"/>
      </markets>
    </company>
  </license>',
 N'555-123-0000'),
(N'DEF456',
 N'NEW HOLLAND',
 N'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
  <license>
    <company companyCode="DEF456">
      <markets>
        <market marketCode="AA"/>
        <market marketCode="FF"/>
        <market marketCode="GG"/>
        <market marketCode="HH"/>
        <market marketCode="KK"/>
      </markets>
    </company>
  </license>',
 N'555-456-0000');

解决方案

无法Markets直接转换XML为，因为nvarchar(max)编码与数据中的“utf-8”冲突。我将转换转移到一个单独的公用表表达式 (CTE, cte_convert) 中，从nvarchar(max)tovarchar(max)到XML。

现在可以使用下一个 CTE( cte_parse)将 XMLc.MarketsXML.nodes()中的节点提取<market>到新列m.Market中。从该列@marketCode中提取属性作为所需值。

然后使用子查询for xml path('')来连接这些值。

with cte_convert as
(
    select c.Code, c.Name, convert(XML, convert(varchar(max), c.Markets)) as MarketsXML, c.Phone
    from Company2 c
),
cte_parse as
(
    select c.Code, c.Name, m.Market.value('@marketCode', 'nvarchar(10)') as MarketCode, c.Phone
    from cte_convert c
    outer apply c.MarketsXML.nodes('/license/company/markets/market') as m(Market)
)
select  cp.Code,
        cp.Name,
        stuff(( select ',' + cp2.MarketCode as MC
                from cte_parse cp2
                where cp2.Code = cp.Code
                for xml path(''), type).value('.', 'nvarchar(max)'),1,1,'') as MarketCodes,
        cp.Phone
from cte_parse cp
group by cp.Code, cp.Name, cp.Phone;

结果

Code   Name        MarketCodes    Phone
------ ----------- -------------- ------------
ABC123 JOHN DEERE  AA,BB,CC,DD    555-123-0000
DEF456 NEW HOLLAND AA,FF,GG,HH,KK 555-456-0000

此原始解决方案使用从 SQL Server 2017 开始可用的string_agg()函数。

样本数据

备注：该列Markets被定义为XML反映其内容。

create table Company
(
    Code nvarchar(6),
    Name nvarchar(11),
    Markets XML,
    Phone nvarchar(12)
);

insert into Company (Code, Name, Markets, Phone) values
('ABC123',
 'JOHN DEERE',
 '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
  <license>
    <company companyCode="ABC123">
      <markets>
        <market marketCode="AA"/>
        <market marketCode="BB"/>
        <market marketCode="CC"/>
        <market marketCode="DD"/>
      </markets>
    </company>
  </license>',
  '555-123-0000'),
('DEF456',
 'NEW HOLLAND',
 '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
  <license>
    <company companyCode="DEF456">
      <markets>
        <market marketCode="AA"/>
        <market marketCode="FF"/>
        <market marketCode="GG"/>
        <market marketCode="HH"/>
        <market marketCode="KK"/>
      </markets>
    </company>
  </license>',
  '555-456-0000');

解决方案

with cte_parse as
(
    select c.Code, c.Name, m.Market.value('@marketCode', 'nvarchar(10)') as MarketCode, c.Phone
    from Company c
    outer apply c.Markets.nodes('/license/company/markets/market') as m(Market)
)
select cp.Code, cp.Name, string_agg(cp.MarketCode, ',') as MarketCodes, cp.Phone
from cte_parse cp
group by cp.Code, cp.Name, cp.Phone;

小提琴

推荐阅读