SQL XML - 从 SQL Server 为发票创建 XML 文件，包括在

数据库问题 得得之家
SQL XML - Create a XML file from SQL Server for an invoice including invoice positions in one XML file(SQL XML - 从 SQL Server 为发票创建 XML 文件，包括在一个 XML 文件中的发票位置)
本文介绍了SQL XML - 从 SQL Server 为发票创建 XML 文件，包括在一个 XML 文件中的发票位置的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我以这种方式创建我的 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>');

这篇关于SQL XML - 从 SQL Server 为发票创建 XML 文件，包括在一个 XML 文件中的发票位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！

相关文档推荐

在EF6中执行复杂的原始SQL查询
Execute complex raw SQL query in EF6(在EF6中执行复杂的原始SQL查询)
SSIS：模型设计问题导致重复-两个事实表可以连接吗？
SSIS: Model design issue causing duplications - can two fact tables be connected?(SSIS：模型设计问题导致重复-两个事实表可以连接吗？)
SQL Server图形数据库-使用多种边类型的最短路径
SQL Server Graph Database - shortest path using multiple edge types(SQL Server图形数据库-使用多种边类型的最短路径)
使用EF核心过滤包括时无效的列名
Invalid column name when using EF Core filtered includes(使用EF核心过滤包括时无效的列名)
如何让多参数的SQL Server过滤程序更快
How should make faster SQL Server filtering procedure with many parameters(如何让多参数的SQL Server过滤程序更快)
如何使用Microsoft SQL Server Management Studio生成数据库的实体关系(ER)图？
How can I generate an entity–relationship (ER) diagram of a database using Microsoft SQL Server Management Studio?(如何使用Microsoft SQL Server Management Studio生成数据库的实体关系(ER)图？)
前端问题php问题Java问题Python问题C/C++问题C#/.NET问题移动开发问题数据库问题
html vue validate adobe dreamweaver hbuilder vscode aptana editor dedecms ckeditor 编辑器 过滤 规则 织梦 图片本地化 模板 缩略图 图集 图片删除 ajax 瀑布流 无限下拉 cms 判断 sql 清除 tag 文档数 angularjs2 按钮 切换效果 vue3 thinkphp yii2 css 项目列表 li go Beego Buffalo Echo Gin Iris Revel 百度云 虚拟主机 pbootcms 伪静态 框架 排序 数据库 对象 字段 sql语句 php 字符串 分割 D3.js bootstrap 函数 svg selectAll 织梦cms 关键词 解析 采集 长度限制 日期 正则表达式