MSSQL : The argument x of the xml data type method

May 18, 2010 by 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:


SET @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" />

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.
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.

Leave a Comment

Query XML data in a function with namespace September 5, 2014 by talaa123

I am facing problems while retrieving data from XML. with xmlnamespaces ('x-elements' as x) select tb.[Profile].value('(x:ppr/x:static/refId)[1]', 'varchar(22)') testCol from table1 tb The above code works perfectly fine. But, when I pass the XML path in a function it compiles correctly but doesn't return data upon calling but just the xml path (that is passed to it). CREATE FUNCTION testFunc ( @p varchar(22) ) RETURNS nvarchar(max) AS BEGIN DECLARE @Rs nvarchar(max); with xmlnamespaces ('x-elements' as x) select @Rs = tb.[Profile].value('(sql:variable("@p_path"))[1]', 'nvarchar(max)') from table1 tb RETURN (@Rs) END The result I am getting is "x:ppr/x:sta" (which is a path not the value) whereas it should return a value like "aJxk9pGntc5V" Please suggest a solution!

fails - solved September 20, 2012 by ekkis

in case you're interested:

UDF: sp_executesql alternative September 19, 2012 by Christoff Truter

Well, one option would be to write a CLR function, I've got a few examples on my site e.g.

fails September 19, 2012 by ekkis

Christoff, yes that would be a solution, however in a UDF I'm not allowed to call sp_executesql - so I'm looking for an alternative. I've posted here: and here: in case you want to see some of the ideas being bandied about

RE: Passing dynamic node September 18, 2012 by Christoff Truter

As far as I know this is not currently supported, you can however achieve the same result using dynamic SQL example: DECLARE @node VARCHAR(255), @query NVARCHAR(MAX) SET @node = '//city[@countryID = 2]' SET @query = 'SELECT item.value(''@cityID'', ''INT'') cityID, item.value(''@value'', ''VARCHAR(255)'') value FROM @cities.nodes(''' + @node + ''') t (item)' exec sp_executesql @query, N'@cities xml', @cities

RE: fails September 17, 2012 by ekkis

yes, that's exactly it. I'm trying to write a UDF that can take a path to a node e.g. select dbo.getVal('/my/path/to/a/node') and will return its text contents

September 13, 2012 by Anonymous

Hi Ekkis. Thank you for the comment. What are you trying to achieve? Judging from your query it seems like you're trying to pass a node name dynamically? (the .nodes function expects an xpath/xquery node type specification)

fails September 13, 2012 by ekkis

I'm struggling with something similar but my from clause looks like this: FROM @cities.nodes('(sql:variable("@countryID")/text())[1]') t (item) and get the complaint: Msg 9335, Level 16, State 1, Procedure xxxxx, Line 11 XQuery [value()]: The XQuery syntax '/function()' is not supported. thoughts?

more details November 23, 2010 by krishnaroopa

In the condition, @cities.nodes('//city[@countryID = sql:variable("@countryID")] What if we need to filter using element value. or we need to give the clause itself, like 'countryid=1' in sql variable and filter nodes? please help out

November 22, 2010 by Rumeysa

Thank you very very much. I was in trouble :S