Article
04/09/2009

Testing SQL-XML Output by Using XQuery

By Ken O. Bonn, Software Development Engineer, Microsoft Corporation. You can view Ken’s blog at https://blogs.msdn.com/kenobonn.

Introduction

Imagine that you’re working on a project that has a large number of stored procedures that output XML. You would like to implement SQL-XML in your project, but it’s daunting to have to test and validate the XML output before it’s consumed by the client. The solution is to implement Database Unit Testing with Visual Studio 2008 Team System Database Edition to ensure the delivery of a quality project, and to provide a framework to ensure that future releases do not break existing functionality. For the developer creating stored procedures that output XML, this article shows several techniques to test output using XQuery, and to implement your test queries in a database test project. The examples in this article focus only on record count, existence, and value tests, which are known as "feature tests". You should also consider other types of database unit tests, including tests on schema, security, and performance.

This article is intended to help you get over the initial barrier of learning how to query XML in SQL Server, so that you can create database unit tests on SQL-XML stored procedures. You can create feature tests much more easily after you have working knowledge and examples of XQuery as applied to XML elements and attributes in Transact-SQL code. You can choose the most suitable technique for a given scenario from the multiple techniques presented below.

This article covers some of the considerations for setting up database unit tests. Then it will use XQuery methods within the context of Visual Studio database test projects to evaluate the behavior of stored procedures. Although it briefly covers creating SQL-XML queries, the focus is on testing the results by using XQuery. Note that SQL-XML and XQuery are both part of Transact-SQL. This article distinguishes SQL-XML from XQuery to clarify the type of function being used.

You should continue reading this article if you want to:

Understand how to use XQuery expressions for specific unit tests.
Find XQuery code that you can re-use without searching the Internet.
Implement database unit testing.
Write and use stored procedures that return XML.

Challenges of Testing XML in SQL Server

Stored procedures which output XML can be challenging to evaluate for several reasons.

Testing for NULL. If the data evaluates to NULL, the node will usually not be present, so you should test for existence of the node or its attributes, not for NULL or empty string. In some cases, the XML output is manipulated to represent NULL as an empty element. The example ManagerAttributeExists in this article provides examples of different means of testing for different representations of NULL. In this example, there is an Element <Manager>, but there are no managers for this employee, so the attributes that identify the manager are missing. Remember that a NULL can be generated not only by nullable columns, but also as the product of outer joins.
Testing for record count. Test for record count should be at the element that constitutes a record, which is not necessarily the root node.
Testing for optional, one, or many values. Are there restrictions on the number of XML nodes? Perhaps you need to test for exactly 5 elements, for example. I recommend testing for an exact number of nodes only if schema constraints require an exact node count, or if your data is so well defined that the number of nodes is predictable. Do not test for 3 elements just because legacy data indicates a pattern, unless you know that breaking the pattern breaks the system. This kind of test is shown in the example EmployeeCount.
Understanding the effects of aliasing. If your XML output is AUTO, changing the alias of the table or column can change your XML schema. In these circumstances, testing of record count is not sufficient, since the possibility of changing the children names would break the procedure. Run through the exercise in the SQL stored procedure XML Elements and Attributes below to see the effect that changing the alias of a table has on the XML output.
Testing whether records exist. One way to test for existence is seen at ManagerExists. This uses the XQuery method "exist", for which the result must be cast to an integer, and then have a Scalar test condition check for the value of 1. Another way to test for existence of records, when specfic record count is not known, is demonstrated at EmployeeExists. Here, we use the XQuery method "query", and then have a Not Empty ResultSet test condition executed.

Setting Up Your Unit Tests

Although you could write and test queries with other software, this article assumes that you are using SQL Server 2008 as your database engine and Visual Studio Team System 2008 Database Edition to perform automated Database Unit Tests. This article also uses the SQL Server 2008 AdventureWorks Database, which you can install from Microsoft SQL Server Product Samples: Database.

Creating the actual project for testing is the easy part. Refer to the MSDN article How to: Create a Test Project for Database Unit Testing. Here is a quick overview of the steps:

Open Visual Studio.
On the File menu, select Add, New Project, then Visual C#, then Test.
After the new project is created, right click the project in Solution Explorer, and select Add, then New Test, then Database Unit Test.

The actual creation of the test - query, test type, parameters – is demonstrated later in this article.

Before you begin testing, you need predictable data. Since this article is focused on unit testing, predictable data is assumed. Tests for unpredictable data from unspecified sources such as data import and user input belong in the realm of integrated testing. Many of the same techniques would still apply. The table below presents several options for creating predictable test data. You can select the options best suited for your testing project.

Methods for Creating Test Data

Method	How To	Discussion
Use Existing Data	The quick way is to use existing data.	This works only if it is a valid assumption that the data is static, such as for domain values. Create a script that derives inputs dynamically, e.g. MAX(KeyID) FROM MyTable, or use specific static data. For this demo, such an assumption is being made to keep on track with the topic, however in practice this is not really unit testing, it resemble integrated testing more, as this is a test of actual user data.
Test Run Setup and Cleanup Scripts	A simple approach is to edit the test run configuration. To do this, open the LocalTestRun.testrunconfig file in the solution. Navigate to the tab "setup and cleanup scripts". Enter path to a batch file, such that the batch file calls SQLCMD for the SQL scripts necessary to run. This will create the data before the test run, and destroy the data after the test run.	This allows for the test execution to be predictable, as you are looking at generated data for each object. SQLCMD database connection is not necessarily same as test project configuration, need to manually ensure the database connections are the same. Aside from that, much less maintenance than having to create data for each object for each specific test. See section on AssemblyInitialize below for much better way to do this.
Use the pre and post events on each test.	Open any test, just above the test name in the upper left corner is a dropdown list, select "(common scripts)". The next dropdown list has two options, select "Test Initialize". Click the link "Click here to create" on the form, and enter your SQL scripts to generate data. Afterwards, repeat for the "Test cleanup" option on the dropdown list to clean up the test data.	It allows for the test execution to be predictable, as you are looking at generated data for each object. This applies to tests individually; it does not apply to the entire test run. This is fine if you need a little more granularity than at the test run.
Use the pre and post events on each test condition.	Another approach is to use the Pre-Test and Post-Test conditions for each test condition. Similar as above for each test. After you create the test, observe there is a combo box towards the top, between the test name and the + symbol. Change the value from Test to Pre-Test, and add your SQL script to load the data. Do similar for the Post-Test option to unload the test data.	As each test can have multiple test conditions, this is pretty fine granularity, so I would use this only for specific scenarios, it may be too much work to do for each test condition.
Generate the data manually	Generate the data manually before running the tests, and to remove it afterwards. This is similar to the automated way described above, yet is done only once per test run.	Similar benefits as to editing the test run configuration, yet done manually. It is better to do so automatically.
Generate the data by using a data generator	You can use a data generator. See Generating Data with Data Generators.	This is considered a best practice, as it should be much cheaper than creating your own scripts. You can use only one data generation plan per test project.
[AssemblyInitialize()] and [AssemblyCleanup()] methods	Usage of the attributes AssemblyInitialize and AssemblyCleanup allow code execution to be run at time of initialization and cleanup. As this may take some C# programming skills whereas the focus here is on SQL, sample code to do so is as below.	It allows for the test execution to be predictable, as you are looking at generated data for each object. By programmatically running the methods, you can access the test project configuration, to manually ensure the database connections are the same. Drawback is that it requires a little coding, but not difficult to implement.

I am currently using a variation of the code below, which maps to the AssemblyInitialize / AssemblyCleanup method. This suits our team fine as it allows us to quickly create data for an in-flight project. My recommendation for most projects would be to use the Data Generator technique described above.

Instead of using inline Transact-SQL as below, we add two new key / value pairs to the app.config file that represent a list of SQL script files to run before and after the test run.

[AssemblyInitialize()]
    public static void IntializeAssembly(TestContext ctx)
    {
        //   Setup the test database based on setting in the
        // configuration file
        ConnectionContext testDbConnection = DatabaseTestClass.TestService.OpenPrivilegedContext();
        System.Data.SqlClient.SqlConnection testConn = (System.Data.SqlClient.SqlConnection)testDbConnection.Connection;
        using (testConn)
        {
            System.Data.SqlClient.SqlCommand cmd = testConn.CreateCommand();
            cmd.CommandText = "Whatever SQL you choose BEFORE test";
            cmd.ExecuteNonQuery();
        }
        DatabaseTestClass.TestService.DeployDatabaseProject();
        DatabaseTestClass.TestService.GenerateData();
    }
    [AssemblyCleanup()]
    public static void CleanupAssembly()
    {
        ConnectionContext testDbConnection = DatabaseTestClass.TestService.OpenPrivilegedContext();
        System.Data.SqlClient.SqlConnection testConn = (System.Data.SqlClient.SqlConnection)testDbConnection.Connection;
        using (testConn)
        {
            System.Data.SqlClient.SqlCommand cmd = testConn.CreateCommand();
            cmd.CommandText = "Whatever SQL you choose AFTER test";
            cmd.ExecuteNonQuery();
        }
    }

While it is necessary to have predictable data for unit testing, you must also be prepared to test for unpredictable data through integrated testing. User input can break a fragile system, and integrated testing should prevent this. If you want to test user data, my recommendation is that you do so in a separate test or even in a separate project, so that you can better isolate the issues that may arise.

Organizing and Naming Your Database Unit Tests

Organizing tests is important. It is confusing to see test results for something with a meaningless name like "DatabaseTest1". If you have 100 or more objects to test, and multiple tests per object, it can quickly become confusing unless you organize your tests to match the objects, and even the object types. In your test project, consider the following guidelines:

Object Type. Create tests for objects such that the folder structure of the test project matches the hierarchy in your database project.
Test Name. Create a filename to match the object name.
Test. Create multiple tests in each file, with one test per SQL query.
Test Condition. For each test, or SQL statement, the results of the SQL can be evaluated by multiple test cases.

An example of organizing the Object Type and the Test Name may be seen in the screen shot below. Here, we store the test for the stored procedure "uspXmlElementsGetEmployeeManagers.sql" in a subfolder called StoredProcedures, and give the test a filename that matches the object itself. Below the Solution Explorer, we see the properties of a Test Condition called EmployeeLastName. This test condition belongs to a test called EmployeeNames which we will examine in the second screen shot below. For each database unit test described in this article, notice the Test Name, the Test, and the Test Condition Name to ensure that you understand the importance of organizing tests.

In the screen shot below, we see that the above object has 4 tests:

EmployeeCount
EmployeeExists
EmployeeName
ManagerExists

The EmployeeName Test is selected, and we see 5 Test Conditions for this test:

EmployeeFirstName
EmployeeLastName
ManagerFirstName
ManagerLastName
BusinessEntityID

Below the Test Conditions, we see the Test Results by Test Name. Clearly, when you have multiple tests with multiple test conditions, it is imperative to give meaningful names using naming guidelines that are agreed upon by all team members.

Creating Stored Procedures Using XML

The examples in this article use stored procedures where all output is in the form of XML elements. The final stored procedure generates XML in both elements and attributes by using the AUTO keyword in the FOR XML clause. Doing so provides an easy way to demonstrate how simple changes such as the aliases of table or field names can break your procedure. This in turn demonstrates the benefit of unit tests to catch these failures.

The code examples that follow are custom derivations of the stored procedure “uspGetEmployeeManagers” from the AdventureWorks2008 database. This procedure is useful to review because it shows an example of using Common Table Expressions, and it does so recursively.

In real life, you would typically generate data before a test run, and remove it afterwards. Here we will assume predictable data, since this is the AdventureWorks2008 database, and simply create stored procedures prior to the test run.

Stored Procedure#1: Dataset

We will make a slight modification to the AdventureWorks 2008 version of the stored procedure to include employees who do not have managers, so that we can include null data. In the final SELECT, change the INNER JOIN to LEFT OUTER JOIN clauses. The resulting stored procedure and its output follow.

USE [AdventureWorks2008]
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES
      WHERE ROUTINE_NAME = 'uspDatasetGetEmployeeManagers')
BEGIN
      DROP PROCEDURE [uspDatasetGetEmployeeManagers]
END
GO
CREATE PROCEDURE [dbo].[uspDatasetGetEmployeeManagers]
    @BusinessEntityID [int]
AS
BEGIN
    SET NOCOUNT ON;
    -- Use recursive CTE query to list out all Employees required
    -- for a particular Manager
    WITH [EMP_cte](
              [BusinessEntityID]
            , [OrganizationNode]
            , [FirstName]
            , [LastName]
            , [JobTitle]
            , [RecursionLevel]
      )
    AS (
        SELECT e.[BusinessEntityID]
                  , e.[OrganizationNode]
                  , p.[FirstName]
                  , p.[LastName]
                  , e.[JobTitle]
                  , 0 -- Get the initial Employee
        FROM [HumanResources].[Employee] e
        INNER JOIN [Person].[Person] as p
        ON p.[BusinessEntityID] = e.[BusinessEntityID]
        WHERE e.[BusinessEntityID] = @BusinessEntityID
        UNION ALL
        SELECT e.[BusinessEntityID]
                  , e.[OrganizationNode]
                  , p.[FirstName]
                  , p.[LastName]
                  , e.[JobTitle]
                  -- Join recursive member to anchor
                  , [RecursionLevel] + 1
        FROM [HumanResources].[Employee] e
        INNER JOIN [EMP_cte]
        ON e.[OrganizationNode] = [EMP_cte].[OrganizationNode].GetAncestor(1)
          -- Join back to Employee to return the manager name
        INNER JOIN [Person].[Person] p
        ON p.[BusinessEntityID] = e.[BusinessEntityID]
    )
    SELECT
              [EMP_cte].[RecursionLevel]
            , [EMP_cte].[BusinessEntityID]
            , [EMP_cte].[FirstName]
            , [EMP_cte].[LastName]
        , [EMP_cte].[OrganizationNode].ToString() AS [OrganizationNode]
        , p.[FirstName] AS 'ManagerFirstName'
        , p.[LastName] AS 'ManagerLastName'
    FROM [EMP_cte]
        --outer join so we show employees with no managers
        LEFT OUTER JOIN [HumanResources].[Employee] e
        ON [EMP_cte].[OrganizationNode].GetAncestor(1) = e.[OrganizationNode]
        LEFT OUTER JOIN [Person].[Person] p
        ON p.[BusinessEntityID] = e.[BusinessEntityID]
    ORDER BY [RecursionLevel] DESC, [EMP_cte].[OrganizationNode].ToString()
    OPTION (MAXRECURSION 25)
END;
GO
EXECUTE [dbo].[uspDatasetGetEmployeeManagers] @BusinessEntityID = 3

As this data illustrates, Ken Sánchez does not have a manager. Terri Duffy reports to Ken, and Roberto Tamburello reports to Terri.

RecursionLevel	BusinessEntityID	FirstName	LastName	OrganizationNode	ManagerFirstName	ManagerLastName
2	1	Ken	Sánchez	/	NULL	NULL
1	2	Terri	Duffy	/1/	Ken	Sánchez
0	3	Roberto	Tamburello	/1/1/	Terri	Duffy

Stored Procedure#2: XML Elements

This is our first stored procedure that uses XML. The SQL is identical to the preceding stored procedure that returns a dataset, except the SQL now uses a FOR XML statement to transform the dataset to XML format.

USE [AdventureWorks2008]
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES
      WHERE ROUTINE_NAME = 'uspXmlElementsGetEmployeeManagers')
BEGIN
      DROP PROCEDURE [uspXmlElementsGetEmployeeManagers]
END
GO
CREATE PROCEDURE [dbo].[uspXmlElementsGetEmployeeManagers]
    @BusinessEntityID [int]
AS
BEGIN
    SET NOCOUNT ON;
    -- Use recursive CTE query to list out all Employees required
    -- for a particular Manager
    WITH [EMP_cte](
              [BusinessEntityID]
            , [OrganizationNode]
            , [FirstName]
            , [LastName]
            , [JobTitle]
            , [RecursionLevel]
      )
    AS (
        SELECT e.[BusinessEntityID]
                  , e.[OrganizationNode]
                  , p.[FirstName]
                  , p.[LastName]
                  , e.[JobTitle]
                  , 0 -- Get the initial Employee
        FROM [HumanResources].[Employee] e
        INNER JOIN [Person].[Person] as p
        ON p.[BusinessEntityID] = e.[BusinessEntityID]
        WHERE e.[BusinessEntityID] = @BusinessEntityID
        UNION ALL
        SELECT e.[BusinessEntityID]
                  , e.[OrganizationNode]
                  , p.[FirstName]
                  , p.[LastName]
                  , e.[JobTitle]
                  -- Join recursive member to anchor
                  , [RecursionLevel] + 1
        FROM [HumanResources].[Employee] e
        INNER JOIN [EMP_cte]
        ON e.[OrganizationNode] = [EMP_cte].[OrganizationNode].GetAncestor(1)
          -- Join back to Employee to return the manager name
        INNER JOIN [Person].[Person] p
        ON p.[BusinessEntityID] = e.[BusinessEntityID]
    )
    SELECT
              [EMP_cte].[RecursionLevel]
            , [EMP_cte].[BusinessEntityID]
            , [EMP_cte].[FirstName]
            , [EMP_cte].[LastName]
        , [EMP_cte].[OrganizationNode].ToString() AS [OrganizationNode]
        , p.[FirstName] AS 'ManagerFirstName'
        , p.[LastName] AS 'ManagerLastName'
    FROM [EMP_cte]
        --outer join so we show employees with no managers
        LEFT OUTER JOIN [HumanResources].[Employee] e
        ON [EMP_cte].[OrganizationNode].GetAncestor(1) = e.[OrganizationNode]
        LEFT OUTER JOIN [Person].[Person] p
        ON p.[BusinessEntityID] = e.[BusinessEntityID]
    ORDER BY [RecursionLevel] DESC, [EMP_cte].[OrganizationNode].ToString()
    OPTION (MAXRECURSION 25)
    FOR XML PATH('Employee'), TYPE, ROOT('Employees')
END;
GO
EXECUTE [dbo].[uspXmlElementsGetEmployeeManagers] @BusinessEntityID = 3

The resulting XML output follows. Notice how the absence of a manager for the first <Employee> node is indicated by the absence of <ManagerFirstName> and <ManagerLastName> elements. (In the database, the Manager field has a NULL value for the first employee.) This is in contrast to the final stored procedure, which will have an XML element representing Manager, but where the NULL condition will be indicated by the absence of attributes for the Manager's first and last names.

<Employees>

<Employee>

<RecursionLevel>2</RecursionLevel>

<BusinessEntityID>1</BusinessEntityID>

<FirstName>Ken</FirstName>

<LastName>Sánchez</LastName>

<OrganizationNode>/</OrganizationNode>

</Employee>

<Employee>

<RecursionLevel>1</RecursionLevel>

<BusinessEntityID>2</BusinessEntityID>

<FirstName>Terri</FirstName>

<LastName>Duffy</LastName>

<OrganizationNode>/1/</OrganizationNode>

<ManagerFirstName>Ken</ManagerFirstName>

<ManagerLastName>Sánchez</ManagerLastName>

</Employee>

<Employee>

<RecursionLevel>0</RecursionLevel>

<BusinessEntityID>3</BusinessEntityID>

<FirstName>Roberto</FirstName>

<LastName>Tamburello</LastName>

<OrganizationNode>/1/1/</OrganizationNode>

<ManagerFirstName>Terri</ManagerFirstName>

<ManagerLastName>Duffy</ManagerLastName>

</Employee>

</Employees>

Stored Procedure#3: XML Elements and Attributes

The last SQL-XML stored procedure will return a mix of elements and attributes. To ensure that the XML elements have proper names, alias the table names as the element names, and use the AUTO option.

Using FOR XML AUTO is my least favorite technique for generating XML, but it’s useful here to illustrate another option for output of XML. I recommend that you alias tables appropriately, and control your XML explicitly. This will help you in case another developer decides to alias the tables without realizing the impact that this has on XML output, and causes your application to fail. Additionally, expecting a correlation between the XML output and the table names or Common Table Expressions (CTE) can lead to uncertainty about the table name you are referencing. In this case, we are aliasing the CTE as "Employee", which could potentially be confused with the Employee table belonging to the HumanResources schema in the AdventureWorks database.

I recommend using the procedure below to experiment with different aliasing – for example, by using M instead of Manager, and CTEemployee instead of Employee. Run your tests as normal, to illustrate how your database unit test can detect failure of procedures.

USE [AdventureWorks2008]
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES
      WHERE ROUTINE_NAME = 'uspXmlAttributesGetEmployeeManagers')
BEGIN
      DROP PROCEDURE [uspXmlAttributesGetEmployeeManagers]
END
GO
CREATE PROCEDURE [dbo].[uspXmlAttributesGetEmployeeManagers]
    @BusinessEntityID [int]
AS
BEGIN
    SET NOCOUNT ON;
    -- Use recursive CTE query to list out all Employees required
    -- for a particular Manager
    WITH Employee(
              [BusinessEntityID]
            , [OrganizationNode]
            , [FirstName]
            , [LastName]
            , [JobTitle]
            , [RecursionLevel]
      )
    AS (
        SELECT e.[BusinessEntityID]
                  , e.[OrganizationNode]
                  , p.[FirstName]
                  , p.[LastName]
                  , e.[JobTitle]
                  , 0 -- Get the initial Employee
        FROM [HumanResources].[Employee] e
        INNER JOIN [Person].[Person] as p
        ON p.[BusinessEntityID] = e.[BusinessEntityID]
        WHERE e.[BusinessEntityID] = @BusinessEntityID
        UNION ALL
        SELECT e.[BusinessEntityID]
                  , e.[OrganizationNode]
                  , p.[FirstName]
                  , p.[LastName]
                  , e.[JobTitle]
                  -- Join recursive member to anchor
                  , [RecursionLevel] + 1
        FROM [HumanResources].[Employee] e
        INNER JOIN Employee
        ON e.[OrganizationNode] = Employee.[OrganizationNode].GetAncestor(1)
          -- Join back to Employee to return the manager name
        INNER JOIN [Person].[Person] p
        ON p.[BusinessEntityID] = e.[BusinessEntityID]
    )
    SELECT
              Employee.[RecursionLevel]
            , Employee.[BusinessEntityID]
            , Employee.[FirstName]
            , Employee.[LastName]
        , Employee.[OrganizationNode].ToString() AS [OrganizationNode]
        , Manager.[FirstName] AS 'ManagerFirstName'
        , Manager.[LastName] AS 'ManagerLastName'
    FROM Employee
        --outer join so we show employees with no managers
        LEFT OUTER JOIN [HumanResources].[Employee] e
        ON Employee.[OrganizationNode].GetAncestor(1) = e.[OrganizationNode]
        LEFT OUTER JOIN [Person].[Person] Manager
        ON Manager.[BusinessEntityID] = e.[BusinessEntityID]
    ORDER BY [RecursionLevel] DESC, Employee.[OrganizationNode].ToString()
    OPTION (MAXRECURSION 25)
   FOR XML AUTO, TYPE, ROOT('EmployeeManagers')
END;
GO
EXECUTE [dbo].[uspXmlAttributesGetEmployeeManagers] @BusinessEntityID = 3

Here is the resulting XML output:

<EmployeeManagers>

<Employee RecursionLevel="2" BusinessEntityID="1" FirstName="Ken" LastName="Sánchez" OrganizationNode="/">

<Manager />

</Employee>

<Employee RecursionLevel="1" BusinessEntityID="2" FirstName="Terri" LastName="Duffy" OrganizationNode="/1/">

<Manager ManagerFirstName="Ken" ManagerLastName="Sánchez" />

</Employee>

<Employee RecursionLevel="0" BusinessEntityID="3" FirstName="Roberto" LastName="Tamburello" OrganizationNode="/1/1/">

<Manager ManagerFirstName="Terri" ManagerLastName="Duffy" />

</Employee>

</EmployeeManagers>

Understanding How to Parse XML by Using XQuery Expressions with SQL-XML Stored Procedures

To understand the basics of these queries, a little explanation is in order. First, these examples consume the output of the stored procedures by inserting the result into a local table variable, @XmlTable. This table contains a single record of datatype XML. When no data is returned, this frequently indicates NULL values. But be careful in making such assumptions in your testing, since sometimes code is written to return an empty root node to represent no data.

Now that the field within the table is effectively a handle to the XML, you can explode the representation of the XML from a single fragment to multiple fragments of specific nodes. This is done by using the SQL CROSS APPLY function and the XQuery nodes() function as the second argument. Think of this technique as similar to a left outer join, in which the first operand is a table, but the second operand is a function that acts upon the table, rather than another table.

Now that we have a data structure of XML nodes that’s similar to rows, we can perform the usual SELECT functions that we are all familiar with. However now we perform XQuery functions on the nodes to extract the desired scalar values. It is on these values that we perform comparisons in our database unit testing.

Using XQuery Expressions on SQL-XML Stored Procedures in Database Unit Tests

Finally we’re ready to create specific types of tests on SQL-XML stored procedures using XQuery.

Earlier, we described how to create a test project in Visual Studio to contain database unit tests. Now, we will create specific tests within that project. To create tests:

Right click the project in Solution Explorer, and select Add New Test.
For the template, choose Database Unit Test, and choose a Test Name to match the object being tested.
The first test that you create will prompt for the database connection. Choose a connection to the AdventureWorks2008 database.
The next form will present you with a "Click here to create" link. After clicking it, the test is created, with a default test condition of inconclusive.
Click the red X button to delete the default test condition. Then, in the dropdown list, and choose the Test Conditions (Test Type) as specified below for each test, and click the green + sign to create them.
Enter the SQL query given below in the SQL pane in the upper left section of each test.
Within each test container that represents the object being tested, multiple tests can be performed on the same object. Rename the test from the default value of "DatabaseTest1" to something meaningful, so that the test results will be easier to interpret.
Remove the manual test "ManualTest1" from the test project. Also remove the "UnitTest1.cs" that is automatically created.

XQuery#1: EmployeeCount

The simplest test gets a count of the records at a certain node.

The query below will find that the count of records is 3. Unfortunately, testing using expressions such as > 1 is not currently supported, so I recommend using this technique only when you have created predictable test data. Do not consider the lack of expressions in the scalar value list as a limitation. The functionality of expressions can easily be applied within the SQL statement itself, as will be demonstrated later using the XQuery exists() function.

DECLARE @XmlTable TABLE (XmlResult XML)
INSERT INTO @XmlTable EXECUTE [dbo].uspXmlElementsGetEmployeeManagers @BusinessEntityID=3
SELECT
--Count number of nodes
 COUNT(*) AS EmployeeCount
FROM
(
SELECT XmlResult FROM @XmlTable
) AS XmlTable(XmlColumn)
CROSS APPLY XmlColumn.nodes('./Employees/Employee') XmlTableFunction(XmlColumn2);

Database Unit Test Configuration

Test Name	uspXmlElementsGetEmployeeManagers
Test	EmployeeCount
Test Condition Name	EmployeeRecordCount
Test Conditions	Scalar Value
Column Number	1
Expected Value	3
ResultSet	1
Row number	1

XQuery#2: ManagerExists

The next test determines whether an element exists.

Here, we use filtering in the nodes to find a specific parent node. Then we examine its subnodes within the SELECT clause for existence. You can also filter by attribute (if appropriate) simply by filtering by the attribute using the @ symbol. Since this test applies to the same object, use the existing test file, and click the + symbol at the top of the database unit test to add the ManagerExists test as defined below. Pay careful attention here. Observe that the expected results for the 4th and 5th columns are zero. Run the code in SQL Server Management Studio, and it appears that the correct values are given. Now, change the test in your unit test to examine the 5th element. Observe that the test now fails, with an error message as "ScalarValueCondition Condition (ManagerExists) Failed: Cannot convert '0' to System.Boolean.". It appears that the scalar condition expects an integer, so casting the Boolean value zero to an integer is necessary to make this test work.

DECLARE @XmlTable TABLE (XmlResult XML)
INSERT INTO @XmlTable EXECUTE [dbo].uspXmlElementsGetEmployeeManagers @BusinessEntityID=3
SELECT
--Check to see they exist
 XmlTableFunction.XmlColumn2.exist('BusinessEntityID[1]') AS [Exist BusinessEntityID]
, XmlTableFunction.XmlColumn2.exist('FirstName[1]') AS [Exist FirstName]
, XmlTableFunction.XmlColumn2.exist('LastName[1]') AS [Exist LastName]
, CAST(XmlTableFunction.XmlColumn2.exist('ManagerFirstName[1]') AS INT) AS [Exist ManagerFirstName]
, XmlTableFunction.XmlColumn2.exist('ManagerFirstName[1]') AS [Exist ManagerFirstName]
FROM
(
SELECT XmlResult FROM @XmlTable
) AS XmlTable(XmlColumn)
CROSS APPLY XmlColumn.nodes('./Employees/Employee[./BusinessEntityID="1"]') XmlTableFunction(XmlColumn2);

Database Unit Test Configuration

Test Name	uspXmlElementsGetEmployeeManagers
Test	ManagerExists
Test Condition Name	ManagerFirstNameExists
Test Type	Scalar Value
Column Number	4 (Manager First Name)
Expected Value	0 (Does not exist)
ResultSet	1
Row number	1

XQuery#3: EmployeeNames

This test is for specific values.

DECLARE @XmlTable TABLE (XmlResult XML)
INSERT INTO @XmlTable EXECUTE [dbo].uspXmlElementsGetEmployeeManagers @BusinessEntityID=3
SELECT
--Check their values.
XmlTableFunction.XmlColumn2.value('BusinessEntityID[1]', 'int') AS BusinessEntityID
,XmlTableFunction.XmlColumn2.value('FirstName[1]', 'nvarchar(50)') AS FirstName
,XmlTableFunction.XmlColumn2.value('LastName[1]', 'nvarchar(50)') AS LastName
,XmlTableFunction.XmlColumn2.value('ManagerFirstName[1]', 'nvarchar(50)') AS ManagerFirstName
,XmlTableFunction.XmlColumn2.value('ManagerLastName[1]', 'nvarchar(50)') AS ManagerLastName
FROM
(
SELECT XmlResult FROM @XmlTable
) AS XmlTable(XmlColumn)
CROSS APPLY XmlColumn.nodes('./Employees/Employee[./BusinessEntityID="1"]') XmlTableFunction(XmlColumn2);

Database Unit Test Configuration

Test Name	uspXmlElementsGetEmployeeManagers
Test	EmployeeNames
Test Condition Name	EmployeeLastName
Test Type	Scalar Value
Column Number	3 (Last Name)
Expected Value	Sánchez
ResultSet	1
Row number	1

XQuery#4: EmployeeExists

This test is an alternate way to look for the existence of a subnode.

Rather than examining a specific scalar value, we check for existence of one (or more) records. This approach is more robust for situations where you want to ensure that there is a record, but you don't know how many records there are. I recommend using this technique generally for existence tests, except for tests where you expect one and only one record, in which case the scalar tests described above are more suitable.

DECLARE @XmlTable TABLE (XmlResult XML)
INSERT INTO @XmlTable EXECUTE [dbo].uspXmlElementsGetEmployeeManagers @BusinessEntityID=3
  SELECT
--Get each node as individual record as XML fragment
XmlTableFunction.XmlColumn2.query('.') AS EmployeeNodes
FROM
(
SELECT XmlResult FROM @XmlTable
) AS XmlTable(XmlColumn)
CROSS APPLY XmlColumn.nodes('./Employees/Employee') XmlTableFunction(XmlColumn2);

Note that, in this case, the Test and the Test Condition Name have the same name, although they are different entities. This is acceptable in the situation where a Test has only one Test Condition. It may be preferable to name the Test Condition "AnyEmployeeExists" or similar, to distinguish the Test Condition from the Test.

Database Unit Test Configuration

Test Name	uspXmlElementsGetEmployeeManagers
Test	EmployeeExists
Test Condition Name	EmployeeExists
Test Type	Not Empty ResultSet
ResultSet	1

XQuery#5: ManagerAttributeExists

To round out this tutorial, examine the following example, which uses a stored procedure that returns a mix of attributes and elements.

First the query filters according on the attribute "BusinessEntityID" of the element <Employee>, in order to look at a specific <Employee> node. Then, within the SELECT statement, it checks for the existence of the "ManagerFirstName" attribute within the <Manager> node. This is an instructive example because it shows filtering via attributes within the CROSS APPLY statement as well as in the SELECT clause.

Take a close look at both results of the query. The check for existence of the node <Manager> is positive, but there is no data (attributes) within that element. Therefore the true test in this case must examine the attribute itself. The existence of the node is not enough to prove that there is a manager for this employee.

DECLARE @XmlTable TABLE (XmlResult XML)
INSERT INTO @XmlTable EXECUTE [dbo].uspXmlAttributesGetEmployeeManagers @BusinessEntityID=3
SELECT
  CAST(XmlTableFunction.XmlColumn2.exist('Manager[1]') AS INT) AS [Exist Manager]
, CAST(XmlTableFunction.XmlColumn2.exist('Manager[1][@ManagerFirstName]') AS INT) AS [Exist ManagerFirstName]
FROM
(
SELECT XmlResult FROM @XmlTable
) AS XmlTable(XmlColumn)
CROSS APPLY XmlColumn.nodes('./EmployeeManagers/Employee[@BusinessEntityID="1"]') XmlTableFunction(XmlColumn2);
--Results:
--1 (incorrect!),0 (correct) for BusinessEntityID=1
--1,1 for BusinessEntityID=2

Database Unit Test Configuration

Test Name	uspXmlAttributesGetEmployeeManagers
Test	ManagerAttributeExists
Test Condition Name	ManagerFirstNameAttributeExists
Test Type	Scalar Value
Column Number	2 (Manager First Name)
Expected Value	0 (Does not exist)
ResultSet	1
Row number	1

Conclusion

Database Unit Testing is essential to the success of database projects. Visual Studio Database Edition provides an infrastructure to automate unit testing. To perform feature testing of stored procedures that output XML, XQuery applied to the XML returns scalar values that can easily be used in automated tests. Remember to be careful when testing for null values, since nulls may be represented in XML may be either as an empty node or no node at all, depending on how the stored procedure is written.

Testing SQL-XML Output by Using XQuery

Introduction

Recommended reading

You should continue reading this article if you want to:

Challenges of Testing XML in SQL Server

Setting Up Your Unit Tests

Organizing and Naming Your Database Unit Tests

Creating Stored Procedures Using XML

Stored Procedure#1: Dataset

Stored Procedure#2: XML Elements

Stored Procedure#3: XML Elements and Attributes

Understanding How to Parse XML by Using XQuery Expressions with SQL-XML Stored Procedures

Using XQuery Expressions on SQL-XML Stored Procedures in Database Unit Tests

XQuery#1: EmployeeCount

XQuery#2: ManagerExists

XQuery#3: EmployeeNames

XQuery#4: EmployeeExists

XQuery#5: ManagerAttributeExists

Conclusion

Additional resources