SQL Query Examples Explaining "For Xml Raw" And "For Xml Auto"

Updated on August 29, 2018
sirama profile image

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

1. Introduction

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:

Sales of Stores - Pubs DB
Sales of Stores - Pubs DB | Source

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:

SQL FOR XML RAW Output (Sample - Not all rows)
SQL FOR XML RAW Output (Sample - Not all rows) | Source

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:

For XML RAW With Root Node
For XML RAW With Root Node | Source

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:

XML RAW with Row Name
XML RAW with Row Name | Source

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

FOR XML RAW - Columns as Elements
FOR XML RAW - Columns as Elements | Source

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:

FOR XML AUTO Example
FOR XML AUTO Example | Source

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

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)