如何将XML中Attribute_ID设为列、Attribute_Value设为值填充至表

需求说明 #

将XML文档中的Attribute_ID作为表的列名，Attribute_Value作为对应列的值填充到表中，同时解析XML时需去除重复数据。

提供的XML文档 #

<ExperianBureauData>
  <NetConnectResponse>
    <CompletionCode>0000</CompletionCode>
    <Products>
      <CreditProfile>
        <ARF_Report> 
          <n836_-_Premier_Attributes>
            <Record_ID>836</Record_ID>
            <Record_Length>314</Record_Length>
            <Message_Code>1</Message_Code>
            <Attribute>
              <Attribute_ID>ALL0135</Attribute_ID>
              <Attribute_Value>000000002</Attribute_Value>
            </Attribute>
            <Attribute>
              <Attribute_ID>ALL2306</Attribute_ID>
              <Attribute_Value>000000000</Attribute_Value>
            </Attribute>
            <Attribute>
              <Attribute_ID>ALL2336</Attribute_ID>
              <Attribute_Value>000000000</Attribute_Value>
            </Attribute>
            <Attribute>
              <Attribute_ID>ALL5742</Attribute_ID>
              <Attribute_Value>000000000</Attribute_Value>
            </Attribute>
            <Attribute>
              <Attribute_ID>ALL5935</Attribute_ID>
              <Attribute_Value>000000000</Attribute_Value>
            </Attribute>
            <Attribute>
              <Attribute_ID>ALL8220</Attribute_ID>
              <Attribute_Value>000000044</Attribute_Value>
            </Attribute>            
          </n836_-_Premier_Attributes>         
        </ARF_Report>
      </CreditProfile>
    </Products>
  </NetConnectResponse>
</ExperianBureauData>

尝试过的SQL语句（未达到预期） #

SELECT DISTINCT       
    -- -- -- -- <<n836_-_Premier_Attributes>/<Attribute>/
    -- -- -- -- Tbl7
    ,Tbl5.value('Attribute_Value[1]','VARCHAR(20)') AS ALL0135 
    ,Tbl5.value('Attribute_Value[1]','VARCHAR(20)') AS ALL2306 
    ,Tbl5.value('Attribute_Value[1]','VARCHAR(20)') AS ALL2336 
    ,Tbl5.value('Attribute_Value[1]','VARCHAR(20)') AS ALL5742  

FROM [dbo].[STG_XML_TstTbl]
CROSS APPLY [XMLReport].nodes('/ExperianBureauData/NetConnectResponse/Products/CreditProfile/ARF_Report/n836_-_Premier_Attributes/Attribute') AS Tbl5(Tbl5);

正确实现方案 #

问题分析 #

原SQL的核心问题：通过CROSS APPLY拆分每个Attribute节点后，所有列都取当前节点的Attribute_Value，导致每一行只有一个列值正确，其余列重复当前节点的值；同时未完成行转列逻辑，也没有实现有效去重。

方案1：静态列实现（Attribute_ID固定） #

如果已知所有需要转换的Attribute_ID，可以用PIVOT完成行转列，同时通过CTE先做去重处理：

WITH AttributeCTE AS (
    SELECT DISTINCT
        -- 关联上级Record_ID，确保每个报告的属性独立
        attr.parent.value('(../Record_ID)[1]', 'INT') AS Record_ID,
        attr.value('(Attribute_ID)[1]', 'VARCHAR(20)') AS Attribute_ID,
        attr.value('(Attribute_Value)[1]', 'VARCHAR(20)') AS Attribute_Value
    FROM [dbo].[STG_XML_TstTbl]
    CROSS APPLY [XMLReport].nodes('/ExperianBureauData/NetConnectResponse/Products/CreditProfile/ARF_Report/n836_-_Premier_Attributes/Attribute') AS attr(parent)
)
SELECT 
    Record_ID,
    [ALL0135], [ALL2306], [ALL2336], [ALL5742], [ALL5935], [ALL8220]
FROM AttributeCTE
PIVOT (
    MAX(Attribute_Value)
    FOR Attribute_ID IN ([ALL0135], [ALL2306], [ALL2336], [ALL5742], [ALL5935], [ALL8220])
) AS PivotTable;

说明

• CTE中的DISTINCT确保每个(Record_ID, Attribute_ID)组合仅保留一条记录，实现去重。
• PIVOT将行格式的属性数据转为列格式，MAX函数用于确保每个列仅取唯一值（因已去重，不影响最终结果）。

方案2：动态列实现（Attribute_ID不固定） #

如果XML中的Attribute_ID不固定，需要动态生成列名适配所有属性：

DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);

-- 提取所有唯一的Attribute_ID，拼接成带引号的列名列表
SELECT @cols = STRING_AGG(QUOTENAME(Attribute_ID), ', ')
FROM (
    SELECT DISTINCT
        attr.value('(Attribute_ID)[1]', 'VARCHAR(20)') AS Attribute_ID
    FROM [dbo].[STG_XML_TstTbl]
    CROSS APPLY [XMLReport].nodes('/ExperianBureauData/NetConnectResponse/Products/CreditProfile/ARF_Report/n836_-_Premier_Attributes/Attribute') AS attr(parent)
) AS UniqueAttributes;

-- 构建动态PIVOT查询语句
SET @query = N'
WITH AttributeCTE AS (
    SELECT DISTINCT
        attr.parent.value(''(../Record_ID)[1]'', ''INT'') AS Record_ID,
        attr.value(''(Attribute_ID)[1]'', ''VARCHAR(20)'') AS Attribute_ID,
        attr.value(''(Attribute_Value)[1]'', ''VARCHAR(20)'') AS Attribute_Value
    FROM [dbo].[STG_XML_TstTbl]
    CROSS APPLY [XMLReport].nodes(''/ExperianBureauData/NetConnectResponse/Products/CreditProfile/ARF_Report/n836_-_Premier_Attributes/Attribute'') AS attr(parent)
)
SELECT Record_ID, ' + @cols + N'
FROM AttributeCTE
PIVOT (
    MAX(Attribute_Value)
    FOR Attribute_ID IN (' + @cols + N')
) AS PivotTable;';

-- 执行动态SQL
EXEC sp_executesql @query;

说明

• 先通过子查询获取所有唯一的Attribute_ID，用STRING_AGG拼接成符合PIVOT要求的列名格式。
• 动态生成SQL语句并执行，自动适配XML中所有存在的Attribute_ID，无需手动维护列名。

内容的提问来源于stack exchange，提问作者Ibru.M