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

Updated on August 2, 2019
sirama profile image

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. | Source

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

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:

Figure 1: Sales of Stores via Pubs Database
Figure 1: Sales of Stores via Pubs Database | Source

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 | Source

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 | Source

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 | Source

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 | Source

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 | Source

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

Questions & Answers

    Comments

      0 of 8192 characters used
      Post Comment

      No comments yet.

      working

      This website uses cookies

      As a user in the EEA, your approval is needed on a few things. To provide a better website experience, owlcation.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

      For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://owlcation.com/privacy-policy#gdpr

      Show Details
      Necessary
      HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
      LoginThis is necessary to sign in to the HubPages Service.
      Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
      AkismetThis is used to detect comment spam. (Privacy Policy)
      HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
      HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
      Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
      CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
      Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
      Features
      Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
      Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
      Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
      Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
      Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
      VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
      PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
      Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
      MavenThis supports the Maven widget and search functionality. (Privacy Policy)
      Marketing
      Google AdSenseThis is an ad network. (Privacy Policy)
      Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
      Index ExchangeThis is an ad network. (Privacy Policy)
      SovrnThis is an ad network. (Privacy Policy)
      Facebook AdsThis is an ad network. (Privacy Policy)
      Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
      AppNexusThis is an ad network. (Privacy Policy)
      OpenxThis is an ad network. (Privacy Policy)
      Rubicon ProjectThis is an ad network. (Privacy Policy)
      TripleLiftThis is an ad network. (Privacy Policy)
      Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
      Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
      Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
      Statistics
      Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
      ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
      Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)
      ClickscoThis is a data management platform studying reader behavior (Privacy Policy)