dimanche 26 avril 2015

Multiple rows are not showing when we convert the table data into XML in SQL

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