Re: Using XML data for query

Hello Han,

I am getting XML as a parameter in my stored procedure. Something
like,
<a col1='val-1' col2='val-2' ... />
I would like to use the key-value pair in my SQL query dynamically.
Something like,
select * from T where col1=@col2 and col2=@col2, ...
Weird is I can't know the column name in runtime. Earlier I used XSL
with output=text to compose the query dynamically. But I am seeking
better idea.

The essential problem here is that since the XQuery is done as part of the underlying execution plan, you'd have to construct this as a dynamic query -- a first query to resolve the where predicate columns and values and a second to actually do the work. e.g.

declare @xquery xml
declare @query nvarchar(4000)
declare @predicates int
set @xquery = '<q emailPromotion="0" title="Ms."/>'
set @query = 'select contactId,firstName,lastName from person.contact where '
set @predicates = @xquery.value('count(/q/@*)','int')
while @predicates > 0
begin
print @predicates
set @query = @query + @xquery.value('local-name((/q/@*)[sql:variable("@predicates")])','nvarchar(100)')+'=' + @xquery.value('(/q/@*)[sql:variable("@predicates")]','nvarchar(100)')
set @predicates = @predicates - 1
if @predicates > 0
set @query = @query + ' and ' end
select cast(@query as xml)
--execute(@query)
go

However, this doesn't actually work. It seems that the use of the sql:varaible function to get the current attribute's messes up the rest of the XQuery. Using the same value as a constant doesn't. I'll see if Dan has any ideas.

And, oh, you have another problem. How are you going to decide when a parameter value needs to be quoted on not?

"Once you start down the dark parth, forever dominate your future it will"

Thanks,
Kent Tegels, DevelopMentor
http://staff.develop.com/ktegels/


.

Relevant Pages

  • Re: Using sp_executesql to dynamically query xml
    ... requires the use of dynamic SQL because I do not know how the xml will be ... XQuery code doesn't look correct either. ... To start, you should probably build a single string for your query, and not use ... DECLARE @SqlCommand nvarchar ...
    (microsoft.public.sqlserver.xml)
  • Re: Query: Using dynamic XQuery expressions (urgent)
    ... In general, when you do not know the complexity of the XQuery, you will need ... XQuery injection attacks if you provide external access to the query ... > declare @NodeToRead varchar ... >> declare @xml xml ...
    (microsoft.public.sqlserver.xml)
  • Re: Using XML data for query
    ... Additionally I need to get XML as ouput param for later use from the dynamic ... (QUERY WITH XSL AND SP_EXECUTESQL) ... declare @paramdef nvarchar ... declare @predicates int ...
    (microsoft.public.sqlserver.xml)
  • Re: CONTAINS performance
    ... FTS predicates, such as CONTAINS. ... As an example, the following query: ... searches - Does it make a difference if B.id and C.id are unique keys? ...
    (microsoft.public.sqlserver.fulltext)
  • Re: many "or" operation make system choose incorrect index
    ... Is there a join in the query? ... too many predicates would disqualify index seeks, ... Please note that there is a certain point at which the compilation time ... lsllcm wrote: ...
    (comp.databases.ms-sqlserver)