xml explicit trouble - 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

Transact-SQL (2000)

xml explicit trouble

Author

Topic

Lady
Starting Member

32 Posts

Posted - 2002-11-21 : 10:38:02

in NorthWind database
how shell I get xml like this:
<Customer CustomerID="ALFKI">
<orders>
<Order OrderID="10643" />
<Order OrderID="10692" />
<Order OrderID="10702" />
<Order OrderID="11011" />
</orders>
</Customer>
<Customer CustomerID="ANATR">
<orders>
<Order OrderID="10308" />
<Order OrderID="10625" />
</orders>
</Customer>

in books online we have following tsql sample:
SELECT 1 as Tag,
NULL as Parent,
Customers.CustomerID as [Customer!1!CustomerID],
NULL as [Order!2!OrderID]
FROM Customers

UNION ALL
SELECT 2,
1,
Customers.CustomerID,
Orders.OrderID
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
ORDER BY [Customer!1!CustomerID], [Order!2!OrderID]
FOR XML EXPLICIT

this example without bold <orders> tag. I have to get it!!!
exerything is OK, but how shell I get <orders> tag?

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-11-21 : 17:12:02

Hi Lady,

I suspect you don't actually need the <orders> tag - but if you do, I think your best bet is probably going to be xsl

did you know you can get the same result by

select Customers.CustomerID, Orders.OrderID
from Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
order by customers.customerid, Orders.OrderID
for xml auto

quote:
I have to get it!!!

Can you explain why you have to have it?

PS - you might want to have a look at
[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=21827[/url]

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Edited by - rrb on 11/21/2002 17:20:01

monkeybite
Posting Yak Master

152 Posts

Posted - 2002-11-21 : 19:02:49

This'll do what you want, i think:


SELECT	1		AS [tag],
	NULL		AS [parent],
	c.CustomerID	AS [Customer!1!CustomerID],
	NULL		AS [orders!2!],
	NULL		AS [Order!3!OrderID]
FROM	dbo.Customers	AS [c]

UNION ALL

SELECT	2		AS [tag],
	1		AS [parent],
	o.CustomerID	AS [Customer!1!CustomerID],
	NULL		AS [orders!2!],
	NULL		AS [Order!3!OrderID]
FROM	dbo.Orders	AS [o]
GROUP BY o.CustomerID

UNION ALL

SELECT	3		AS [tag],
	2		AS [parent],
	o.CustomerID	AS [Customer!1!CustomerID],
	NULL		AS [orders!2!],
	o.OrderID	AS [Order!3!OrderID]
FROM	dbo.Orders	AS [o]
ORDER BY [Customer!1!CustomerID], [tag], [parent]
FOR XML EXPLICIT

There's two things to notice here.

First, to get the sorting and grouping correct, there must be a key field appearing in all of the union statements, in this case it is CustomerID.

Second, to append the correct number of <orders/> tags, simply select the key value from the orders tables, and use GROUP BY to get only one per customer. Finally, sort by your key value ([CustomerID]), then [tag] and [parent] to get the nesting correct.
-- monkey

-- edited the [orders!2!] tag to the correct case!
-- edited again and added an explanation to the solution

Edited by - monkeybite on 11/21/2002 19:05:08

Edited by - monkeybite on 11/21/2002 19:11:34

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources