I have two table as given below.
OrderHeader
PKOrderHeader CustomerCode DocumentRef SiteCode
1 JOE TEST1 TH
2 POL TEST2 CO
3 GEO TEST3 KH
OrderDetails
FKOrderHeader ProductCode RotationLineNo
1 PRD1 1
1 PRD2 2
2 PRD3 2
3 PRD4 3
I need to get the XML string as below after converting the table data as XML string
< ORDERS>
<SO>
<HD>
< PKOrderHeader >1< /PKOrderHeader >
< CustomerCode > JOE < /CustomerCode >>
</HD>
<HO>
< DocumentRef > TEST1 < / DocumentRef >
< SiteCode > TH< / SiteCode >>
</HO>
<LO>
< FKOrderHeader >1< FKOrderHeader >
< ProductCode > PRD1 < ProductCode >
< RotationLineNo > 1 < RotationLineNo >
</LO>
<LO>
< FKOrderHeader >1< FKOrderHeader >
< ProductCode > PRD2 < ProductCode >
< RotationLineNo > 2< RotationLineNo >
</LO>
</SO>
<SO>
<HD>
< PKOrderHeader >2< /PKOrderHeader >
< CustomerCode > POL < /CustomerCode >>
</HD>
<HO>
< DocumentRef > TEST2 < / DocumentRef >
< SiteCode > CO< / SiteCode >>
</HO>
<LO>
< FKOrderHeader >2< FKOrderHeader >
< ProductCode > PRD2 < ProductCode >
< RotationLineNo > 2 < RotationLineNo >
</LO>
</SO>
<SO>
<HD>
< PKOrderHeader >3< /PKOrderHeader >
< CustomerCode > GOE < /CustomerCode >>
</HD>
<HO>
< DocumentRef > TEST3 < / DocumentRef >
< SiteCode > KH< / SiteCode >>
</HO>
<LO>
< FKOrderHeader >3< FKOrderHeader >
< ProductCode > PRD3 < ProductCode >
< RotationLineNo > 3 < RotationLineNo >
</LO>
</SO>
< /ORDERS>
The query that I used to generate the XML string is as given
SELECT (SELECT PKOrderHeader, CustomerCode FROM #OrderHeader FOR XML PATH(''), TYPE) AS HD, (SELECT DocumentRef, SiteCode FROM #OrderHeader FOR XML PATH(''), TYPE) AS HO,
(SELECT FKOrderHeader, ProductCode, RotationLineNo FROM #OrderDetail FOR XML PATH(''), TYPE) AS LO FOR XML PATH('SO'), ROOT('ORDERS')
But when I generated the XML string I am getting only the Single rows data as XML string as liken given below. Also the LO section is also not showing the multiple rows.
< ORDERS>
<SO>
<HD>
< PKOrderHeader >1< /PKOrderHeader >
< CustomerCode > JOE < /CustomerCode >>
</HD>
<HO>
< DocumentRef > TEST1 < / DocumentRef >
< SiteCode > TH< / SiteCode >>
</HO>
<LO>
< FKOrderHeader >1< FKOrderHeader >
< ProductCode > PRD1 < ProductCode >
< RotationLineNo > 1 < RotationLineNo >
</LO>
</SO>
< /ORDERS>
So can anyone help me to get multiple row data as XML string
Aucun commentaire:
Enregistrer un commentaire