SQL Server Question - Query to XML

Question

Alright, I've got 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 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>

Which is fine by me, except I'd like " <Order_x0020_Details> " to only read as " <Order Details> " but I cannot figure out how to do this. Any suggestions? Thanks

Answer 1

It's putting the x0020 because you have a space in the Order Details table name.

Modify your query to use an alias for that table and it should fix it (notice the OrderDetails I've added):

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;