Tuesday, March 28, 2006

ExecuteXmlReader woes

These days I get problems which by no means are "funny" (I brand a problem as funny which is quite challenging and still fun to work on). So the problem is this: given a one-to-many relationship between two tables in Sql Server, generate a one-time report in excel which linearizes (I don't know whether it's the correct term) the entire data in one row for a given main entity. Confused? so was I when I got this requirement, let me try to explain the problem (often times knowing the problem itself means you're half way there!) by giving an e.g. consider two tables Orders and OrderLines, Order table contains the data about the order (let's say the customer name, date entered etc.) and the OrderDetails contains data about the line items in a single order (let's say the product information abt the data that was ordered etc.). Here's a snippet of a highly denormalized (it works for the e.g. that I'm abt to give) design of these two tables in db:
OrderId int pk,
CustomerName varchar(100)

OrderId int not null fk_refers_to_Order
ProductName varchar(100).
Here's a sample set of data:
Order Table:


Order Details Table:


The generated report should have output in this format:

OrderIdProduct 1Product 2

So how do you solve it, well here's what I did...loaded the data using for xml explicit so that the OrderLines data is rolled up and added to Order node as children. Wrote an Xsl to iterate through the nodes and print them in a csv format and loaded the csv in xls. Different story that I faced quite a few issues with this approach! Firstly, for xml explicity returns the resultset as a fragment (i.e. there is no root node), during ASP/VB days I could add a dummy root node quite easily in the xml template but I couldn't any easy way of achieving it in ADO.Net! The only approach that I found was on serverside.net which added the dummy root node in the for xml query itself. Once I got the XmlTextReader, I loaded it into a dataset with XmlReadMode.InferSchema and used dataset.WriteXml to write the entire xml to a file (I had to follow this approach just to get the entire xml from "for xml" query, the query analyzer always truncates the results if it exceeds certain characters). With the xml file in hand I wrote some quick n dirty (this operation was one time only and hence there was no need to automate the entire process) xsl in cooktop which would save the generate a csv output.
The relevant snippets of code and xsl are below:

XmlTextReader r = (XmlTextReader)cmd.ExecuteXmlReader();

Update: Had to remove the xsl listing as it was randomly blewing up my feed! Will put it back on once I figure it out.

No comments:

Post a Comment