Querying XML Columns in SQL Server 2008

We make use of XML columns in a few places on our current project.  XML columns are great for storing “blobs” of data when you want to be able to change the schema of the stored data without having to make database changes (it also provides flexibility in what you store in that column).

Occasionally, we need to query based on parts of the XML in these columns.  Now because these are XML columns it’s not quite as easy to query on parts of the column as an int or nvarchar column.  This is further complicated when the XML being stored uses namespaces. 

Let’s start with the following table:

customer_table

And here’s a sample of the XML stored in the column:

<CustomerInfo xmlns=http://schemas.datacontract.org/2004/07/Sample.Dto
               xmlns:i="http://www.w3.org/2001/XMLSchema-instance"
               xmlns:d="http://custom.domain.example.org/Addresses">
    <FirstName>Jeremy</FirstName>
    <LastName>Wiebe</LastName>
    <d:Address>
    <d:AddressId>82FC06FA-46A9-47CA-81AA-A0B968BCEE49</d:AddressId>
    <d:Line1>123 Main Street</d:Line1>
    <d:City>Toronto</d:City>
    </d:Address>
</CustomerInfo>

Now we could query for this row in the table by first name using the following query:

WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema-instance' as i,
                    'http://schemas.datacontract.org/2004/07/Sample.Dto' as s,
                    'http://custom.domain.example.org/Addresses' as d)
     select CustomerInfo.value('(/s:CustomerInfo/s:FirstName)[1]', 'nvarchar(max)') as FirstName,
            CustomerInfo.value('(/s:CustomerInfo/s:LastName)[1]', 'nvarchar(max)') as LastName,
            CustomerInfo.value('(/s:CustomerInfo/d:Address/d:City)[1]', 'nvarchar(max)') as City,
     from   Customer
     where  MessageXml.value('(/s:CustomerInfo/d:Address/d:AddressId)[1]', 'nvarchar(max)') = '82FC06FA-46A9-47CA-81AA-A0B968BCEE49'

This would list out the FirstName, LastName, and City for any row where the AddressId was ‘82FC06FA-46A9-47CA-81AA-A0B968BCEE49’. There are a two things to note:

Namespaces

Any time you query XML (in any language/platform) that include XML your query must take that into account.  In T-SQL, you define namespaces and their prefixes using the WITH XMLNAMESPACES statement.  This statement defines the namespace prefix and associated namespace URI. 

Once you’ve defined them, you can use the namespace prefix in your XPath queries so that you can properly identify the XML elements you want.  One final note about namespaces here is that we have to define a namespace prefix for the namespace that was the default namespace in the XML sample.  As far as I know there’s no way to define a default XML namespace using the WITH XMLNAMESPACES statement.

XPath Query results

Notice that the XPath queries (the CustomerInfo.value() calls) always index into the results to get the first item in the result.  The .value() function always returns an array of matching XML nodes, so if you only want the first (which is often the case, you need to index into the results.  Also note that with these XPath queries, the results are a 1-based array, not 0-based.  So in this example we’re always taking the first element in the result set.

 

There are other functions you can use to query XML columns in T-SQL and you can find out more about this in the Books Online or via MSDN.   This page is a good starting point for querying XML columns: http://msdn.microsoft.com/en-us/library/ms189075.aspx.

Advertisements

>Uncovering intermittent test failures using VS Load Tests

>

Our team build has been flaky lately with several unit tests that fail intermittently (and most often only on the build server).  I was getting fed up with a build that was broken most of the time so I decided to see if I could figure out why the tests fail.

Working on the premise that the intermittent failure was either multiple tests stepping on each other’s toes or timing related.  I decided the easier one to figure out would be the timing issue. 

To create a new load test:

1) Select “Test –> New Test” and select “Load Test” from the “Add New Test” dialog

Add New Test

2) You will be presented with a Wizard that will help you build a load test.  I don’t know what each setting means, but for the most part I left the settings at their defaults.  I’ve noted the exceptions below.

a) Welcome

New Load Test Wizard - Welcome

b) Scenario

New Load Test Wizard - Scenario

c) Load Pattern

New Load Test Wizard - Scenario - Load Pattern

d) Test Mix Model

New Load Test Wizard - Scenario - Test Mix Model

e) Test Mix

The Test Mix step allows you to select which tests should be included in the load test.  For my purposes, I selected the single test that was failing intermittently.  If you select multiple tests this step allows you to manage the distribution of each test within the load.

New Load Test Wizard - Scenario - Test Mix

f) Counter Sets

New Load Test Wizard - Counter Sets

g) Run Settings

Finally, you are able to manage the Warm up duration and Run duration.  Warm up duration is used to “warm up” the environment.  During this time the load test controller simply runs the tests but does not include their results in the load test.  The Run duration represents how long the load test controller will run the mix of tests selected in the “Test Mix” step.

New Load Test Wizard - Run Settings

Once you click Finish, you will be presented with a view of the load test you’ve just created.  You can now click on the “Run test” button to execute the load test. 

Gotcha: The first time I tried to do a load test it complained that it couldn’t connect to the database.  You can resolve this by running the following SQL script and then going into Visual Studio and selecting Test –> Administer Test Controllers…  This will bring up the following dialog. 

Administer Test Controllers 

The only thing you need to configure in this dialog is the database connection (the script will create a database called “LoadTest”) so in my case the connection string looked like this:

Data Source=(local);Initial Catalog=LoadTest;Integrated Security=True

Now that you have the load test created, you are ready to run it.  Click the “Run test” button on the load test window (shown below). 

Load Test - Ready to Run

While the load test is running, Visual Studio will show you 4 graphs that give you information about the current test run.  You can play with the option checkboxes to show/hide different metrics.

In my case, as the load test ran the unit test I’d selected started to fail.  This at least showed me that I could reproduce the failure on my local machine now. 

Next I added a bit of code to the unit test prior to the assertion that was failing.  Instead of doing the assertion I added code to check for the failure condition and called Debugger.Break(). 

I ran the test again, but this time as I hit the Debugger.Break calls I could step through the code very well because the load test was running 25 simultaneous threads (simulating 25 users).  I dug around a bit and found that you can change the number of simultaneous users.  You can adjust the setting by selecting the “Constant Load Pattern” node and adjusting the “Constant User Count” in the Properties window.

I ran the test again and this time I was able to debug the test and figure out what was wrong. 

Overall using the load test tool in Visual Studio was quite easy to set up and get going.  Other than the requirement of a database (which is used to store the load test results) it was very easy to set up.

>An Introduction to StructureMap

>

Over the next several months I will be presenting a walkthrough of StructureMap.  I will be starting with the basics of how to construct objects using StructureMap and then move on to more advanced topics. 

I know there is lots of great information out there around how to use StructureMap.  My intention for writing this series is mostly selfish.  I want to learn StructureMap, and as I learn it I’ll be blogging the features I’m learning as I go. 

I’m currently working on a project that uses the Unity dependency injection container.  This container has worked very well for us, but as I’ve read various blogs and articles I’ve come to realize that a good DI container provides more than just DI.  It’s my intention to learn the basic usage of StructureMap, but also dig into the more advanced usages as well as much of it’s support infrastructure that helps you keep your dependency injection code to a minimum (thinking of auto-wiring, convention-based registration, etc).

I’ll also be updating this post as I write each new article so that this post can remain as a table of contents for the whole series.

  1. Manual component registration and resolution
  2. Auto-wiring components
  3. Convention-based registration
  4. Component lifetime management

>Beware Calculations in Unit Tests

>

Today our project’s continuous integration build failed.  I traced it down to a unit test that was exercising a scheduling class.  The class takes a schedule and figures out the next time it should run based on the current date. 

Now in our project we’ve provided a way to stub the current date or current time by having a SystemClock class that has a static Instance property.  If we need to we can push a stubbed instance into that property in a unit test to control what the current date/time is.

The problem in the unit test was that it was relying on DateTime.Now and then figuring out what the expected next scheduled time should be right in the unit test.  The calculations were wrong in the unit test and caused the unit test to fail.

Almost every time I see calculations in unit tests to figure out what the expected value should be (especially date-based calculations) I cringe because I see those as unit tests waiting to fail.  If you’ve copied your calculations from the class under test, who says you got the calculations right in the production class?  If you did the calculations differently, your unit test’s calculations could be wrong.  Wherever possible I prefer to keep the moving parts to a minimum and have constant dates as my expected values in date-based unit tests.

>ReSharper 4.5 – Tip #2 – Generate Members

>One of the other developers on the team I’m on pointed out a very handy feature in R# yesterday. It is the “Generate Members” feature and can be accessed using Ctrl+Ins. This handy tool is like a central point to accessing the generation features in R#. The nice thing is that the generated members are placed at the current location and you can generate as many members as you like in one shot. This makes doing things like wrapping a bunch of private fields really easy. So, the next time you are using Alt+Enter (Quick Fix) to generate some code using R#, consider using Alt+Ins and see what it can do.

>ReSharper 4.5 – Tip #1 – Wildcards in Go To Type shortcut

>

I tend to use the Go To Type shortcut in R# alot (ctrl+N for you keyboard junkies). It was only recently that I found out that you can use simple wildcards when using the shortcut.

In the above screenshot you can see that I’m looking for all classes that start with “Pet” and end with “View”. I don’t use this feature every time I want to jump to a type, but often I can’t remember the exact name of a class but I’ll know pieces of the name and this helps in those cases (it’s especially helpful when I just know how the class name ends, eg. “*Presenter”).