Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Building XML and controlling Nodes Expand / Collapse
Author
Message
Posted Monday, April 21, 2014 9:48 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 5:11 PM
Points: 266, Visits: 560
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



Post #1563532
Posted Monday, April 21, 2014 11:13 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:57 AM
Points: 1,953, Visits: 5,083
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>

Post #1563567
Posted Monday, April 21, 2014 11:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 5:11 PM
Points: 266, Visits: 560
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"?>
<VFPDataSet xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="C:\PHC15\AddTableToSchema_Nest.xsd">

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



Post #1563574
Posted Tuesday, April 22, 2014 3:00 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:57 AM
Points: 1,953, Visits: 5,083
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"

Post #1563726
Posted Tuesday, April 22, 2014 4:08 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 5:11 PM
Points: 266, Visits: 560
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:

<SalesOrders>
<SalesOrder SalesOrderID="44116">
<Products>
<Product OrderQty="2">
<ProductID>
<SalesOrderDetailID CarrierTrackingNumber="1D67-419C-A1" />709<SalesOrderDetailID>1834</SalesOrderDetailID></ProductID>
</Product>
<Product OrderQty="2">
<ProductID>
<SalesOrderDetailID CarrierTrackingNumber="1D67-419C-A1" />772<SalesOrderDetailID>1835</SalesOrderDetailID></ProductID>
</Product>
<Product OrderQty="1">
<ProductID>
<SalesOrderDetailID CarrierTrackingNumber="1D67-419C-A1" />771<SalesOrderDetailID>1836</SalesOrderDetailID></ProductID>
</Product>
<Product OrderQty="3">
<ProductID>
<SalesOrderDetailID CarrierTrackingNumber="1D67-419C-A1" />777<SalesOrderDetailID>1837</SalesOrderDetailID></ProductID>
</Product>
<Product OrderQty="1">
<ProductID>
<SalesOrderDetailID CarrierTrackingNumber="1D67-419C-A1" />778<SalesOrderDetailID>1838</SalesOrderDetailID></ProductID>
</Product>
<Product OrderQty="3">
<ProductID>
<SalesOrderDetailID CarrierTrackingNumber="1D67-419C-A1" />773<SalesOrderDetailID>1839</SalesOrderDetailID></ProductID>
</Product>
</Products>
</SalesOrder>
</SalesOrders>

i wait for your reply and many thanks
Best Regards
Luis Santos



Post #1563739
Posted Tuesday, April 22, 2014 4:24 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:57 AM
Points: 1,953, Visits: 5,083
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;

Post #1563750
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse