Метод XML.exist() в SQL запросе не работает корректно с переменными

Question

Основная задача заключалась в переносе логики поиска нужных XML файлов по заданному со стороны пользователя XPath. Для этого использовал приведение хранимых XML из text типа в XML и готовые из коробки методы работы с XML. В моем случае это метод exist. С ним возникла проблема - он не работает с SQL переменными. Так как, в моем случае, я отправляю запрос на сторону БД и передаю в нем параметры, где XPath задается динамически, я могу использовать только значение данной переменной. Пример кода, когда переменная в exist не работает и равенство с 1 всегда верно для всех XML и возвращает true, что выгружает абсолютно все записи, а не только те, которые удовлетворяют XPath:

exec sp_executesql N'SELECT TOP(1000) ord.TrackingID, ord.DateCreate, ord.OrderType, ord.Error_Description, ord.Response, ord.Request
                    FROM FilteredByXPath AS ord
                    WHERE (ord.Response.exist(''sql:variable("@XPath")'') = 1) OR (ord.Request.exist(''sql:variable("@XPath")'') = 1)',
                    N'@TrackingID nvarchar(4000),@ShowAutotestCases bit,@DateFrom datetime,@DateTo datetime,@InputOrderSource nvarchar(1),@Agency int, @XPath nvarchar(max)',
                    @TrackingID=NULL,@ShowAutotestCases=0,@DateFrom='1753-01-01 00:00:00',@DateTo='9999-12-31 23:59:59.997',@InputOrderSource=N'%',@Agency=0, @XPath=N'//*[text() = "10:33:34.9375000-05:00"]'

Но если мы напрямую попробуем вставить выражение из переменной @XPath в качестве аргумента метода exist, то получим корректную выборку.

exec sp_executesql N'SELECT TOP(1000) ord.TrackingID, ord.DateCreate, ord.OrderType, ord.Error_Description, ord.Response, ord.Request
                    FROM FilteredByXPath AS ord
                    WHERE (ord.Response.exist(''//*[text() = "10:33:34.9375000-05:00"]'') = 1) OR (ord.Request.exist(''//*[text() = "10:33:34.9375000-05:00"]'') = 1)',
                    N'@TrackingID nvarchar(4000),@ShowAutotestCases bit,@DateFrom datetime,@DateTo datetime,@InputOrderSource nvarchar(1),@Agency int, @XPath nvarchar(max)',
                    @TrackingID=NULL,@ShowAutotestCases=0,@DateFrom='1753-01-01 00:00:00',@DateTo='9999-12-31 23:59:59.997',@InputOrderSource=N'%',@Agency=0, @XPath=N'//*[text() = "10:33:34.9375000-05:00"]'

Возможно какая-то проблема при использовании sql:variable метода или что-то ещё. Буду признателен за полезные комментарии и советы.

Answer 1

Можно подготовить оператор SQL и использовать функцию QUOTENAME() для обработки выражения XPath. И только после этого используйте sp_executesql.

SQL

DECLARE @XPath NVARCHAR(100) = N'//*[text() = "10:33:34.9375000-05:00"]';

DECLARE @sql NVARCHAR(1024) = N'SELECT TOP(1000) ord.TrackingID, ord.DateCreate, ord.OrderType, ord.Error_Description, ord.Response, ord.Request
                    FROM FilteredByXPath AS ord
                    WHERE (ord.Response.exist(' + QUOTENAME(@Xpath,CHAR(39)) + ') = 1) OR (ord.Request.exist(' + QUOTENAME(@Xpath,CHAR(39)) + ') = 1)';

PRINT @sql;

EXEC sp_executesql @sql, ...

БЛОГ НА HUSL

Метод XML.exist() в SQL запросе не работает корректно с переменными

Ответы (1 шт):