Re: Query: Using dynamic XQuery expressions (urgent)

In general, when you do not know the complexity of the XQuery, you will need
to use sp_executesql. Note that you will have to mediate against SQL and
XQuery injection attacks if you provide external access to the query
parameterization in any case...

Best regards
Michael

"Michael Rys [MSFT]" <mrys@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:uohwK$H7FHA.808@xxxxxxxxxxxxxxxxxxxxxxx
> Hi Narenda and Kent
>
> Cleaning up Kent's second solution, you can write:
>
> declare @NodeToRead varchar(150)
>
> set @NodeToRead = 'City'
>
> declare @xml xml
>
> set @xml = '<ApplicationRequest>
>
> <DataObject>
>
> <City>
>
> <CityId>1</CityId>
>
> <Name>Pune</Name>
>
> </City>
>
> </DataObject>
>
> </ApplicationRequest>'
>
> select @xml.value('(for $node in (/ApplicationRequest/DataObject/*)
>
> where (local-name($node) = sql:variable("@NodeToRead"))
>
> return ($node/Name))[1]','nvarchar(max)')
>
>
>
> or rewrite the query to the shorter XPath expression
>
> select @xml.value('(/ApplicationRequest/DataObject/*[local-name() =
> sql:variable("@NodeToRead")]/Name)[1]','nvarchar(max)')
>
>
>
> In general, we have seen use cases for parameterizing the query methods
> with a string value. However there are some security issues to consider.
>
> I would appreciate if you can send your feature request through the MSDN
> Feedback center or send a mail to sqlwish (at) microsoft (dot) com. That
> way, the powers that keep track of customer feedback will see this issue
> appear and other people can vote on it (in the feedback center).
>
> Best regards
>
> Michael
>
> "Kent Tegels" <ktegels@xxxxxxxxxxx> wrote in message
> news:b87ad74ed978c7ba3ca6cda530@xxxxxxxxxxxxxxxxxxxxx
>> Hello Narendra,
>>
>> Remember that the XQuery expression itself must be a string literally.
>> Now, yes, there are constructors, but those don't seem to work in the
>> where predicate where we'd want them to do for this. Here's the closest I
>> could come with quickly:
>>
>> declare @NodeToRead varchar(150)
>> set @NodeToRead = 'City'
>> declare @query nvarchar(max)
>>
>> set @query = '
>> declare @xml xml
>> set @xml = ''<ApplicationRequest>
>> <DataObject>
>> <City>
>> <CityId>1</CityId>
>> <Name>Pune</Name>
>> </City>
>> </DataObject>
>> </ApplicationRequest>''
>> select @xml.value(''(for $node in (/ApplicationRequest/DataObject/*)
>> where (local-name($node) = "' + @NodeToRead + '")
>> return ($node/Name))[1]'',''nvarchar(max)'')'
>>
>> exec sp_executesql @stmt=@query
>>
>> BLECH!
>>
>> This would be nicer if it worked...
>>
>> declare @NodeToRead varchar(150)
>> set @NodeToRead = 'City'
>> declare @xml xml
>> set @xml = '<ApplicationRequest>
>> <DataObject>
>> <City>
>> <CityId>1</CityId>
>> <Name>Pune</Name>
>> </City>
>> </DataObject>
>> </ApplicationRequest>'
>> select @xml.value('(for $node in (/ApplicationRequest/DataObject/*)
>> where (local-name($node) = "{sql:variable("@NodeToRead")}")
>> return ($node/Name))[1]','nvarchar(max)')
>>
>> I've forwarded this up to MRys as a suggestion for the next release...
>>
>> Thank you,
>> Kent Tegels
>> DevelopMentor
>> http://staff.develop.com/ktegels/
>>
>>
>
>


.

Relevant Pages

  • Re: Query: Using dynamic XQuery expressions (urgent)
    ... or rewrite the query to the shorter XPath expression ... > declare @NodeToRead varchar ... > declare @xml xml ...
    (microsoft.public.sqlserver.xml)
  • 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: Using XML data for query
    ... I would like to use the key-value pair in my SQL query dynamically. ... 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. ... declare @query nvarchar ... declare @predicates int ...
    (microsoft.public.sqlserver.xml)
  • Re: use sql:variable to declare xml namespaces in stored procedures
    ... basically I would like to parameterized the value of the default namespace in the xquery. ... declare @ns nvarcharset @ns = 'microsoft' declare @x xml ... Note that if you remove the sql:variable subsitution, the query works. ...
    (microsoft.public.sqlserver.xml)
  • 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)
Loading