[dba-SQLServer] FOR XML EXPLICIT

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...