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
Go to Top of Page

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?
Go to Top of Page

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
Go to Top of Page

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)..
Go to Top of Page

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
Go to Top of Page

Bunce
Starting Member

13 Posts

Posted - 2005-08-02 : 02:05:41
Good idea!

Thanks again for your help.

Cheers,
A
Go to Top of Page
   

- Advertisement -