问题描述
我以这种方式创建我的 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://www.51sjk.com/Upload/Articles/1/0/337/337937_20221201144034050.2#' 作为 xades,'http://www.51sjk.com/Upload/Articles/1/0/337/337937_20221201144040082.jpg' 作为 xsi,'http://www.51sjk.com/Upload/Articles/1/0/337/337937_20221201144040596.jpg' 作为 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://www.51sjk.com/Upload/Articles/1/0/337/337937_20221201144034050.2#' 作为 xades, 'http://www.51sjk.com/Upload/Articles/1/0/337/337937_20221201144040082.jpg' 为 xsi, 'http://www.51sjk.com/Upload/Articles/1/0/337/337937_20221201144040596.jpg' 作为 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.51sjk.com/Upload/Articles/1/0/337/337937_20221201144040596.jpg"xmlns:xsi=http://www.51sjk.com/Upload/Articles/1/0/337/337937_20221201144040082.jpg"xmlns:xades="http://www.51sjk.com/Upload/Articles/1/0/337/337937_20221201144034050.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://www.51sjk.com/Upload/Articles/1/0/337/337937_20221201144034050.2#' as xades, 'http://www.51sjk.com/Upload/Articles/1/0/337/337937_20221201144040082.jpg' as xsi, 'http://www.51sjk.com/Upload/Articles/1/0/337/337937_20221201144040596.jpg' 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.51sjk.com/Upload/Articles/1/0/337/337937_20221201144040596.jpg" xmlns:xsi="http://www.51sjk.com/Upload/Articles/1/0/337/337937_20221201144040082.jpg" xmlns:xades="http://www.51sjk.com/Upload/Articles/1/0/337/337937_20221201144034050.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.51sjk.com/Upload/Articles/1/0/337/337937_20221201144040596.jpg" xmlns:xsi="http://www.51sjk.com/Upload/Articles/1/0/337/337937_20221201144040082.jpg" xmlns:xades="http://www.51sjk.com/Upload/Articles/1/0/337/337937_20221201144034050.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://www.51sjk.com/Upload/Articles/1/0/337/337937_20221201144034050.2#' as xades , 'http://www.51sjk.com/Upload/Articles/1/0/337/337937_20221201144040082.jpg' as xsi , 'http://www.51sjk.com/Upload/Articles/1/0/337/337937_20221201144040596.jpg' 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.51sjk.com/Upload/Articles/1/0/337/337937_20221201144040596.jpg" xmlns:xsi="http://www.51sjk.com/Upload/Articles/1/0/337/337937_20221201144040082.jpg" xmlns:xades="http://www.51sjk.com/Upload/Articles/1/0/337/337937_20221201144034050.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>');