SQL-FOR XML-将几乎相同的元素彼此相邻放置

debugcn 发表于 Dev

dns_nx

我有一个存储过程，可以XML根据UBL-TR-2.1标准生成文档。我刚刚学习了如何向schemeID="VKN"查询中的元素添加属性（即）。现在我有一个不同的问题：

该UBL-TR-2.1标准定义了把3次cac:PartyIdentification用cbc:ID，但不同的schemeID属性，你可以在这里看到：

...
<cac:PartyIdentification>
   <cbc:ID schemeID="VKN">1190538652</cbc:ID>
</cac:PartyIdentification>
<cac:PartyIdentification>
   <cbc:ID schemeID="TICARETSICILNO">622171</cbc:ID>
</cac:PartyIdentification>
<cac:PartyIdentification>
   <cbc:ID schemeID="MERSISNO">0119053865200011</cbc:ID>
</cac:PartyIdentification>
...

因此，我在sql查询中尝试了此操作（我仅发布查询的相关部分）：

SELECT
    @XMLData = xmldat.xmldataCol 
FROM
(
    SELECT
        (

        SELECT
            ....
            'VKN'               as 'cac:AccountingSupplierParty/cac:Party/cac:PartyIdentification/cbc:ID/@schemeID',
            v2.TAXNRM           as 'cac:AccountingSupplierParty/cac:Party/cac:PartyIdentification/cbc:ID',
            'TICARETSICILNO'    as 'cac:AccountingSupplierParty/cac:Party/cac:PartyIdentification/cbc:ID/@schemeID',
            '622171'            as 'cac:AccountingSupplierParty/cac:Party/cac:PartyIdentification/cbc:ID',
            'MERSISNO'          as 'cac:AccountingSupplierParty/cac:Party/cac:PartyIdentification/cbc:ID/@schemeID',
            '0119053865200011'  as 'cac:AccountingSupplierParty/cac:Party/cac:PartyIdentification/cbc:ID',
            ...
        FROM 
            vorgang2 (nolock) v2
        FOR XML PATH('') , ROOT('Invoice') 
    ) as xmldataCol
) as xmldat

但这会引发错误（这里是德语的原始消息）：

消息6852，级别16，状态1，过程sp_RTIR_TR_Export_to_XML，行85 [批处理开始第7行]以属性为中心的cac：AccountingSupplierParty / cac：Party / cac：PartnerIdentification / cbc：ID / @ schemeID列可能位于FOR的XML层次结构中XML PATH不遵循以非属性为中心的同级。

用英语是这样的：

消息6852，级别16，状态1，过程sp_RTIR_TR_Export_to_XML，行85 [批处理开始第7行] FOR XML中XML层次结构中的属性信任的cac：AccountingSupplierParty / cac：Party / cac：PartyIdentification / cbc：ID / @ schemeID列PATH不得跟随不受属性信任的父母元素。

因此，问题似乎在于，这些元素几乎相同。并且在sql将它们彼此相邻放置时遇到了麻烦。有什么解决办法吗？提前谢谢了！

更新

null在两者之间添加的解决方案无法正常工作，因为我在以下位置得到了此结果xml：

<cac:AccountingSupplierParty>
   <cac:Party>
      <cac:PartyIdentification>
         <cbc:ID schemeID="VKN"/>
      </cac:PartyIdentification>
   </cac:Party>
</cac:AccountingSupplierParty>
<cac:AccountingSupplierParty>
   <cac:Party>
      <cac:PartyIdentification>
         <cbc:ID schemeID="TICARETSICILNO">622171</cbc:ID>
      </cac:PartyIdentification>
   </cac:Party>
</cac:AccountingSupplierParty>
<cac:AccountingSupplierParty>
   <cac:Party>
      <cac:PartyIdentification>
         <cbc:ID schemeID="MERSISNO">0119053865200011</cbc:ID>
      </cac:PartyIdentification>
   </cac:Party>
</cac:AccountingSupplierParty>

元素<cac:AccountingSupplierParty>和<cac:Party>不应重复。结构应如下所示：

<cac:AccountingSupplierParty>
   <cac:Party>
      ...
      <cac:PartyIdentification>
         <cbc:ID schemeID="VKN">1190538652</cbc:ID>
      </cac:PartyIdentification>
      <cac:PartyIdentification>
         <cbc:ID schemeID="TICARETSICILNO">622171</cbc:ID>
      </cac:PartyIdentification>
      <cac:PartyIdentification>
         <cbc:ID schemeID="MERSISNO">0119053865200011</cbc:ID>
      </cac:PartyIdentification>
      ...
   </cac:Party>
</cac:AccountingSupplierParty>

骄傲的埃德

您需要使用空列名将其分开。确定其无限制的行。

        SELECT      
            (SELECT            
                'VKN'               as 'cac:PartyIdentification/cbc:ID/@schemeID',
                 v3.TAXNRM        as 'cac:PartyIdentification/cbc:ID',
                null,
                'TICARETSICILNO'    as 'cac:PartyIdentification/cbc:ID/@schemeID',
                '622171'            as 'cac:PartyIdentification/cbc:ID',
                null,
                'MERSISNO'          as 'cac:PartyIdentification/cbc:ID/@schemeID',
                '0119053865200011'  as 'cac:PartyIdentification/cbc:ID'

             FROM 
             vorgang2(nolock) v3 
             WHERE v3.pk = v2.pk FOR XML path('cac:Party')
                    ,root('cac:AccountingSupplierParty')
                    ,type)

        FROM 
            vorgang2(nolock) v2

        FOR XML PATH('') , ROOT('Invoice')

输出：