sql-serversql-server-2008t-sqlsubqueryselect-for-xml

Subquery in FROM clause is causing unwanted ELEMENT in FOR XML AUTO output


I'm using Northwind to model the SQL I believe I'll need with my actual data for a Google Maps application. I think I've got the query working properly but when returned in XML the hierarchy is not the way I need it.

SELECT   Marker.CustomerID
    ,Marker.CompanyName
    ,Marker.TotalAmount
    ,o.OrderID
    ,o.Freight
FROM     
    (
            SELECT   c.CustomerID
            ,c.CompanyName
            ,c.Address
            ,COUNT(o.freight) as TotalOrders
            ,SUM(o.freight) as TotalAmount
            FROM     Customers c
             INNER JOIN Orders o
               ON c.CustomerID = o.CustomerID
                where c.CustomerID = 'ALFKI' or c.CustomerID = 'ANTON'
                group by c.CustomerID
                        ,c.CompanyName
                        ,c.Address
    ) AS Marker                         

     INNER JOIN Orders o
       ON Marker.CustomerID = o.CustomerID
where Marker.CustomerID = 'ALFKI' or Marker.CustomerID = 'ANTON'
order by 1
for xml auto, root('root')

Here is the XML returned:

<root>
<c CustomerID="ALFKI" CompanyName="Alfreds Futterkiste">
 <Marker TotalAmount="225.5800">
  <o OrderID="10643" Freight="29.4600" />
  <o OrderID="10692" Freight="61.0200" />
  <o OrderID="10702" Freight="23.9400" />
  <o OrderID="10835" Freight="69.5300" />
  <o OrderID="10952" Freight="40.4200" />
  <o OrderID="11011" Freight="1.2100" />
 </Marker>
</c>
<c CustomerID="ANTON" CompanyName="Antonio Moreno Taquería">
 <Marker TotalAmount="268.5200">
  <o OrderID="10365" Freight="22.0000" />
  <o OrderID="10507" Freight="47.4500" />
  <o OrderID="10535" Freight="15.6400" />
  <o OrderID="10573" Freight="84.8400" />
  <o OrderID="10677" Freight="4.0300" />
  <o OrderID="10682" Freight="36.1300" />
  <o OrderID="10856" Freight="58.4300" />
 </Marker>
</c>
</root>

The subquery using c as an alias for the customers table is unwanted in the XML; yet, it seems like I need it because CustomerID would be ambiguous since it is in both orders and customers. I would like to know how to show only o within Marker as mocked-up below:

<root>
<Marker CustomerID="ALFKI" CompanyName="Alfreds Futterkiste" TotalAmount="225.5800">
  <o OrderID="10643" Freight="29.4600" />
  <o OrderID="10692" Freight="61.0200" />
  <o OrderID="10702" Freight="23.9400" />
  <o OrderID="10835" Freight="69.5300" />
  <o OrderID="10952" Freight="40.4200" />
  <o OrderID="11011" Freight="1.2100" />
</Marker>

Solution

  • Normally, I've always thought it a bit "cooler" to use a subquery as in the OP but as commented, the CTE idea from @DVT produced same XML so I rewrote as follows. By avoiding the subquery and populating a temporary table, I was able to get the elements in the XML hierarchy as desired:

    CREATE TABLE #TempMarkers
    (
        CustomerID nchar(5) NOT NULL,
        CompanyName nvarchar(40) NOT NULL,
        Address nvarchar(60) NULL,
        TotalOrders int NULL,
        TotalAmount money NULL
    )
    INSERT INTO #TempMarkers
    (CustomerID
    ,CompanyName
    ,Address
    ,TotalOrders
    ,TotalAmount
    )
    SELECT  c.CustomerID
            ,c.CompanyName
            ,c.Address
            ,COUNT(o.freight) as TotalOrders
            ,SUM(o.freight) as TotalAmount
            FROM     Customers c
             INNER JOIN Orders o
               ON c.CustomerID = o.CustomerID
                where c.CustomerID = 'ALFKI' or c.CustomerID = 'ANTON'
                group by c.CustomerID
                        ,c.CompanyName
                        ,c.Address
    SELECT   Marker.CustomerID
            ,Marker.CompanyName
            ,Marker.TotalAmount
            ,Marker.TotalOrders
            ,o.OrderID
            ,o.Freight
    FROM #TempMarkers Marker     
         INNER JOIN Orders o
           ON Marker.CustomerID = o.CustomerID
    where Marker.CustomerID = 'ALFKI' or Marker.CustomerID = 'ANTON'
    order by 1
    for xml auto, root('root')