SQL Server 2008 : FOR XML RAW

This is amazing to see how we can get the output of SQL Queries in XML Format.

See complete article from Pinal Dave in subscription email below.

 

Namaste!

  Anugrah Atreya

 

From: Journey to SQLAuthority [mailto:no-reply@wordpress.com]
Sent: Tuesday, August 30, 2011 12:16 PM
To: Anugrah Atreya
Subject: Daily digest for August 30, 2011

 

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - What is XML? - Day 30 of 35 pinaldave | August 30, 2011 at 7:00 am | Categories: Joes 2 Pros, Pinal Dave, PostADay, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | URL: http://wp.me/p2NUQ-3FK

Let's look at another example from the Employee table.  If you ran the reset script for this chapter, you should see 14 JProCo employees showing in your Employee table.

Next we will add FOR XML RAW to view the result from the Employee table as an XML output using the raw mode. We have changed our Employee table result to output as XML RAW. Notice that every row of our XML RAW output is labeled "row" by default.

We next will add a root to our output. We will add the keyword ROOT to our existing code (see figure below) and then look at our revised output.  We are adding the keyword ROOT in order to see a root node in our XML output. We now see the root node (a.k.a., the root element). Not only is our output more readable and organized, but this is considered "well-formed XML"

Now let's put the data into elements. We can see each employee now has three sub-elements under the top element, which is "row"

Each row has three child elements (FirstName, LastName, and LocationID). The exception is John Marshbank, who only has two elements. If we query the Employee table, we quickly see the reason for this is that John Marshbank is the only one with a NULL LocationID. John Marshbank has just two sub-elements beneath the top element, "row".

Our mystery is solved – we understand John Marshbank's having just two data sub-elements is caused by his LocationID value having a NULL value.  Suppose the program which needs to consume our result requires three data sub-elements. Or suppose company policy specifies that each employee record must contain three data sub-elements. John Marshbank's record doesn't meet the criteria and would thus be in violation of the policy.

XSINIL

For fields in SQL Server which include a null value for some records but are populated with regular values in the other records, you will seem to have missing tags for the null record. Often this is alright, as missing tags are presumed to be null. The XSINIL option allows you to force an XML tag to be present for every field in the query, even if the underlying data has NULL field values. Our next example will show us how to make a LocationID tag appear for John Marshbank.

If you require all tags to be present (even if they have no data), then you can specify the XSINIL option for your XML stream. The XSINIL option will force tags to be present for all fields of all records, including those which contain null values. Let's rerun our prior code and add the XSINIL option.

We now see a third sub-element for John Marshbank. The LocationID tag is no longer missing. It is present and shows the value xsi:nil="true" in place of a LocationID. Our objective has been met:  John Marshbank's record now includes three data elements thanks to XSINIL.

Note: If you want to setup the sample JProCo database on your system you can watch this video. For this post you will want to run the SQLInteropChapter1.0Setup.sql script from Volume 5.

Add a comment to this post