hash_bucket()

Archive for March 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

Oj oj oj! Hela min dag, kanske hela veckan blev just helt fantastisk! (Sarskilt rekomenderad till alla rollspelare!)

http://www.dn.se/DNet/jsp/polopoly.jsp?d=2453&a=603517

Sunday

Posted on: March 11, 2007

This morning we woke up to a steel-gray sky and the promise of rain hanging in the air. Strong winds had been rocking the house all night, and as the morning gloom slowly turned to day it still whined and whistled through the trees and wires outside our window.

S has the measles, a virus disease that most of us in Sweden, due to vaccination, never get a chance to actually experience up front. In Japan however, there are still cases reported every year, mostly because some people refrain from the vaccination due to some uncertainties regarding possible side effects that have yet to be thoroughly researched.

The measles is a really bad and troublesome disease. It starts as a regular cold with fevers and coughing. This then fades and gives way to swollen lymph’s, aching joints, more fever and a really bad dry cough. After a few days a red rash appears, usually in the face at first, that rapidly spreads all over the body. Needless to say this rash itches really bad. Bad enough to keep you from sleeping at nights and driving you crazy during the day. Starting with the rash, or just before it, the inside of the mouth also starts to ache bad, making it difficult to eat.

All in all it is a very painful experience, and even though it is not so dangerous it is just plain awful while it happens. Usually it lasts for about 2-2.5 week, but the rash can stay on for up to a month.
Thus I am trying my best to act doctor, cooking soups, buying medicine and so on, and even though it is very hard to see S in so much pain, I am also a little bit happy that I get a chance to take care of her a little… I know it’s bad to think so bit it still makes me feel good to know that I can do something for her :).

As the afternoon hours start rolling in the strong winds of the morning are finally packing up the gray clouds and leaving, opening the sky to a bright blue and endless open. I love the majestic sight of the city bathing in sunlight under the dark, almost black clouds. The sky divided in two with a beautiful spring afternoon on one side and a dark winter sky on the other. Still the sun is already getting ahead of itself, playing around amongst the skyscrapers, reflecting in a thousand windows, accompanied by the sounds of the neighbourhood kids getting back out to play in the streets.

Sometimes making fun of other cultures really misses the spot, sometimes it is just plain trashing, ignorance or bashing. However, this one, produced by a pair of japanese comedians called the Ramenz is just great. Especially since it it done with so much love for the cultural phenomenon it portrays. :) These guys have made a whole bunch of these, but this one is the only one I could find with subtitles. Enjoy, and more importantly, learn!

Honestly I don’t know if I should laugh or cry… I feel sorry for the poor thing, but at the same time.. the scene is just too funny…. Men in black suits, a cat with an antenna in its tail, a taxi driver, Russian spies…

Read about it for yourself:

http://en.wikipedia.org/wiki/Acoustic_Kitty


.

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,346 hits