Based upon your xml and what I believe I'm understanding, you want to chectk the position "count" of the criteria nodes in your XML
Again I'm not sure if you can use sql:variable inside of a XQUERY value statement. You could try it like below to see if that works.
SET @WHERE =(SELECT SearchCriteriaXML.value('/Criterias/Criteria[position()=sql:variable("@LoopCount")]/ColumName[1]', 'varchar(100)')
If it doesn't work then try
SET @WHERE =(SELECT SearchCriteriaXML.query('/Criterias/Criteria[position()=sql:variable("@LoopCount")]/ColumName')
my question for you now however is, if this is going to be appended to your where clause, what are you checking it against? You have alot of missing code in your post which makes it difficult in determining how you are going about buildng your stored procedure.
Diamsorn
Contributor
2119 Points
384 Posts
Re: How to passing dynamic value to XQuery from SQL 2005 Stored Procedure?
Feb 07, 2008 05:19 PM|LINK
Based upon your xml and what I believe I'm understanding, you want to chectk the position "count" of the criteria nodes in your XML
Again I'm not sure if you can use sql:variable inside of a XQUERY value statement. You could try it like below to see if that works.
SET @WHERE =(SELECT SearchCriteriaXML.value('/Criterias/Criteria[position()=sql:variable("@LoopCount")]/ColumName[1]', 'varchar(100)')
If it doesn't work then try
SET @WHERE =(SELECT SearchCriteriaXML.query('/Criterias/Criteria[position()=sql:variable("@LoopCount")]/ColumName')
my question for you now however is, if this is going to be appended to your where clause, what are you checking it against? You have alot of missing code in your post which makes it difficult in determining how you are going about buildng your stored procedure.
My Blog