SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 FOR XML EXPLICIT
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Bunce
Starting Member

Australia
13 Posts

Posted - 08/01/2005 :  03:48:13  Show Profile  Reply with Quote
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>


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

mwjdavidson
Aged Yak Warrior

United Kingdom
735 Posts

Posted - 08/01/2005 :  06:21:38  Show Profile  Reply with Quote
See this link: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=52721 for how to include a single root.
The properties element is similar except that you must include and order by the state id in this select.

Mark
Go to Top of Page

Bunce
Starting Member

Australia
13 Posts

Posted - 08/01/2005 :  07:33:58  Show Profile  Reply with Quote
Cheers Mark.

I'm not sure I follow though...

Where in that example did you actually specify the phrase <stages>?

How can you specify different column aliases in a UNION query? Don't they have to be indentical?
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 08/01/2005 :  08:37:10  Show Profile  Reply with Quote
I think the conventional wisdom is that your wrap it in a "rather empty" UNION ALL

USE Northwind
GO
SELECT	1	AS Tag, 
	NULL	AS Parent,
	NULL	AS [Customer!2!CustomerID],
	NULL	AS [Order!3!OrderID],
	0	AS [root!1!Customer!hide]
UNION ALL
SELECT	2			AS Tag, 
	1			AS Parent,
	Customers.CustomerID	AS [Customer!2!CustomerID],
	NULL			AS [Order!3!OrderID],
	1			AS [root!1!Customer!hide]
FROM Customers
UNION ALL
SELECT	3			AS Tag,  
	2			AS Parent,
	Customers.CustomerID	AS [Customer!2!CustomerID],
	Orders.OrderID		AS [Order!3!OrderID],
	1			AS [root!1!Customer!hide]
FROM	Customers
	JOIN Orders
		ON Orders.CustomerID = Customers.CustomerID
ORDER BY [root!1!Customer!hide], [Customer!2!CustomerID], [Order!3!OrderID]
FOR XML EXPLICIT

Kristen

Edited by - Kristen on 08/01/2005 08:37:34
Go to Top of Page

Bunce
Starting Member

Australia
13 Posts

Posted - 08/01/2005 :  20:20:07  Show Profile  Reply with Quote
Awesome. Thanks or that - worked fine.

So the root works OK, but I'm not sure I can add another select inbetween state and property as wouldn't it break the parent/child link between the two?

In the Northwind example above it would mean adding a third select between Customers and Orders (ie - an <orders> tag)..
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 08/02/2005 :  00:15:40  Show Profile  Reply with Quote
You should be able to stick another UNION in that JOINS to the Orders table, but doesn't return any Order data, and then change the existing Level 3 stuff to become Level 4

That will involve adding a new Column to all the UNIONs :-(

When we build stuff like this in development we number them all with gaps of 10 and put a comment marker at the end of each column list so that we can easily add new [blank] columns to all the UNIONs with a global Find&Replace

Kristen
Go to Top of Page

Bunce
Starting Member

Australia
13 Posts

Posted - 08/02/2005 :  02:05:41  Show Profile  Reply with Quote
Good idea!

Thanks again for your help.

Cheers,
A
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000