I have the a stored procedure to generate an XML document based on the UBL-TR-2.1 standard. This is the query where the xml data is generated: A sample output of this query is this XML file: This works so far so good. But now some XML elements shall have attribute in the element itself. One example is: How can I

SQL – FOR XML – How to add property to tag

I have the a stored procedure to generate an XML document based on the UBL-TR-2.1 standard. This is the query where the xml data is generated:

WITH XMLNAMESPACES (
                                'urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2' as ext,
                                'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' as cbc,
                                'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' as cac,
                                'http://uri.etsi.org/01903/v1.3.2#' as xades,
                                'http://www.w3.org/2001/XMLSchema-instance' as xsi,
                                'http://www.w3.org/2000/09/xmldsig#' as ds
                           )    
SELECT
    @XMLData = xmldat.xmldataCol 
FROM
(
    SELECT
        (

        SELECT
            -- HIER XML Daten generieren
            ''                                          as 'ext:UBLExtensions',
            ''                                          as 'ext:UBLExtensions/ext:UBLExtension',
            ''                                          as 'ext:UBLExtensions/ext:UBLExtension/ext:ExtensionContent',
            '2.1'                                       as 'cbc:UBLVersionID',
            'TR1.2'                                     as 'cbc:CustomizationID',
            ''                                          as 'cbc:ProfileID',
            v2.nummer                                   as 'cbc:ID',
            'false'                                     as 'cbc:CopyIndicator',
            cast(@TransGUID as nvarchar(256))           as 'cbc:UUID',
            v2.datum                                    as 'cbc:IssueDate',
            cast(cast(v2._TSTAMP as datetime) as time)  as 'cbc:IssueTime',
            ''                                          as 'cbc:InvoiceTypeCode',
            v2.FUSSTEXT                                 as 'cbc:Note',
            (
                select
                    count(*)
                from
                    vorgpos2 vp2 (nolock)
                where
                    vp2.vorgang2 = v2._kenn
            )                                           as 'cbc:LineCountNumeric',
            ''                                          as 'cac:AccountingSupplierParty/cac:Party/cbc:WebsiteURI',
        FROM 
            vorgang2 (nolock) v2
        inner join 
            SYSLAND (nolock) sl on v2.SYSLAND = sl._kenn
        inner join 
            (select nummer, [name2] from vorlauf (nolock) where art = 4) as sl_land on sl.LAND = sl_land.NUMMER 
        inner join 
            fibupar (nolock) fm on sl.fibupar = fm._kenn 
        inner join 
            (select nummer, [name2] from vorlauf (nolock) where art = 4) as k_land on v2.LAND = k_land.NUMMER 
        left outer join 
            FibuMBnk (nolock) fm_bank on fm._kenn = fm_bank.FIBUMAND and v2.WAEHRUNG = fm_bank.WAEHRUNG
        left outer join 
            (select nummer, [name] from vorlauf (nolock) where vorlauf.art = 22) as waehr on v2.WAEHRUNG = waehr.NUMMER 
        inner join 
            kunden k (nolock) on v2.[partner] = k.[_kenn]
        WHERE 
            v2._kenn = @KENN
        FOR XML PATH('') , ROOT('Invoice') 
    ) as xmldataCol
) as xmldat

A sample output of this query is this XML file:

<?xml version="1.0" encoding="UTF-8"?>
<Invoice xmlns:ext="urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2" xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2" xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2" xmlns:xades="http://uri.etsi.org/01903/v1.3.2#" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xsi:schemaLocation="urn:oasis:names:specification:ubl:schema:xsd:Invoice-2 ../xsdrt/maindoc/UBL-Invoice-2.1.xsd" xmlns="urn:oasis:names:specification:ubl:schema:xsd:Invoice-2">
<ext:UBLExtensions>
   <ext:UBLExtension>
      <ext:ExtensionContent/>
   </ext:UBLExtension>
</ext:UBLExtensions>
<cbc:UBLVersionID>2.1</cbc:UBLVersionID>
<cbc:CustomizationID>TR1.2</cbc:CustomizationID>
<cbc:ProfileID/>
<cbc:ID>50000520</cbc:ID>
<cbc:CopyIndicator>false</cbc:CopyIndicator>
<cbc:UUID>8E74EDF1-7BE5-4FE0-8F84-C5224ABA6B48</cbc:UUID>
<cbc:IssueDate>2019-10-15T00:00:00</cbc:IssueDate>
<cbc:IssueTime>09:31:13</cbc:IssueTime>
<cbc:InvoiceTypeCode/>
<cbc:Note>KARGO</cbc:Note>
<cbc:LineCountNumeric>1</cbc:LineCountNumeric>
<cac:AccountingSupplierParty>
   <cac:Party>
      <cbc:WebsiteURI/>
   </cac:Party>
</cac:AccountingSupplierParty>
</Invoice>

This works so far so good. But now some XML elements shall have attribute in the element itself. One example is:

<cbc:ID schemeID="VKN_TCKN">7330354914</cbc:ID>

How can I add the schemeID attribute with its value into my query?

Thanks for any help in advance!

Answer

Add this column before your cbc:ID

'VKN_TCKN' as 'cbc:ID/@SchemeID',
v2.nummer  as 'cbc:ID',

Advertisement

Answer