using a variable for a subscript in xquery value

Question

Post reply

using a variable for a subscript in xquery value

Saurabh Dwivedy

SSCommitted

Points: 1887
More actions
April 26, 2009 at 3:41 am

#203880

Dear All,
Is it possible to use a variable as subscript in order to loop the code a fixed number of times. I am pasting the code below:
My apologies if you all may not find the code properly formatted and indented. I am still learning how to write proper code.
/*Push the xml data in an xml variable*/
DECLARE @y XML
SET @y = '
Tove
Jani
Reminder
Dont forget me this weekend!
Jay
Saurabh
Reminder2
Dont forget me this weekend!
Suneel
Ashish Kohad
Info on Ujjwal
Sorry Couldnt call you yesterday!
'
DROP TABLE myxmltable1
CREATE TABLE myxmltable1(to1 nvarchar(50), from1 nvarchar(50),
heading1 nvarchar(50), body1 nvarchar(100))
DECLARE @X INT, @CNT INT
SET @x = 3
SET @CNT = 1
WHILE @CNT <= @X
BEGIN
INSERT INTO myxmltable1(to1, from1, heading1, body1)
(
SELECT
x.y.value('declare namespace z="http://dwivedys.blogspot.com"; z:to[@CNT]', 'nvarchar(50)'),
x.y.value('declare namespace z="http://dwivedys.blogspot.com"; z:from[@CNT]', 'nvarchar(50)') ,
x.y.value('declare namespace z="http://dwivedys.blogspot.com"; z:heading[@CNT]', 'nvarchar(50)') ,
x.y.value('declare namespace z="http://dwivedys.blogspot.com"; z:body[@CNT]', 'nvarchar(100)')
FROM @y.nodes('declare namespace z="http://dwivedys.blogspot.com";/note/z:SAU') as x (y)
)
SET @CNT = @CNT + 1
END
[/code]
I basically want the "INSERT INTO myxmltable1" to repeat three times, each time with a different subscript (with the help of @CNT variable). But sql throws an error when I do this.
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 3 posts - 1 through 2 (of 2 total)

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

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

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

Saurabh Dwivedy SSCommitted Points: 1887 More actions · Answer 2

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!