CODEWITHSUNDEEP
sqlsql-serverxmlxpathxquery
Szymon Seliga
Szymon Seliga

Reputation: 820

Evaluating XQuery/XPath expressions from table

I have a table that contains XQuery expressions. I'd like to evaluate the expression from each row for a given XML data structure. I'm trying this.

DECLARE @somexml xml
SET @somexml = '<root><nodeA>true</nodeA><nodeB>false</nodeB></root>'

SELECT
    @somexml.query('sql:column("af.Expression")' ) AS Fee,
    [af].Expression 
FROM 
    [AerialFees] af

The Expression column contains values of type NVARCHAR(MAX) such as: - xs:boolean(/root[1]/nodeA[1])

When executing this SELECT the expressions don't get evaluated, instead the expression itself is returned (btw, of type XML)

Fee                        Expression
==================================================
xs:boolean(/root[1]/nodeA[1])    xs:boolean(/root[1]/nodeA[1])

If a replace @somexml.query('sql:column("af.Expression")' ) with the actual expression @somexml.query('xs:boolean(/root[1]/nodeA[1])' ) the expression gets correctly evaluated.

But how can I get the above to work?

Upvotes: 1

Views: 705

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

You need to build the query dynamically something like this:

DECLARE @somexml XML
SET @somexml = '<root><nodeA>true</nodeA><nodeB>false</nodeB></root>'

DECLARE @Fees Table
(
    RowNum int,
    Expression nvarchar(max)
)   

INSERT INTO @Fees
SELECT 
    RowNum = ROW_NUMBER() OVER(ORDER BY af.idAerialFee),
    [af].Expression 
FROM 
    [AerialFee] af 

DECLARE @MaxRownum INT
SET @MaxRownum = (SELECT MAX(RowNum) FROM @Fees)

DECLARE @Iter INT
SET @Iter = (SELECT MIN(RowNum) FROM @Fees)

DECLARE @SQL NVARCHAR(MAX)

WHILE @Iter <= @MaxRownum
BEGIN
    -- Get XQuery
    SELECT @SQL = 'SELECT @somexml.query(''' + af.Expression + ''') AS Fee'
    FROM @Fees af
    WHERE RowNum = @Iter

    -- Evaluate XQuery
    EXEC sp_executesql @SQL, N'@somexml xml', @somexml

    -- Get next rows number
    SET @Iter = @Iter + 1
END

I know of no other way to provide the XPath expression to a query in SQL Server.

You could try to add the expression directly:

select @somexml.query(af.Expression)
from AerialFees as af

But that gives you the error message

Msg 8172, Level 16, State 1, Line 27
The argument 1 of the XML data type method "query" must be a string literal.

Or you could try to use sql:column (as you did) but that just returns the value in the column. It does not try to evaluate the expression.

xsl:evaluate as suggested by Dimitre Novatchev is not supported in SQL Server.

Upvotes: 2

Related Questions