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’)