"Craig HB" wrote:

> 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