CODEWITHSUNDEEP
sqlsql-serversql-server-2008xqueryxquery-sql
stormCloud
stormCloud

Reputation: 993

XQuery Comparison Expressions in SQL Column

I would like to have a table that I can store XQuery Comparison Expressions in, so that I can evaluate them in a query.

I've been doing a bit of R&D into if it is possible, and I'm struggling.

If I put an XQuery expression in a column, then it seems to evaluate differently to if I put the XQuery expression directly into the query. For example, when I run the below query:

declare 
  @x xml = ''

create table #condition
(
    condition nvarchar(255)
)

insert into #condition
values
('''1''=''1''')

select 
  condition, 
  @x.query('sql:column("condition")'),
  @x.query('''1''=''1''')
from #condition

I would expect this to return:

'1'='1', true, true

However it actually returns:

'1'='1', '1'='1', true

Does anybody know how I can evaluate comparison expressions that are stored in a column?

The eventual plan is to be able to use this technique to filter down rows of a table based on XQuery conditions present. So ultimately I'd want to be able to do this in the where clause of a select statement.

I've put the above example into an sql fiddle encase it is useful.

Many thanks

Upvotes: 2

Views: 258

Answers (2)

har07
har07

Reputation: 89305

Short answer: Unfortunately you can't.

sql:column("condition") will be evaluated to a suitable XML primitive data type based on the table column type. In this case the value from condition column will always be evaluated as XML string type instead of an XQuery statement, as you have figured out from running your sample query. And I can't see anyway of evaluating dynamic XQuery statement, unless you want to construct the entire query dynamically and execute it later on possibly using sp_executesql.

Upvotes: 1

Ashish Kumar
Ashish Kumar

Reputation: 157

Try this query:

declare @x xml = ''

create table #condition ( condition nvarchar(255) )

insert into #condition values ('''1''=''1''') select condition, case when col1 like col2 then 'True' else 'False' END col, quer from
( select condition, PARSENAME(REPLACE(condition,'=','.'),2) col1, PARSENAME(REPLACE(condition,'=','.'),1) col2 , @x.query('''1''=''1''') as quer from #condition )base

Upvotes: 0

Related Questions