Exam 70-461 topic 1 question 222 discussion

Actual exam question from Microsoft's 70-461

Question #: 222
Topic #: 1

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="2000-01-01T00:00:00" Amount="3400.00" />
<ORDERS OrderID="2" OrderDate="2001-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')

Show Suggested Answer

Suggested Answer: G 🗳️
Need to use FOR XML PATH ('Customers') to get the XML output oriented towards the Customers Element.
Example: Specifying XPath-like column names
In the following query the ProductModelID column name specified starts with '@'. Therefore, an attribute of the <row> element that has the corresponding column value is created in the resulting XML.
SELECT ProductModelID AS "@id",

Name -

FROM Production.ProductModel -
WHERE ProductModelID=122 OR ProductModelID=119
FOR XML PATH ('ProductModelData');
This is the result:
<ProductModelData id="122">
<Name>All-Purpose Bike Stand</Name>
</ProductModelData>
<ProductModelData id="119">
<Name>Bike Wash</Name>
</ProductModelData>
References:
https://docs.microsoft.com/en-us/sql/relational-databases/xml/examples-using-path-mode

by lilupv at Sept. 3, 2020, 7:33 a.m.

Comments

Submit Cancel

lilupv

4 years, 9 months ago

I think, it is E!

upvoted 1 times

...