java criteria exist,JPA Criteria API调用SQL Server数据库存在函数

I am building a query using the JPA's CriteriaBuilder to call the SQL Server exist function to find data based on an XML field and it is failing to run due to a The argument 1 of the XML data type method "exist" must be a string literal. error.

I traced the SQL generated and get the same error when I try the query in SQL Server Management Studio. I've simplified the SQL to the following for reference

declare @p1 int

set @p1=NULL

exec sp_prepexec @p1 output,

N'@P0 varchar(8000)',

N'select id, name from mytable where xmlfield.exist(@P0)=1 order by id desc',

'true()'

select @p1

The interesting thing is when I try the query by itself, it runs fine and returns the results.

select id, name from mytable where xmlfield.exist('true()')=1 order by id desc;

Any thoughts on why the generated parameterized SQL statement generated does not work?

解决方案

As the error message states, the value passed into the "exist" function must be a string literal. The example given is trying to pass a variable into the function, but then when you're running the query by itself it works just fine because you're passing in a string literal.

For example...

DECLARE @xml XML = '';

DECLARE @P0 NVARCHAR(500) = 'true()';

SELECT @xml.exist(@P0) -- will not work

SELECT @xml.exist('true()') -- will work

For the longest time I thought this made these functions worthless because I didn't know you could use variables within the string literal. Here is an example:

DECLARE @xml XML = '';

DECLARE @item_id INT;

SET @item_id = 1; -- "test" element does not exist

SELECT @xml.exist('/root/item[@id=sql:variable("@item_id")]/test')

SET @item_id = 2; -- "test" element does exist

SELECT @xml.exist('/root/item[@id=sql:variable("@item_id")]/test')