The FOR XML clause does not work any magic. It performs just like any other
UNION and is dependent on the strict ordering of the underlying universal
table. Comment out the FOR XML clause and you can see the variance between
adding DESC in different places. The problem in your scenario is that the
first part of the union (customer) has null in the [Order!2!id] field and
therefore descending causes the initial tag (1) to be placed at the end of
the sort sequence. Without changing the structure of the query, you could
put a high value in that field such as, "99999999 as [OrderDetail!3!id!id]"
and it would sort correct in the scenario "ORDER BY [Customer!1!cid],
[Order!2!id] DESC".

I would add a different sort key in the query to come up with the response
that you desire. Remember that you can always check the ordering of the
universal table by commenting out the FOR XML clause.


"Dave" <> wrote in message
> Hi, I'm inquiring whether you can sort DESC using the
> following ORDER BY clause taken out of the SQL Online
> Books example on the Northwind DB.
> In this case, can you sort the ORDER from recent to
> oldest? Or even the ORDER details instead of the default
> ascending order? I've tried just plugging in DESC but it
> raises a
> Server: Msg 6833, Level 16, State 1, Line 1
> Parent tag ID 1 is not among the open tags. FOR XML
> EXPLICIT requires parent tags to be opened first. Check
> the ordering of the result set.
> Thanks, Dave.
> SELECT 1 as Tag,
> NULL as Parent,
> C.CustomerID as [Customer!1!cid],
> C.ContactName as [Customer!1!name],
> NULL as [Order!2!id],
> NULL as [Order!2!date],
> NULL as [OrderDetail!3!id!id],
> NULL as [OrderDetail!3!pid!idref]
> FROM Customers C
> SELECT 2 as Tag,
> 1 as Parent,
> C.CustomerID,
> O.OrderID,
> O.OrderDate,
> FROM Customers C, Orders O
> WHERE C.CustomerID = O.CustomerID
> SELECT 3 as Tag,
> 2 as Parent,
> C.CustomerID,
> O.OrderID,
> OD.OrderID,
> OD.ProductID
> FROM Customers C, Orders O, [Order Details] OD
> WHERE C.CustomerID = O.CustomerID
> AND O.OrderID = OD.OrderID
> ORDER BY [Customer!1!cid], [Order!2!id]
> This is the partial result:
> <Customer cid="ALFKI" name="Maria Anders">
> <Order id="10643" date="1997-08-25T00:00:00">
> <OrderDetail id="10643" pid="28"></OrderDetail>
> <OrderDetail id="10643" pid="39"></OrderDetail>
> </Order>
> <Order id="10692" date="1997-10-03T00:00:00">
> <OrderDetail id="10692" pid="63"></OrderDetail>
> </Order>
> <Order id="10702" date="1997-10-13T00:00:00">
> <OrderDetail id="10702" pid="3"></OrderDetail>
> <OrderDetail id="10702" pid="76"></OrderDetail>
> </Order>
> </Customer>