2

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
stormCloud
  • 983
  • 1
  • 9
  • 24

2 Answers2

1

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.

har07
  • 88,338
  • 12
  • 84
  • 137
  • 1
    Hi, the more I look into it, the more I understand why dynamic XQuery statements are impossible. The only way I think you could fudge it would be to use dynamic sql as you suggest, but I'd rather not use that solution in this case. Thanks for pointing me in the right direction! Back to the drawing board on this one... – stormCloud Aug 06 '15 at 19:38
0

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

Ashish Kumar
  • 157
  • 1
  • 7
  • Hi, this would work for a simple X=Y statement, however it wouldn't work for more complex xquery statements (such as X>Y, (X+Y) – stormCloud Aug 06 '15 at 19:35