[dba-SQLServer] FOR XML EXPLICIT

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.