# Wednesday, July 30, 2003

When new data comes in from monitors, it needs to be processed and then stored in the DB.  (See Xml on Large Power Transformers and Industrial Batteries for some background.)  I’m currently pondering how to manage that, and what I’m thinking is that I’ll process the incoming data as XML, using XPath and some strongly typed methods for doing inserts.  The issue I’m wrestling with is how to get the XML data into the database.  We’re currently using SQLXML for some things, so I could style it into an updategram.  Initially I was thinking of just chucking the whole incoming XML doc into a stored proc, and then using SQL Server 2K’s OPENXML method to read the document inside the stored procedure and do all the inserts from there.  The advantage is that I can rip up the doc and do a bunch of inserts into normalized tables and keep it all in the context of one transaction in the sproc.  Also, it keeps me from having to write C# code to parse out all the stuff that’s in the doc and do the inserts from there (although that’s also an option).  Usually I’m opposed to putting much code into the DB, since it’s the hardest bit to scale, but in this case it wouldn’t be business logic, just data parsing, which seems like a pretty reasonable thing for a database application to do. 

With that all out of the way, my concern is that OPENXML is fairly complex, and would require some relatively involved TSQL (which I can do, but would rather not, and hate to maintain).  Also, I worry about it all being made irrelevant by Yukon, which is supposed to have some pretty wizzy new XML related data storage stuff.  

Another option would be to style the incoming data into a dataset and use ADO.NET for the dirty work.  Since I’m really only ever doing inserts, not updates, it should be pretty straightforward. 

Sigh.  Too many choices.

Saturday, August 02, 2003 4:03:59 AM (Pacific Daylight Time, UTC-07:00)
I've had great results with OPENXML. Its very easy to insert unknown rows of data with a single trans with relatively few lines of code. Much less code than parsing it in the sproc, and more elegant than having DA code call the sproc multiple times. I'll resort to the XP mantra and deal with the new version of SQL Server when it gets here. By the way Patrick, its pretty freaky to be flipping through a book in Powells and see your face, and realize I saw you speak a little while back. Way to go! :D
Andrew Hay
Friday, August 01, 2003 8:27:02 PM (Pacific Daylight Time, UTC-07:00)
Over the last few years I've coded and re-coded the SOAP-based data load for our company's product. I have used each of the technologies that you mention for this function, each one with distinct pros and cons. In the first iteration, pre-.Net, I had to write my own SOAP/XML & XSD validation and shredder in javascript on ASP, utilizing SQLXML with annotated XSD to map to the database. The great thing about this implementation was that the XML Schema centralized the mapping of the XML to the relational database. The bad thing...All of the round-trips to the database, and performance. I later re-implemented in C#, using custom SoapExtensions to intercept the raw XML stream, validate with XSD, and pass the segment of XML used for an insert or update to a single T-SQL Procedure, as a TEXT argument, then use OPENXML to create a table-like rowset on the XML data. Which was great, simply because I could join it to the various tables in our databases. The best thing about this was, it was about 20 times faster for the same functionality. Although, I dislike having so much business logic in a proc, somehow it just violates the idea of a 3-tier design.
Kris Williams
Comments are closed.