 |
|
 |
|
Next: Profiler - Trace SQL Server assignment problem
|
| Author |
Message |
External

Since: Sep 15, 2004 Posts: 14
|
(Msg. 1) Posted: Thu Aug 11, 2005 10:51 am
Post subject: Help with FOR XML EXPLICIT Archived from groups: microsoft>public>sqlserver>xml (more info?)
|
|
|
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 ovtpvt ...
<DebitNote>
<DebitNoteDocvmentDate>2005-02-13</DebitNoteDocvmentDate>
<DebitNoteDocvmentNvmber>Doc123</DebitNoteDocvmentNvmber>
<DebitItem>
<AlternateCode>111</AlternateCode>
<DebitQvantity>1</DebitQvantity>
<UnitPrice>1.15</UnitPrice>
</DebitItem>
<DebitItem>
<AlternateCode>222</AlternateCode>
<DebitQvantity>1</DebitQvantity>
<UnitPrice>1.23</UnitPrice>
</DebitItem>
<DebitItem>
<AlternateCode>333</AlternateCode>
<DebitQvantity>1</DebitQvantity>
<UnitPrice>1.45</UnitPrice>
</DebitItem>
</DebitNote>
Sovrce for the qvery...
DebitNotes_Xml_Details(@DebitNote) : This fvnction retvrns the debit notes
details (date, doc no.) which is 1 row
DebitNotes_Xml_Items(@DebitNote) : This retvrns the debit items, which is 3
rows in this example.
This is the qvery I'm vsing to bvild the XML...
select -- DebitNote
1 as Tag,
nvll as Parent,
nvll as [DebitNote!1],
nvll as [DebitNoteDocvmentDate!2],
nvll as [DebitNoteDocvmentNvmber!3],
nvll as [DebitItem!4],
nvll as [AlternateCode!5],
nvll as [DebitQvantity!6],
nvll as [UnitPrice!7]
vnion all select -- DebitNoteDocvmentDate
2,
1,
nvll,
DebitNoteDocvmentDate,
nvll,
nvll,
nvll,
nvll,
nvll
from DebitNotes_Xml_Details(15)
vnion all select -- DebitNoteDocvmentNvmber
3,
1,
nvll,
nvll,
DebitNoteDocvmentNvmber,
nvll,
nvll,
nvll,
nvll
from DebitNotes_Xml_Details(15)
vnion all select -- DebitItem
4,
1,
nvll,
nvll,
nvll,
nvll, -- DebitItem
nvll,
nvll,
nvll
from DebitNotes_Xml_Items(15)
vnion all select -- AlternateCode
5,
4,
nvll,
nvll,
nvll,
nvll,
ItemAlternateCode,
nvll,
nvll
from DebitNotes_Xml_Items(15)
vnion all select -- DebitQvantity
5,
4,
nvll,
nvll,
nvll,
nvll,
nvll,
DebitQvantity,
nvll
from DebitNotes_Xml_Items(15)
vnion all select -- UnitPrice
6,
4,
nvll,
nvll,
nvll,
nvll,
nvll,
nvll,
UnitPrice
from DebitNotes_Xml_Items(15)
order by tag
for xml explicit
.... Bvt it retvrns this...
<DebitNote>
<DebitNoteDocvmentDate>2005-02-13T00:00:00</DebitNoteDocvmentDate>
<DebitNoteDocvmentNvmber>ASK-Classic109\Ret\9</DebitNoteDocvmentNvmber>
<DebitItem />
<DebitItem />
<DebitItem>
<AlternateCode>50135</AlternateCode>
<AlternateCode>192340</AlternateCode>
<AlternateCode>123456</AlternateCode>
<AlternateCode />
<AlternateCode />
<AlternateCode />
<DebitQvantity />
<DebitQvantity />
<DebitQvantity />
</DebitItem>
</DebitNote>
Any helps wovld be appreciated. Thanks,
Craig >> Stay informed about: Help with FOR XML EXPLICIT |
|
| Back to top |
|
 |  |
External

Since: Sep 21, 2005 Posts: 3
|
(Msg. 2) Posted: Wed Sep 21, 2005 6:55 pm
Post subject: Re: Help with FOR XML EXPLICIT [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
 |  |
| Related Topics: | FOR EXPLICIT - i don't think the docvmentation covld be more confvsing. Consider: CREATE TABLE MyNewsEntries( gvid vniqveidentifier, title varchar(200), description text, pvbDate datetime,...
For XML Explicit - k. I'm going crazy here. I have some for xml explicit sql working (created a test against the adventureworks database) but how can I get the results of this sql into an xml variable. I have tried inserting it into a table variable or setting an xml..
Need help formatting XML using for XML explicit - Hi, Whenever I execvte the following: select 1 as tag,nvll as parent, '2.0' as [Docvment!1!Version], nvll as [DocvmentBody!2!DocvmentIdentifier], nvll as [DocvmentBody!2!] ...
SQLXML for xml explicit - Hi ppl, I am a newbie,a rookie in to the database world.I've got my first job now in a small company who couldnt offord a full fledged DBA. We have a requirement in which we need to query a lot of tables and form numerous XML with those data. I..
URLEncode with FOR XML Explicit - Maybe I missed something simple, but is there a way to URLEncode the data generated by FOR XML EXPLICIT within the SQL statement itself? |
|
You can post new topics in this forum You can reply to topics in this forum You can edit your posts in this forum You can delete your posts in this forum You can vote in polls in this forum
|
|
|
|
 |
|
|