SQL XML - 从 SQL Server 为发票创建 XML 文件，包括在一个 XML 文件中的发票位置

2021-09-10 00:00:00 xml tsql xquery sql-server

我以这种方式创建我的 xml 文件(我没有显示所有输出字段，因为字段非常多):

DECLARE @ID_Rechnung int = 8;使用 XML 命名空间 ('urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2' 作为 ext，'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' 作为 cbc，'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' 作为 cac，'http://uri.etsi.org/01903/v1.3.2#' 作为 xades，'http://www.w3.org/2001/XMLSchema-instance' 作为 xsi，'http://www.w3.org/2000/09/xmldsig#' 作为 ds)选择@XMLData = xmldat.xmldataCol从(选择(选择-- HIER XML 日期生成器'' AS 'ext:UBExtensions','' AS 'ext:UBExtensions/ext:UBExtension','' AS 'ext:UBExtensions/ext:UBExtension/ext:ExtensionContent','2.1' AS 'cbc:UBLVersionID','TR1.2' AS 'cbc:CustomizationID','' AS 'cbc:ProfileID',Rechnungen.Nummer AS 'cbc:ID','假' AS 'cbc:CopyIndi​​cator','' AS 'cbc:UUID',CAST(Rechnungen.Datum AS Date) AS 'cbc:IssueDate'从再造在哪里rechnungen.id = @ID_RechnungFOR XML PATH(''), ROOT('发票')) 作为 xmldataCol

这很好用 - 我得到以下 XML:

<ext:UBExtensions><ext:UBExtension><ext:ExtensionContent/></ext:UBExtension></ext:UBExtensions><cbc:UBLVersionID>2.1</cbc:UBLVersionID><cbc:CustomizationID>TR1.2</cbc:CustomizationID><cbc:ProfileID/><cbc:ID>R200001</cbc:ID><cbc:CopyIndi​​cator>false</cbc:CopyIndi​​cator><cbc:UUID/><cbc:IssueDate>2020-06-29</cbc:IssueDate></发票>

但现在我需要在同一个文件中的发票位置.

此 SQL 应包含在第一个中，日期应作为 xml 文件中的发票行:

<预><代码>选择Rechnungpos.ID AS 'cac:InvoiceLine/cbc:ID',Rechnungpos.Anzahl AS 'cac:InvoiceLine/cbc:InvoicedQuantity'从重新定位在哪里RechnungPos.id_Rechnung = @ID_Rechnung

输出应该是这样的:

<ext:UBExtensions><ext:UBExtension><ext:ExtensionContent/></ext:UBExtension></ext:UBExtensions><cbc:UBLVersionID>2.1</cbc:UBLVersionID><cbc:CustomizationID>TR1.2</cbc:CustomizationID><cbc:ProfileID/><cbc:ID>R200001</cbc:ID><cbc:CopyIndi​​cator>false</cbc:CopyIndi​​cator><cbc:UUID/><cbc:IssueDate>2020-06-29</cbc:IssueDate><cac:InvoiceLine><cbc:ID>1<(cbc:><cbc:InvoicedQuantity>3</cbc:InvoicedQuantity></cac:InvoiceLine><cac:InvoiceLine><cbc:ID>5<(cbc:><cbc:InvoicedQuantity>1</cbc:InvoicedQuantity></cac:InvoiceLine><cac:InvoiceLine><cbc:ID>9<(cbc:><cbc:InvoicedQuantity>2</cbc:InvoicedQuantity></cac:InvoiceLine></发票>

这是生成测试数据的代码:

创建表 [dbo].[Rechnungen]([id] [int] 非空，[数字] [nvarchar](20) 非空，[基准] [日期时间] NOT NULL)INSERT INTO Rechnungen (id, Nummer, Datum) VALUES (8, 'R200001', '29.06.2020')创建表 [dbo].Rechnungpos([id] [int] 非空，[id_Rechnung] [int] 非空，[Anzahl] [浮动] 非空)INSERT INTO RechnungPos (id, id_Rechnung, Anzahl) VALUES (1, 8, 3)INSERT INTO RechnungPos (id, id_Rechnung, Anzahl) 值 (5, 8, 1)INSERT INTO RechnungPos (id, id_Rechnung, Anzahl) VALUES (9, 8, 2)

它必须在不同的版本上运行 - 我的版本是 SQL Server 2019

我该怎么做?

感谢您的帮助，托马斯.

解决方案

感谢 Yitzhak Khabinsky 的帮助 - 这是完整的解决方案:

DECLARE @ID_Rechnung int = 8,@XMLData xml;WITH XMLNAMESPACES ('urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2' as ext, 'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' 作为 cbc, 'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' 作为 cac, 'http://uri.etsi.org/01903/v1.3.2#' 作为 xades, 'http://www.w3.org/2001/XMLSchema-instance' 为 xsi, 'http://www.w3.org/2000/09/xmldsig#' 作为 ds)选择@XMLData = xmldat.xmldataCol从(选择 (选择-- HIER XML 数据生成器'' AS 'ext:UBExtensions','' AS 'ext:UBExtensions/ext:UBExtension','' AS 'ext:UBExtensions/ext:UBExtension/ext:ExtensionContent','2.1' AS 'cbc:UBLVersionID','TR1.2' AS 'cbc:CustomizationID','' AS 'cbc:ProfileID',Rechnungen.Nummer AS 'cbc:ID','假' AS 'cbc:CopyIndi​​cator','' AS 'cbc:UUID',CAST(Rechnungen.Datum AS Date) AS 'cbc:IssueDate','' AS 'cbc:InvoiceTypeCode',Rechnungen.Bemerkung1 AS 'cbc:Note',@Waehrung AS 'cbc:DocumentCurrencyCode',@Waehrung AS 'cbc:TaxCurrencyCode',Rechnungen.Auftrag AS 'cac:OrderReference/cbc:ID',-- 维尔考夫'' AS 'cac:AccountingSupplierParty/cac:Party/cbc:EndpointID','' AS 'cac:AccountingSupplierParty/cac:Party/cac:PartyIdentification/cbc:ID','' AS 'cac:AccountingSupplierParty/cac:Party/cac:PartyName/cbc:Name','' AS 'cac:AccountingSupplierParty/cac:Party/cac:PostalAddress/cbc:StreetName','' AS 'cac:AccountingSupplierParty/cac:Party/cac:PostalAddress/cbc:CityName','' AS 'cac:AccountingSupplierParty/cac:Party/cac:PostalAddress/cbc:PostalZone','' AS 'cac:AccountingSupplierParty/cac:Party/cac:PostalAddress/cac:Country/cbc:IdentificationCode','' AS 'cac:AccountingSupplierParty/cac:Party/cac:PartyTaxScheme/cbc:CompanyId','增值税' AS 'cac:AccountingSupplierParty/cac:Party/cac:PartyTaxScheme/cac:TaxScheme/cbc:ID','' AS 'cac:AccountingSupplierParty/cac:Party/cac:PartyLegalEntity/cbc:RegistrationName','' AS 'cac:AccountingSupplierParty/cac:Party/cac:PartyLegalEntity/cbc:CompanyID','' AS 'cac:AccountingSupplierParty/cac:Party/cac:PartyLegalEntity/cbc:CompanyLegalForm','' AS 'cac:AccountingSupplierParty/cac:Party/cac:Contact/cbc:Name','' AS 'cac:AccountingSupplierParty/cac:Party/cac:Contact/cbc:Telephone','' AS 'cac:AccountingSupplierParty/cac:Party/cac:Contact/cbc:ElectronicMail',——考弗'' AS 'cac:AccountingCustomerParty/cac:Party/cbc:EndpointID',Rechnungen.DebKreNr AS 'cac:AccountingCustomerParty/cac:Party/cac:PartyIdentification/cbc:ID',Rechnungen.DebBez01 + ' ' + DebBez02 AS 'cac:AccountingCustomerParty/cac:Party/cac:PartyName/cbc:Name',Rechnungen.DebStrasse AS 'cac:AccountingCustomerParty/cac:Party/cac:PostalAddress/cbc:StreetName',Rechnungen.DebOrt AS 'cac:AccountingCustomerParty/cac:Party/cac:PostalAddress/cbc:CityName',Rechnungen.DebPLZ AS 'cac:AccountingCustomerParty/cac:Party/cac:PostalAddress/cbc:PostalZone',Rechnungen.DebLandKFZ AS 'cac:AccountingCustomerParty/cac:Party/cac:PostalAddress/cac:Country/cbc:IdentificationCode',Rechnungen.DebUMSTID AS 'cac:AccountingCustomerParty/cac:Party/cac:PartyTaxScheme/cbc:CompanyID','增值税' AS 'cac:AccountingCustomerParty/cac:Party/cac:PartyTaxScheme/cac:TaxScheme/cbc:ID',Rechnungen.DebBez01 + ' ' + DebBez02 AS 'cac:AccountingCustomerParty/cac:Party/cac:PartyLegalEntity/cbc:RegistrationName','' AS 'cac:AccountingCustomerParty/cac:Party/cac:Contact/cbc:Name','' AS 'cac:AccountingCustomerParty/cac:Party/cac:Contact/cbc:Telephone','' AS 'cac:AccountingCustomerParty/cac:Party/cac:Contact/cbc:ElectronicMail',-- Kontoverbindung Verkäufer'' AS 'cac:PaymentMeans/cbc:PaymentMeansCode','' AS 'cac:PaymentMeans/cac:PayeeFinancialAccount/cbc:ID','' AS 'cac:PaymentMeans/cac:PayeeFinancialAccount/cbc:Name','' AS 'cac:PaymentMeans/cac:PayeeFinancialAccount/cac:FinancialInstitutionBranch/cbc:ID',--'' AS 'cac:PaymentTerms/cbc:Note',——斯图恩@Waehrung AS 'cac:TaxTotal/cbc_TaxAmount/@currencyID',CAST(Rechnungen.BetragMWST AS nvarchar(15)) AS 'cac:TaxTotal/cbc_TaxAmount',@Waehrung AS 'cac:TaxTotal/cac:Taxubtotal/cbc:TaxableAmount/@currencyID',CAST(Rechnungen.BetragNetto AS nvarchar(15))AS 'cac:TaxTotal/cac:Taxubtotal/cbc:TaxableAmount',@Waehrung AS 'cac:TaxTotal/cac:Taxubtotal/cbc:TaxAmount/@currencyID',CAST(Rechnungen.BetragMWST AS nvarchar(15))AS 'cac:TaxTotal/cac:Taxubtotal/cbc:TaxAmount','' AS 'cac:TaxTotal/cac:Taxubtotal/cac:TaxCategory/cbc:ID',CAST(Rechnungen.MWST AS nvarchar(2)) AS 'cac:TaxTotal/cac:Taxubtotal/cac:TaxCategory/cbc:Percent','增值税' AS 'cac:TaxTotal/cac:Taxubtotal/cac:TaxCategory/cac:TaxScheme/cbc:ID',@Waehrung AS 'cac:LegalMonetaryTotal/cbc:LineExtensionAmount/@currencyID',CAST(Rechnungen.BetragNetto AS nvarchar(15))AS 'cac:LegalMonetaryTotal/cbc:LineExtensionAmount',@Waehrung AS 'cac:LegalMonetaryTotal/cbc:TaxExclusiveAmount/@currencyID',CAST(Rechnungen.BetragNetto AS nvarchar(15))AS 'cac:LegalMonetaryTotal/cbc:TaxExclusiveAmount',@Waehrung AS 'cac:LegalMonetaryTotal/cbc:TaxInclusiveAmount/@currencyID',CAST(Rechnungen.BetragBrutto AS nvarchar(15))AS 'cac:LegalMonetaryTotal/cbc:TaxInclusiveAmount',@Waehrung AS 'cac:LegalMonetaryTotal/cbc:PayableAmount/@currencyID',CAST(Rechnungen.BetragBrutto AS nvarchar(15))AS 'cac:LegalMonetaryTotal/cbc:PayableAmount',(SELECT Rechnungpos.id AS [cbc:ID], CAST(Rechnungpos.Anzahl AS INT) AS [cbc:InvoicedQuantity]从 Rechnungpos WHERE RechnungPos.id_Rechnung = @id_RechnungFOR XML PATH('r'), TYPE, ROOT('root'))FROM Rechnungen哪里 Rechnungen.id = @ID_RechnungFOR XML PATH(''), TYPE, ROOT('发票')) 作为 xmldataCol) 作为 xmldat;选择@XMLData.query('<发票 xmlns:ds="http://www.w3.org/2000/09/xmldsig#"xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance"xmlns:xades="http://uri.etsi.org/01903/v1.3.2#"xmlns:cac=urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2"xmlns:cbc=urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2"xmlns:ext=urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2">{对于/Invoice/*[local-name()!=root"] 中的 $x返回 $x，对于/Invoice/root/r 中的 $xreturn <cac:InvoiceLine>{$x/*}</cac:InvoiceLine>}</发票>');

I create my xml file in this way (I do not show all output fields because there are very many fields):

DECLARE @ID_Rechnung int = 8;

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',
            Rechnungen.Nummer                           AS 'cbc:ID',
            'false'                                     AS 'cbc:CopyIndicator',
            ''                                          AS 'cbc:UUID',
            CAST(Rechnungen.Datum AS Date)              AS 'cbc:IssueDate'      
            
        FROM 
            rechnungen  
        WHERE 
            rechnungen.id = @ID_Rechnung


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

This works fine - i get the following XML:

<Invoice xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xades="http://uri.etsi.org/01903/v1.3.2#" xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2" xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2" xmlns:ext="urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-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>R200001</cbc:ID>
  <cbc:CopyIndicator>false</cbc:CopyIndicator>
  <cbc:UUID />
  <cbc:IssueDate>2020-06-29</cbc:IssueDate>
</Invoice>

But now i need the invoice positions in the same file.

This SQL should be included in the first one and the date should be as invoice line in the xml file:


SELECT
  Rechnungpos.ID                        AS 'cac:InvoiceLine/cbc:ID',
  Rechnungpos.Anzahl                    AS 'cac:InvoiceLine/cbc:InvoicedQuantity'
FROM
  RechnungPos 
WHERE
  RechnungPos.id_Rechnung = @ID_Rechnung

The output should be this:

<Invoice xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xades="http://uri.etsi.org/01903/v1.3.2#" xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2" xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2" xmlns:ext="urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-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>R200001</cbc:ID>
  <cbc:CopyIndicator>false</cbc:CopyIndicator>
  <cbc:UUID />
  <cbc:IssueDate>2020-06-29</cbc:IssueDate>
  <cac:InvoiceLine>
    <cbc:ID>1<(cbc:>
    <cbc:InvoicedQuantity>3</cbc:InvoicedQuantity>
  </cac:InvoiceLine>
  <cac:InvoiceLine>
    <cbc:ID>5<(cbc:>
    <cbc:InvoicedQuantity>1</cbc:InvoicedQuantity>
  </cac:InvoiceLine>
  <cac:InvoiceLine>
    <cbc:ID>9<(cbc:>
    <cbc:InvoicedQuantity>2</cbc:InvoicedQuantity>
  </cac:InvoiceLine>
</Invoice>

Here is the Code to generate Test Data:

CREATE TABLE [dbo].[Rechnungen](
    [id] [int]  NOT NULL,
    [Nummer] [nvarchar](20) NOT NULL,
    [Datum] [datetime] NOT NULL
)

INSERT INTO Rechnungen (id, Nummer, Datum) VALUES (8, 'R200001', '29.06.2020')

CREATE TABLE [dbo].Rechnungpos(
    [id] [int]  NOT NULL,
    [id_Rechnung] [int] NOT NULL,
    [Anzahl] [float] NOT NULL
)

INSERT INTO RechnungPos (id, id_Rechnung, Anzahl) VALUES (1, 8, 3) 
INSERT INTO RechnungPos (id, id_Rechnung, Anzahl) VALUES (5, 8, 1) 
INSERT INTO RechnungPos (id, id_Rechnung, Anzahl) VALUES (9, 8, 2)

it has to run on different versions - my version is SQL Server 2019

How can i do that?

Thanks for help, Thomas.

解决方案

Thanks, Yitzhak Khabinsky for help - heres the complete solution:

DECLARE @ID_Rechnung int = 8,
    @XMLData xml;

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',
            Rechnungen.Nummer                           AS 'cbc:ID',
            'false'                                     AS 'cbc:CopyIndicator',
            ''                                          AS 'cbc:UUID',
            CAST(Rechnungen.Datum AS Date)              AS 'cbc:IssueDate',
            ''                                          AS 'cbc:InvoiceTypeCode',
            Rechnungen.Bemerkung1                       AS 'cbc:Note',
            @Waehrung                                   AS 'cbc:DocumentCurrencyCode',
            @Waehrung                                   AS 'cbc:TaxCurrencyCode',
            Rechnungen.Auftrag                          AS 'cac:OrderReference/cbc:ID',
            -- Verkaüfer
            ''                                          AS 'cac:AccountingSupplierParty/cac:Party/cbc:EndpointID',
            ''                                          AS 'cac:AccountingSupplierParty/cac:Party/cac:PartyIdentification/cbc:ID',
            ''                                          AS 'cac:AccountingSupplierParty/cac:Party/cac:PartyName/cbc:Name',
            ''                                          AS 'cac:AccountingSupplierParty/cac:Party/cac:PostalAddress/cbc:StreetName',
            ''                                          AS 'cac:AccountingSupplierParty/cac:Party/cac:PostalAddress/cbc:CityName',
            ''                                          AS 'cac:AccountingSupplierParty/cac:Party/cac:PostalAddress/cbc:PostalZone',
            ''                                          AS 'cac:AccountingSupplierParty/cac:Party/cac:PostalAddress/cac:Country/cbc:IdentificationCode',
            ''                                          AS 'cac:AccountingSupplierParty/cac:Party/cac:PartyTaxScheme/cbc:CompanyId',
            'VAT'                                       AS 'cac:AccountingSupplierParty/cac:Party/cac:PartyTaxScheme/cac:TaxScheme/cbc:ID',
            ''                                          AS 'cac:AccountingSupplierParty/cac:Party/cac:PartyLegalEntity/cbc:RegistrationName',
            ''                                          AS 'cac:AccountingSupplierParty/cac:Party/cac:PartyLegalEntity/cbc:CompanyID',
            ''                                          AS 'cac:AccountingSupplierParty/cac:Party/cac:PartyLegalEntity/cbc:CompanyLegalForm',
            ''                                          AS 'cac:AccountingSupplierParty/cac:Party/cac:Contact/cbc:Name',
            ''                                          AS 'cac:AccountingSupplierParty/cac:Party/cac:Contact/cbc:Telephone',
            ''                                          AS 'cac:AccountingSupplierParty/cac:Party/cac:Contact/cbc:ElectronicMail',
            -- Käufer
            ''                                          AS 'cac:AccountingCustomerParty/cac:Party/cbc:EndpointID',
            Rechnungen.DebKreNr                         AS 'cac:AccountingCustomerParty/cac:Party/cac:PartyIdentification/cbc:ID',
            Rechnungen.DebBez01 + ' ' + DebBez02        AS 'cac:AccountingCustomerParty/cac:Party/cac:PartyName/cbc:Name',
            Rechnungen.DebStrasse                       AS 'cac:AccountingCustomerParty/cac:Party/cac:PostalAddress/cbc:StreetName',
            Rechnungen.DebOrt                           AS 'cac:AccountingCustomerParty/cac:Party/cac:PostalAddress/cbc:CityName',
            Rechnungen.DebPLZ                           AS 'cac:AccountingCustomerParty/cac:Party/cac:PostalAddress/cbc:PostalZone',
            Rechnungen.DebLandKFZ                       AS 'cac:AccountingCustomerParty/cac:Party/cac:PostalAddress/cac:Country/cbc:IdentificationCode',
            Rechnungen.DebUMSTID                        AS 'cac:AccountingCustomerParty/cac:Party/cac:PartyTaxScheme/cbc:CompanyID',
            'VAT'                                       AS 'cac:AccountingCustomerParty/cac:Party/cac:PartyTaxScheme/cac:TaxScheme/cbc:ID',
            Rechnungen.DebBez01 + ' ' + DebBez02        AS 'cac:AccountingCustomerParty/cac:Party/cac:PartyLegalEntity/cbc:RegistrationName',
            ''                                          AS 'cac:AccountingCustomerParty/cac:Party/cac:Contact/cbc:Name',
            ''                                          AS 'cac:AccountingCustomerParty/cac:Party/cac:Contact/cbc:Telephone',
            ''                                          AS 'cac:AccountingCustomerParty/cac:Party/cac:Contact/cbc:ElectronicMail',
            -- Kontoverbindung Verkäufer
            ''                                          AS 'cac:PaymentMeans/cbc:PaymentMeansCode',
            ''                                          AS 'cac:PaymentMeans/cac:PayeeFinancialAccount/cbc:ID',
            ''                                          AS 'cac:PaymentMeans/cac:PayeeFinancialAccount/cbc:Name',
            ''                                          AS 'cac:PaymentMeans/cac:PayeeFinancialAccount/cac:FinancialInstitutionBranch/cbc:ID',
            --''                                            AS 'cac:PaymentTerms/cbc:Note',
            -- Steuern
            @Waehrung                                   AS 'cac:TaxTotal/cbc_TaxAmount/@currencyID',
            CAST(Rechnungen.BetragMWST AS nvarchar(15)) AS 'cac:TaxTotal/cbc_TaxAmount',
            @Waehrung                                   AS 'cac:TaxTotal/cac:Taxubtotal/cbc:TaxableAmount/@currencyID',
            CAST(Rechnungen.BetragNetto AS nvarchar(15))AS 'cac:TaxTotal/cac:Taxubtotal/cbc:TaxableAmount',
            @Waehrung                                   AS 'cac:TaxTotal/cac:Taxubtotal/cbc:TaxAmount/@currencyID',
            CAST(Rechnungen.BetragMWST  AS nvarchar(15))AS 'cac:TaxTotal/cac:Taxubtotal/cbc:TaxAmount',
            ''                                          AS 'cac:TaxTotal/cac:Taxubtotal/cac:TaxCategory/cbc:ID',
            CAST(Rechnungen.MWST AS nvarchar(2))        AS 'cac:TaxTotal/cac:Taxubtotal/cac:TaxCategory/cbc:Percent',
            'VAT'                                       AS 'cac:TaxTotal/cac:Taxubtotal/cac:TaxCategory/cac:TaxScheme/cbc:ID',
            @Waehrung                                   AS 'cac:LegalMonetaryTotal/cbc:LineExtensionAmount/@currencyID',
            CAST(Rechnungen.BetragNetto AS nvarchar(15))AS 'cac:LegalMonetaryTotal/cbc:LineExtensionAmount',
            @Waehrung                                   AS 'cac:LegalMonetaryTotal/cbc:TaxExclusiveAmount/@currencyID',
            CAST(Rechnungen.BetragNetto AS nvarchar(15))AS 'cac:LegalMonetaryTotal/cbc:TaxExclusiveAmount',
            @Waehrung                                   AS 'cac:LegalMonetaryTotal/cbc:TaxInclusiveAmount/@currencyID',
            CAST(Rechnungen.BetragBrutto AS nvarchar(15))AS 'cac:LegalMonetaryTotal/cbc:TaxInclusiveAmount',
            @Waehrung                                   AS 'cac:LegalMonetaryTotal/cbc:PayableAmount/@currencyID',
            CAST(Rechnungen.BetragBrutto AS nvarchar(15))AS 'cac:LegalMonetaryTotal/cbc:PayableAmount',

    (
        SELECT Rechnungpos.id AS [cbc:ID]
            , CAST(Rechnungpos.Anzahl AS INT) AS [cbc:InvoicedQuantity] 
        FROM Rechnungpos WHERE RechnungPos.id_Rechnung = @id_Rechnung
        FOR XML PATH('r'), TYPE, ROOT('root')
    )
FROM Rechnungen 
WHERE Rechnungen.id = @ID_Rechnung
FOR XML PATH(''), TYPE, ROOT('Invoice')

) AS xmldataCol
) AS xmldat;

SELECT @XMLData
.query('<Invoice xmlns:ds="http://www.w3.org/2000/09/xmldsig#"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xmlns:xades="http://uri.etsi.org/01903/v1.3.2#"
         xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2"
         xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2"
         xmlns:ext="urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2">
{
   for $x in /Invoice/*[local-name()!="root"]
   return $x,
     for $x in /Invoice/root/r
     return <cac:InvoiceLine>{$x/*}</cac:InvoiceLine>
}
</Invoice>');

相关文章