Query: Using dynamic XQuery expressions (urgent)

18 Nov 2005 11:21 AM

Narendra

hi Guys,

I am trying to use sql server variable in the xQuery expression, but
it's not working.
In my case name of the element is dynamic and is stored in the sql
server variable.

Please refer to following sample
* this statement generates error "select @xml.value( @expr
,'varchar(500)')"

It would be gr8 if somebody could tell me an approach to query dynamic
expressions.

Thanks
Narendra

declare @xml xml
set @xml = '<ApplicationRequest>
        <DataObject>
                <City>
                      <CityId>1</CityId>
                                  <Name>Pune</Name>
                    </City>
        </DataObject>
    </ApplicationRequest>'

declare @NodeToRead varchar(150)

set @NodeToRead = 'City'

declare @expr nvarchar(250)
set @expr = '(/ApplicationRequest/DataObject/' + @NodeToRead +
'/Name)[1]'

select @xml.value( @expr ,'varchar(500)')

18 Nov 2005 3:50 PM

Kent Tegels

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/

18 Nov 2005 8:36 PM

Michael Rys [MSFT]

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

Show quoteHide quote

"Kent Tegels" <kteg***@develop.com> wrote in message
news:b87ad74ed978c7ba3ca6cda530@news.microsoft.com...
> 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/
>
>

18 Nov 2005 8:48 PM

Michael Rys [MSFT]

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

Show quoteHide quote

"Michael Rys [MSFT]" <m***@online.microsoft.com> wrote in message
news:uohwK$H7FHA.808@TK2MSFTNGP09.phx.gbl...
> 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" <kteg***@develop.com> wrote in message
> news:b87ad74ed978c7ba3ca6cda530@news.microsoft.com...
>> 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/
>>
>>
>
>

18 Nov 2005 9:30 PM

Kent Tegels

Hello Michael Rys [MSFT],

ARRRGH!

I think I tried every variation of this EXCEPT for just assuming that sql:variable
didn't need to be in a constructor at all.

Thanks!
Kent

21 Nov 2005 5:52 AM

Narendra Chaudhari

Hi Eugene,Michael,Kent

Thanks a lot for ur solutions.

Both solutions are working (With loop & without loop). I could read
values of dynamic node.

You guys were really a good help

Thanks & Regards

Narendra Chaudhari

*** Sent via Developersdex http://www.developersdex.com ***

18 Nov 2005 8:46 PM

Eugene Kogan [MSFT]

Here's a couple of examples on how to achieve this:

select @xml.value('
(/ApplicationRequest/DataObject/*[local-name() =
sql:variable("@NodeToRead")]/Name)[1]
','nvarchar(500)')

select @xml.query('
/ApplicationRequest//*[local-name() = sql:variable("@NodeToRead")]/Name
')

Best regards,
Eugene
---
This posting is provided "AS IS" with no warranties, and confers no rights.

Show quoteHide quote

"Narendra" <narendr***@gmail.com> wrote in message
news:1132312867.852168.258060@f14g2000cwb.googlegroups.com...
> hi Guys,
>
> I am trying to use sql server variable in the xQuery expression, but
> it's not working.
> In my case name of the element is dynamic and is stored in the sql
> server variable.
>
> Please refer to following sample
> * this statement generates error "select @xml.value( @expr
> ,'varchar(500)')"
>
> It would be gr8 if somebody could tell me an approach to query dynamic
> expressions.
>
> Thanks
> Narendra
>
> declare @xml xml
> set @xml = '<ApplicationRequest>
> <DataObject>
>         <City>
>               <CityId>1</CityId>
>                               <Name>Pune</Name>
>             </City>
> </DataObject>
>   </ApplicationRequest>'
>
> declare @NodeToRead varchar(150)
>
> set @NodeToRead = 'City'
>
>
> declare @expr nvarchar(250)
> set @expr = '(/ApplicationRequest/DataObject/' + @NodeToRead +
> '/Name)[1]'
>
> select @xml.value( @expr ,'varchar(500)')
>