Search code examples
sqlsql-serverxmlfor-xml

SQL - FOR XML - Placing nearly same elements next to each other

I have the a stored procedure to generate an XML document based on the UBL-TR-2.1 standard. I just learned how to add attributes (i.e. schemeID="VKN") to an element inside my query. Now I have a different issue:

The UBL-TR-2.1 standard defines to put 3 times cac:PartyIdentification with cbc:ID, but different schemeID attributes as you can see here:

...
<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>
...

So I tried this in my sql query (I post only the relevant parts of the query):

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

But this throws an error (here orignal message in german):

Msg 6852, Level 16, State 1, Procedure sp_RTIR_TR_Export_to_XML, Line 85 [Batch Start Line 7] Die attributzentrierte cac:AccountingSupplierParty/cac:Party/cac:PartyIdentification/cbc:ID/@schemeID-Spalte darf in der XML-Hierarchie in FOR XML PATH nicht auf ein nicht attributzentriertes gleichgeordnetes Element folgen.

In english it is something like this:

Msg 6852, Level 16, State 1, Procedure sp_RTIR_TR_Export_to_XML, Line 85 [Batch Start Line 7] The attribute-trusted cac:AccountingSupplierParty/cac:Party/cac:PartyIdentification/cbc:ID/@schemeID column in the XML hierarchy in FOR XML PATH must not follow a non-attribute-trusted parental element.

So the issue seems to be, that the elements are nearly the same. And here got sql trouble with placing them next to each other. Is there any solution to this problem? Many thanks in advance!

UPDATE

The solution with adding null in between, does not work properly, because I get this results in the 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>

The elements <cac:AccountingSupplierParty> and <cac:Party> should not be repeated. The structure should be like this:

<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>
Solution

  • you need to separate it with empty column name. to determine its unbounded rows.

            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')

    output:

    enter image description here