Need help to export result of my query (XML result) to disk

Question

Post reply

Need help to export result of my query (XML result) to disk

luissantos

SSCertifiable

Points: 7391

June 23, 2019 at 11:01 pm

#3656751

Hello comunity,

I need to export this result to filename on my disk, how can do that :

I post above the query that produce my XML File:

DECLARE @Stamp AS VARCHAR(25) = 'ADM19062261955,812000001'
DECLARE @XML XML = N'';
DECLARE @XMLSTR NVARCHAR(MAX) = N'';

;WITH bi_Lines AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY bi.bostamp ORDER BY bi.lordem asc) AS LineNumber
FROM bi WHERE bostamp = @Stamp
AND bi.qtt <> 0 AND bi.ettdeb<> 0 --se quisermos tirar linhas sem qtt
)

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.Linhas
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.iva AS [LineTax/Taxrate],
fil.ettdeb AS [LineTotal]
FROM bi_lines fil
WHERE fil.bostamp = bo.bostamp
AND fil.qtt <> 0 AND fil.ettdeb <> 0
FOR XML PATH('Linha'), TYPE
) AS fi_xml(Linhas)
WHERE bo.bostamp = @Stamp
FOR XML PATH('Order'), TYPE
)
);

SELECT @XMLSTR = CAST(@XML AS NVARCHAR(MAX))


SELECT CONVERT(NVARCHAR(MAX), @XMLSTR, 1) AS meuXml

this query produce my XML file Above:

<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>300.000000</GrossValue>
</OrderTotal>
<Linhas>
<Linha>
<Linenumber>1</Linenumber>
<Quantity>
<Amount>1.0000</Amount>
</Quantity>
<Product>
<SuppliersProductCode>A001</SuppliersProductCode>
<Description>Adufe</Description>
</Product>
<Price>
<UnitPrice>100.000000</UnitPrice>
</Price>
<LineTax>
<Taxrate>23.00</Taxrate>
</LineTax>
<LineTotal>100.000000</LineTotal>
</Linha>
<Linha>
<Linenumber>2</Linenumber>
<Quantity>
<Amount>1.0000</Amount>
</Quantity>
<Product>
<SuppliersProductCode>B001</SuppliersProductCode>
<Description>Bombos</Description>
</Product>
<Price>
<UnitPrice>200.000000</UnitPrice>
</Price>
<LineTax>
<Taxrate>23.00</Taxrate>
</LineTax>
<LineTotal>200.000000</LineTotal>
</Linha>
</Linhas>
</Order>

I try to use BCP but without success, sometimes with errors or empty file.

I also try to create temptable to store the result and after that run the BCP command but without success.

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE

SELECT * INTO #temptable
FROM (SELECT CONVERT(NVARCHAR(MAX), @XMLSTR, 1) AS meuXml) a



EXEC xp_cmdshell 'bcp "select * from #temptable" queryout "C:\EDI\bcptest.xml" -c -T -x'

Could anyone give me a solution to solve this, because it´s the last step to finish my deploy.

Many thanks,

Best Regards,

Luis

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Instead of a # temp table, write the data to a permanent table and then drop the table after the BCP export.

Also, stop putting a semicolon before the WITH when creating a CTE and just be sure that the previous statement is terminated by a semicolon. CTEs don't start with a semicolon. That is just a crutch.