SQL Query Examples for Using "FOR XML RAW" and "FOR XML AUTO"
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.
The join query combines rows from two or more tables based on a related column between them.
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:
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:
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:
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:
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:
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:
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).