Francisco Tapia
fhtapia at gmail.com
Mon Aug 15 23:05:53 CDT 2005
That link has all the answers, and a cleaner way to write this query from hell ;). Thanks for the tip On 8/12/05, Haslett, Andrew <andrew.haslett at ilc.gov.au> wrote: > > Wow - two FOR XML EXPLICIT questions in 2 weeks! > > Had the same problem - basically you want to add the surrounding empty > elements of <wsorder> and <wsorderitems>? > > The root one isn't too hard to do. The other one seems a bit tricker > but according to those who helped me in the below post, should be doable > by adding another UNION statement as you mentioned: > http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53050 > > Post back if these don't help you out. > > Cheers, > Andrew > > -----Original Message----- > From: dba-sqlserver-bounces at databaseadvisors.com > [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of > Francisco Tapia > Sent: Saturday, 13 August 2005 2:33 AM > To: SQL Server 2k List; dba-sqlserver at databaseadvisors.com > Subject: [dba-SQLServer] FOR XML EXPLICIT > > Happy Friday to all, > This is not really that long, but I've included code to help simplfy > what I'm talking about... > > I have the following FOR XML Select, it produces the a valid XML object. > > SELECT Top 1 > 1 as TAG, > 0 AS Parent, > PONUM [Header!1!PONUM!element], > RRDate [Header!1!RRDate!element], > NULL [OrderItems!2!CPN!element], > NULL [OrderItems!2!AddedDate!element] > FROM tbl_RR WHERE PONUM = '50803-01HB' > > UNION ALL > > SELECT 2 AS TAG, > 1 AS Parent, > R.PONUM [OrderItems!2!PONUM!element], > NULL, > CPN [OrderItems!2!CPN!element], > AddedDate [OrderItems!2!AddedDate!element] FROM tbl_RR R INNER JOIN > tbl_RRLineItems RL ON R.RRID = RL.RRID WHERE R.PONUM = '50803-01HB' > FOR XML EXPLICIT > > The XML object looks like this: > <Header> > <PONUM>5080301HB</PONUM> > <RRDate>20050810T07:51:40.633</RRDate> > <OrderItems> > <CPN>925</CPN> > <AddedDate>20050810T07:51:40.680</AddedDate> > </OrderItems> > </Header> > > This is almost where I need this, the xml that I'm trying to match is > formated in this manner: > <WSOrder> > <Header> > <PONUM>5080301HB</PONUM> > <RRDate>20050810T07:51:40.633</RRDate> > </Header> > <OrderItems> > <WSOrderItem> > <CPN>925</CPN> > <AddedDate>20050810T07:51:40.680</AddedDate> > </WSOrderItem> > </OrderItems> > </WSOrder> > > Since I'm almost there, I'm sure I just need to modify my Select (For > XML) with maybe 2 more union alls, but I was wondering if anyone has > done it this way before and could lend a pointer in the right direction > :) > > Thanks, > -- > -Francisco > http://pcthis.blogspot.com |PC news with out the jargon! > http://sqlthis.blogspot.com | Tsql and More... > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > > > IMPORTANT - PLEASE READ *** This email any any files transmitted with it > are confidential and may contain information protected by law from > disclosure. If you have received this message in error, please notify the > sender immediately and delete this email from your system. No warranty is > given that this email or files, if attached to this email, are free from > computer viruses or other defects. They are provided on the basis the user > assumes all responsibility for loss, damage or consequence resulting > directly or indirectly from their use, whether caused by the negligence of > the sender or not. > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > -- -Francisco http://pcthis.blogspot.com |PC news with out the jargon! http://sqlthis.blogspot.com | Tsql and More...