如何为SQL FOR XML查询添加命名空间、指定外层根节点及XML声明

现有查询及输出 #

原始SQL查询：

SELECT DISTINCT
    t.Code AS Prime,
    t.nom AS Alfa,
    t.Nom AS Name,
    t.Adresse AS Street,
    t.CP AS ZipCode,
    t.Localite AS City, 
    CASE t.CodePays 
        WHEN NULL THEN 'BE' 
        WHEN '' THEN 'BE' 
        ELSE t.CodePays 
    END AS Country,
    CASE t.CodeLangue 
        WHEN 'NL' THEN 1 
        WHEN 'FR' THEN 2 
        WHEN 'EN' THEN 3 
        WHEN 'DE' THEN 4 
        ELSE 1 
    END AS Language,
    'EUR' AS CurrencyCode,
    CASE t.Tva WHEN '' THEN 0 ELSE 1 END AS VATCode,
    CASE t.Tva WHEN '' THEN 0 ELSE 1 END AS VATStatus,
    t.Tva AS VATNumber,
    CASE t.CodePays 
        WHEN NULL THEN 'BE' 
        WHEN '' THEN 'BE' 
        ELSE t.CodePays 
    END AS CountryVATNumber,
    0 AS Status /* 0=pas importé*/ 
FROM 
    tiers t 
INNER JOIN 
    tiersexport te ON t.code = te.code 
WHERE 
    t.CodeTypeTiers IN (1, 3)
ORDER BY 
    t.Code 
FOR XML PATH('Customer'), ROOT('Customers');

当前生成的XML：

<Customers>
  <Customer>
  ...
  </Customer>
</Customers>

期望的XML格式 #

需要生成包含外层<ImportExpMPlus>节点、指定命名空间和XML声明的结构：

<?xml version="1.0" encoding="ISO-8859-1"?>
<ImportExpMPlus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <Customers>
        <Customer>
        ...
        </Customer>
    </Customers>
</ImportExpMPlus>

尝试后的问题 #

添加WITH XMLNAMESPACES后，命名空间被绑定到<Customers>节点，而非目标的<ImportExpMPlus>节点，且缺少外层节点和XML声明：

<Customers xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <Customer>
      ...
    </Customer>
</Customers>

解决方案 #

通过嵌套子查询保留内部XML结构，在外层生成目标根节点，并正确绑定命名空间，同时处理XML声明。修改后的SQL如下：

基础实现（自动生成声明） #

WITH XMLNAMESPACES(
    'http://www.w3.org/2001/XMLSchema' AS xsd,
    'http://www.w3.org/2001/XMLSchema-instance' AS xsi
)
SELECT 
    (
        SELECT DISTINCT
            t.Code AS Prime,
            t.nom AS Alfa,
            t.Nom AS Name,
            t.Adresse AS Street,
            t.CP AS ZipCode,
            t.Localite AS City, 
            -- 修正NULL判断逻辑，原写法无法匹配NULL值
            CASE 
                WHEN t.CodePays IS NULL OR t.CodePays = '' THEN 'BE' 
                ELSE t.CodePays 
            END AS Country,
            CASE t.CodeLangue 
                WHEN 'NL' THEN 1 
                WHEN 'FR' THEN 2 
                WHEN 'EN' THEN 3 
                WHEN 'DE' THEN 4 
                ELSE 1 
            END AS Language,
            'EUR' AS CurrencyCode,
            CASE WHEN t.Tva = '' THEN 0 ELSE 1 END AS VATCode,
            CASE WHEN t.Tva = '' THEN 0 ELSE 1 END AS VATStatus,
            t.Tva AS VATNumber,
            CASE 
                WHEN t.CodePays IS NULL OR t.CodePays = '' THEN 'BE' 
                ELSE t.CodePays 
            END AS CountryVATNumber,
            0 AS Status /* 0=pas importé*/ 
        FROM 
            tiers t 
        INNER JOIN 
            tiersexport te ON t.code = te.code 
        WHERE 
            t.CodeTypeTiers IN (1, 3)
        ORDER BY 
            t.Code 
        FOR XML PATH('Customer'), ROOT('Customers'), TYPE
    )
FOR XML PATH(''), ROOT('ImportExpMPlus'), XMLSCHEMA;

强制指定ISO-8859-1编码（手动拼接声明） #

如果需要严格输出ISO-8859-1编码的XML声明，可通过变量拼接实现：

DECLARE @xml XML;

WITH XMLNAMESPACES(
    'http://www.w3.org/2001/XMLSchema' AS xsd,
    'http://www.w3.org/2001/XMLSchema-instance' AS xsi
)
SET @xml = (
    SELECT 
        (
            SELECT DISTINCT
                t.Code AS Prime,
                t.nom AS Alfa,
                t.Nom AS Name,
                t.Adresse AS Street,
                t.CP AS ZipCode,
                t.Localite AS City, 
                CASE 
                    WHEN t.CodePays IS NULL OR t.CodePays = '' THEN 'BE' 
                    ELSE t.CodePays 
                END AS Country,
                CASE t.CodeLangue 
                    WHEN 'NL' THEN 1 
                    WHEN 'FR' THEN 2 
                    WHEN 'EN' THEN 3 
                    WHEN 'DE' THEN 4 
                    ELSE 1 
                END AS Language,
                'EUR' AS CurrencyCode,
                CASE WHEN t.Tva = '' THEN 0 ELSE 1 END AS VATCode,
                CASE WHEN t.Tva = '' THEN 0 ELSE 1 END AS VATStatus,
                t.Tva AS VATNumber,
                CASE 
                    WHEN t.CodePays IS NULL OR t.CodePays = '' THEN 'BE' 
                    ELSE t.CodePays 
                END AS CountryVATNumber,
                0 AS Status /* 0=pas importé*/ 
            FROM 
                tiers t 
            INNER JOIN 
                tiersexport te ON t.code = te.code 
            WHERE 
                t.CodeTypeTiers IN (1, 3)
            ORDER BY 
                t.Code 
            FOR XML PATH('Customer'), ROOT('Customers'), TYPE
        )
    FOR XML PATH(''), ROOT('ImportExpMPlus'), TYPE
);

-- 手动拼接指定编码的XML声明
SELECT '<?xml version="1.0" encoding="ISO-8859-1"?>' + CAST(@xml AS VARCHAR(MAX));

核心调整说明 #

• 嵌套子查询+TYPE关键字：将原查询包裹并添加TYPE，使子查询结果以XML类型返回，保留内部结构不被转义。
• 命名空间绑定：WITH XMLNAMESPACES的命名空间会自动绑定到最外层的<ImportExpMPlus>节点，符合需求。
• 外层XML构造：FOR XML PATH(''), ROOT('ImportExpMPlus')生成目标外层节点，并将内部<Customers>作为子元素。
• NULL判断修正：原CASE t.CodePays WHEN NULL写法无法匹配NULL值，改为WHEN t.CodePays IS NULL才能正确处理空值场景。

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