Saving an XML file from an XML Query in SQL Server


This is a short one that was spurred by the following question:

Any idea how to export an xml document from ms sql 2008 using vb.net into a
folder some where? I am running an SP that generates the xml already.

The answer is divided into a bunch of sections.

Streams and EndPoints

We have to go back to my previous entry on streams and endpoints (see Understanding Data in .NET). If you read it, you remember that endpoints are where data is persisted and streams are devices to get data from one endpoint to another. Examples of endpoints in this question:

  1. SQL Server
  2. XML file on the file system

I am not going to reiterate the entire article, so read it if you need more info.

XML in SQL Server

If you want to play along, download Adventure Works. I am simply using the Address table in the Person schema. I am running SQL Server 2005 on this particular machine, although I have SQL 2008 R2 on my home machine. It works on any of these (SQL 2005, SQL 2008, SQL 2008 R2).

To select, I have a couple of choices with the FOR XML clause. If I like attributes, I might do the following:

Code Snippet
  1. select Top 1 *
  2. from Person.Address
  3. FOR XML PATH(‘Person’), ROOT(‘Doc’)

This produces XML in elements:

<Doc>
  <Person>
    <AddressID>1</AddressID>
    <AddressLine1>1970 Napa Ct.</AddressLine1>
    <City>Bothell</City>
    <StateProvinceID>79</StateProvinceID>
    <PostalCode>98011</PostalCode>
    <rowguid>9AADCB0D-36CF-483F-84D8-585C2D4EC6E9</rowguid>
    <ModifiedDate>1998-01-04T00:00:00</ModifiedDate>
  </Person>
</Doc>

Or I could choose to use XML RAW:

Code Snippet
  1. select TOP 1 *
  2. from Person.Address
  3. FOR XML RAW, ROOT(‘Doc’)

This creates attributes, but uses the generic name “row”:

<Doc>
  <row AddressID="1" AddressLine1="1970 Napa Ct." City="Bothell" StateProvinceID="79"
     PostalCode="98011" rowguid="9AADCB0D-36CF-483F-84D8-585C2D4EC6E9"
     ModifiedDate="1998-01-04T00:00:00" />
</Doc>

Or  FOR XML AUTO:

Code Snippet
  1. select TOP 1 *
  2. from Person.Address
  3. FOR XML AUTO, ROOT(‘Doc’)

This produces the name Person, which it figures from table name

<Doc>
  <Person.Address AddressID="1" AddressLine1="1970 Napa Ct." City="Bothell" StateProvinceID="79"
      PostalCode="98011" rowguid="9AADCB0D-36CF-483F-84D8-585C2D4EC6E9"
      ModifiedDate="1998-01-04T00:00:00" />
</Doc>

There is also FOR XML EXPLICIT for more complex examples, but I end up having to create the “map” of the elements/attributes to use this format. The method is not important to this post. I just show the following to give an idea of what SQL Server does.

NOTE: Each of these examples has a full XML document. If I merely want snippets I drop the ROOT portion of the statement.

Getting the Data

There is no reason to format this data into objects, so we can pull it with a SqlDataReader. Here is a very simple pattern:

Code Snippet
  1. string connectionString =
  2.     "server=(local);database=AdventureWorks;Integrated Security=SSPI;";
  3. string sql =
  4.     "select Top 1 * from Person.Address FOR XML PATH(‘Person’), ROOT(‘Doc’)";
  5. SqlConnection conn = new SqlConnection(connectionString);
  6. SqlCommand cmd = new SqlCommand(sql, conn);
  7. try
  8. {
  9.     conn.Open();
  10.     SqlDataReader reader = cmd.ExecuteReader();
  11. }
  12. finally
  13. {
  14.     conn.Dispose();
  15. }

The reader now contains the data. Of course, the above snippet does nothing other than sets things up.

Outputting XML

If all we want is a file, then we could do this with a StreamWriter. Something like this:

Code Snippet
  1. string connectionString =
  2.     "server=(local);database=AdventureWorks;Integrated Security=SSPI;";
  3. string sql =
  4.     "select Top 1 * from Person.Address FOR XML PATH(‘Person’), ROOT(‘Doc’)";
  5. SqlConnection conn = new SqlConnection(connectionString);
  6. SqlCommand cmd = new SqlCommand(sql, conn);
  7. try
  8. {
  9.     conn.Open();
  10.     SqlDataReader reader = cmd.ExecuteReader();
  11.     StreamWriter writer = new StreamWriter("C:\projects\test.xml");
  12.     while (reader.Read())
  13.     {
  14.         writer.Write(reader[0]);
  15.     }
  16.     writer.Dispose();
  17. }
  18. finally
  19. {
  20.     conn.Dispose();
  21. }

Fairly straightforward. This will not check the validity of the XML, but we are fairly confident it is valid as is. There are enhancements we could make if we needed to validate the XML, etc. We could also move into other readers and writers.

The main takeaway here is that the reader is effectively streaming data from the database and the writer is taking that stream of data and writing it out. I wish I had a bit more time and could play with this some more, but this solves the problem at hand and alleviates having to explain XML.

Peace and Grace,
Greg

Twitter: @gbworld

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: