If you have forgotten the syntax for using parameters in XPath queries within SQL Server 2005 and SQL Server 2008 the following should refresh your memory.
Hard coded example:
SELECT
*
FROM
dbo.AuditLog
WHERE
InsertedRows.exist(‘/LOG/@USERID[contains(.,"DOMAIN\JSmith")]‘) = 1
This XPath query works, but it is hard coded for one value.
Concatenated example:
DECLARE @affectedUserName NVARCHAR(50)
SET @affectedUserName = ‘DOMAIN\JSmith’
SELECT
*
FROM
dbo.AuditLog
WHERE
InsertedRows.exist(‘/LOG/@USERID[contains(.,"' + @affectedUserName + '"))]‘) = 1
This XPath query does not work. You will receive the following error message if you try to use this code: The argument 1 of the XML data type method “exist” must be a string literal.
SQL variable example:
DECLARE @affectedUserName NVARCHAR(50)
SET @affectedUserName = ‘DOMAIN\JSmith’
SELECT
*
FROM
dbo.AuditLog
WHERE
InsertedRows.exist(‘/LOG/@USERID[contains(.,sql:variable("@affectedUserName"))]‘) = 1
This XPath query works and accepts a parameter to work with multiple values.
SQL Server Expert, Steve Abraham, holds 8 Microsoft certifications and specializes in SQL Server and .Net Framework architecture, high availability, capacity planning, development, and performance tuning, and SQL Server Recovery.
Excellent work sir!!! This has been a thorn in my side for a couple of weeks. Thank you very much!
can you do this with xquery path with the value function. such as:
DECLARE @RowColumn varchar(500)
SET @RowColumn = ‘DynamicPath’
select config.value(‘(/root/row/sql:variable(“@RowColumn”)[1]‘, ‘int’)
from dbo.KC_Config
I don’t think so. The main difference between the examples is that the original example has a defined path and it is looking for a value to be found at the specified path. Your example seems to be looking to dynamically create the path for each row in the result set – sort of a “Dynamic XML” so to speak. So I don’t think this is currently possible.