[dba-SQLServer] FOR XML EXPLICIT

Hey all.

I need to output a query in a given XML format and I figure I'd have a
stab using the FOR XML clause of SQL 2000 first rather than in code.

I've worked out the basics using the FOR XML EXPLICIT clause and am able
to output a structure such as this:

<state id="SA">
	<property id="5">
		<name>Prop1</name>
		<area>35</area>
	</property>
	<property id="10">
		<name>Prop2</name>
		<area>55</area>
	</property>
</state>
<state id="NSW">
	<property id="24">
	....
</state>


However I haven't worked out how to add 'empty' surrounding tags (I
think they may be called 'associations' in XML speak), so the schema
would become:

(note the addition of the <states> and <properties> tags)

<states>
	<state id="SA">
		<properties>
			<property id="5">
				<name>Prop1</name>
				<area>35</area>
			</property>
			<property id="10">
				<name>Prop2</name>
				<area>55</area>
			</property>
		</properties>	
	</state>
	<state id="NSW">
		<properties>
			<property id="24">
			....
		</properties>
	</state>
</states>

(apologies if the formatting doesn't stick)

Any ideas? Anyone familiar with using XML in SS?

For reference, my actual current query is below - I figured the above
example was easier to use.

SELECT  
	1             		as Tag, 
	NULL          		as Parent,
	c.textstate		as [state!1!idstate],
	null			as [property!2!name!element],
	null			as [property!2!areaHA!element],
	null			as [property!2!dateGranted!element],
	null	 		as
[property!2!titleHoldingBody!element],
	null 			as [property!2!idproperty]
FROM         
	dbo.tblStates c 
Where 
	c.IDState<>0
union all
SELECT  
	2,             		 
	1,          		
	b.textstate,
	a.ShortLandName,		
	a.area,			
	a.GrantDate,		
	a.THBName,
	a.IDProperty
FROM         
	dbo.tblStates b, dbo.vw_LandPurchases_WebsiteExport_Prop a
where 
	a.IDState = b.IDState
Order By
	[state!1!idstate],[property!2!idproperty]
for xml explicit

Cheers,
Andrew


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.