SQL Query Examples for Using "FOR XML RAW" and "FOR XML AUTO" - Owlcation - Education
Updated date:

SQL Query Examples for Using "FOR XML RAW" and "FOR XML AUTO"

Author:

I am a software engineer. I have been working with C++, MFC, and .net technologies for 15 years. I like video games and reading books.

XML through SQL allows computers to exchange data.

XML through SQL allows computers to exchange data.

Most programmers are aware of "extensible mark-up language", or XML. XML is often used for exchanging data between two computers. Most contemporary web applications and web service providers handle XML. SQL Server 2005 and updated versions are capable of generating XML from a SQL database.

When used with the SQL query, the FOR XML clause represents the query output from SQL as XML. The following article gives examples of how to use FOR XML.

Join Query and Pubs Database

The user must understand Pubs Database for these examples to make sense. Conversely, it is not mandatory to have Pubs Database to use FOR XML and it is possible to assemble these examples in a similar fashion with other schema tables.

We are going to use the Stores and Sales table represented in the Pubs Database throughout the entire article. Now, have a look at the Join query shown in Figure 1:

Figure 1: Sales of Stores via Pubs Database

Figure 1: Sales of Stores via Pubs Database

The query shown in Figure 1 pulls three columns from the Stores table. The last two columns ord_num and qty are drawn from the Sales table. On the whole, the query shows the sales achieved by the Stores. Even though we have redundancies in the stor_name column, we need those errors in this article for a later example using FOR XML.

Generating RAW XML

The FOR XML RAW construct at the end of the Select query is responsible for generating the XML content. Even though the output is XML, it looks like the data returned in row and column format that we usually see the output window of the SQL Server Management Studio (SSMS). The Example 1 query code is shown here:

--Example 01 -Generating RAW XML
SELECT 
	store.stor_id AS store_id, 
	store.stor_name, store.city,
	sale.ord_num, sale.qty	
FROM 
	Stores store Inner Join Sales sale
	ON store.stor_id = sale.stor_id
	FOR XML RAW;

When we execute the above query, we get the XML result shown in Figure 2:

Figure 2: SQL FOR XML RAW Output Sans Some Rows

Figure 2: SQL FOR XML RAW Output Sans Some Rows

Raw XML with Root Node

In Figure 2, we saw an XML error in the second row stating a duplicated element name called "row" present in the XML. In order to avoid duplication, we can store all the rows in a root element. Take a look at the Example 2 SQL query code:

--Example 02 - Raw XML with Root
SELECT 
	store.stor_id AS store_id, 
	store.stor_name, store.city,
	sale.ord_num, sale.qty	
FROM 
	Stores store Inner Join Sales sale
	ON store.stor_id = sale.stor_id
	FOR XML RAW, ROOT('ORDERS');

We can add the ROOT construct to the FOR XML clause in SQL and that will arrange all the resulting rows as a single child element of that root. In the above example (2), we named the root element ORDERS. See the resulting XML in Figure 3:

Figure 3: For XML RAW With Root Node

Figure 3: For XML RAW With Root Node

The above Figure 3 XML shows that all the records are enclosed by the root element ORDERS. As a result, we can see that the red squiggly line in the second row from Figure 1 is gone. The XML is error-free now just by incorporating a root node. Note that a parent (or root) can have multiple children with the same element name.

Naming the Row in RAW XML

Each row in Figures 2 and 3 are named "row" by default. We can instead provide a meaningful name for the row returned by the query. Example 3 code details how:

--Example 03 - Naming the Row of Raw XML
SELECT 
	store.stor_id AS store_id, 
	store.stor_name, store.city,
	sale.ord_num, sale.qty	
FROM 
	Stores store Inner Join Sales sale
	ON store.stor_id = sale.stor_id
	FOR XML RAW('Order'), ROOT('ORDERS');

Note the use of row name at the end of FOR XML RAW. In the above example, we asked to name each row "Order" which consequently produced the XML renaming element row as Order. The resulting XML query output is shown in Figure 4:

Figure 4: XML RAW With Row Name

Figure 4: XML RAW With Row Name

Change Attributes as Elements

In all previous examples, the XML results show the column name and its values are attributes. We can display these attributes as elements so that the XML is easy to read. Example 4 code shows you how:

--Example 04 Change Attributes as Elements
SELECT 
	store.stor_id AS store_id, 
	store.stor_name, store.city,
	sale.ord_num, sale.qty	
FROM 
	Stores store Inner Join Sales sale
	ON store.stor_id = sale.stor_id
	FOR XML RAW('Order'), ROOT('ORDERS'), ELEMENTS;

By default, the FOR XML construct will display the columns as attributes. In the above code example, we used the “ELEMENTS” keyword to display the columns as elements. The XML result in Figure 5 shows how the attributes are displayed as elements:

Figure 5: FOR XML RAW Columns as Elements

Figure 5: FOR XML RAW Columns as Elements

FOR XML AUTO Maintains Hierarchy

Let us look at the previous XML output in Figure 5 once again. The elements store_id, stor_name, and city are displayed twice as there are two sales on the store 6380 with two different order numbers. We can avoid this repetition by using FOR XML AUTO instead of the FOR XML RAW. Example 5 shows this:

--Example 05 Maintain Hierarchy
SELECT 
	store.stor_id AS store_id, 
	store.stor_name, store.city,
	sale.ord_num, sale.qty	
FROM 
	Stores store Inner Join Sales sale
	ON store.stor_id = sale.stor_id
	FOR XML AUTO, ROOT('ORDERS'), ELEMENTS;

The output of the resulting XML is shown in Figure 6:

Figure 6: FOR XML AUTO Output Example

Figure 6: FOR XML AUTO Output Example

There are two pieces of information we should notice. One is the column order in the select clause of the query and the other one is the FOR XML AUTO in place of the FOR XML RAW. Since the Store columns are arranged before the Sales column, in the resulting XML the Sale elements are treated as a child. Note that there is only one Store element for these two Sales (marked yellow).