Which Transact-SQL query should you use?

You administer a Microsoft SQL Server database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)

You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format:
<CUSTOMERS Name="Customer A" Country="Australia">
<ORDERS OrderID="1" OrderDate="2001-01-01T00:00:00" Amount="3400.00" />
<ORDERS OrderID="2" OrderDate="2002-01-01T00:00:00" Amount="4300.00" />
</CUSTOMERS>
Which Transact-SQL query should you use?
A. SELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders
INNER JOIN Customers
ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML RAW
B. SELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders
INNER JOIN Customers
ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML RAW, ELEMENTS
C. SELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders
INNER JOIN Customers
ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO
D. SELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders
INNER JOIN Customers
ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO, ELEMENTS
E. SELECT Name, Country, OrderId, OrderDate, Amount
FROM Orders
INNER JOIN Customers
ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO
F. SELECT Name, Country, OrderId, OrderDate, Amount
FROM Orders
INNER JOIN Customers
ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO, ELEMENTS
G. SELECT Name
AS ‘@Name’, Country AS ‘@Country’, OrderId, OrderDate, Amount
FROM Orders
INNER JOIN Customers
ON Orders.CustomerId= Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML PATH (‘Customers’)
H. SELECT Name AS ‘Customers/Name’, Country
AS ‘Customers/Country’, OrderId, OrderDate, Amount
FROM Orders
INNER JOIN Customers
ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML PATH (‘Customers’)

microsoft-exams

One thought on “Which Transact-SQL query should you use?

  1. No correct answer is actually given.
    In my opinion, the answer should be E = FOR XML AUTO
    All the details in the answer block do relate to FOR XML PATH, but this does not give you the result which the question is looking for.

Leave a Reply

Your email address will not be published. Required fields are marked *


The reCAPTCHA verification period has expired. Please reload the page.