Need to Close tag for each line on document to create my XML File

• luissantos

  SSCertifiable

  Points: 7391

  More actions

  June 22, 2019 at 9:31 pm

  #3656530

  Hello community,

  I have a little problem to solve, above i send my script:

  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
  FROM BO
  CROSS APPLY
  (
  SELECT distinct
  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_lines fil
  WHERE fil.bostamp = bo.bostamp
  AND fil.qtt <> 0 AND fil.ettdeb <> 0
  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;
  
  

  In fact i can produce my XML File like this :

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

  But i need for each line of my document opening TAG <OrderLine> and closing TAG </OrderLine> like that:

  <?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>300.000000</GrossValue>
  </OrderTotal>
  <OrderLine>  --Opening first line of my document
  <Product>
  <SuppliersProductCode>A001</SuppliersProductCode>
  <Description>Adufe</Description>
  </Product>
  <Price>
  <UnitPrice>100.000000</UnitPrice>
  </Price>
  <LineTotal>100.000000</LineTotal>
  <Quantity>
  <Amount>1.0000</Amount>
  </Quantity>
  <LineTax>
  <Taxrate>23.00</Taxrate>
  </LineTax>
  </OrderLine> -- Closing the first line of my document
  
  <OrderLine>  --Opening Second line of my document
  <Product>
  <SuppliersProductCode>B001</SuppliersProductCode>
  <Description>Bombos</Description>
  </Product>
  <Price>
  <UnitPrice>200.000000</UnitPrice>
  </Price>
  <LineTotal>200.000000</LineTotal>
  <Quantity>
  <Amount>1.0000</Amount>
  </Quantity>
  <LineTax>
  <Taxrate>23.00</Taxrate>
  </LineTax>
  </OrderLine> -- Closing the Second line of my document
  </Order>

  could someone help me to solve this issue.

  Many thanks,

  Luis

   

• Mike01

  SSChampion

  Points: 11395

  More actions

  June 22, 2019 at 11:21 pm

  #3656573

  Can you provide ddl and sample data.  We don't have your tables and data

  For better, quicker answers, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/