| Craig HB 2005-08-11, 1:26 pm |
| 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>
< DebitNoteDocumentDat
e>2005-02-13</ DebitNoteDocumentDat
e>
< DebitNoteDocumentNum
ber>Doc123</ DebitNoteDocumentNum
ber>
<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_Detai
ls(@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 & #91;DebitNoteDocumen
tDate!2],
null as & #91;DebitNoteDocumen
tNumber!3],
null as [DebitItem!4],
null as & #91;AlternateCode!5]
,
null as & #91;DebitQuantity!6]
,
null as [UnitPrice!7]
union all select -- DebitNoteDocumentDat
e
2,
1,
null,
DebitNoteDocumentDat
e,
null,
null,
null,
null,
null
from DebitNotes_Xml_Detai
ls(15)
union all select -- DebitNoteDocumentNum
ber
3,
1,
null,
null,
DebitNoteDocumentNum
ber,
null,
null,
null,
null
from DebitNotes_Xml_Detai
ls(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>
< DebitNoteDocumentDat
e>2005-02-13T00:00:00</ DebitNoteDocumentDat
e>
< DebitNoteDocumentNum
ber>ASK-Classic109\Ret\9</ DebitNoteDocumentNum
ber>
<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
|