Welcome to dbForumz.com!
FAQFAQ    SearchSearch      ProfileProfile    Private MessagesPrivate Messages   Log inLog in

Help with FOR XML EXPLICIT

 
   Database Forums (Home) -> XML RSS
Next:  Profiler - Trace SQL Server assignment problem  
Author Message
Craig HB2

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
Login to vote
Alwik

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?)

Hi
if you still looking for an answer - add fields to order by - tag is not
enough

Tomik

 >> Stay informed about: Help with FOR XML EXPLICIT 
Back to top
Login to vote
Display posts from previous:   
Related Topics:
FOR EXPLICIT - i don't think the doc&#118;mentation co&#118;ld be more conf&#118;sing. Consider: CREATE TABLE MyNewsEntries( g&#118;id &#118;niq&#118;eidentifier, title varchar(200), description text, p&#118;bDate 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 exec&#118;te the following: select 1 as tag,n&#118;ll as parent, '2.0' as [Doc&#118;ment!1!Version], n&#118;ll as [Doc&#118;mentBody!2!Doc&#118;mentIdentifier], n&#118;ll as [Doc&#118;mentBody!2!] &#11...

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?
   Database Forums (Home) -> XML All times are: Pacific Time (US & Canada)
Page 1 of 1

 
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



[ Contact us | Terms of Service/Privacy Policy ]