You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database
named SalesDB. The SalesDB is shown in the following database diagram:
You need to write a Transact-SQL query that display a single row in the following XML format:
<Invoices InvoiceID=”1001″ Date=”2012-10-01T00:00:00″, Value=”1000.00″ Customer=”Customer
Name” ShippedTo=”Customer City” />
Which of the following SELECT statement would you write?
A.
SELECT in.InvoiceID, in.InvoiceDate AS [Date], in.InvoiceValue AS [Value], cu.CustomerName
AS [Name], cu.CustomerCity AS [ShippedTo] FROM Invoices AS in
INNER JOIN Customers AS cu ON in.CustomerID = cu.CustomerID
WHERE cu.CustomerID = 1001
FOR XML RAW
B.
SELECT InvoiceID, InvoiceDate AS [Date], InvoiceValue AS [Value], CustomerName AS
[Name], CustomerCity AS [ShippedTo] FROM Invoices
INNER JOIN Customers ON Invoices.CustomerID = Customers.CustomerID
WHERE Customers.CustomerID = 1001
FOR XML
C.
SELECT Invoices.InvoiceID, Invoices.InvoiceDate AS [Date], Invoices.InvoiceValue AS [Value],
Customers.CustomerName AS [Name], Customers.CustomerCity AS [ShippedTo] FROM Invoices
INNER JOIN Customers ON Invoices.CustomerID = Customers.CustomerID
WHERE Customers.CustomerID = 1001
FOR XML AUTO
D.
SELECT InvoiceID, InvoiceDate AS [Date], InvoiceValue AS [Value], CustomerName AS
[Name], CustomerCity AS [ShippedTo] FROM Invoices
INNER JOIN Customers ON Invoices.CustomerID = Customers.CustomerID
WHERE Customers.CustomerID = 1001
FOR XML AUTO, RAW
Explanation:
I think the answer is C
0
0
Correct answer is C
0
0
[A] Correct – Is tabular. Each row has consists of column name values in the column
It is not [C] XML AUTO as does not have nested elements
0
0
I agree, there aren’t any nested elements in the result set, so it must be xml raw.
0
0
The question is wrong
It’s C because It start with the table name – <Invoices – as Auto does
and not with – <row – as raw does
It's A because there are no separation between the join just one line
0
0
You are right . People need to understand the difference between For XML Raw and For XMl Auto in join T-sql query.
within a join , Raw is flat whereas the Auto is hiearachy. Please run those codes and check the difference.
0
0
Yes, you are right, neither answer will the produce the correct output:
A.
C.
Correct query is:
SELECT in.InvoiceID, in.InvoiceDate AS [Date], in.InvoiceValue AS [Value], cu.CustomerName
AS [Customer], cu.CustomerCity AS [ShippedTo] FROM Invoices AS in
INNER JOIN Customers AS cu ON in.CustomerID = cu.CustomerID
WHERE cu.CustomerID = 1001
FOR XML RAW(‘Invoices’)
0
0
A.
C.
0
0
The correct answer is C. The valid explanation to that is, XML AUTO will allow the Table Name or Alias Name value as a Tag value. Thus C validate the XML AUTO Output. You can get the similar output with RAW if ……RAW (‘Invoices’) is mentioned.
0
0
All answers are wrong… the customername field should be aliased as “Customer” NOT “Name”
0
0
Rock, you’re correct, but if you see the logic then “C” is logically the correct answer. Yes the author has made a mistake in writing the alias.
0
0
C is correct answer.
0
0
IS NOT XML RAW, the answer is wrong, the correct answer is C
0
0
C
0
0
Option A is Correct answer Author have missed table tag as FOR XML RAW(‘Invoices ‘)
SELECT in.InvoiceID, in.InvoiceDate AS [Date], in.InvoiceValue AS [Value], cu.CustomerName
AS [Name], cu.CustomerCity AS [ShippedTo] FROM Invoices AS in
INNER JOIN Customers AS cu ON in.CustomerID = cu.CustomerID
WHERE cu.CustomerID = 1001
FOR XML RAW(‘Invoices ‘)
Option C is not correct answer because if FOR XML AUTO Means end of output should be end with but in our case it was ShippedTo=”Customer City” />
so it is proved that OPTION A is correct.
0
0
How can you say that C is correct?
This is the output of C:
A should be correct if the FOR XML clause were written as
FOR XML RAW(‘Invoices’)
B and D are invalid answers.
0
0
Sorry cannot post correct C answer.
The result of C is nested.
0
0
I think the XML is bad written.
Why there is a comma between Data and Value?
Date=”2012-10-01T00:00:00″, Value=”1000.00″
0
0
By the way, part of that new 200Q 70-461 dumps are available here:
https://drive.google.com/open?id=0B-ob6L_QjGLpfnJldlZxTklTaHM0akpJUzhja2pETHJOS0owMzd4eVk1UTVNQUpvdlVxVWM
Best Regards!
0
0
it should be raw(‘Invoices’)
0
0