[dba-SQLServer] FOR XML EXPLICIT

I don't see what purpose is served by the states and properties
entities.  Why do you need them at all?

Charlotte Foust


-----Original Message-----
From: Haslett, Andrew [mailto:andrew.haslett at ilc.gov.au] 
Sent: Monday, August 01, 2005 12:36 AM
To: dba-sqlserver at databaseadvisors.com
Subject: [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.
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com