SQL Server Question - XML ​​Request

Question

Ok, I have this query:

Select Orders.OrderID, ProductID, UnitPrice, Quantity, Orders.OrderDate From [Order Details]
left join Orders on Orders.OrderID=[Order Details].OrderID
where Orders.OrderID='10248' or Orders.OrderID = '10249'
FOR XML Auto, Elements;

and when I execute it, it gives the following XML:

<Orders>
  <OrderID>10248</OrderID>
  <OrderDate>1996-07-04T00:00:00</OrderDate>
  <Order_x0020_Details>
    <ProductID>11</ProductID>
    <UnitPrice>15.4000</UnitPrice>
    <Quantity>12</Quantity>
  </Order_x0020_Details>
  <Order_x0020_Details>
    <ProductID>42</ProductID>
    <UnitPrice>10.7800</UnitPrice>
    <Quantity>10</Quantity>
  </Order_x0020_Details>
  <Order_x0020_Details>
    <ProductID>72</ProductID>
    <UnitPrice>38.2800</UnitPrice>
    <Quantity>5</Quantity>
  </Order_x0020_Details>
</Orders>
<Orders>
  <OrderID>10249</OrderID>
  <OrderDate>1996-07-05T00:00:00</OrderDate>
  <Order_x0020_Details>
    <ProductID>14</ProductID>
    <UnitPrice>20.4600</UnitPrice>
    <Quantity>9</Quantity>
  </Order_x0020_Details>
  <Order_x0020_Details>
    <ProductID>51</ProductID>
    <UnitPrice>46.6400</UnitPrice>
    <Quantity>40</Quantity>
  </Order_x0020_Details>
</Orders>

This is normal, but I would like to " <Order_x0020_Details> "read only how " <Order Details> ", but I cannot figure out how to do this. Any suggestions? Thanks

Answer 1

You should be able to simply alias the table [Order Details]in your query.

Select Orders.OrderID, ProductID, UnitPrice, Quantity, Orders.OrderDate 
    From [Order Details] OrderDetails
        left join Orders 
            on Orders.OrderID=OrderDetails.OrderID
    where Orders.OrderID = '10248' 
       or Orders.OrderID = '10249'
    FOR XML Auto, Elements;

Answer 2

It puts x0020 because you have a space in the name of the order table.

Modify your query to use an alias for this table, and it should fix it (note the added OrderDetails elements):

Select Orders.OrderID, ProductID, UnitPrice, Quantity, Orders.OrderDate From [Order Details] OrderDetails
left join Orders on Orders.OrderID=OrderDetails.OrderID
where Orders.OrderID='10248' or Orders.OrderID = '10249'
FOR XML Auto, Elements;