Building XML and controlling Nodes

Question

Post reply

Building XML and controlling Nodes

luissantos

SSCertifiable

Points: 7391
More actions
April 21, 2014 at 9:48 am

#290422

Hello comunity
I have create TSQL query to output XML results, like that:
SELECT top 1 c.fdata,
c.fno,
c.nome,
c.morada,
c.local,
c.codpost,
(
SELECT fi.ftstamp,
fi.fno,
fi.ref,
fi.qtt,
fi.etiliquido
FROM FI
INNER JOIN ft ON fi.ftstamp = ft.ftstamp
WHERE fi.ftstamp = 'jm12081366002,349698778 '
FOR XML PATH('Line'),TYPE
) ,
(SELECT
fi.ftstamp,
fi.desconto
FROM FI
INNER JOIN ft ON fi.ftstamp = ft.ftstamp
WHERE fi.ftstamp = 'jm12081366002,349698778 '
FOR XML PATH('LineDiscount'),ROOT('Line') ,TYPE
) FROM ft c
where c.ftstamp = 'jm12081366002,349698778 '
FOR XML PATH('Document'),ROOT('VFPDATASET'),TYPE;
The atual result is
<VFPDATASET>
<Document>
<fdata>2012-08-13T00:00:00</fdata>
<fno>121005551</fno>
<nome>BEPPI SUISSE SARL </nome>
<morada>RUE DE LYON 74</morada>
<local> </local>
<codpost>1203 GENEVE - SUISSE</codpost>
<Line>
<ftstamp>jm12081366002,349698778 </ftstamp>
<fno>121005551</fno>
<ref>2111831</ref>
<qtt>8.000</qtt>
<etiliquido>111.600000</etiliquido>
</Line>
<Line>
<ftstamp>jm12081366002,349698778 </ftstamp>
<fno>121005551</fno>
<ref>2105140</ref>
<qtt>12.000</qtt>
<etiliquido>96.120000</etiliquido>
</Line>
...
Only on the end and for each line, they appear :
<Line>
<LineDiscount>
<ftstamp>jm12081366002,349698778 </ftstamp>
<desconto>10.00</desconto>
</LineDiscount>
<LineDiscount>
<ftstamp>jm12081366002,349698778 </ftstamp>
<desconto>10.00</desconto>
</LineDiscount>
<LineDiscount>
<ftstamp>jm12081366002,349698778 </ftstamp>
<desconto>10.00</desconto>
</LineDiscount>
<LineDiscount>
I have 2 problems:
1. i cannot able to put "LineDiscount" into the node "Line"Like:
<VFPDATASET>
<Document>
<fdata>2012-08-13T00:00:00</fdata>
<fno>121005551</fno>
<nome>BEPPI SUISSE SARL </nome>
<morada>RUE DE LYON 74</morada>
<local> </local>
<codpost>1203 GENEVE - SUISSE</codpost>
<Line>
<ftstamp>jm12081366002,349698778 </ftstamp>
<fno>121005551</fno>
<ref>2111831</ref>
<qtt>8.000</qtt>
<etiliquido>111.600000</etiliquido>
<LineDiscount>
<ftstamp>jm12081366002,349698778 </ftstamp>
<discount>10.00</discount>
</LineDiscount>
</Line>
......
2.Also the Result for TAG "Line" and "LineDiscount" are based on the same table "FI" (line of my onvoice)
3. the join clause from (Header invoice "FT") to the (line invoice "FI") are made by on field "FI.FTSTAMP".
How can i remove this field on the result of "Line" , and if is possible to create a SubTag "LineDiscount", how also to remove them.
CTE query can solve this issue.
Many Thanks
Luis Santos

Viewing 6 posts - 1 through 5 (of 5 total)

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

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 1

You are almost there, look at this code, should get you over this hurdle

😎

SELECT

SDH.SalesOrderID AS '@SalesOrderID'

,(SELECT

SD.OrderQty AS 'Product/@OrderQty'

,SD.CarrierTrackingNumber AS 'Product/ProductID/SalesOrderDetailID/@CarrierTrackingNumber'

,SD.ProductID AS 'Product/ProductID'

,SD. SalesOrderDetailID AS 'Product/ProductID/SalesOrderDetailID'

FROM Sales.SalesOrderDetail SD

WHERE SD.SalesOrderID = SDH.SalesOrderID

FOR XML PATH(''),TYPE) AS Products

FROM Sales.SalesOrderHeader SDH

WHERE SDH.SalesOrderID = 44115

FOR XML PATH('SalesOrder'),TYPE, ROOT('SalesOrders');

Result

<SalesOrders>

<SalesOrder SalesOrderID="44115">

<Products>

<Product OrderQty="2">

<ProductID>

<SalesOrderDetailID CarrierTrackingNumber="5360-4C42-90" />777<SalesOrderDetailID>1815</SalesOrderDetailID></ProductID>

</Product>

<Product OrderQty="1">

<ProductID>

<SalesOrderDetailID CarrierTrackingNumber="5360-4C42-90" />747<SalesOrderDetailID>1816</SalesOrderDetailID></ProductID>

</Product>

<Product OrderQty="1">

<ProductID>

<SalesOrderDetailID CarrierTrackingNumber="5360-4C42-90" />743<SalesOrderDetailID>1817</SalesOrderDetailID></ProductID>

</Product>

<Product OrderQty="3">

<ProductID>

<SalesOrderDetailID CarrierTrackingNumber="5360-4C42-90" />707<SalesOrderDetailID>1818</SalesOrderDetailID></ProductID>

</Product>

<Product OrderQty="1">

<ProductID>

<SalesOrderDetailID CarrierTrackingNumber="5360-4C42-90" />708<SalesOrderDetailID>1819</SalesOrderDetailID></ProductID>

</Product>

<Product OrderQty="3">

<ProductID>

<SalesOrderDetailID CarrierTrackingNumber="5360-4C42-90" />775<SalesOrderDetailID>1820</SalesOrderDetailID></ProductID>

</Product>

<Product OrderQty="2">

<ProductID>

<SalesOrderDetailID CarrierTrackingNumber="5360-4C42-90" />771<SalesOrderDetailID>1821</SalesOrderDetailID></ProductID>

</Product>

<Product OrderQty="2">

<ProductID>

<SalesOrderDetailID CarrierTrackingNumber="5360-4C42-90" />716<SalesOrderDetailID>1822</SalesOrderDetailID></ProductID>

</Product>

<Product OrderQty="7">

<ProductID>

<SalesOrderDetailID CarrierTrackingNumber="5360-4C42-90" />774<SalesOrderDetailID>1823</SalesOrderDetailID></ProductID>

</Product>

<Product OrderQty="1">

<ProductID>

<SalesOrderDetailID CarrierTrackingNumber="5360-4C42-90" />748<SalesOrderDetailID>1824</SalesOrderDetailID></ProductID>

</Product>

<Product OrderQty="4">

<ProductID>

<SalesOrderDetailID CarrierTrackingNumber="5360-4C42-90" />778<SalesOrderDetailID>1825</SalesOrderDetailID></ProductID>

</Product>

<Product OrderQty="3">

<ProductID>

<SalesOrderDetailID CarrierTrackingNumber="5360-4C42-90" />742<SalesOrderDetailID>1826</SalesOrderDetailID></ProductID>

</Product>

<Product OrderQty="2">

<ProductID>

<SalesOrderDetailID CarrierTrackingNumber="5360-4C42-90" />712<SalesOrderDetailID>1827</SalesOrderDetailID></ProductID>

</Product>

<Product OrderQty="2">

<ProductID>

<SalesOrderDetailID CarrierTrackingNumber="5360-4C42-90" />773<SalesOrderDetailID>1828</SalesOrderDetailID></ProductID>

</Product>

<Product OrderQty="2">

<ProductID>

<SalesOrderDetailID CarrierTrackingNumber="5360-4C42-90" />741<SalesOrderDetailID>1829</SalesOrderDetailID></ProductID>

</Product>

<Product OrderQty="2">

<ProductID>

<SalesOrderDetailID CarrierTrackingNumber="5360-4C42-90" />711<SalesOrderDetailID>1830</SalesOrderDetailID></ProductID>

</Product>

<Product OrderQty="9">

<ProductID>

<SalesOrderDetailID CarrierTrackingNumber="5360-4C42-90" />772<SalesOrderDetailID>1831</SalesOrderDetailID></ProductID>

</Product>

<Product OrderQty="2">

<ProductID>

<SalesOrderDetailID CarrierTrackingNumber="5360-4C42-90" />745<SalesOrderDetailID>1832</SalesOrderDetailID></ProductID>

</Product>

<Product OrderQty="2">

<ProductID>

<SalesOrderDetailID CarrierTrackingNumber="5360-4C42-90" />714<SalesOrderDetailID>1833</SalesOrderDetailID></ProductID>

</Product>

</Products>

</SalesOrder>

</SalesOrders>

luissantos SSCertifiable Points: 7391 More actions · Answer 2

Hello Eirikur

Thanks for your Reply, i will go to try understand them and try.

also i solve the problema like this:

SELECT top 1 c.fdata,

c.fno,

c.nome,

c.morada,

c.local,

c.codpost,

(

SELECT --fi.ftstamp,

--fi.fno,

fi.ref,

fi.qtt,

fi.etiliquido ,

(SELECT fii.desconto from fi fii

WHERE fii.ftstamp = 'jm12081366002,349698778 '

AND fii.ref = fi.ref AND fii.fistamp = fi.fistamp FOR XML PATH('Linediscount'),TYPE)

FROM FI

INNER JOIN ft ON fi.ftstamp = ft.ftstamp

WHERE fi.ftstamp = 'jm12081366002,349698778 '

FOR XML PATH('Line'),TYPE

)

--,

--(SELECT

--fi.ftstamp,

--fi.desconto

-- FROM FI

--INNER JOIN ft ON fi.ftstamp = ft.ftstamp

--WHERE fi.ftstamp = 'jm12081366002,349698778 '

--FOR XML PATH('LineDiscount'),ROOT('Line') ,TYPE

--)

FROM ft c

where c.ftstamp = 'jm12081366002,349698778 '

FOR XML PATH('Document'), ROOT('VFPDataSet'),TYPE;

Now, for finish the output, i need to place on the beginning these 2 lines:

<?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>

Note : that the end of my XML file must stay like this:

</VFPDataSet>

Can you help me, based on my query, how can do that.

Many thanks for your reply

Beste regards

Luis Santos

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 3

If you are going to switch the encoding from UTF-16 to Windows-1252, you will have to work with the final output as NVARCHAR(MAX) as it cannot be cast back to SQL Server XML data type.

AdventureWorks2012 sample:

DECLARE @XML XML = N'';

DECLARE @XMLSTR NVARCHAR(MAX) = N'';

SELECT @XML = (

SELECT

SDH.SalesOrderID AS '@SalesOrderID'

,(SELECT

SD.OrderQty AS 'Product/@OrderQty'

,SD.CarrierTrackingNumber AS 'Product/ProductID/SalesOrderDetailID/@CarrierTrackingNumber'

,SD.ProductID AS 'Product/ProductID'

,SD. SalesOrderDetailID AS 'Product/ProductID/SalesOrderDetailID'

FROM Sales.SalesOrderDetail SD

WHERE SD.SalesOrderID = SDH.SalesOrderID

FOR XML PATH(''),TYPE) AS Products

FROM Sales.SalesOrderHeader SDH

WHERE SDH.SalesOrderID = 44116

FOR XML PATH('SalesOrder'),TYPE, ROOT('SalesOrders'));

select @XMLSTR = N'<?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>

<VFPDataSet xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="C:\PHC15\AddTableToSchema_Nest.xsd">' + CAST(@XML AS NVARCHAR(MAX))

Also, the Schema Location should not be a local path, xsi:noNamespaceSchemaLocation="C:\PHC15\AddTableToSchema_Nest.xsd"

😎

luissantos SSCertifiable Points: 7391 More actions · Answer 4

Hello Eirikur

Thanks for your reply, but when i run your script on Adventureworks2012 database, i dont see the xml output, i see only the message:

Command complete sucessfully

Also, i need the information by node and not in line, for exemple when i run only this part os script:

SELECT

SDH.SalesOrderID AS '@SalesOrderID'

,(SELECT

SD.OrderQty AS 'Product/@OrderQty'

,SD.CarrierTrackingNumber AS 'Product/ProductID/SalesOrderDetailID/@CarrierTrackingNumber'

,SD.ProductID AS 'Product/ProductID'

,SD. SalesOrderDetailID AS 'Product/ProductID/SalesOrderDetailID'

FROM Sales.SalesOrderDetail SD

WHERE SD.SalesOrderID = SDH.SalesOrderID

FOR XML PATH(''),TYPE) AS Products

FROM Sales.SalesOrderHeader SDH

WHERE SDH.SalesOrderID = 44116

FOR XML PATH('SalesOrder'),TYPE, ROOT('SalesOrders')

Output:

</Product>

</Product>

</Product>

</Product>

</Product>

</Product>

</Products>

</SalesOrder>

</SalesOrders>

i wait for your reply and many thanks

Best Regards

Luis Santos

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 5

Just remove the @ from the column aliases to get the result as a node.

As I said in the previous post, switching encoding is not supported so the output will have to be a character string representation of the xml:

😎

DECLARE @XML XML = N'';

DECLARE @XMLSTR NVARCHAR(MAX) = N'';

SELECT @XML = (

SELECT

SDH.SalesOrderID AS 'SalesOrderID'

,(SELECT

SD.OrderQty AS 'Product/OrderQty'

,SD.CarrierTrackingNumber AS 'Product/ProductID/SalesOrderDetailID/CarrierTrackingNumber'

,SD.ProductID AS 'Product/ProductID'

,SD. SalesOrderDetailID AS 'Product/ProductID/SalesOrderDetailID'

FROM Sales.SalesOrderDetail SD

WHERE SD.SalesOrderID = SDH.SalesOrderID

FOR XML PATH(''),TYPE) AS Products

FROM Sales.SalesOrderHeader SDH

WHERE SDH.SalesOrderID = 44116

FOR XML PATH('SalesOrder'),TYPE, ROOT('SalesOrders'));

select @XMLSTR = N'<?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>

<VFPDataSet xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="C:\PHC15\AddTableToSchema_Nest.xsd">' + CAST(@XML AS NVARCHAR(MAX))

SELECT @XMLSTR;