Re: Using sp_executesql to dynamically query xml

Tech-Archive recommends: Speed Up your PC by fixing your registry

It looks like your problems stem from the use of dynamic SQL but once your
XQuery code doesn't look correct either.

To start, you should probably build a single string for your query, and not use
parameters. Try something like this

DECLARE @Query nvarchar(255)

DECLARE @SqlCommand nvarchar(1000)


SET @Query = N'//*:Description/@title = "Project:A"'

SET @SqlCommand = 'SELECT * FROM ACTIVITY WHERE

ACTIVITY_DETAIL.value(''' + @Query + ''',''bit'') = cast(1 as bit)'


EXEC sp_executesql @SqlCommand


This will actually fail because of the static typing of XPath expression
//*:Description/@title = "Project:A

It's hard to fix it without knowing exactly what you're trying to accomplish but
if you want to retrieve the value of the *:Description element that contains an
attribute title with a value equal to "Project:A" then the expression should be

(//*:Description[@title="Project:A"])[1]


I hope this helps. Please come back if you run into any more problems.


Denis Ruckebusch
http://blogs.msdn.com/denisruc
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm


"Andy Webb" <Andy Webb@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1E37045D-ACF0-408B-8FFF-8BE22429C510@xxxxxxxxxxxxxxxx
Hi,

I am writing a stored procedure which will allow callers to specify an
XQuery that will be passed to the value function on an Xml column. I want to
substitute this value into the query using sp_executesql to avoid the
possibility of SQL Injection. However the following statement results in an
error. How do I go about doing this without resorting to concatenating input
parameters into my query.

DECLARE @Query nvarchar(255)
DECLARE @SqlCommand nvarchar(1000)

SET @Query = N'//*:Description/@title = "Project:A"'
SET @SqlCommand = 'SELECT * FROM ACTIVITY WHERE
ACTIVITY_DETAIL.value(@QUERY,''bit'') = cast(1 as bit)'

EXEC sp_executesql @SqlCommand, N'@QUERY nvarchar(255)', @QUERY = @QUERY

This results in the the follwing error:

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

Going one step further and quoting @QUERY does not help:

DECLARE @Query nvarchar(255)
DECLARE @SqlCommand nvarchar(1000)

SET @Query = N'//*:Description/@title = "Project:A"'
SET @SqlCommand = 'SELECT * FROM ACTIVITY WHERE
ACTIVITY_DETAIL.value(''@QUERY'',''bit'') = cast(1 as bit)'

EXEC sp_executesql @SqlCommand, N'@QUERY nvarchar(255)', @QUERY = @QUERY

This results in the error

Msg 2390, Level 16, State 1, Line 1
XQuery [ACTIVITY.ACTIVITY_DETAIL.value()]: Top-level attribute nodes are not
supported

How can I work around these errors and provide a dynamic query without using
concatenation?




.

Relevant Pages

  • Re: Need assistance with query
    ... Using that query, you can't do that. ... Declare @OKI int -- OKIData Printer ... Declare @ESI int -- ESI Printer boxes ...
    (microsoft.public.sms.admin)
  • 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: Need assistance with query
    ... for and the query that you are looking for is a complicated query to write ... Declare @OKI int -- OKIData Printer ... Declare @ESI int -- ESI Printer boxes ...
    (microsoft.public.sms.admin)
  • Re: Parameter Query - need previous 2 days worth of data
    ... if you declare the parameter and its ... Even if you intend to use a parameter only once in the query, ... something in the Date/time format? ... So the where clause will look like this WHERE Start_Date> ...
    (comp.databases.ms-access)
  • Re: query parameter order
    ... Declare your parameters in the order you want them to appear. ... Select Query: Parameters from the Menu ... Two of the parameters are called for using square brackets (with prompt text enclosed) on the Criteria: line, and the third is called for similarly but on the Field: line. ... As it stands now the user is prompted for the third criteria first, but I'd like for it to be last. ...
    (microsoft.public.access.queries)