hash_bucket()

we are all FOR XML

Posted on: March 29, 2007

A while back I architected a content management system for a client. The system is built on a combination of web services, windows services, databases and rich web browser client technology, and during this process I made heavy use of the built in support for XML in the Microsoft SQL Server 2005 combined with the tools available in the System.XML namespace.

One of the problems that many developers seem to run into early on however, is how to get the beautiful XML that the server can produce for you, out to files that can be published.

Using the FOR XML syntax in T-SQL, and its various constructs, it is relatively easy to get SQL Server to produce nicely formatted output, including schema definitions, namespaces, user-defined paths and so on.

In this post I would like to just walk through a simple example that shows a very basic procedure for producing an XML representation of a set of tables, and how to get that representation out to disk. The example will use an SQL Server with a stored procedure for producing the XML, and a small C# method that takes the constructed XML and saves it to disk.

First off, lets take a look at the FOR XML syntax. (The official documentation can be found here)

To put it simple, FOR XML can be tucked on to any SELECT statement in order to format the result set not as a set of ROWs but as an XML structure. In its most simple form it might look something like this:

SELECT * FROM MyDatabaseTableWithPeople FOR XML AUTO

(Don’t worry about the AUTO keyword, we’ll get to that in a moment)

This query might produce a result looking something like this (assuming I have only 2 columns, a Name and a BIT that shows how friendly we are…):

<MyDatabaseTableWithPeople Name=”Kalle” Friend=”1″ />
<MyDatabaseTableWithPeople Name=”Kalle” Friend=”1″ />
<MyDatabaseTableWithPeople Name=”Sven” Friend=”0″ />
<MyDatabaseTableWithPeople Name=”Anna” Friend=”1″ />
<MyDatabaseTableWithPeople Name=”Jocke” Friend=”1″ />

While this is nice, a lot of information is missing and the formatting looks like it might need some work. The reason why we see each table row as an XML row is due to the AUTO keyword that we tucked on to the end of our query. FOR XML can be used in 4 different formatting ‘modes’:

  1. RAW
  2. AUTO
  3. EXPLICIT
  4. PATH

We wont go into all of these in this post, and truth to be said they are more than just ‘formatting modes’. But for this very basic introduction that will suffice.

In AUTO mode, the resulting XML will be nested based on how you construct your SELECT query, thus in order to achieve nested XML you will need to construct nested statements. This is beyond the scope of this post, but you can read more about it here. AUTO can be nice if all you need is a minimal XML representation to be injected into another database or another XML document, but for simple scenarios when you just want to get the data out, RAW might be a better option. This is the option we will use in this example.

(A side not, actually AUTO is a pretty intelligent little fellow that will for example try to organize entries that share for example an ID in a hierarchical way, this can be very nice for real world data and I encourage you to research this further if you find this post interesting.)

Let’s change the AUTO keyword to RAW and rerun our query:

<Row Name=”Kalle” Friend=”1″ />
<Row Name=”Kalle” Friend=”1″ />

Looks about the same, with one immediate difference, the name of the table has been replaced with the word Row. This is because the RAW keyword will simple format the output of the SQL query as XML without making any guesses as to how we might want to name our XML Nodes. Let’s change that Row word into something more nice, a name that might be useful in parsing or databinding, and that makes sense in the context of the data:

Our SQL query now reads:

SELECT * FROM MyDatabaseTableWithPeople FOR XML RAW (‘Friend’)

and the result is:

<Friend Name=”Kalle” Friend=”1″ />
<Friend Name=”Kalle” Friend=”1″ />

Much nicer right.

Now the next thing we probable want to do is change the attributes into child nodes. This can easily be accomplished by adding the key word ELEMENTS to our query, like so:

SELECT * FROM MyDatabaseTableWithPeople FOR XML RAW (‘Person’), ELEMENTS

Giving us:

<Person>
<Name>Kalle</Name>
<Friend>1</Friend>
</Person>

Already an improvement!

We might also want to accommodate for NULL values in our data, as not doing so would mean that they are simply left out of the XML all together, which might lead to parsing errors later on. Doing so is simple, just add the keyword XNSINIL after the ELEMENTS key word, like so:

SELECT * FROM MyDatabaseTableWithPeople FOR XML RAW (‘Person’), ELEMENTS XSINIL

This construct will also add an xmlns attribute to each item in your XML. Might seem a bit redundant but it makes for safer output. What actually goes on under the hood is a different discussion, and this example is meant to be simple. Google if you want to know more.

Now before we are finished, let’s also add a root XML node to make our document complete. In a real world scenario you probable want to add more data at other levels in the document, but for us this will suffice:

SELECT * FROM MyDatabaseTableWithPeople FOR XML RAW (‘Person’), ROOT (‘PeopleList’), ELEMENTS XSINIL

Adding the ROOT key word also brings another advantage, namely that the xmlns attribute is now only defined on the root element, which makes for cleaner XML.

Ok, so now we are happy with our rudimentary and simple XML document, so let’s save our query in a stored procedure and write some client code to get it out of the database.

The following method can be placed in any C# context provided that you reference the System.Xml, System.Data.Sql and System.Data.SqlClient.

	SqlConnection conn =
	     new SqlConnection(someConnectionString);
	conn.Open();
	string cmdText = nameOfYourStoredProcedure;

	SqlCommand xmlExportCom = new SqlCommand();
	xmlExportCom.CommandType = CommandType.StoredProcedure;
	xmlExportCom.CommandText = cmdText;
	xmlExportCom.Connection = conn;

	try
	{
	  XmlDocument d = new XmlDocument();
	  XmlReader x = xmlExportCom.ExecuteXmlReader();
	  d.Load(x);
	  using (XmlWriter w =
		XmlTextWriter.Create(path, settings))
	  {
	  	d.Save(w);
		w.Close();
		x.Close();
	  }
	}
	catch (Exception)
	{
		//do something
	}
	finally
	{
		xmlExportCom.Dispose();
		conn.Close();
		conn.Dispose();
	}

(The code is a little bit verbose for clarity…)

So what do we have here… Let’s break it down into steps:

  1. Open a connection to the database using some connection string
  2. Store the name of your StoredProcedure in a string for easy reference
  3. Build an SqlCommand, setting the CommandType to StoredProcedure, the CommandText to the string you created in step 2, and the Connection to the connection created in step 1
  4. Instantiate an XmlDocument to hold the result of the SqlCommand
  5. Instantiate an XmlReader to the result of the SqlCommand by executing its ExecuteXmlReader() method, which returns an XmlReader
  6. Load the XmlReader into the XmlDocument
  7. Instantiate an XmlWriter with the path(string) to where you want to save your docuemnt and (optionally) an XmlWriterSettings object that might specify for example indentation.
  8. Save the XmlDocument using the XmlWriter
  9. Close the XmlWriter
  10. Close the XmlReader
  11. Dispose the SqlCommand
  12. Close and Dispose the SqlConnection.

And Voila! There’s your pretty XML file steaming fresh from the Sql Database onto your disk in pretty formatted human readable text :)

That’s it. Please consult the links below for more info:

Constructing XML Using FOR XML

Using XML in SQL Server

System.Xml Namespace

Advertisements

13 Responses to "we are all FOR XML"

One must say that your blog is… diverse in its range of topics, if nothing else ;-)

A very well formatted guide, exactly the type of walkthrough that’s always hard to find, regardless of subject. Well fitting a former guldmorot-nominee! ;-)

Speaking of XML, I have had some experience trying to parse XML through inDesign CS2, which boasts awesome XML skillz. However, there’s no way to create styling in a way resembling XSLT, instead every expected instance of content must be laid out by hand…. meaning that, if you have for instance a list containing twenty book titles with accompanying information and twelve movies + info, you have to create twenty empty book template paragraphs, and twelve empty movie paragraphs. If you then add a book to your database, you have to edit the inDesign-document, moving everything about to make room for another empty book paragraph to be filled with data…. so very very un-dynamic. I was for a long time convinced that I’d missed some important aspect, but I don’t think so. The only way to get around it is to create two separate documents, one for books and one for movies, in this example, fill both with loads of empty paragraphs, import the XML data, delete the empty paragraphs, and merge the documents…

thank you! I actually want to write more of these, but the net has such a wealth of information that sometimes I just feel like, .. is there really need for another XyZ-walkthough?? And then sometimes you come across a problem domain where people seem to ask the same questions over and over again..

About the CS2 XML support. That is interesting. Actually I haven’t had PS installed for a long time, since I almost never do any work like that anymore… But I would love to sneak a peak at it and see what it looks like. Perhaps you could write your own plug-in to better format and handle XML files, or maybe there already are some out there…

I’m tempted to simply write “arp”. But I think I actually get the gist of what you are writing here, so let me instead say: Keep up the good work.

spoof?
You need any applications developed over there just let me know and maybe we can work something out! You people do such an important and wonderful work. I often get the feeling that it would be great to be able to help… :)

Thanks globbe, I really appreciate that. And we just might have something where you’re input would be of great value. Actually, I am currently in the process of developing a comunication strategy for a Bolivian educational institute, but focused on their digital environment. Since money and resources are as scarce as always we’ve naturally opted to go with an open source CMS (drupal). I’ll have some of the more important conceptual documents translated and send’em over. It would be great to get your opinion on this :)

Just send it over and I’d love to give you my 2-cents! You have my email address right?

Daniel – Drupal is a great CMS! I’ve just started playing around with it myself. It is insanely modulable (?) and easy to use.

Peter, drupal is a delight isn’t it… I couldn’t agree with you more. Actually, I would love to get your opinion on this as well. Ok, if I send over some material for you to look at?

Globbe, Thanks, I’ve got your email right here :)

Daniel!
Send it over :)
Hmm I know little of Drupal, but they seem to have a very vibrant user community so getting support and help should be easy. Also module development seems easy enough so I guess extending it wouldn’t be too much of a hassle.

Peter!
What kind of scenario are you using it in? Have you done any module-dev?

I haven’t had time to do any development work with drupal – and I probably won’t find that time anytime soon. However, I don’t find myself feeling constrained by that, the system is so modular in itself that I don’t think I will need to do any serious dev. work in the near future.

Daniel – ofc, send it over!

Peter.
Judging by the info at their website custom mod.dev. is only necessary if you have a very custom scenario OR you have a personal interest in programming against it. The mod community seems very big indeed. Still knowing you knowing me, you’re probably not going to be all contempt with an out-of-the-box scenario ;). Anyhow if you do start to look into dev. please let me know as I would love to here what kind of experience it is.

(Truth be told I am also looking into community sites at the moment, like Community Server and DotNetNuke and so on…)

Ylia

Ich nicht ferschtein this articklees

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

.

This blog has no clear focus. It has a focus though, it's just not very clear at the moment...

Dev Env.

Visual Studio 2008 Prof / NUnit / Gallio / csUnit / STools (ExactMagic) / doxygen / dxCore / TypeMock / TestDriven.net / SequenceViz / CLRProfiler / Snoop / Reflector / Mole / FxCop / Subversion / TortoiseSVN / SlikSVN / CruiseControl.net / msbuild / nant

Blog Stats

  • 81,304 hits