Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  problem in creating XML using For XML explicit

Author  Topic  sesh Starting Member 2 Posts Posted - 2002-04-15 : 07:32:54 I am trying to generate an XML in SqlServer using for XML Explicit using universal tables. The XML that was generated is of different format. kindly help. The Required Structure: <market cid,pno> <Detail no> <invoice InvNO,Date,Amount> </invoice> </Detail> </Market> but the structure that was generated by below sql is <market cid,pno/> <Detail no/> <invoice InvNO,Date,Amount/> SQL Query SELECT 1 as Tag, NULL as Parent, C.Company as [market!1!cid], C.ponumber as [market!1!Pno], NULL as [Detail!2!no], NULL as [Invoice!3!InvNo], NULL as [Invoice!3!Date], NULL as [Invoice!3!Amount] FROM PurchaseOrder C UNION ALL SELECT 2 as Tag, 1 as Parent, C.Company, NULL, O.LineItemNo, NULL, NULL, NULL FROM PurchaseOrder C, PODetail O WHERE C.Company = O.Company and C.PONumber = O.PONumber UNION ALL SELECT 3 as Tag, 2 as Parent, C.Company, NULL, O.LineItemNo, OD.InvoiceNo, OD.InvoiceDate, OD.InvoiceAmount FROM PurchaseOrder C, PODetail O, InvoiceDetail OD WHERE C.Company = O.Company and C.PONumber = O.PONumber and O.Company = OD.Company and O.PONumber = OD.PONumber and O.LineItemNo = OD.LineItemNo and C.Company =1050 ORDER BY [market!1!cid], [Detail!2!no] FOR XML EXPLICIT