如何用T-SQL生成带XML声明与inventory根节点的指定XML输出文件？

当然可以实现！你目前的脚本已经搞定了product节点，只需要扩展一下FOR XML的结构，把inventory根节点纳入进来，再处理XML声明的问题就行。我给你两种方案，一种基于你现用的EXPLICIT模式，另一种是更简洁的PATH模式，按需选择：

方案一：基于FOR XML EXPLICIT实现 #

首先调整脚本，通过UNION ALL添加根节点的定义，让product节点成为它的子节点：

SELECT 
    -- 定义inventory根节点（Tag=1，父节点为0即顶级节点）
    1 AS [Tag],
    0 AS [Parent],
    'TEST' AS [inventory!1!company_acro_name],
    '' AS [inventory!1!company_mail_to],
    'N' AS [inventory!1!hold_on_error],
    -- product节点的字段先设为NULL占位
    NULL AS [product!2!recordtype],
    NULL AS [product!2!quantity],
    NULL AS [product!2!supplierassortmentid],
    NULL AS [product!2!date]
UNION ALL
SELECT 
    -- 定义product节点（Tag=2，父节点是inventory的Tag=1）
    2 AS [Tag],
    1 AS [Parent],
    -- 根节点的字段设为NULL
    NULL, NULL, NULL,
    'INVENTORY' AS [product!2!recordtype],
    CAST([Available_Quantity] AS INT) AS [product!2!quantity],
    [Product] + '_' + [Matrix_Code_1] + '-' + [Matrix_Code_2] AS [product!2!supplierassortmentid],
    CONVERT(SMALLDATETIME, CURRENT_TIMESTAMP) AS [product!2!date]
FROM [dbo].[TStock] 
WHERE Company = 4
FOR XML EXPLICIT

这个脚本会生成带属性的inventory根节点，所有product节点都会嵌套在它里面。

接下来添加XML声明：SQL Server默认不会输出XML声明，我们可以通过拼接字符串的方式实现，同时用TYPE参数避免转义问题：

SET NOCOUNT ON;

SELECT CAST('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' AS NVARCHAR(MAX)) +
       CAST((
           -- 嵌入上面的完整EXPLICIT查询
           SELECT 
               1 AS [Tag],
               0 AS [Parent],
               'TEST' AS [inventory!1!company_acro_name],
               '' AS [inventory!1!company_mail_to],
               'N' AS [inventory!1!hold_on_error],
               NULL AS [product!2!recordtype],
               NULL AS [product!2!quantity],
               NULL AS [product!2!supplierassortmentid],
               NULL AS [product!2!date]
           UNION ALL
           SELECT 
               2 AS [Tag],
               1 AS [Parent],
               NULL, NULL, NULL,
               'INVENTORY' AS [product!2!recordtype],
               CAST([Available_Quantity] AS INT) AS [product!2!quantity],
               [Product] + '_' + [Matrix_Code_1] + '-' + [Matrix_Code_2] AS [product!2!supplierassortmentid],
               CONVERT(SMALLDATETIME, CURRENT_TIMESTAMP) AS [product!2!date]
           FROM [dbo].[TStock] 
           WHERE Company = 4
           FOR XML EXPLICIT, TYPE
       ) AS NVARCHAR(MAX)) AS FullXML;

方案二：用FOR XML PATH实现（更简洁） #

如果你愿意换一种更直观的写法，FOR XML PATH会更容易维护，代码也更简洁：

SET NOCOUNT ON;

SELECT CAST('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' AS NVARCHAR(MAX)) +
       CAST((
           SELECT 
               'TEST' AS '@company_acro_name',
               '' AS '@company_mail_to',
               'N' AS '@hold_on_error',
               -- 嵌套product节点
               (
                   SELECT 
                       'INVENTORY' AS '@recordtype',
                       CAST([Available_Quantity] AS INT) AS '@quantity',
                       [Product] + '_' + [Matrix_Code_1] + '-' + [Matrix_Code_2] AS '@supplierassortmentid',
                       CONVERT(SMALLDATETIME, CURRENT_TIMESTAMP) AS '@date'
                   FROM [dbo].[TStock] 
                   WHERE Company = 4
                   FOR XML PATH('product'), TYPE
               )
           FOR XML PATH('inventory'), TYPE
       ) AS NVARCHAR(MAX)) AS FullXML;

这个脚本同样能生成你需要的完整XML结构，包括声明和带属性的根节点。

注意事项 #

• 用TYPE参数让子查询返回XML类型，这样拼接时不会出现不必要的转义字符。
• SET NOCOUNT ON是为了避免查询结果中出现影响XML结构的行数信息。

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