PrepAway - Latest Free Exam Questions & Answers

Which Transact-SQL query should you use?

You administer a Microsoft SQL Server 2012 databasenamed ContosoDb. Tables are defined as shown in the
exhibit. (Refer to the Exhibit.)

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.

Which Transact-SQL query should you use?

PrepAway - Latest Free Exam Questions & Answers

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

Explanation:
Verified answer as correct.

8 Comments on “Which Transact-SQL query should you use?”

  6. Mr.Awesome says:

    The correct answer is H:

    Explanation: FOR XML PATH clause is used to identify the name of the outer element, In this case the outer element is Orders. you can specify and alias for a column to the name used for the attribute or element in the XML file to something different than the column name. When you use a name that includes a slash, the elements will be nested inside an elements identified by the first part of the alias. in this case, that element is Customers.

    http://msdn.microsoft.com/en-us/library/ms189885.aspx




    0



    0
    1. Mr.Awesome says:

      Opps nope provided answer is correct the path in H is (customers) which makes it wrong.
      D is the correct answer.

      Explanation: FOR XML AUTO clause causes the data to be formatted with each table represented by an element and each column represented as an attribute. when used with a join the table in the join clause will be used as the name of the element.




      0



      0

Leave a Reply