XML in SQL Server 2008

September 24, 2008

This is a follow-up to my look at XML in SQL Server 2005.

With the recent release of SQL Server 2008, Microsoft made updates to the XML support in SQL Server. In particular, they made improvements to the XML Schema Definitions (XSD) that they support, they added support for the let clause in XQuery FLWOR expressions, and they added support for certain XML data manipulation insertions.

It is good to see support for the XQuery let clause, although I actually removed a reference to their lack of support for the let clause from my previous post because I felt that it was not a big deal. The expanded XML schema and XML manipulation support will prove useful for users. However, as far as I know (and I am relying on the Microsoft documentation here), the primary issues remain:

  • SQL Server does not allow multiple versions of a schema in the same schema collection.
  • SQL Server does not support indexing individual elements and attributes.
  • The SQL/XML implementation includes non-standard extensions.
  • SQL Server does not support standalone XQuery.

And you still need to carefully evaluate the performance of the following for your environment:

  • Queries based on path expressions.
  • Queries against large XML documents.
  • Creating and updating indexes.

The following sources were consulted when compiling this post:

  • What’s New for XML in SQL Server 2008, SQL Server Technical Article, August 2008.
About these ads

18 Responses to “XML in SQL Server 2008”

  1. Ricardo Says:

    Very interesting blog. I’m interested in XML DB. Could you write something about XML support in Oracle 11g?


  2. Hi Ricardo,

    I’ll add a post covering Oracle in the next few days.

    Thanks,
    Conor.

  3. Tim Gilbert Says:

    Hi Conor.

    When you state, “SQL Server does not support indexing individual elements and attributes,” would you please articulate the impact of this limitation? Could you give an example where such XML indexing might be used and for what business benefits? (presumably performance and processing cost)

    Useful blog. Thanks again.


  4. Being able to index individual XML elements allows you to create highly-targetted indexes. Because you index only the information for which you want to speed up access, your index size is smaller. Having a smaller index means that working with the index when responding to queries is faster. It also means that creating and updating the index is faster (and remember that you need to update the index whenever records are added, changed, or deleted). Of course, it also means that you have lower storage requirements and costs.

    So being able to index individual XML elements allows you to lower storage requirements, reduce the load on the server during index creation and updates, and speed up query response.

    Of course, these benefits are predicated upon having a situation in which you want to create an index on individual items–a situation that is quite common. This situation is analogous to creating an index on select columns in a relational database, instead of creating an index on an entire table. One simple example might be… let’s say you have XML data that contains address information, and you typically run queries where speeding the responsiveness of queries on the City and State information is very important. In this case, you would create an index only for the tags with the City and State information, and not for any other tags.

  5. Hooman Says:

    does microsoft sql server 2008 support native xml database? or in other hand is microsoft sql server 2008 a native xml database?
    thank you.


  6. Hi,

    Yes, SQL Server 2008 does include “native” XML support. In fact, all major relational vendors offer the ability to natively store XML data. There are some differences in the XML features supported by the different vendors. For instance, if you need to use XML schemas to validate your data, then you should be careful about your choice of database. However, the key difference between the different vendors is the performance of their implementations. I have seen organizations achieve wildly different performance levels with different vendors.

  7. Greg Says:

    Hi,

    I wonder about performance to use sql 2008 to store my data in different table with multiple JOIN to get data or use xml to store my data.

    What’s faster ? simply store data like this in different table or store data in xml doc.

  8. mim Says:

    Hi,

    i’m doing my senior project about XML DBMS, and I wonder about the differentiate between DB2 and MS SQL server 2008, which one is give better benefit. Could you please write the comparison between these two DBMS

    Regards,


    • Hi Mim,
      please also see Conor’s post on SQL Server 2005, which you can find at
      http://nativexmldatabase.com/2008/09/19/xml-in-sql-server-2005/

      Most of the information for XML in SQL Server 2005 is still accurate for SQL Server 2008. For example, some of the key differences between DB2 and SQL Server include:
      * In DB2 you can define indexes on individual attributes and elements. In SQL Server you have to index ALL attributes and elements, which can be a lot more expensive for insert/update/delete operations.
      * This “primary XML index” in SQL Server is actually a relational table that contains one row for every XML element and attribute. Is that native XML storage? You decide. DB2 stores XML in a unique parsed tree format and does not map XML elements to rows in a relational table.
      * DB2 supports XQuery as a first class language in itself. In SQL Server, XQuery always has to be embedded in SQL statements.
      * DB2 supports the XML functions that are defined in the SQL standard. SQL Server does not.
      * DB2 allows documents in the same XML column to be validated against different versions of an evolving XML Schema. As far as I know, this is not possible in SQL Server.

      Hope this helps!

  9. jacek francuz Says:

    Hello,

    I find your website very useful for me considering a fact that I’m currently writing my master thesis which focuses on XML native databases. I need to compare most important vendors’ products(db2,11i,sql server 2008) functionality and standards compliance. As far as I know only Oracle has published summary of features that are and that are not SQL standard compliant[http://www.filibeto.org/sun/lib/nonsun/oracle/11.1.0.6.0/B28359_01/server.111/b28286/ap_standard_sql009.htm]. I’d really appreciate if you could share your knowledge in this subject(if only you have such one) about other DB’s(db2,sql server 2008). I bet these producers had no will to publish it, but maybe I have missed something out.

    Many thanks

    Jacek


    • Hi Jacek, I’m not aware of a detailed list that itemizes how DB2 and SQL Server comply with the SQL standard. In general you will find that Oracle and DB2 follow the SQL standard very closely, but you may find minor exceptions or extensions in either product. Microsoft/SQL Server seems to have a different view on standards. For example, all the core XML functions of the SQL standard (XMLEXISTS, XMLQUERY, XMLTABLE, etc.) are supported in Oracle and DB2, but not in SQL Server. SQL Server may have similar functions, but their names and arguments etc. tend to differ from the SQL standard. As far as I know, SQL Server has no function that is close to the SQL function XMLTABLE, which is very popular in DB2 and Oracle.

      I think that standards, such as SQL, SQL/XML, XQuery, etc. are very important. But, producing a meticulous list of every single function and every single language construct should probably not be the main focus of comparing database systems. Standards can contain features that are very rarely used in real applications, so in every database system you can find some esoteric details of a standard that aren’t supported. But, that’s not necessarily a problem for database applications. Database characteristics such as performance, ease of use, scalability, high availability, low maintenance, automatic tuning, etc. tend to be a lot more important for most users.

      Matthias

  10. jacek francuz Says:

    Thank you,

    I really appreciate your help Mr. Nicola. Of course you’re right- database system’s analysys won’t be surely based on detailed list of differences between features of DB systems- I just wanted to read thru it and get little deeper into this subject for my own information. Information you provided above aren’t something enigmatic and hard to find- they’re rather commonly known. Anyway- you summarized it very well- briefly and you touched the idea so I’d like to thank you so much for that- it was really helpful.

    What I’ve realised during my whole ,,play” with XML Native Databases that they’re significantly different(even DB2 and Oracle that are,as you noticed, almost fully SQL/XML compliant) in their fundamentals, approach to this whole XML subject.

    As a second part of my post(I’m not sure if it’s correct place to do this) I’d like to suggest that you could invite some other vendors’ authorities(as you are from IBM) to some kind of dialog here. This would make, in my opinion, the blog more independent so that it could be taken more seriously by people that need to know something more on this subject. Of course I’ve noticed that you add a note about being members of IBM team on posts that are a comparison of different DBs which I find great, but you know- it’s still seems like it’s little biased.

    Once again- that you so much for your help.

    Cheers

    Jacek

  11. Ricardo Says:

    Is it feasible to publish some step by step tutorial regarding running TPoX against MSSQL 2008? I really need some help. I’m struggling with this for a couple months. I need SQL Server results for a next week :(

    Maybe You have some quick tutorial for establishing CLASSPATH and command line commands?

  12. reza Says:

    hi
    thanks of your knowledge
    im a web developer(but bigneer)
    i want to direct me about xml technology that in future wheather useful?
    and please send me your responds to my email:rhosseini58@yahoo.com
    and if i have a question can ask you
    very thanks


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

Follow

Get every new post delivered to your Inbox.

Join 51 other followers

%d bloggers like this: