四虎成人网,久久99精品国产麻豆婷婷洗澡,一级高清

本文介紹了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 文件中的發票位置的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！

【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題，如果有圖片或者內容侵犯了您的權益，請聯系我們刪除處理，感謝您的支持！

久久久久久久av_日韩在线中文_看一级毛片视频_日本精品二区_成人深夜福利视频_武道仙尊动漫在线观看

SQL XML - 從 SQL Server 為發票創建 XML 文件，包括在

問題描述

相關文檔推薦