|
podgehb
Starting Member
18 Posts |
 Posted - 2005-08-11 : 13:59:20
|
| I am try to create an XML doc that is a debit note consisting of many debit items (3 in this example). This is the desired output ... <DebitNote> <DebitNoteDocumentDate>2005-02-13</DebitNoteDocumentDate> <DebitNoteDocumentNumber>Doc123</DebitNoteDocumentNumber> <DebitItem> <AlternateCode>111</AlternateCode> <DebitQuantity>1</DebitQuantity> <UnitPrice>1.15</UnitPrice> </DebitItem> <DebitItem> <AlternateCode>222</AlternateCode> <DebitQuantity>1</DebitQuantity> <UnitPrice>1.23</UnitPrice> </DebitItem> <DebitItem> <AlternateCode>333</AlternateCode> <DebitQuantity>1</DebitQuantity> <UnitPrice>1.45</UnitPrice> </DebitItem> </DebitNote> Source for the query... DebitNotes_Xml_Details(@DebitNote) : This function returns the debit notes details (date, doc no.) which is 1 row DebitNotes_Xml_Items(@DebitNote) : This returns the debit items, which is 3 rows in this example. This is the query I'm using to build the XML... select -- DebitNote 1 as Tag, null as Parent, null as [DebitNote!1], null as [DebitNoteDocumentDate!2], null as [DebitNoteDocumentNumber!3], null as [DebitItem!4], null as [AlternateCode!5], null as [DebitQuantity!6], null as [UnitPrice!7] union all select -- DebitNoteDocumentDate 2, 1, null, DebitNoteDocumentDate, null, null, null, null, null from DebitNotes_Xml_Details(15) union all select -- DebitNoteDocumentNumber 3, 1, null, null, DebitNoteDocumentNumber, null, null, null, null from DebitNotes_Xml_Details(15) union all select -- DebitItem 4, 1, null, null, null, null, -- DebitItem null, null, null from DebitNotes_Xml_Items(15) union all select -- AlternateCode 5, 4, null, null, null, null, ItemAlternateCode, null, null from DebitNotes_Xml_Items(15) union all select -- DebitQuantity 5, 4, null, null, null, null, null, DebitQuantity, null from DebitNotes_Xml_Items(15) union all select -- UnitPrice 6, 4, null, null, null, null, null, null, UnitPrice from DebitNotes_Xml_Items(15) order by tag for xml explicit ... But it returns this... <DebitNote> <DebitNoteDocumentDate>2005-02-13T00:00:00</DebitNoteDocumentDate> <DebitNoteDocumentNumber>ASK-Classic109\Ret\9</DebitNoteDocumentNumber> <DebitItem /> <DebitItem /> <DebitItem> <AlternateCode>50135</AlternateCode> <AlternateCode>192340</AlternateCode> <AlternateCode>123456</AlternateCode> <AlternateCode /> <AlternateCode /> <AlternateCode /> <DebitQuantity /> <DebitQuantity /> <DebitQuantity /> </DebitItem> </DebitNote> Any helps would be appreciated. Thanks, Craig |
|
Help with FOR XML EXPLICIT