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,
AndrewYou can do it by adding another tag (and therefore another UNION) to your
query in which the empty "container" element is craeted by selecting NULL as
shown in the following example from Northwind.
An alternative approach would be to use an annotated schema with a
sql:is-constant annotation.
cheers,
Graeme
sample code
--
Use Northwind
SELECT 1 AS Tag,
NULL AS Parent,
NULL AS [Invoices!1],
NULL AS [Invoice!2!InvoiceNo],
NULL AS [Invoice!2!Date],
NULL AS [Item!3!Product],
NULL AS [Item!3!Price!element]
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
NULL,
OrderID,
OrderDate,
NULL,
NULL
FROM Orders
UNION ALL
SELECT 3,
2,
NULL,
O.OrderID,
NULL,
P.ProductName,
OD.UnitPrice
FROM Orders O JOIN [Order Details] OD
ON O.OrderID = OD.OrderID
JOIN Products P
ON OD.ProductID = P.ProductID
ORDER BY [Invoice!2!InvoiceNo], [Item!3!Product]
FOR XML EXPLICIT
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"Bunce" <Bunce@.discussions.microsoft.com> wrote in message
news:8A76BF8A-8A30-472A-89B3-AA6068AF5701@.microsoft.com...
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|||Note that this becomes much easier in SQL Server 2005 (see
http://msdn.microsoft.com/library/e...l/forxml2k5.asp). Note
that I find such wrapper elements to be of questionable value when they only
provide an additional level of indirection in your tree. Since XPath has
list semantics, /Invoice already gives you all invoices. If you add
additional properties to Invoices such as summary information, it makes
sense.
Best regards
Michael
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:OQmoBJtlFHA.1204@.TK2MSFTNGP12.phx.gbl...
> You can do it by adding another tag (and therefore another UNION) to your
> query in which the empty "container" element is craeted by selecting NULL
> as
> shown in the following example from Northwind.
> An alternative approach would be to use an annotated schema with a
> sql:is-constant annotation.
> cheers,
> Graeme
> sample code
> --
> Use Northwind
> SELECT 1 AS Tag,
> NULL AS Parent,
> NULL AS [Invoices!1],
> NULL AS [Invoice!2!InvoiceNo],
> NULL AS [Invoice!2!Date],
> NULL AS [Item!3!Product],
> NULL AS [Item!3!Price!element]
> UNION ALL
> SELECT 2 AS Tag,
> 1 AS Parent,
> NULL,
> OrderID,
> OrderDate,
> NULL,
> NULL
> FROM Orders
> UNION ALL
> SELECT 3,
> 2,
> NULL,
> O.OrderID,
> NULL,
> P.ProductName,
> OD.UnitPrice
> FROM Orders O JOIN [Order Details] OD
> ON O.OrderID = OD.OrderID
> JOIN Products P
> ON OD.ProductID = P.ProductID
> ORDER BY [Invoice!2!InvoiceNo], [Item!3!Product]
> FOR XML EXPLICIT
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "Bunce" <Bunce@.discussions.microsoft.com> wrote in message
> news:8A76BF8A-8A30-472A-89B3-AA6068AF5701@.microsoft.com...
> 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
>
