SQL "For Xml Raw" And "For Xml Auto" Examples
Now days, most of the programmers are aware of the “Extensible Mark-up Language” shortly called as XML. The XML is utilized extensively when the programmers wants to exchange data between two computers. Of course, today most of the Web Applications and Web Service providers handle XML. SQL Server 2005 and above has an excellent capability of generating the XML from the database.
The “FOR XML” clause when used with the SQL Query does the magic of representing the query output as XML. In this article, we will explore the “FOR XML” in detail with examples.
2. About the Join Query of this Article
This article needs pubs database to work with the examples. Conversely, it is not mandatory to have pubs database and one can resemble the similar examples with some other schema tables.
In the entire Article, we are going to use Stores and Sales table of the Pubs database. Now, have a look at the Join query shown below:
In the above query, we are pulling 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 Stores column, we need those in this article to explain the FOR XML.
3. 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 Management studio. The example query is shown below:
--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 above query, we get the XML result as shown below:
4. Raw XML with Root Node
In the earlier section, we saw an XML error in the second row stating duplicates element name called row present in the XML. In order to avoid it we can store all the rows in a root element. Now, look at the SQL Query below:
--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 with FOR XML Clause in SQL and that will place all the resulting rows as a child element of that Root. In the above example, we named the root element as ORDERS. Now look at the resulting XML below:
The above 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 is missing now. In essence, the XML is error free now just by having the root node. Note that a Parent can have multiple children with same element name.
5. Naming the Row in RAW XML
Each row in the previous example is named as row by default. We can provide a meaningful name for the row returned by the query. The example is below:
--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 usage of row name at the end of the FOR XML RAW. In the above example, we asked to name each row as Order which consequently produced the XML renaming element row as Order. The resulting XML output is below:
6. Change Attributes as Elements
In all previous examples, from the XML result display, we can notice column name and its values are shown as attributes. We can display these attributes as Elements so that the XML is easily readable. The example for doing that is below:
--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 example, we used the “ELEMENTS” keyword to display the columns as elements. The XML result below shows how the attributes are displayed as Elements (Compare with Previous output):
7. FOR XML AUTO - Maintains Hierarchy
Let us look at the previous xml output once again. The Elements store_id, stor_name, 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 the “FOR XML AUTO” instead of the “FOW XML RAW”. Now, look at the Example below:
--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 the below depiction:
There are two information we should notice. One is the column order in the Select Clause of the query and the other one is the XML AUTO in place of the XML Raw. Since the Store Columns are arranged before the Sales column, in the resulting XML the Sale elements are treated as a child. Also note that there is only one Store element for these two Sales (Marked as yellow).