Querying XML in SQL

I like to use my blog as a library for myself, to be able to easily find solutions I have used before.  Here is a simple example of querying an XML string using SQL. 

In this example I have inserted one XML string into a temporary table, with two items called pages, and two reference of type ‘book’.  The resulting select statements will pull any and all pages from the XML string, and any ‘book’ references.  There can be multiple types of references with varying fields, which you can query in the same way, simply be altering the text in the .nodes in the FROM statement.

    DECLARE @xml as XML
    DECLARE @tblXML AS TABLE  (Response xml )
SELECT '<response>
      <name>First Page</name>
      <name>Second Page</name>
     </page>  </pages>
      <name>How I met your mother</name>
      <name>Covered in Bees</name>
</response>' as Response

SELECT @xml = Response from @tblXML

                ID        =    t.item.value('(id)[1]','int'),
                BookName    =    t.item.value('(name)[1]', 'nvarchar(50)'),
                IsActive    =    case when t.item.value('(activestatus)[1]', 'nvarchar(10)') = 'false' THEN cast(0 as bit) ELSE cast(1 as bit) END
            FROM @xml.nodes('/response/references/reference[type/text() = "book"]') as t(item)

            SELECT ID        =    t.item.value('(id)[1]','int'),
                PageName    =    t.item.value('(name)[1]', 'nvarchar(50)')
            FROM @xml.nodes('/response/pages/page') as t(item)

You can also query a table directly rather than storing the XML in a variable by using CROSS APPLY, like this.

            SELECT ID        =    t.item.value('(id)[1]','int'),
                PageName    =    t.item.value('(name)[1]', 'nvarchar(50)')
            FROM @tblXML x
            CROSS APPLY
            Response.nodes ('/response/pages/page') as t(item)

I hope you find this useful.

Compare Data and Database Schemas

I was thrilled to discover the functionality to compare database data and schemas is available in Microsoft Visual Studio.  It not only allows you to compare two databases, but it will update the target database or write an update script for you to allow you to update the target database.

Most of this functionality has existed in Visual Studio Team System Database Edition since 2005.  Team System Database edition is used by database developers and administrators and includes advanced tools for database change management and testing.  Database schema comparison is now available in Visual Studio Premium and Visual Studio Ultimate since 2010.  Unfortunately it’s not in Professional or Express.

Having the database schema compare functionality in Visual Studio is awesome for developers who straddle several worlds. It’s nice to be able to build an application that affects or relies on a database, and also be able to compare the database schemas among environments, even if building the database doesn’t fall within your purview, all within one tool. 

The comparison is really easy to do. Look in Tools->SQL Server


Select your source and target database


Click Compare and it will compare the databases.  Here I have compared the schemas of two databases.


The tool highlights the differences. In this case a new field called [newfield] exists in the table [Threads] in the source database but not the destination database.

Click the script icon to generate a SQL script for the changes to the destination database.


Or click the Update button to actually do the update of the destination database. 

How sweet is that? I know I’ll be using this regularly in all kinds of development.