Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
FOR XML RAW
The RAW mode appends the <row> tag to the output, and does not provide the table name. If you need to know which table the data is coming from, the RAW mode is of no help. On the other hand, the RAW mode is very useful when you don't really care about the source of the data; so if you have a query joining multiple tables, and you just want to grab the data without caring about the table nametags, use the RAW mode, as follows:
SELECT TOP 2 Customers.CompanyName, Customers.Country, Orders.OrderDate, Orders.ShipCity FROM Orders INNER JOIN Customers ON orders.customerid = customers.customerid FOR XML RAW
Results:
<row CompanyName="Vins et alcools Chevalier" Country="France" OrderDate="1996-07-04T00:00:00" ShipCity="Reims"/> <row CompanyName="Toms Spezialitäten" Country="Germany" OrderDate="1996-07-05T00:00:00" ShipCity="Münster"/>
Notice that the RAW mode does not support the ELEMENTS option; you're limited to retrieving data as attributes.
The subqueries used with the RAW mode behave exactly the same way as joins—none of the table names is displayed:
SELECT au_lname, au_fname, title = ISNULL( (SELECT TOP 1 title FROM titles INNER JOIN titleauthor ON titles.title_id = titleauthor.title_id WHERE titleauthor.au_id = authors.au_id), 'no titles for this author') FROM authors WHERE au_lname = 'bennet' OR au_lname = 'blotchet-halls' FOR XML RAW
Results:
<row au_lname="Bennet" au_fname="Abraham" title="The Busy Executive's Database Guide"/> <row au_lname="Blotchet-Halls new new" au_fname="Reginald" title="Fifty Years in Buckingham Palace Kitchens"/>
The RAW mode does support the aggregate functions and GROUP BY clause of the SELECT statement. The following example retrieves an average quantity from the order details table in Northwind database:
SELECT average_quantity = AVG(quantity) FROM [order details] FOR XML RAW
Result:
<row average_quantity="23"/>
The next example counts the number of orders per customer. For brevity, I limited the output to the customers who have placed 18 or more orders:
SELECT CustomerID, COUNT(*) AS NumberOfOrders FROM Orders GROUP BY CustomerID HAVING COUNT(*) > 18 FOR XML RAW
Results:
<row CustomerID="ERNSH" NumberOfOrders="30"/> <row CustomerID="FOLKO" NumberOfOrders="19"/> <row CustomerID="HUNGO" NumberOfOrders="19"/> <row CustomerID="QUICK" NumberOfOrders="28"/> <row CustomerID="SAVEA" NumberOfOrders="31"/>
You can also return a scalar variable in XML using the RAW mode, as the following query demonstrates:
DECLARE @string VARCHAR(50) SELECT @string = 'my XML article' SELECT @string AS MyArticle FOR XML RAW
Results:
<row MyArticle="my XML article"/>