Talk to the Experts

In a previous post, I discussed the exist() method as part of the XQuery for the Non-Expert blog series.  When working with the exist() method you may not always want to hardcode in the attribute values that you are searching for.  To accomplish this I’ll be discussing how to use variables within XQuery statements.

Variable Syntax

When you need to bring variables into XQuery statements, like the exist() method you will use the sql:variable() function.  This function allows a variable from SQL statement to be passed into an XQuery expression.

The syntax for using variables is:

sql:variable("variableName")

In this syntax, the “variableName” is the variable declared in the T-SQL batch.  This will allow an existence search for any value within an XML document.

Variable Setup

As with the previous posts, we’ll use the setup script from the exist() method post:

IF OBJECT_ID('tempdb..#ExistExample') IS NOT NULL
DROP TABLE #ExistExample
GO
CREATE TABLE #ExistExample
(
XMLDocument xml
)
INSERT INTO #ExistExample
VALUES ('<Building type="skyscraper">
<Name>Sears Tower</Name>
<Floor level="1" />
<Floor level="2" />
<Floor level="3">
<Room number="3.1" internet="cable"/>
<Room number="3.2" internet="wifi"/>
</Floor>
<Floor level="4">
<Room number="4.1" bed="king" />
<Room number="4.2" bed="queen"/>
</Floor>
</Building>')
INSERT INTO #ExistExample
VALUES ('<Building type="skyscraper">
<Name>IDS Building</Name>
<Floor level="01" />
<Floor level="02" />
<Floor level="03" />
<Floor level="04">
<Room number="04.1" />
<Room number="04.2" />
</Floor>
</Building>')
SELECT * FROM #ExistExample

Now that we have this data to use, let’s take a look at how variables can be used against XQuery statements.

Element Variable Example

In this first example, we will be looking at how to find out if an element contains the a value that is contained in a variable.  For this, we want to retrieve all of the XML documents in the table that have the value “Sears Tower” in the Name element under the Building element.  The XQuery path to this location is /Building/Name.

This query will have a variable called @BuildingName.  We want to find out if the value at /Building/Name is equal to the @BuildingName variable.  To check this wrap brackets around the element and check it’s equality against sql:variable(“variableName”).  At this point, the XQuery path changes to /Building/Name[text() = sql:variable("@BuildingName")].

Putting this together, your query might look like the following:

DECLARE @BuildingName varchar(30)
SET @BuildingName = 'Sears Tower'
--Element with the value of @BuildingName
SELECT c.query('.') AS XMLFragment
,c.value('(Name/text())[1]','varchar(25)') AS BuildingName
FROM #ExistExample
CROSS APPLY XMLDocument.nodes('/Building') as t(c)
WHERE c.exist('/Building/Name[text() = sql:variable("@BuildingName")]') = 1

Executing the query will return these results:

As you can see, the row returned is the document with “Sears Tower” as the building name.  Switching the variable around to the other value will change the results and without too much effort.

Attribute Variable Example

This next example will probably be the one that you will use most often.  In this case we want to look at attribute values and determine if they contain specific values.  As with the previous example, we want to do the exist() check with a variable instead of a hard coded attribute value.  There are two variation of attribute checks that we want to do in this next query.

The first check is to examine the XML document to find out if any of the Room elements contain a queen value for the bed attribute.  The XQuery path to the bed attribute is /Building/Floor/Room/@bed.  To check this value, we wrap the bed attribute in brackets and check it against the @BedType variable.  The final XQuery for that is /Building/Floor/Room[@bed = sql:variable("@BedType")].

The other check is to see if a specific Room element has the bed attribute populated with the variable value.  We will use the nodes() method to shred the XML document to the /Building/Floor/Room node of the XML document.  Similar to above the XQuery for the exist() method will be .[@bed = sql:variable("@BedType")].

This query will look like following:

DECLARE @BedType varchar(30)
SET @BedType = 'queen'
--Rooms with beds of @BedType
SELECT c.query('.') AS XMLFragment
,c.value('(@number)','decimal(3,1)') AS RoomNumber
,c.value('(@bed)','varchar(10)') AS BedType
,c.exist('/Building/Floor/Room[@bed = sql:variable("@BedType")]') AS FromRoot
,c.exist('.[@bed = sql:variable("@BedType")]') AS FromNode
FROM #ExistExample
CROSS APPLY XMLDocument.nodes('/Building/Floor/Room') as t(c)

When you execute the query these will be your results:

Examining the FromRoot column, the first four rows are listed as having the queen bed existing within the XML document.  In the fourth row, the FromNode column shows that this row has the queen bed.

Variable Wrap-Up

Using the sql:variable() function to examine XML documents raises the bar on what can be queried from within XML documents.  Instead of writing a query per value that needs to be checked, variables can be leveraged to write a single query instead.  If you have any questions on this usage, feel free to leave it in the comments.

Related posts:

1. XQuery for the Non-Expert – Exist
2. XQuery for the Non-Expert – Value
3. XQuery for the Non-Expert – Query