08
Apr
10

Chapter 10 – Introduction to XQuery on SQL Server

Over the years, I have worked with many developers that loved to persist XML in databases.  Even before SQL Server officially “supported” the storage of xml as a “native” data type, I would find developers adding in text data types to a schema and persisting XML(esque) data.  The attraction of XML to a developer is signficant.  It’s self defining schema creates an attribute or a set of attributes that are intuitive and easy to work with.  For a DBA, the value of XML being persisted in the database has varied a great deal.  This is primarily due to its size and also the performance hits that are associated with any large data type.  Even with an in-depth knowledge of persisting in-row data it can be a challenge to design a data model with significant XML data types that’s expected to perform similarly to the tried and true relational data model with those old boring data types such as char, varchar, int and numeric. 

Personally, I’m a fan of persisting XML in a database when certain conditions are met.  If the xml document is going to be utilized as an entire document, I’m largely a fan of having it in the database.  If a document is going to have references to another document, stored in another column or in another table and then a “join” between them is requested; I begin to shake my head… slowly at first but then vigorously.  XML is one of the better examples of a great technology being used in incorrect manners. 
It’s great to see Michael Coles – Blog – reference the XDM standard.  In my experience this is one of the least understood and most important aspects of persisting XML in a relational database.  If the schema of the XML is not congruent throughout a column of data in a table, the performance and management aspect of maintaining that data is beyond relational difficult.  This is also true for the elements in XML.  Much like the relational model, the XML model will work better, when persisted in a relational database engine, if similar thought processes are maintained such as “Null” nor “Not Null”.  In the case of the chapter’s example, The element of ‘” 
 <Officers> 
  <Colonel id = “1”>Harland Sanders</Colonel> 
  <Colonel id = “2”>Tom Parker</Colonel> 
  <Colonel id = “3”>Henry Knox</Colonel> 
  </Officers>” 
  versus an element of ” 
  <Officers> 
  </Officers>” 
 

The difference is quite obvious.  However, if there are many examples of both, much like the traditional data model with NULL columns of data, there is a performance hit when it comes to the value of an index and of the SQL Server engine to access the data in the most efficient manner possible.  The same is true for XML data and their related indexes and their efficiency.  This actually reminds me of an earlier chapter that referenced meta-data and selectivity.  Yet again it’s another example of how great this book is continually inter-leaving common components of relational design methodologies as they pertain to SQL Server.
 

Great chapter Michael.  Considering that this was an “introduction” to Xquery I was very impressed that you went well beyond just Xquery and you largely explained XML and a lot of elements (pun intended) that comprise the XML data type in SQL Server.

Advertisements

0 Responses to “Chapter 10 – Introduction to XQuery on SQL Server”



  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Chapters

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 13 other followers