June 22, 2019 at 5:47 pm
Hello community,
I have a little problem with this query, because the XML that are produce return only fields of the header document , Tag [Order], but not the lines for this document on TAG [OrderLine].
This is my code:
DECLARE @Stamp AS VARCHAR(25) = 'ADM19062261955,812000001'
DECLARE @XML XML = N'';
DECLARE @XMLSTR NVARCHAR(MAX) = N'';
SELECT
@XML = (SELECT
(
SELECT
memissao AS [OrderHead/OrderCurrency/Currency],
bo.obrano AS [OrderReferences/BuyersOrderNumber],
bo.dataobra AS [OrderDate],
bo.nome AS [Buyer/Party],
bo.ncont AS [InvoiceTo/InvoiceToReferences],
bo.etotal AS [OrderTotal/GrossValue]
FROM BO
CROSS APPLY
(
SELECT
Rtrim(fil.ref) AS [Product/SuppliersProductCode],
Rtrim(fil.design) AS [Product/Description],
fil.edebito AS [Price/UnitPrice],
fil.ettdeb AS [LineTotal],
fil.qtt AS [Quantity/Amount],
fil.iva AS [LineTax/Taxrate]
FROM bi fil
WHERE fil.bostamp = bo.bostamp
AND fil.qtt <> 0 AND fil.ettdeb <> 0
FOR XML PATH('OrderLine'), TYPE
) AS fi_xml(OrderLines)
WHERE bo.bostamp = @Stamp
FOR XML PATH('Order'), TYPE
)
);
SELECT
@XMLSTR = N'<?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>' + CAST(@XML AS NVARCHAR(MAX))
SELECT
CONVERT(NVARCHAR(MAX), @XMLSTR, 1) AS meuXml;
The XML file return:
<?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>
<Order>
<OrderHead>
<OrderCurrency>
<Currency>EURO</Currency>
</OrderCurrency>
</OrderHead>
<OrderReferences>
<BuyersOrderNumber>3</BuyersOrderNumber>
</OrderReferences>
<OrderDate>2019-03-03T00:00:00</OrderDate>
<Buyer>
<Party>Bernardo Santiago </Party>
</Buyer>
<InvoiceTo>
<InvoiceToReferences>502323434 </InvoiceToReferences>
</InvoiceTo>
<OrderTotal>
<GrossValue>0.000000</GrossValue>
</OrderTotal>
</Order>
My CROSS APPLY dont had the TAGS:
roduct/SuppliersProductCode + Product/Description + Price/UnitPrice+LineTotal+Quantity/Amount+LineTax/Taxrate , in NEW TAG [OrderLine ] inside the main TAG [Order].
someone could give me help.
Many thanks,
Best regards,
Luis
June 22, 2019 at 6:08 pm
Hello community,
Sorry, please forget thi Post , i solve it .
Many, thanks,
Luis
June 22, 2019 at 8:50 pm
You really should post your solution. It may help others with a similar problem.
June 22, 2019 at 11:06 pm
Hello Lynn
Sorry you are right.
DECLARE @Stamp AS VARCHAR(25) = 'ADM19062261955,812000001'
DECLARE @XML XML = N'';
DECLARE @XMLSTR NVARCHAR(MAX) = N'';
SELECT
@XML = (SELECT
(
SELECT
memissao AS [OrderHead/OrderCurrency/Currency],
bo.obrano AS [OrderReferences/BuyersOrderNumber],
bo.dataobra AS [OrderDate],
bo.nome AS [Buyer/Party],
bo.ncont AS [InvoiceTo/InvoiceToReferences],
bo.etotaldeb AS [OrderTotal/GrossValue],
fi_xml.OrderLine «« Solution : I forget to mentioned this line
FROM BO
CROSS APPLY
(
SELECT distinct
--fil.LineNumber AS [Linenumber],
fil.qtt AS [Quantity/Amount],
Rtrim(fil.ref) AS [Product/SuppliersProductCode],
Rtrim(fil.design) AS [Product/Description],
fil.edebito AS [Price/UnitPrice],
fil.ettdeb AS [LineTotal],
fil.iva AS [LineTax/Taxrate]
FROM bi_lines fil
WHERE fil.bostamp = bo.bostamp
AND fil.qtt <> 0 AND fil.ettdeb <> 0
--GROUP BY fil.bistamp,Rtrim(fil.ref),Rtrim(fil.design),fil.edebito,fil.ettdeb,fil.qtt,fil.iva
--FOR XML AUTO
FOR XML PATH(''), TYPE
) AS fi_xml(OrderLine)
WHERE bo.bostamp = @Stamp
FOR XML PATH('Order'), TYPE
)
);
SELECT
@XMLSTR = N'<?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>' + CAST(@XML AS NVARCHAR(MAX))
SELECT
CONVERT(NVARCHAR(MAX), @XMLSTR, 1) AS meuXml;
Best regards and thanks for your reply
Luis
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply