January 30, 2015 at 7:45 am
Hello!
I`m new to programming with XML.
What I have to do is write a script which produces an XML-string from an invoice. The problem is that it has to work with 2 discounts. And I don´t know how to do that.
The script which works so far is (The problem section is the ReductionAndSurchargeListLineItemDetails - section !):
CREATE PROCEDURE wws.pEInvoice
@AnwenderID INT
AS
BEGIN
DECLARE @BelegNr INT = (SELECT AvBelegNr FROM dbo.tblXSYS WHERE AvAnwenderID = @AnwenderID);
DECLARE @BillerName VARCHAR(85);
DECLARE @BillerAdress VARCHAR(65);
DECLARE @BillerZIP CHAR(4);
DECLARE @BillerTown VARCHAR(35);
DECLARE @BillerCountryCode CHAR(2) = 'AT';
DECLARE @BillerCountry CHAR(7) = 'Austria'
DECLARE @BillerVAT VARCHAR(20);
DECLARE @BillerPhone VARCHAR(20);
DECLARE @BillerEMail VARCHAR(50);
DECLARE @BillerBankName CHAR(10) = (SELECT Bank FROM dbo.tblBankKassa WHERE (ERechnung=1));
DECLARE @BillerBankBIC CHAR(11) = (SELECT BIC FROM dbo.tblBankKassa WHERE (ERechnung=1));
DECLARE @BillerBankIBAN VARCHAR(34) = (SELECT IBAN FROM dbo.tblBankKassa WHERE (ERechnung=1));
DECLARE @BillerCreditorID VARCHAR(35);
DECLARE @DocumentType VARCHAR(15);
DECLARE @LogoURL VARCHAR(50);
DECLARE @TaxCode CHAR(5);
DECLARE @ManualProcessing VARCHAR(5);
DECLARE @DocumentTitle VARCHAR(24);
DECLARE @MandateReference VARCHAR(35);
SELECT
@BillerName = M.Name0
,@BillerVAT = M.[UID]
,@BillerBankIBAN = M.IBAN
,@BillerCreditorID = M.CreditorID
,@BillerAdress = CONCAT(M.Strasse, ' ', M.Hausnummer)
,@BillerZIP = M.PLZ
,@BillerTown = M.Ort
,@BillerPhone = CONCAT(L.VW, C.FNVW, C.Tel)
,@BillerEMail = C.Email
,@DocumentType = B.DocumentType
,@LogoURL = C.LogoURL
,@ManualProcessing = CASE WHEN ManualProcessing = 1 THEN 'true' ELSE 'false' END
,@DocumentTitle = CONCAT(B.DocumentType, '_', BelegNr)
,@MandateReference = K.MandateReference
FROMdbo.tblABeleg AS B
INNER JOINdbo.tblAKunden AS K ON B.KNummer = K.KNummer
INNER JOINdbo.tblAKunden AS M ON K.BetriebsID = M.KNummer
INNER JOINdbo.tblAKundenCommunication AS C ON M.KNummer = C.KNummer
INNER JOINLookup.Countries AS L ON M.CountryCode = L.CountryCode
WHERE (B.BelegNr = @BelegNr)
DECLARE @Header NVARCHAR(MAX) =
'<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="http://www.ebinterface.at/download/invoiceexample/4p1/invoice.xslt"?>
<Invoice
n1:GeneratingSystem="GSS Backoffice 2015"
n1:DocumentType="' + @DocumentType + '"
n1:InvoiceCurrency="EUR"
n1:ManualProcessing="' + @ManualProcessing + '"
n1:DocumentTitle="' + @DocumentTitle + '"
n1:Language="ger"
xsi:schemaLocation="http://www.ebinterface.at/schema/4p1/Invoice.xsd"
xmlns="http://www.ebinterface.at/schema/4p1/"
xmlns:n1="http://www.ebinterface.at/schema/4p1/"
xmlns:n2="http://www.altova.com/samplexml/other-namespace"
xmlns:ext="http://www.ebinterface.at/schema/4p1/extensions/ext"
xmlns:sv="http://www.ebinterface.at/schema/4p1/extensions/sv"
xmlns:dsig="http://www.w3.org/2000/09/xmldsig#"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
';
DECLARE @Invoice xml = (
SELECT
CAST(b.BelegNr AS CHAR(9)) AS 'InvoiceNumber'
,LEFT(CONVERT(CHAR(14), b.Belegdatum, 126), 10) AS 'InvoiceDate'
/*********** BEGINN Delivery ***********/
,LEFT(CONVERT(CHAR(14), b.Leistungsdatum, 126), 10) AS 'Delivery/Date'
/*********** BEGINN Biller (Angaben zum Rechnungssteller) ***********/
,@BillerVAT AS 'Biller/VATIdentificationNumber'
,K.InvoiceRecipientID AS 'Biller/InvoiceRecipientsBillerID'
--,b.OrderReference AS 'InvoiceRecipient/OrderReference/OrderID'--Auftragsnummer des Ministeriums
--,LEFT(CONVERT(CHAR(14), b.BelegDatum, 126),10) AS 'Biller/OrderReference/ReferenceDate'
--,'12345678' AS 'InvoiceRecipient/OrderReference/Description'
,@BillerName AS 'Biller/Address/Name'
,@BillerAdress AS 'Biller/Address/Street'
,@BillerTown AS 'Biller/Address/Town'
,@BillerZIP AS 'Biller/Address/ZIP'
,@BillerCountryCode AS 'Biller/Address/Country/@N1_CountryCode'
,@BillerCountry AS 'Biller/Address/Country'
,@BillerPhone AS 'Biller/Address/Phone'
,@BillerEMail AS 'Biller/Address/Email'
/*********** ENDE Biller ***********/
/*********** BEGINN InvoiceRecipient (Angaben zum Rechnungsempfänger) ***********/
,ISNULL(K.[UID],'00000000') AS 'InvoiceRecipient/VATIdentificationNumber'
--,b.KNummer AS 'InvoiceRecipient/BillersInvoiceRecipientID' --Lieferantennummer unter der dAS Unternehmen beim Bund/Ministerium geführt wird
--,@IROROID AS 'InvoiceRecipient/OrderReference/OrderID'--Auftragsnummer des Ministeriums
,K.Name0 AS 'InvoiceRecipient/Address/Name'
,CONCAT(K.Strasse, ' ', K.Hausnummer) AS 'InvoiceRecipient/Address/Street'
,K.Ort AS 'InvoiceRecipient/Address/Town'
,K.PLZ AS 'InvoiceRecipient/Address/ZIP'
,L.CountryCode AS 'InvoiceRecipient/Address/Country/@N1_CountryCode'
,L.Name AS 'InvoiceRecipient/Address/Country'
/*********** ENDE InvoiceRecipient ***********/
/*********** BEGINN Details (Beinhaltet die einzelnen Rechnungspositionen) ***********/
,(
SELECT
bp.POS AS 'ListLineItem/PositionNumber'
,A.Artikelname AS 'ListLineItem/Description'
,bp.ANR AS 'ListLineItem/ArticleNumber'
,A.Einheit AS 'ListLineItem/Quantity/@N1_Unit'
,CONVERT(DECIMAL(10,0), bp.Menge) AS 'ListLineItem/Quantity'
,CONVERT(DECIMAL(10, 2), bp.Preis) AS 'ListLineItem/UnitPrice'
,T.TaxCode AS 'ListLineItem/VATRate/@N1_TaxCode'
,CONVERT(DECIMAL(5,2),bp.MwSt) AS 'ListLineItem/VATRate'
/*********** BEGIN ReductionAndSurchargeListLineItemDetails (Rabatte) ***********/
-- HOW IT SHOULD LOOK LIKE: both discounts are separated by <ReductionListLineItem> and </ReductionListLineItem>
--<ReductionAndSurchargeListLineItemDetails>
--<ReductionListLineItem>
--<BaseAmount>510</BaseAmount>
--<Percentage>10.00</Percentage>
--<Amount>51.00</Amount>
--</ReductionListLineItem>
--<ReductionListLineItem> -- <-- is missing!
--<BaseAmount>459</BaseAmount>
--<Percentage>5.00</Percentage>
--<Amount>22.95</Amount>
--</ReductionListLineItem> -- <-- is missing!
--</ReductionAndSurchargeListLineItemDetails>
-- EVERY ITEM has to be capseled.
--Rabatt1:
,CONVERT(DECIMAL(10,0), bp.Menge * bp.Preis) AS 'ListLineItem/ReductionAndSurchargeListLineItemDetails/ReductionListLineItem/BaseAmount'
,CONVERT(DECIMAL(5,2), bp.Rabatt) AS 'ListLineItem/ReductionAndSurchargeListLineItemDetails/ReductionListLineItem/Percentage'
,CONVERT(DECIMAL(10,2), bp.Rabattsumme1) AS 'ListLineItem/ReductionAndSurchargeListLineItemDetails/ReductionListLineItem/Amount'
--Rabatt2:
,CONVERT(DECIMAL(10,0),((bp.menge * bp.Preis) - (bp.menge * bp.Preis * bp.Rabatt) / 100)) AS 'ListLineItem/ReductionAndSurchargeListLineItemDetails/ReductionListLineItem/BaseAmount'
,CONVERT(DECIMAL(5,2), bp.Rabatt2) AS 'ListLineItem/ReductionAndSurchargeListLineItemDetails/ReductionListLineItem/Percentage'
,CONVERT(DECIMAL(10,2), bp.Rabattsumme2) AS 'ListLineItem/ReductionAndSurchargeListLineItemDetails/ReductionListLineItem/Amount'
/*********** ENDE ReductionAndSurchargeListLineItemDetails (Rabatte) ***********/
,CONVERT(DECIMAL(10, 2), bp.Netto * bp.Menge) AS 'ListLineItem/LineItemAmount'
FROMdbo.tblABelegDetails AS bp
INNER JOINdbo.tblArtikel AS A ON bp.ANR = A.ANR
INNER JOINLookup.MwSt AS T ON bp.MwSt = T.MWST
WHERE(b.BelegNr = bp.BelegNr)
FOR XML Path(''),Type
) AS 'Details/ItemList'
/*********** ENDE Details ***********/
/*********** BEGINN Tax ***********/
,(
SELECT
CONVERT(DECIMAL(10,2), SUM(bp.Netto)) AS 'TaxedAmount'
,T.TaxCode AS 'VATRate/@N1_TaxCode'
,CONVERT(DECIMAL(5,2), SUM(bp.MwSt)) AS 'VATRate'
,CONVERT(DECIMAL(10, 2), SUM(bp.Brutto - bp.Netto)) AS 'Amount'
FROMdbo.tblABelegDetails AS bp
INNER JOINLookup.MwSt AS T ON bp.MwSt = T.MWST
WHERE(b.BelegNr = bp.BelegNr)
GROUP BY CONVERT(DECIMAL(5,2), bp.MwSt), T.TaxCode
FOR XML Path(''),Type
) AS 'Tax/VAT/VATItem'
/*********** ENDE Tax ***********/
/*********** BEGIN TotalGrossAmount (Rechnungsbruttobetrag) ***********/
,(
SELECT CONVERT(DECIMAL(10, 2),SUM(bp.Brutto))
FROM dbo.tblABelegDetails AS bp
WHERE(b.BelegNr = bp.BelegNr)
GROUP BY bp.BelegNr
FOR XML Path('TotalGrossAmount'),Type
)
/*********** ENDE TotalGrossAmount ***********/
,(
SELECT CONVERT(DECIMAL(10, 2),SUM(bp.Brutto))
FROM dbo.tblABelegDetails AS bp
WHERE(b.BelegNr = bp.BelegNr)
GROUP BY bp.BelegNr
FOR XML Path('PayableAmount'),Type
)
/*********** BEGIN PaymentMethod **********/
,CASE b.PaymentMethod
WHEN 'NoPayment' THEN (SELECT 'Bitte nicht einzahlen' AS 'Comment' FOR XML Path('PaymentMethod'),Type)
WHEN 'DirectDebit' THEN (SELECT CONCAT('Der Rechnungsbetrag wird am ', CONVERT(CHAR(10), DATEADD(DAY, Z.NettoTage, GETDATE()), 104), ' von Ihrem Konto eingezogen.') AS 'Comment' FOR XML Path('PaymentMethod'),Type)
WHEN 'SEPADirectDebit' THEN (SELECT
'B2C' AS 'SEPADirectDebit/Type'
,@BillerBankIBAN AS 'SEPADirectDebit/IBAN'
,@BillerName AS 'SEPADirectDebit/BankAccountOwner'
,@BillerCreditorID AS 'SEPADirectDebit/CreditorID'
,@MandateReference AS 'SEPADirectDebit/MandateReference'
,LEFT(CONVERT(CHAR(14), DATEADD(DAY, Z.NettoTage, GETDATE()), 126), 10) AS 'SEPADirectDebit/DebitColletionDate'
FOR XML Path('PaymentMethod'),Type
)
WHEN 'UniversalBankTransaction' THEN (SELECT
@BillerBankName AS 'UniversalBankTransaction/BeneficiaryAccount/BankName'
,@BillerBankBIC AS 'UniversalBankTransaction/BeneficiaryAccount/BIC'
,@BillerBankIBAN AS 'UniversalBankTransaction/BeneficiaryAccount/IBAN'
,@BillerName AS 'UniversalBankTransaction/BeneficiaryAccount/BankAccountOwner'
FOR XML Path('PaymentMethod'),Type
)
END
/*********** ENDE PaymentMethod **********/
/*********** BEGIN PaymentConditions **********/
,(
SELECT
LEFT(CONVERT(CHAR(14), DATEADD(DAY, Z.NettoTage, GETDATE()), 126), 10) AS 'DueDate'
,LEFT(CONVERT(CHAR(14), DATEADD(DAY, Z.SkontoTage, GETDATE()), 126), 10) AS 'Discount/PaymentDate'
,CAST(Z.Skonto AS DECIMAL(10,2)) AS 'Discount/Percentage'
FOR XML Path ('PaymentConditions'), Type
)
/*********** ENDE PaymentConditions **********/
/*********** BEGIN PresentationDetails ***********/
,(
SELECT
'http://www.gearwheel.at/' AS 'URL'
FOR XML Path ('PresentationDetails'),Type
)
/*********** ENDE PresentationDetails ***********/
FROMdbo.tblABeleg AS b
INNER JOIN dbo.tblAKunden AS K ON b.KNummer = K.KNummer
INNER JOIN Lookup.Countries AS L On K.CountryCode = L.CountryCode
INNER JOINdbo.tblXZahlungsbedingungen AS Z ON K.ZahlungsbedingungID = Z.ZahlungsbedingungID
WHERE(B.BelegNr = @BelegNr)
FOR XML Path('Invoice')
);
DECLARE @FileContent NVARCHAR(MAX)
=REPLACE(REPLACE(
CONVERT(NVARCHAR(MAX),@Invoice),
'<Invoice>', @Header),
'n1_', 'n1:')
DECLARE @FileContent1 NVARCHAR(MAX)
=REPLACE(REPLACE(
CONVERT(NVARCHAR(MAX),@FileContent),
'<Invoice>', @Header),
'xsi_', 'xsi:')
DECLARE @FileContent2 NVARCHAR(MAX)
=REPLACE(REPLACE(
CONVERT(NVARCHAR(MAX),@FileContent1),
'<Invoice>', @Header),
'eb_', 'eb:')
;
It would be great if someone out there could help a me!
Thanks a lot from Austria
Martin
February 28, 2015 at 11:54 am
Your XML-SQL is too complex. It's better to do it by following "pseudo"-code:
SELECT 'field'
, another_field
-- ( starts a new XML level
, (
SELECT row_field
, another_row_field
FROM rows_table t2
WHERE t2.orderno = t.orderno -- Link back to your base table
FOR XML PATH(''), ROOT('rows')
)
, (
SELECT discount
, discount_rate
FROM rows_table t2
WHERE t2.orderno = t.orderno -- Link back again!
AND discount > 0
FOR XML PATH(''), ROOT('discounts')
)
, ...even_more_fields...
FROM base_table t
FOR XML PATH(''), ROOT('einvoice')
This will output it much nicely, and you don't need to do long AS /blablablab/blablabl aliases
Inner FOR XMLs can have even more XML levels
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply