SQL Server 2019：多表生成XML节点及添加字段类型节点问题

需求说明 #

需将多表的列名作为XML节点的<Name>值，对应字段值作为<Value>，同时为每个节点添加列的原始类型<Type>节点；所有属性字段需整合到同一个<Attributes>节点下的<Attribute>子节点中。

示例XML结构 #

<Product>
    <ProductName>Product1</ProductName>
    <Attributes>
        <Attribute>
            <Name>Attr1</Name>
            <Value>True</Value>
            <Type>BOOL</Type>
        </Attribute>
        <Attribute>
            <Name>Attr2</Name>
            <Value>1.0000000000</Value>
            <Type>DECIMAL</Type>
        </Attribute>
        <Attribute>
            <Name>Weight</Name>
            <Value>155</Value>
            <Type>INT</Type>
        </Attribute>
    </Attributes>
</Product>

现有表结构及数据 #

CREATE TABLE PRODUCT(ProductID int, ProductName nvarchar(40))
INSERT INTO PRODUCT VALUES (1, 'Product1')

CREATE TABLE PRODUCT_WEIGHT(ProductID int, CurrentWeight int)
INSERT INTO PRODUCT_WEIGHT VALUES (1,155)

CREATE TABLE ATTRIBUTE_(ProductID int, Attr1 BIT, Attr2 decimal(28,10), Attr3 nvarchar(40), Attr4 int)
INSERT INTO ATTRIBUTE_ VALUES (1, 1, 1.0, NULL, NULL)

遇到的问题 #

• 使用(SELECT a.*,pw.* FOR XML RAW) AS XML)时，报错“The transaction has aborted”
• 单独查询PRODUCT_WEIGHT生成节点时，导致<Attributes>节点重复

解决方案 #

核心思路 #

通过UNION ALL将不同表的字段数据统一为Name-Value-Type的结构，再利用FOR XML PATH分层构建目标XML结构，确保所有属性归属于同一个<Attributes>节点。

实现代码 #

SELECT
    p.ProductName,
    (
        SELECT
            attr.Name,
            attr.Value,
            attr.Type
        FROM (
            -- 提取PRODUCT_WEIGHT的Weight属性
            SELECT
                'Weight' AS Name,
                CAST(pw.CurrentWeight AS NVARCHAR(MAX)) AS Value,
                'INT' AS Type
            FROM PRODUCT_WEIGHT pw
            WHERE pw.ProductID = p.ProductID

            UNION ALL

            -- 提取ATTRIBUTE_表的Attr1属性
            SELECT
                'Attr1' AS Name,
                CASE WHEN a.Attr1 = 1 THEN 'True' ELSE 'False' END AS Value,
                'BOOL' AS Type
            FROM ATTRIBUTE_ a
            WHERE a.ProductID = p.ProductID AND a.Attr1 IS NOT NULL

            UNION ALL

            -- 提取ATTRIBUTE_表的Attr2属性
            SELECT
                'Attr2' AS Name,
                CAST(a.Attr2 AS NVARCHAR(MAX)) AS Value,
                'DECIMAL' AS Type
            FROM ATTRIBUTE_ a
            WHERE a.ProductID = p.ProductID AND a.Attr2 IS NOT NULL

            UNION ALL

            -- 提取ATTRIBUTE_表的Attr3属性
            SELECT
                'Attr3' AS Name,
                a.Attr3 AS Value,
                'NVARCHAR' AS Type
            FROM ATTRIBUTE_ a
            WHERE a.ProductID = p.ProductID AND a.Attr3 IS NOT NULL

            UNION ALL

            -- 提取ATTRIBUTE_表的Attr4属性
            SELECT
                'Attr4' AS Name,
                CAST(a.Attr4 AS NVARCHAR(MAX)) AS Value,
                'INT' AS Type
            FROM ATTRIBUTE_ a
            WHERE a.ProductID = p.ProductID AND a.Attr4 IS NOT NULL
        ) attr
        FOR XML PATH('Attribute'), ROOT('Attributes'), TYPE
    )
FROM PRODUCT p
WHERE p.ProductID = 1
FOR XML PATH('Product'), TYPE

关键问题解决说明 #

1. 整合到同一<Attributes>节点：
   通过UNION ALL将PRODUCT_WEIGHT和ATTRIBUTE_的所有属性字段合并为一个统一的数据集，再用FOR XML PATH('Attribute'), ROOT('Attributes')生成单一的<Attributes>节点，避免了多次查询导致的节点重复问题。

2. 添加原始类型<Type>节点：

   • 已知表结构时，可直接手动指定字段类型（如示例中的BOOL、DECIMAL）；
   • 若需动态适配表结构变化，可通过查询INFORMATION_SCHEMA.COLUMNS获取字段类型，示例代码如下：

     SELECT
         COLUMN_NAME AS Name,
         DATA_TYPE AS Type
     FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_NAME = 'ATTRIBUTE_' AND COLUMN_NAME IN ('Attr1','Attr2','Attr3','Attr4')
     

     将该查询与业务数据关联，即可动态生成<Type>节点的值。

3. 解决原查询报错问题：
   原SELECT a.*,pw.* FOR XML RAW的方式因多表直接拼接易产生数据重复或事务超时，改用UNION ALL拆分属性字段后，逻辑更清晰，避免了此类问题。

内容的提问来源于stack exchange，提问作者user76864978