MSSQL : The argument x of the xml data type method

May 18, 2010 by Christoff Truter SQL   XML  

While playing around with the xml data type in SQL I noticed an interesting issue (maybe it will save someone some time), observe the following snippet:

DECLARE @cities AS XML

SET @cities =
'<cities>
	<city cityID="1" value="Berlyn" countryID="1" />
	<city cityID="2" value="Hamburg" countryID="1" />
	<city cityID="3" value="Frankfurt" countryID="1" />
	<city cityID="4" value="Rotterdam" countryID="2" />
	<city cityID="5" value="Amsterdam" countryID="2" />
</cities>'

SELECT item.value('@cityID', 'INT') cityID,
		item.value('@value', 'VARCHAR(255)') value
FROM @cities.nodes('//city[@countryID = 1]') t (item)

Notice the XPath "nodes('//city[@countryID = 1]')", this statement will return all the cities listed for Deutschland in the XML string.

If we want to make this more generic we can always simply pass a value to a where clause on the query like this:

SELECT item.value('@cityID', 'INT') cityID,
		item.value('@value', 'VARCHAR(255)') value
FROM @cities.nodes('//city') t (item)
WHERE item.value('@countryID', 'INT') = @countryID

But what if we want(perhaps need) to pass a value to a XPath statement? At first we might consider doing the following:

SELECT item.value('@cityID', 'INT') cityID,
		item.value('@value', 'VARCHAR(255)') value
FROM @cities.nodes('//city[@countryID = ' + @countryID + ']') t (item)

This will however generate an error "The argument 1 of the xml data type method "nodes" must be a string literal", for this purpose e.g. interaction between relational and xml data, one can use expressions like sql:variable & sql:column.

DECLARE @countryID AS INT
SET @countryID = 1
SELECT item.value('@cityID', 'INT') cityID,
		item.value('@value', 'VARCHAR(255)') value
FROM @cities.nodes('//city[@countryID = sql:variable("@countryID")]') t (item)

sql:variable being a variable available within scope of our stored procedure, sql:column a field available within our query.

Some additional reading.