For those of you who are lucky enough to work with DB2 and .NET, you're probably already aware that you can run into some issues if you're  not using the correct DB2 Driver.

I've been writing some SSIS packages that move some data from a DB2 environment into a SQL Server 2005  Database. I also had to write a few .NET applications that spoke to the DB2 database. Here's a couple drivers that I used and some of the notes regarding them. I've also included a few links that help in regards to development.

Drivers

• Microsoft OLE DB2: DB2OLEDB Driver 
  • This driver is part of the Feature Pack for SQL Server 2005.
  • This Driver worked for most of the time until I needed to work with Large Objects (BLOB/CLOB). Unfortunately I found this after I did a bunch of work -
    • "The OLE DB Provider for DB2 does not currently support the DB2 Large Object (LOB) types." - Distributed Query Support Using the OLE DB Provider for DB2

• IBM DB2 Driver for ODBC and CLI
  • Please note, I COULD NOT get the IBM web site to work with IE7. I had to use Firefox to download the file.  
    • Link Name: IBM DB2 Driver for ODBC and CLI
    • File Name: v9fp2_win_db2driver_for_odbc_cli.zip
  • Download the file, install and you'll have the drivers on your machine.
  • The thing is, this worked great for the BLOB/CLOB fields, but when the SSIS Package went to transform the DateTime fields it complained about data loss because DB2 has more DateTime precision than SQL Server 2005.
    • This was solved by using the Microsoft OLE DB2 Provider as it did not complain about the DateTime. The team I worked with knew that we'd lose some millionths of a second, but that was acceptable for the project. Basically the Microsoft Provider didn't complain about it like the DB2 provider did. The data came through the conversion correctly, so we were fine with it.

Other Useful Information/Tools

• SQLState Error Message Reference
  • Sometimes SSIS can throw some nasty HRESULT errors your way and you're left scratching your head trying to figure out what went wrong. If the message includes a SQLState, look in this reference, it will probably be in here, or will point you in the right direction.

• WinSQL Lite
  • I like to say its like the SQL Management Studio for DB2. A very nice GUI that allows you to interact with the DB2 system. A lot of "old schoolies" (as I call them) like to use the command line to hammer away at DB2. Me? I like to look at the GUI when I'm looking at tabular data. This tool will make your life 10 times easier when looking for data anomalies.

The Problem

I needed to write something like this ...

SELECT * FROM CustomerTest WHERE Customer_Id = 1;

The data returned needed to return the row as well as the data inside of the XML Column in that row, in the same set. (The data in the XML column needed to be returned as sets of records as well.) The XML Contained was simple XML that looked like this:

(click for larger image)

The XML Column was called "Extra_Info". This is where the above XML was stored.

Ok, lets get to the point... here's what its supposed to look like when the query is run:

(click for larger image)

A little more about the problem domain..

Awhile back I had a client who wanted to store data in an XML Field, but the XML for each row could be extremely different in each row. One row might have a XML Document that has 1 set of data, while the next might have 10. These XML documents that were being stored did not share a common schema -- not at all. But we needed to return the data in an SQL Query or SPROC that would allow the user to view the data as if it were tables in the database.

Solution

So how do we do that with one Query?

Easy - SQL CLR Integration.

We can create a CLR Stored Procedure to do this for us.
Here are the steps the CLR SPROC will go through to return the data.

1. Query the DB for the record.
2. Get the XML from the column.
3. Load the XML into a DataSet object.
4. Send the original record results of the row to the client.
5. Create record sets off of the DataSet using SqlDataRecord object.
6. Send each record back to the client.

Code time baby...

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Collections.Generic;
using System.Data.Common;
using System.Xml;


public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void TestProcedure(SqlInt32 recordId)
    {
        DataSet dataset = null; 

        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            string xml = string.Empty;
            using (SqlCommand command = new SqlCommand("SELECT * FROM dbo.CustomerTest WHERE Customer_Id = @recordId"))
            {
                command.Parameters.Add(new SqlParameter("@recordId", recordId));
                command.Connection = connection;
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }

                using (DbDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        xml = reader["Extra_Info"].ToString();
                        
                        // Load in the XML. 
                        XmlReader xmlReader = new XmlTextReader(xml, XmlNodeType.Document, null);
                        dataset = new DataSet();
                        dataset.ReadXml(xmlReader);
                    }

                }
                SqlContext.Pipe.ExecuteAndSend(command);

            }

            
        }

        if (dataset != null)
        {
            foreach (DataTable table in dataset.Tables)
            {
                // Set up the record 
                List<SqlMetaData> metaData = new List<SqlMetaData>();
                foreach (DataColumn column in table.Columns)
                {
                    metaData.Add(new SqlMetaData(column.ColumnName, SqlDbType.Variant));
                }
                SqlDataRecord record = new SqlDataRecord(metaData.ToArray());

                // Mark the begining of the result-set.
                SqlContext.Pipe.SendResultsStart(record);

                foreach (DataRow row in table.Rows)
                {
                    foreach (DataColumn column in table.Columns)
                    {
                        record.SetValue(table.Columns.IndexOf(column), row[table.Columns.IndexOf(column)]);
                    }

                    // Send the row back 
                    SqlContext.Pipe.SendResultsRow(record);
                }
                // Mark the end of the result-set.
                SqlContext.Pipe.SendResultsEnd();
            }
        }

    }

}

Code Review

The sproc is created with the code above.

If we find a record we load the xml into a variable and then load the DataSet with the XML using the ReadXml Method of the DataSet.

if (reader.Read())
{
  xml = reader["Extra_Info"].ToString();
                      
  // Load in the XML. 
  XmlReader xmlReader = new XmlTextReader(xml, XmlNodeType.Document, null);
  dataset = new DataSet();
  dataset.ReadXml(xmlReader);
}

Next, we loop through each data table and each record in the data table to create a SqlDataRecord. To build an SqlDataRecord we need a Record definition.These record definitions are defined as SqlMetaData objects. Therefore we need populate an array of SqlMetaData objects by looping through the columns to get the column information. At this point we're building the columns in which the data will exist in.

// Set up the record 
List<SqlMetaData> metaData = new List<SqlMetaData>();
foreach (DataColumn column in table.Columns)
{
  metaData.Add(new SqlMetaData(column.ColumnName, SqlDbType.Variant));
}
SqlDataRecord record = new SqlDataRecord(metaData.ToArray());

From here, we will build the actual records. This is done by the following method:

• Inform the context that we're going to begin sending a record by using the SendResultsStart method of the Pipe class.
• When a record is built, we want to send it to the client by calling the SendResultsRow method. This sends the row to the client. We will do this for each record built.
• After the records have been sent we want to tell the client we're done sending records for this set. We do this by calling the SendResultsEnd method.

This happens for each DataTable in the DataSet. And then finally the results are sent back to the client.

Resolution

Once deployed, the sproc (which I've named "TestProcedure") will show up under the StoredProcedures area of SQL Server, as shown here:

And now you have a stored procedure that will return results of the row as well as the data that is in the XML column, all in the same result set.

Uses: You could use this as an extensibility point in your application  by allowing users to add custom fields to the application at run time and then storing the application fields and field data in an XML file which is stored in an SQL Server column. Think of it as a database inside of a database. You could then use it to query the row/column to get the results into a readable form for Crystal or Reporting Services.

UPDATE: I added the publications I also read at the bottom. :)

I've been asked what blogs I read, podcasts I listen to and screencasts I watch. So here's a comprehensive list for ya'll. :)

Blogs (in no particular order)

• Scott Hanselman
• Jeff Atwood
• Martin Fowler
• Jeremy D. Miller
• K. Scott Allen
• Mads Kristensen
• Sahil Malik
• Roy Osherove
• Jean-Paul S. Boodhoo
• Bil Simser
• Dino Esposito

Then at times I'll browse the MSDN Blogs. Also note, these are what I call my "essential" blogs. I read these daily. I have about 30 other blogs that I read if I have time.

Podcasts (in no particular Order)

• .NET Rocks
• Hanselminutes
• ARCast with Ron Jacobs
• Polymorphic Podcast
• this Week in Tech
• Agile Talk
• Agile Toolkit Podcast
• Confessions of an Agile Activist
• Lean Agile Straight Talk
• Microsoft Technet Podcasts
• Nitobis Enterprise AJAX Podcast

Other Podcasts I listen to when I can

• Ruby on Rails Podcast
• Rubyology
• Railscast

Screencasts/Virtual Labs (in no particular order)

• DNR TV
• Microsoft Office 2007 Screencasts - How Do I? Screencasts
• Best Tech Videos (Microsoft)
• Microsoft Virtual Labs

Publications

• MSDN Magazine
• ASP.NET Pro
• Visual Studio Magazine
• CoDe Magazine
• Dr. Dobb's Journal

During the development of any new greenfield project you're going to probably need to interact with a datastore.
From my experience, 8 out of 10 times this is going to be a database acting as the datastore, and in my case, it's normally SQL Server.

Database Modeling

When in the design phase we're modeling. Creating a representation of what we're going to build. In the design of a database I use Visio to do this by building an ERD Diagram.

If you're like me you're still probably using Visio 2002/2003. If you're also like me, you don't have the Enterprise Architect version of Visio that allows you to forward engineer the database from the ERD Diagram that you've created.

So, therein lies a problem. I have to design my database in Visio, then manually create the SQL and run it in query analyzer to get my database. Seem's like double the work to me.

1. Create the diagram and all the types and relationshiops
2. Copy it by hand into SQL Server Management Studio or the Query Window

I've always felt that Microsoft should have included forward engineering for Visio, regardless of the version. Unfortunately they didnt feel the same way.

Model to SQL

Fortunately there is a solution. A company called Orthogonal Software has a Visio plug-in called Orthogonal Toolbox that will allow you to export the data from the Visio document to an XML File.

Orthogonal has provided a couple XSLT's that you can apply (look for XSLT on that page). One of which has been altered to export SQL.

The XSLT file that originally was found with the product exported the SQL into one long string. No line breaks, nothing. Just a long string. After some futz'n around with the XSLT I was able to format the SQL that was transformed into readable SQL.

For example, a XSLT generated SQL Code that looks like this:

CREATE TABLE dbo.Employee( 
   [Employee_Id] bigint NOT NULL,
   [Name] nvarchar(100) NOT NULL
);
GO

How to implement

1. Download Orthogonal Toolbox and install it.
2. Create your Visio ERD Diagram.
3. Click the Orthogonal Toolbox Button in the toolbar.
4. Export to XML.
5. Put the XSLT files (download at the bottom of this post) into the same file as the XML File.
6. Open the newly created XML File and add this line after the XML Definition.
    <?xml-stylesheet type="text/xsl" href="ERD-SQL2005.xslt"?>
6. Open the XML file in Internet Explorer
7. Copy/Paste what you see into Query Analyzer and run.

Thats it. You've now gone from Database ERD Model to SQL Code in a few quick steps. TONS of time saved.

Update for SQL Server 2005

The original XSLT was for use with SQL Server 2000. Schemas were not supported in 2000, therefore everything started with "dbo". To get around this, I reworked the XSLT and changed it to use the table name.

When I'm designing for a SQL Server 2005 database I'll give my tables names such as [My_Schema].[Table_Name]. Therefore when I export and apply my XSLT, the new exported SQL will look like this:

CREATE TABLE [HumanResources].[Employee]( 
   [Employee_Id] bigint NOT NULL,
   [Name] nvarchar(100) NOT NULL   
);
GO

Where "HumanResources" is the schema and "Employee" is the table name.

Downloads

2000and2005XSLTs.zip (2.96 KB)

(Download Excel Spreadsheet example at the bottom.)

Sometimes when developing I need to work with data. Sometimes this data is from external sources such as other partners in the business. Unfortunately when I get this data does not follow a consistent format therefore I'm left with formatting it for entry into a data store, usually a SQL Server.

In my last post about how to create store locator, I had this same problem. I had to import a list of company addresses for that tutorial. The only place I knew where to get a good list of locations for a store locator would be off of Starbucks Store Locator site. Through the trusty copy/paste method, I was able to grab about 200 store results in a matter of minutes from the website.

Now back to the original problem, the data format. The data looked like this after is was copied...

1. 1385 Metropolitan Ave (UCO)
1385 Metropolitan Avenue
Bronx NY, 10462
United States

I needed it to look like this (for ease of use, plus its easier to debug):

1385 Metropolitan Ave (UCO)
1385 Metropolitan Avenue
Bronx
NY
10462
 
Which will finally transform into this:

INSERT INTO dbo.Locations(LocationName, Address, City, State, Zip) VALUES ('1385 Metropolitan Ave (UCO)', '1385 Metropolitan Avenue', 'Bronx', 'NY', '10462')

Hmmm... problem. Now I could take my time, write an SQL insert script for each and every one of these, but that would take forever.

Instead we can use Excel to do this for us.

First off, all credit for this sweet little trick goes to Chris Lawson (a developer I worked with during my time at Todd McFarlane Producitons, SPAWN.COM). He's the one who showed it to me, I'm just showing it to you. :)

To get the data formatted into SQL insert scripts we need to use a few functions from Excel. We'll mainly use LEFT, MID, RIGHT, TRIM and SEARCH. Using those 4 methods you can pretty much get anything you want out of the string, and thats what I've done.

Note: It might be very helpful if you downloaded my sample at the bottom of this post and read this while looking at the sample, just so you can see how it was done.

Step 1: Separate the Data

We're going to want to take our data from this format:

1. 1385 Metropolitan Ave (UCO)
1385 Metropolitan Avenue
Bronx NY, 10462

and organize it to:
 
1385 Metropolitan Ave (UCO)
1385 Metropolitan Avenue
Bronx
NY
10462

Which will eventually get transformed into SQL, like this ...

INSERT INTO dbo.Locations(LocationName, Address, City, State, Zip) VALUES ('92nd & 3rd', '1642 Third Avenue', 'New York', 'NY', '101283618')

a) Get the Title to look like: 1385 Metropolitan Ave (UCO)

Tasks:
 - Get rid of the "1. " 

This will return the text in A1 without "1. " in it.

This takes the contents in cell A1 (the text with the 1. 1385 Metro...) and gets rid of the 1. through some simple text manipulation. The SEARCH method takes a parameter to search for. Utilizing the single character wildcard "?" I'm able to find where the numbering 'ends' and then get the text after that utilizing the LEN and RIGHT functions.

I'm going to use the same method for each line...
 

Address

City

State

Zip

Step 2. - Creating the SQL

Now that we have all the fields separated, we can easily create a SQL Script off of this.
Simply concatinate them together.

(click for larger view)

Now that we have all of this done, we can copy, and paste the this code (or click and drag the bottom right corner) throughout the sheet.
This will create the fields and SQL for us.

Then, two lines down...

Which gives us...

Now, select the entire "C" COLUMN and copy it ...

Now move that to any column down the line from column "C", lets say, "E". Right click and click "Paste Special"

Then click "Values"

Step 3 - Extracting the SQL

Now all the actual text values will be placed into the columns. You can now select Column "E" and copy all of those values.

The reason we have to paste special is because otherwise we'd be copying the column caluculations, instead of a pretty SQL script, we'd get a row with no data, such as:

INSERT INTO dbo.Locations(LocationName, Address, City, State, Zip) VALUES ('', '', '', '', '')

By pasting special, we're telling Excel we want the VALUES of the cells, not the actual calculations.

Now that you've "pasted as special" with values, you can now copy column "E" and then paste that into your SQL Management Studio and run. For simplicity sake, here are a few rows from the actual Excel file that is attached to this post.

INSERT INTO dbo.Locations(LocationName, Address, City, State, Zip) VALUES ('1385 Metropolitan Ave (UCO)', '1385 Metropolitan Avenue', 'Bronx', 'NY', '10462')
INSERT INTO dbo.Locations(LocationName, Address, City, State, Zip) VALUES ('Whitestone', '138-11 20th Avenue', 'Whitestone', 'NY', '113562451')
INSERT INTO dbo.Locations(LocationName, Address, City, State, Zip) VALUES ('Target Flushing T-1150', '13505 20th Ave', 'Flushing', 'NY', '11356')
INSERT INTO dbo.Locations(LocationName, Address, City, State, Zip) VALUES ('LGA - Delta Flight Ctr-Delta Term', 'La Guardia International Airport', 'Flushing', NY', '11371')
INSERT INTO dbo.Locations(LocationName, Address, City, State, Zip) VALUES ('Flushing - Main Street', '41-02 Main Street', 'Flushing', 'NY', '11372')

Then execute your script and you're on your way.

Conclusion

When is it appropiate to use this method? IT DEPENDS. If you have 10 records, no. If you have 1000 records? Yes. Its all about productivity. Ask yourself, is it going to be faster for me to spend 5 minutes writing this in EXCEL or is it going to take me 5 minutes to import them manually? This is up to you to answer. Hopefully this helps you on your next project. :)

Download File

starbucks-locations-xls.zip (52.71 KB)

Full Visual Studio 2005 solution included at the bottom of this post.

Update 2008-11-22: Fixed a bug with the variable definition. Also Moved the source code to CodePlex. From now on, all updates to the source will be made to the codeplex site: codeplex.com/StoreLocator

Update 2008-10-14: Added a SQL file so that the DB can be generated for any other type of DB provider that you may wish to use. See bottom of post for SQL File.

Many organizations have multiple locations throughout the country. A lot of the time the organizations customers will want to know where the closet location of that store is. We’ve all become accustomed to going to a companies website and finding the store locator and locating the store that’s closest to us. This is a simple thing for a user to do. But building a store locator is far from simple and can become quite complex, real quick. I’ve put together a simple example that will enable you to add store locator functionality to your ASP.NET website in no time.

Let’s start by covering the basics….

Requirements for store locator functionality: 
   - Database to store your store locations
   - Each store location needs a Longitude / Latitude 
   - A method to retrieve Latitude / Longitude, also known as Geocoding
   - A Web Front end that allows users to search and view the results.

Screen Shots

Search Screen:

Results Screen (click for larger view):

The Database

The database is very simple. We have one table, two sprocs, and 3 functions. It is in the App_Data folder of this solution.

The Location table holds the store locations.

The GetNearbyLocations sproc gets the locations that are closest to the user through a mathematical calculation that is inside of the sproc.

The InsertLocation sproc inserts a new location into the Location table (we’ll get to that near the end).

The three functions: XAxis, YAxis, ZAxis are used in the distance calculation that is inside of the sproc.

Important Note: I’m not the genius that wrote this mathematical calculation. Therefore, I can’t take credit for it. The database table, functions and distance sproc are all modeled after this post on MSDN. I have altered quite a few things especially the GetNearbyLocations sproc where I actually calculate the Earths X,Y, and Z axis’ at runtime. 

The Geocoding – Getting the Latitude / Longitude

The latitude and longitude are needed to perform the distance calculation in the GetNearbyLocations stored procedure. The Latitude and Longitude of each store is stored in the Locations table (see the database image above) with the location record itself.

How to do we get the Latitude / Longitude?

That’s where the Google Maps API comes into place. Google Maps allows you to sign up for a free API key.

Important Note: If you are going to test this locally, you will need to get a API key that is associated with your local machine, e.g.: http://localhost:port/  where “port” is your port number, such as: localhost:4688, localhost:1637, etc).

Where put your Google Maps API Key
The key is located in two places (I know, its not best practice) but, it’s the only way I could get it to work without spending hours on figuring it out. It’s located in the web.Config and in the Default.aspx page.

Default.aspx page (click for larger view)

web.Config

In a previous post I had built a class that retrieved the latitude and longitude of an address. By using this class while a new location is being added, (AddNewStore.aspx), the Lat/Lon coordinates are then retrieved and saved.

protected void addNewStoreButton_Click(object sender, EventArgs e)
{
   string address = String.Format("{0}, {1}, {2} {3}", 
   addressTextBox.Text,
   cityTextBox.Text,
   stateTextBox.Text,
   zipTextBox.Text);
   Coordinate addressCoordinates = Geocode.GetCoordinates(address);
   ... // Other code ommitted
}

The Web Front End

The web front end is simple (errrrrrrr… kind of). The application requires 2 things:
1. An address
2. A distance to search within.

The user enters this information, it’s passed to the database, the calculation is performed and the records that match the distance compared to the address are returned. Simple, right?

Now its time for the “somewhat” confusing part: the task of integrating the Google map with clickable markers. Google doesn’t offer markers that are numbered (from what I could find). So I found some on the net and included them in this project. Which leads me to the next point…

A maximum of 100 results will be returned.

This is set within the stored procedure. The reason we only return 100 records is because we only have 100 icon images (1 through 100). Plus, placing more markers on the page would have a negative impact on the memory and processor on the user’s machine. Furthermore, who’s really going to look at 100 results? The user is probably only looking for the closest couple of stores. Not the top 100.

Google Marker Placement

In order to place markers on the screen we needed lat/lon points to pass to Google’s API. This poses a problem because our data is in the DB, but Google Maps API needs it on the client to process it. Therefore, while on the server, while we have the data in a dataset (below) ...

/// <summary>
/// Gets the store locations and the coordinate for the "from" area (the address the user entered).
/// </summary>
/// <param name="coordinate">A coordinate.</param>
/// <param name="data">A LocationsData dataset.</param>
private LocationsData GetLocationData(Coordinate coordinate)
{
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["StoresDb"].ConnectionString);
connection.Open();

string SQL = "GetNearbyLocations";
SqlCommand command = new SqlCommand(SQL, connection);
command.Parameters.Add(new SqlParameter("@CenterLatitude", coordinate.Latitude));
command.Parameters.Add(new SqlParameter("@CenterLongitude", coordinate.Longitude));
command.Parameters.Add(new SqlParameter("@SearchDistance", distanceDropDown.SelectedValue));
command.Parameters.Add(new SqlParameter("@EarthRadius", 3961)); // In Miles
command.CommandType = CommandType.StoredProcedure;

SqlDataAdapter da = new SqlDataAdapter(command);
LocationsData data = new LocationsData();
da.Fill(data.Location);
return data;
}

... we have to create a string representation of a JSON object array that holds all this info, this is done in the GetJSONLocations method. Please see the method for more documentation on how that variable is created.

Info that the JSON object holds: 
   - Location Name
   - Address 
   - Url Encoded Address
   - Latitude
   - Longitude

This info was written to the screen using the RegisterClientScriptBlock.

Using the same method of as before we create another JSON variable (homeSpatialInfo) that holds the “home info”. This info is the address that the user typed in.

This information is used for the popup marker, as well as the link that is provided within the popup marker. Clicking on the “directions” link (within the popup marker) will take you to Google’s map site with the “to” and “from” address parameters already populated, therefore giving you a directional map from the location you typed in, to the location that you clicked on the map. Smooth, eh?

(click for larger view)

Adding a new Location

To add a new location, fire up the AddNewStore.aspx page and type in the required info (in this case I’m requiring all fields, but this can be changed to fit your own needs) and submit. If the address cannot be found, (Google Maps API returns “0” for both the Lat/Lon coordinates that it cannot find) it will inform you. Otherwise the address will be added and then upon your next search, it will be returned in the results (if the location is within the distance specified).

Database Note: The locations that are in the Stores.mdf that come with this solution are 200 Starbucks stores that are within the New York Area. This is not a comprehensive list, this is just a list I happened to have for testing purposes.

And that’s all folks!

Download and enjoy. :)

StoreLocator.zip (521.92 KB)

SQL for DB (7.3 KB)

In my previous post I showed you how to return XML from the SQL Server Database. To further this topic, you can also format your XML to use Xml elements. This time, we're going to take a list of Customers and view their orders along with some information from the order, thats right, its straight from MSDN (with a couple tweaks).

This SQL Query will return Xml formatted as formatted elements.

Returns:

<Customers>
   <Customer>
      <CustomerID>1</CustomerID>
      <CustomerType>S</CustomerType>
         <OrderHeader>
            <CustomerID>1</CustomerID>
            <SalesOrderID>43860</SalesOrderID>
            <Status>5</Status>
         </OrderHeader>
         <OrderHeader>
            <CustomerID>1</CustomerID>
            <SalesOrderID>44501</SalesOrderID>
            <Status>5</Status>
         </OrderHeader>
         <OrderHeader>
            <CustomerID>1</CustomerID>
            <SalesOrderID>45283</SalesOrderID>
            <Status>5</Status>
         </OrderHeader>
         <OrderHeader>
            <CustomerID>1</CustomerID>
            <SalesOrderID>46042</SalesOrderID>
            <Status>5</Status>
         </OrderHeader>
      </Customer>
      ...
<Customers>

Explanation

The XML AUTO mode tells SQL Server to return the data in a XML format. We have provided the Sales.Customer table an alias of "Customer". If I had not done this, the results would have printed each element as:

<Sales.Customer>...</Sales.Customer>

This is not desirable.

The ELEMENTS option tells SQL Server to return the data as Xml elements, not as attributes. Had we eliminated this from the query, our query would have returned this:

This can work, but this time I wanted to demostrate the ELEMENT option.

The ROOT('Customers') allows us to set a root Xml element named "Customers".

Conclusion

While this may not be the next best thing you've ever seen, its still cool and it can save you time in the long run. Possible uses might include a quick way to get data out of your system and into a XML format for a one time use. Or you could use it for your data access layer and have it return XML to the business layer that might be used in a web service.

Its been reported that working with large XML Docs in .NET can be memory intensive, so perhaps this might help in processing and in saving memory on that server thats running on an old desktop that your boss had laying around. (We've all worked at a company who had a 'server' which was nothing more than a beefy desktop sitting in the server room.) :)

You can easily generate Xml from a DataTable by using the .WriteXml() method.

But you can also bypass this and retrieve the Xml directly SQL Server 2005 by running the following command:

USE Northwind;

SELECT    EmployeeID,
        LastName,
        FirstName
FROM    dbo.Employees
FOR XML AUTO;

This will return an Xml representation of the data that looks like this:

<dbo.Employees EmployeeID="1" LastName="Davolio" FirstName="Nancy" />
<dbo.Employees EmployeeID="2" LastName="Fuller" FirstName="Andrew" />
<dbo.Employees EmployeeID="3" LastName="Leverling" FirstName="Janet" />
<dbo.Employees EmployeeID="4" LastName="Peacock" FirstName="Margaret" />
<dbo.Employees EmployeeID="5" LastName="Buchanan" FirstName="Steven" />
<dbo.Employees EmployeeID="6" LastName="Suyama" FirstName="Michael" />
<dbo.Employees EmployeeID="7" LastName="King" FirstName="Robert" />
<dbo.Employees EmployeeID="8" LastName="Callahan" FirstName="Laura" />
<dbo.Employees EmployeeID="9" LastName="Dodsworth" FirstName="Anne" />

For more information on FOR XML and its modes, click here.