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.
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:
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:
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:
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:
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:
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:
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).