[dba-SQLServer] FOR XML EXPLICIT

Data needs to go to an external party who have existing automated
process requiring data in this format. 

I'm currently in contact with them to see if this can be changed.

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
Charlotte Foust
Sent: Tuesday, 2 August 2005 1:52 AM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [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

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