CODEWITHSUNDEEP
sql-serverxml
Polyna
Polyna

Reputation: 21

FOR XML AUTO, RAW SQL Server

I am reading for MS 70-461 exam and when taking a look on a previous test I face the following question:

Question: You need to write a Transact-SQL query that displays all the products received by a singlesupplier in the following XML format:

<Suppliers SupplierID="22" Company="Company Name" ContactNumber="510 250 6400">
     <Products ProductID="100" UnitPrice="249.00" UnitsInStock="7" />
     <Products ProductID="118" UnitPrice="559.00" UnitsInStock="12" />
</Suppliers>

Correct Answer

SELECT Suppliers.SupplierID, 
       Suppliers.CompanyName AS [Company],
       Suppliers.ContactNumber, 
       Products.ProductID, 
       Products.UnitPrice, 
       Products.UnitsInStock 
FROM Suppliers
INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID 
WHERE Suppliers.SupplierID = 22 
FOR XML AUTO, RAW

I try to run similar query by using FOR XML AUTO, RAW in SQL server 2012 and SQL Azure and in both cases I receive an error. Is FOR XML AUTO, RAW valid? How should I write the query in order to return the above XML?

Thank you for your help

Upvotes: 2

Views: 7163

Answers (1)

gofr1
gofr1

Reputation: 15987

Based on MSDN article, RAW and AUTO are two different modes and can not be used together:

In a FOR XML clause, you specify one of these modes:

  • RAW
  • AUTO
  • EXPLICIT
  • PATH

The RAW mode generates a single element per row in the rowset that is returned by the SELECT statement. You can generate XML hierarchy by writing nested FOR XML queries.

The AUTO mode generates nesting in the resulting XML by using heuristics based on the way the SELECT statement is specified. You have minimal control over the shape of the XML generated. The nested FOR XML queries can be written to generate XML hierarchy beyond the XML shape that is generated by AUTO mode heuristics.

So you should use FOR XML AUTO in order to generate output specified in question.

Upvotes: 2

Related Questions