SQL and XML

In my previous blog post I talked about the SQL standard.  It is tempting to visualize a standard as a list of rules nailed to a wall.  However things in this industry have a habit of becoming a moving target.  

Since the initial standard was adopted in 1986, there have been six revisions, of which the most recent was in 2008.  Technology today is far different than it was in 1986, but the same principals still apply, so the standard has evolved to incorporate new ideas in with the old rules.

XML related features were added in 2003 to coincide with the explosion of XML related technologies.  It became a selling point, so most of the major database vendors quickly added support for native handling of XML.  Since I have recently been working in the realm of MSSQL, I will concentrate on their implementation.

Microsoft SQL Server 2005 added a native XML data type that can be used to store XML documents, or fragments of XML.  A fragment is defined as an XML instance without an enclosing top level element.  Before the inclusion of this data type, users who wished to store an XML file would have to parse the data and store it separately.

So as a contrived example, say a shipping company gets notified a delivery is made and they only really care about the carrier id and the time of the delivery.  However the XML document contains a lot of extra information, such as the address, the size of the package, where it originated, and all the steps along the way.

Prior to the availability of the XML data type, they would have had to create space in their database for each individual piece of information if they wanted to retain it for future use.  Now, since the input document is already in XML format, it is a fairly trivial task to create a table and store the entire document in the database.

TABLE “DeliveryInfo”

(

“CARRIER_ID” int not null,

“DELIVERY_DATE” date not null,

“NOTIFICATION” XML not null

);

That’s it.  Far easier to use, however just because you can do something doesn’t mean it’s a best practice.  Much depends on the particular business objective.

XML is ubiquitous these days, so knowing what to do with it is an important part of what we do as programmers.  I’ve shown you one way of storing it, so in my next blog I’ll talk about another way to create it.

One thought on “SQL and XML

  1. Pingback: Tweets that mention SQL and XML | EXTOL Technology Blog -- Topsy.com

Leave a Reply

Your email address will not be published. Required fields are marked *


*