Cool stuff

I've just discovered something I didn't know I could do.

The bit nobody seems to write about when they're writing about data-driven applications is SQL Server's facility to produce query output as XML. In its simplest form, the T-SQL for getting the Authors table from the Pubs Database is:

Select * from Authors FOR XML AUTO, ELEMENTS

MSDN's otherwise excellent documentation gets a bit sketchy after that, although I do remember reading somewhere (although I can't remember where. I'm going to have to go through my books to find out) that the AUTO keyword essentially lets SQL Server arrange the data as it sees fit. The ELEMENTS keyword tells it to arrange fields and relationships as a nested set of elements.

Once that's been created as your SQLCommand object, you can then create an XmlReader object using the SQLCommand.ExecuteXMLReader() method. After that, the world is your XML-oyster. Having discovered this (I was dimly aware of it, but didn't think MSDE supported it) I think I'll have to have a play with it.

I think I just need to think of a few applications for it. One of the ones that shouts out at me is html-based reporting. Not only can I produce reports that look nice without creating too much of a load on the server (XMLReader's streaming nature means that the machine's not sitting with half a ton of DataSet in memory) dynamically, but I can create archived reports by feeding it straight through an XSLT transformation and saving it as an HTML page. Previously, all the archive reporting I've done uses hideously long and drawn out SQL Statements to try and guess the state of the data I was working with at the time in question - not any more.

And it's another thing that I know now that I didn't before, which is the main thing.