Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Building XML and controlling Nodes


Building XML and controlling Nodes

Author
Message
luissantos
luissantos
Old Hand
Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)

Group: General Forum Members
Points: 319 Visits: 749
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



Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6766 Visits: 17739
You are almost there, look at this code, should get you over this hurdle
Cool

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
luissantos
Old Hand
Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)

Group: General Forum Members
Points: 319 Visits: 749
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



Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6766 Visits: 17739
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"

Cool
luissantos
luissantos
Old Hand
Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)

Group: General Forum Members
Points: 319 Visits: 749
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



Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6766 Visits: 17739
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:
Cool

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;


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search