FOR XML EXPLICIT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

FOR XML EXPLICIT

Author

Topic

Bunce
Starting Member

13 Posts

Posted - 2005-08-01 : 03:48:13

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

735 Posts

Posted - 2005-08-01 : 06:21:38

See this link: [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=52721[/url] 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

Bunce
Starting Member

13 Posts

Posted - 2005-08-01 : 07:33:58

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?

Kristen
Test

22859 Posts

Posted - 2005-08-01 : 08:37:10

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

Bunce
Starting Member

13 Posts

Posted - 2005-08-01 : 20:20:07

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

Kristen
Test

22859 Posts

Posted - 2005-08-02 : 00:15:40

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

Bunce
Starting Member

13 Posts

Posted - 2005-08-02 : 02:05:41

Good idea!

Thanks again for your help.

Cheers,
A

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources