You have two tables named Customers and Orders. They are related by a foreign key constraint on the CustomerID on each table. You need to generate the following XML structure that includes all customers and their related orders.
<Root>
<Customer>
<CustomerName>Customer1</CustomerName>
<Orders>
<Order>
<OrderDate>1/1/2008</OrderDate>
<OrderValue>422</OrderValue>
</Order>
<Order>
<OrderDate>4/8/2008</OrderDate>
<OrderValue>300</OrderValue>
</Order>
…
</Orders>
…
</Customer>
<Root>
Which query should you use?
A.
SELECT CustomerName, OrderDate, OrderValue
FROM Customers c JOIN Orders o ON o.CustomerID = c.CustomerID
FOR XML AUTO, TYPE
B.
SELECT * FROM (
SELECT CustomerName, NULL AS OrderDate, NULL AS OrderValue
FROM Customers
UNION ALL
SELECT NULL, OrderDate, OrderValue FROM Orders) CustomerOrders
FOR XML AUTO, ROOT(‘Root’)
C.
SELECT CustomerName, (
SELECT OrderDate, OrderValue
FROM Orders
FOR XML PATH(‘Order’))
FROM Customers FOR XML PATH(‘Customer’), ROOT(‘Root’), TYPE
D.
SELECT CustomerName, (
SELECT OrderDate, OrderValue
FROM Orders
WHERE Orders.CustomerId = Customers.CustomerId FOR XML PATH(‘Order’), TYPE) Orders
FROM Customers FOR XML PATH(‘Customer’), ROOT(‘Root’)