MSSQL : The argument x of the xml data type method "y" must be a string literal.
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.
Posted by - Christoff Truter
Date - 2010-05-18 22:28:09
Comments
Post comment