Post reply

using a variable for a subscript in xquery value

  • April 27, 2009 at 10:08 am

    #984466

    Use sql:variable

    SELECT

    x.y.value('declare namespace z="http://dwivedys.blogspot.com"; z:TO[sql:variable("@CNT")][1]', 'nvarchar(50)'),

    x.y.value('declare namespace z="http://dwivedys.blogspot.com"; z:FROM[sql:variable("@CNT")][1]', 'nvarchar(50)') ,

    x.y.value('declare namespace z="http://dwivedys.blogspot.com"; z:HEADING[sql:variable("@CNT")][1]', 'nvarchar(50)') ,

    x.y.value('declare namespace z="http://dwivedys.blogspot.com"; z:BODY[sql:variable("@CNT")][1]', 'nvarchar(100)')

    FROM @y.nodes('declare namespace z="http://dwivedys.blogspot.com";/NOTE/z:SAU') as x (y)

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • April 29, 2009 at 9:58 am

    #986075

    Anything that I enclose in [] has to be numeric otherwise SS throws an error. In your solution you are using Sql variable as well as hardcoding the subscript TO[sql variable("@cnt")][1]. This does not seem to solve the problem of making the subscript itself variable so that it can be looped a fixed number of times. Sorry if I am not understanding it correctly. Could you please take another shot at explaining your logic?

    Saurabh Dwivedy
    ___________________________________________________________

    My Blog: http://tinyurl.com/dwivedys

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537

    Be Happy!

Viewing 2 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply