EDIT 2:我终于找到了避免重复命名空间的方法.首先创建没有命名空间的嵌套 XML，然后加入它:

更新(2017 年 12 月)

这种解决方法实际上仍然没有帮助.命名空间 xmlns="" 被视为 * 中的所有内容不在命名空间内... 您可以将结果转换为 NVARCHAR(MAX) 并使用 REPLACE 去掉 xmlns="".然后您可以将字符串重新转换为 XML.微软的耻辱，10(！！)岁的问题(见下面的链接)仍未解决.请去那里投票！

EDIT 2: I finally found a way to avoid repeated namespaces. First you create the nested XML without the namespace, then you join it:

UPDATE (Dec 2017)

This workaround is still not really helpfull actually. The namespaces xmlns="" are taken as *everything inside is not within a namespace... You might convert the result to NVARCHAR(MAX) and use REPLACE to get rid of xmlns="". Then you can re-convert the string to XML. Shame on Microsoft, that the 10(!!) years old issue (see link below) is still unsolved. Please go there and vote!

DECLARE @lim_Live_Inbound TABLE(lim_msg_id VARCHAR(100),lim_request_transaction_id VARCHAR(100),client_name VARCHAR(100));
INSERT INTO @lim_Live_Inbound VALUES
 ('021/00210006/20160225/000002','00012016-02-25000000023','Mr Piet')
,('021/00210006/20160225/000002','00012016-02-25000000022','Mrs Name');

DECLARE @nestedXMLs TABLE(MsgId VARCHAR(100),nestedXML XML);

WITH GrpMsg AS
(
    SELECT DISTINCT lim_msg_id AS MsgId
    FROM @lim_Live_Inbound
)
INSERT INTO @nestedXMLs 
SELECT MsgId 
     ,(
        SELECT innerTbl.lim_request_transaction_id AS OrgnlMsgId
              ,innerTbl.client_name AS name
        FROM @lim_Live_Inbound AS innerTbl
        WHERE innerTbl.lim_msg_id=GrpMsg.MsgId
        FOR XML PATH('OrgnlGrpInfAndSts'),TYPE
      ) 
FROM GrpMsg;

WITH XMLNAMESPACES(DEFAULT 'urn:iso:std:iso:20022:tech:xsd:001.002.001.04')
,GrpMsg AS
(
    SELECT DISTINCT lim_msg_id AS MsgId
    FROM @lim_Live_Inbound
)
SELECT GrpMsg.MsgId AS [GrpHdr/MsgId]
      ,n.nestedXML AS [node()]
FROM GrpMsg
INNER JOIN @nestedXMLs AS n ON GrpMsg.MsgId=n.MsgId
FOR XML PATH('FIToFIPmtStsRp'),ROOT('Document')

结果

<Document xmlns="urn:iso:std:iso:20022:tech:xsd:001.002.001.04">
  <FIToFIPmtStsRp>
    <GrpHdr>
      <MsgId>021/00210006/20160225/000002</MsgId>
    </GrpHdr>
    <OrgnlGrpInfAndSts xmlns="">
      <OrgnlMsgId>00012016-02-25000000023</OrgnlMsgId>
      <name>Mr Piet</name>
    </OrgnlGrpInfAndSts>
    <OrgnlGrpInfAndSts xmlns="">
      <OrgnlMsgId>00012016-02-25000000022</OrgnlMsgId>
      <name>Mrs Name</name>
    </OrgnlGrpInfAndSts>
  </FIToFIPmtStsRp>
</Document>

您将使用 CAST(REPLACE(CAST(TheXMLHere AS NVARCHAR(MAX)),' xmlns=""','') AS XML) 去除错误的空命名空间....

You'd use CAST(REPLACE(CAST(TheXMLHere AS NVARCHAR(MAX)),' xmlns=""','') AS XML) to get rid of the wrong empty namespaces....

这是重复命名空间 - 但这在语法上是正确的，但很烦人(阅读此处:https://connect.microsoft.com/SQLServer/feedback/details/265956/suppress-namespace-attributes-in-nested-select-for-xml 语句)

This is repeating the namespace - but this is syntactically correct, yet annoying (read here: https://connect.microsoft.com/SQLServer/feedback/details/265956/suppress-namespace-attributes-in-nested-select-for-xml-statements)

DECLARE @lim_Live_Inbound TABLE(lim_msg_id VARCHAR(100),lim_request_transaction_id VARCHAR(100),client_name VARCHAR(100));
INSERT INTO @lim_Live_Inbound VALUES
 ('021/00210006/20160225/000002','00012016-02-25000000023','Mr Piet')
,('021/00210006/20160225/000002','00012016-02-25000000022','Mrs Name');

WITH XMLNAMESPACES(DEFAULT 'urn:iso:std:iso:20022:tech:xsd:001.002.001.04')
,GrpMsg AS
(
    SELECT DISTINCT lim_msg_id AS MsgId
    FROM @lim_Live_Inbound
)
SELECT MsgId AS [GrpHdr/MsgId]
     ,(
        SELECT innerTbl.lim_request_transaction_id AS OrgnlMsgId
              ,innerTbl.client_name AS name
        FROM @lim_Live_Inbound AS innerTbl
        WHERE innerTbl.lim_msg_id=GrpMsg.MsgId
        FOR XML PATH('OrgnlGrpInfAndSts'),TYPE
      ) 
FROM GrpMsg
FOR XML PATH('FIToFIPmtStsRp'),ROOT('Document')

结果

<Document xmlns="urn:iso:std:iso:20022:tech:xsd:001.002.001.04">
  <FIToFIPmtStsRp>
    <GrpHdr>
      <MsgId>021/00210006/20160225/000002</MsgId>
    </GrpHdr>
    <OrgnlGrpInfAndSts xmlns="urn:iso:std:iso:20022:tech:xsd:001.002.001.04">
      <OrgnlMsgId>00012016-02-25000000023</OrgnlMsgId>
      <name>Mr Piet</name>
    </OrgnlGrpInfAndSts>
    <OrgnlGrpInfAndSts xmlns="urn:iso:std:iso:20022:tech:xsd:001.002.001.04">
      <OrgnlMsgId>00012016-02-25000000022</OrgnlMsgId>
      <name>Mrs Name</name>
    </OrgnlGrpInfAndSts>
  </FIToFIPmtStsRp>
</Document>

这是第一种方法

我不知道您的数据来自哪里，但是 - 绝对是硬编码 - 这就是方法:

This is the first approach

I don't know where your data comes from, but - absolutely hard coded - this was the approach:

WITH XMLNAMESPACES(DEFAULT 'urn:iso:std:iso:20022:tech:xsd:001.002.001.04')
SELECT 0 AS [GrpHdr/MsgId]
      ,0 AS [OrgnlGrpInfAndSts/OrgnlMsgId]
FOR XML PATH('FIToFIPmtStsRp'),ROOT('Document')

结果

<Document xmlns="urn:iso:std:iso:20022:tech:xsd:001.002.001.04">
  <FIToFIPmtStsRp>
    <GrpHdr>
      <MsgId>0</MsgId>
    </GrpHdr>
    <OrgnlGrpInfAndSts>
      <OrgnlMsgId>0</OrgnlMsgId>
    </OrgnlGrpInfAndSts>
  </FIToFIPmtStsRp>
</Document>