Home > Articles > Data > SQL Server

  • Print
  • + Share This
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&apos;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"/>
  • + Share This
  • 🔖 Save To Your Account