XML in the Database
-
It's been an interesting week and I've been surprised at the response to the articles and editorials this week. It seems at the conferences and in newsgroups that many people are quite excited about using XML in their applications.
However the response has been quite the opposite here from DBAs that don't want XML in their databases. That's how I feel, but I thought I was in the minority. I think a relational database should do what it does well, store, index, and retrieve sets of data in a normalized fashion. All that other processing should take place in application servers.
Actually I wish Microsoft would come up with a specific "App Server" designed to host CLR objects, decompose or build XML, and perform some of the processing that we ask of our database server. Shouldn't they be able to configure this "server" to connect through back channels at high speeds and be easy to develop against? Maybe this is BizTalk or something else I'm missing, but I'd like to see the database server be just that: the database server. Let other software connect in and perform the fancy processing that we're trying to extend SQL Server to do.
I'm not saying that XML should never be in the database. It is an interesting use for some problems, like passing in an array of values. But why can't we just get the capability to pass arrays to stored procedures?
It's been interesting and I appreciate the responses. I've had some interest from authors to write more on XML, so perhaps we'll get enough for an XML week in March!
Send in your articles!! Use them as the basis for a speech at the PASS Summit. You've got until March 2 to submit them!
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
Hi Steve (and everyone else)
I am a developer that just happens to use an SQL server instance and "got stuck" with its administration, due to a lack of IT staff in my immediate department.
I take on board everything you say - let the database just do what it's good at - and let other technologies do what they are good at. And futhermore, let an interface of sorts join the two together.
While I certainly make sure that my application servers have appropriate disaster recovery processes in place and that there is sufficient redundant hardrware options too - it's normally a pretty safe bet that there is a "great" emphasis placed on these "disaster" themes with regards to servers that house your data.
With that in mind and with my developer's hat on.... it is a great place to store / use my XML documents.
Gavin Baumanis
Smith and Wesson. The original point and click device.
-
Steve,
I'm on your side. I've never understood why it was such a big deal to store xml in the database. Xml itself is a type of database structure. A very crude one, but a type of hierarchical structure. While it has it's uses, I don't see why a relational database should offer xml structures other than maybe take it as an input to stored procs or have routines to output it from a stored proc. Either way, those are parsing functions, not storage functions. If an app just needs some place to stuff some xml, to me they can just use an appropriate varchar/text column and stuff it in. It's just text. (And I'm a developer, not a DBA)
-
I think beyond passing arrays, being able to pass in structures would resolve the need for passing in xml. As soon as xml is introduced as a parameter to a sproc the interface to that sproc is no longer clear. Anything in any format could be passed in.
My confidence in the validity of parms passed in is reduced. It really lends itself to confusing coding. I have seen this in the past, where a string becomes xml and the xml allows multiple parms to get passed in. Then all kinds of funky coding (read hack) begins, to deal with this, not architected for, approach.
-
I've seen some SQL 2000 procs where varchar was used to hold xml going in and out of a procedure and it did look like a royal hack.
-
The reason for that is very simple 2000 does not shred or decompose the XML
Kind regards,
Gift Peddie -
My thoughts would be to use XML in the database only where a clear case for it can be justified, with a prejudice against.
If my "job" was to store and retrieve XML documents that had relationships to other data, I would consider it, but I would weigh the pros and cons just like storing any other image type data in the database.
When you get into decomposing XML on the fly to run queries, you're manipulating data too many times. Decompose and store relational data in the relational database, just like it says in RDBMS 101. I've had this argument with developers that wanted to store ANSI X12 documents as BLOBs, making copies of specific elements in related tables for running queries. Might be a good solution until you discover that you need to add yet another element for a particular business purpose. And then another.
In general, I'd say keep the database relational unless you find a really good reason not to. Use XML (or X12) as a batch data or message transfer format.
-
Why can't developers just go the extra step and save the individual data elements to a relational structure. If you're storing XML in the database and not taking advantage of the relational aspects then you might as well just save them to the file system. The purpose of a RDBMS is to provide data in a format that is easy to maintain and report on. You can't have constraints on the XML stored in the database so why bother putting it there in the first place.
For the record, I am primarily a DBA but I have a lot of development experience.
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog -
I too am in the camp of not liking it, at least not as a primary way of storing data. We do use XML though in the company I now work for, and it sort of makes sense.
We work with a lot of forms, and forms that change often, and has new forms with new elements added all the time. So what we do is we store the primary information about the policy in a proper maner, but store all detail information in XML so that a new form can be added easily. The business unit managers can design a new form and have it added with minimal work on IT's part. Works pretty well, even performance against it is decent.
-
A SQL Server is a server dedicated only to SQL Server <period>
A new 'application' server is definitely needed for CLR, XML and anything else that is not SQL <period>
SET RESTATE_PREVIOUS_POST ON
NIMDB
SET RESTATE_PREVIOUS_POST OFF
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
I'm both developer and defacto-DBA. I agree that SQL Server ought to stick to what it does best (manage relational data) and we should use something more purpose-built for XML. While I'll be using some XML messaging to transmit data to SQL, if I want the XML documents and want to process them as such, then I am going to use Tamino or some other XML database.
------------
Buy the ticket, take the ride. -- Hunter S. Thompson -
I agree with you, Steve. I think it is great to have XML handlers that can easily translate from a table to XML and vice versa. After all, the point of an XML use in this case should be to correspond to a database table or row. So there should be functions to take SQL data and make it XML and another function to take XML and make it into a table or recordset that SQL Server can work with.
But I don't think (if I understand correctly) that XML should be a data type or otherwise be a part of SQL Server data structures. The division of labor makes sense. Plus I don't know how much overhead it adds to the SQL Server to handle XML when the recordsets get gigantic.
Just my two cents.
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html -
One more thing. According to the W3C spec, "Extensible Markup Language (XML) is a simple, very flexible text format derived from SGML (ISO 8879). Originally designed to meet the challenges of large-scale electronic publishing, XML is also playing an increasingly important role in the exchange of a wide variety of data on the Web and elsewhere."
A search for the word "storage" returned no results on the page. This reinforces what I have read in the past about XML being for data interchange and not for data storage.
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog -
Yes, that's a good point. I'm no XML expert but what you quoted makes sense to me. Plus the couple of times I have used XML, it has been a fairly complex structure to work with, something that to me indicates it falls squarely on the application development side.
An interesting approach might be for DBAs and database developers to be able to articulate their resistance to including XML in any database structures and put that stuff on the app server to interact with SQL -- even if Microsoft touts SQL Server's XML capabilities. I'm sure there will be exceptions where XML will be the way the leadership decides to go, but excluding it from the DB as a rule and with some cogent reasons (such as XML's intent as an interchange format) seems quite sensible to me. And it would be less likely to be taken as obstructionist grumbling.
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html -
I wish we could either pass arrays or table variables as parameters. Doesn't it just make sense that if they call it a table VARIABLE that we could pass it as such?
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply