SQL生成XML时移除命名空间及添加元素属性问题求助

1. 移除节点多余命名空间 #

原因：你代码中定义的WITH XMLNAMESPACES ('?xml version="1.0" encoding="UTF-8"?' as ext)属于错误用法：XML版本/编码声明不属于命名空间范畴，且你整个查询逻辑没有用到ext前缀，该命名空间定义完全冗余。
解决方法：直接删除WITH XMLNAMESPACES这一行代码即可，节点的xmlns:ext多余属性会自动消失。
如果确实需要输出XML声明，可在最终将XML结果转为字符串类型时，在头部手动拼接<?xml version="1.0" encoding="UTF-8"?>即可，不需要在XML数据结构中定义。

2. 为元素添加type属性 #

SQL Server的FOR XML PATH语法支持通过@前缀的别名输出节点属性，你只需要调整InvoiceLines子查询中tax-amount的字段定义即可：
将原代码中

[TaxAmount] as [tax-amount],

修改为

'decimal' AS 'tax-amount/@type',
[TaxAmount] AS 'tax-amount',

注意：属性定义必须放在对应节点值的定义之前，且两者的节点路径完全一致，才能正确生成<tax-amount type="decimal">xxx</tax-amount>格式的节点。

修改后完整代码示例 #

DECLARE @ID_Rechnung int = 1978,
    @XMLData xml;

-- 已删除多余的WITH XMLNAMESPACES定义
SELECT
    @XMLData = xmldat.xmldataCol 
FROM
(
SELECT (
       SELECT
            -- HIER XML Daten generieren
            [InvoiceHeader].[InvoiceDate]            AS 'invoice-date',
            ([InvoiceHeader].[InvoiceNumber])                           AS 'invoice-number',
       cast(replace([InvoiceHeader].[GrossValue],' ','') as decimal(18,2))                           AS 'gross-total',
        cast(replace([InvoiceHeader].[NetValue],' ','') as decimal(18,2))                           AS 'amount-due',
        [InvoiceHeader].[VatRate]                           AS 'tax-rate',
        -- 如果头部的tax-amount也需要加属性，也按同样规则修改
        cast(replace([InvoiceHeader].[VatValue],' ','') as decimal(18,2))                           AS 'tax-amount',
        [ImagePath] AS 'image-scan-url',
        [InvoiceType] AS 'document-type',
        [LegalEntityVATNo] AS  'account-type/id',
        [LegalEntityName] AS  'account-type/name',
        [SupplierCode] as 'supplier/number',
        [Currency] as 'currency/code',   

    (
        SELECT rtrim([InvoiceLines].[LineNumber]) AS [order-line-num]
            , [PONumber] as [po-number],
            CAST([InvoiceLines].[UnitPrice] AS decimal(18,2)) AS Price ,
            [Quantity] as quantity,
            -- 新增type属性定义
            'decimal' AS 'tax-amount/@type',
            [TaxAmount] as [tax-amount],
            [LineTotal] as [total],
            [Decsription] as description


        FROM [InvoiceLines] WHERE [InvoiceLines].[DOCID] = @id_Rechnung
        FOR XML PATH('Invoice-line'), ROOT('invoice-lines'), TYPE
    )
FROM [InvoiceHeader] 
WHERE [InvoiceHeader].[DOCID]  = @ID_Rechnung
FOR XML PATH(''), TYPE, ROOT('invoice-header')

) AS xmldataCol
) AS xmldat;

SELECT  @XMLData
.query('<invoice-header>

{
   for $x in /invoice-header/*[local-name()!="root"]
   return $x,
     for $x in /invoice-header/root/r
     return <invoice-lines>/<invoice-line>{$x/*}</invoice-line></invoice-lines>
}
</invoice-header>');

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