Dynamic XPath in SQL query

Question

Post reply

Dynamic XPath in SQL query

coen.van.dijk

SSC Enthusiast

Points: 143
More actions
August 22, 2011 at 1:56 am

#255906

Hello,
I have created a stored procedure which contains lines like below:
if @int = '0'
begin
select @alert = (select replace(@alert,'{' + CAST(@int AS CHAR(1)) + '}',@N1.value('AlertParameters[1]/AlertParameter1[1]','varchar(max)')))
end
and
if @int = '1'
begin
select @alert = (select replace(@alert,'{' + CAST(@int AS CHAR(1)) + '}',@N1.value('AlertParameters[1]/AlertParameter2[1]','varchar(max)')))
end
It works but it's not really nice coding. I would like the 'AlertParameters[1]/AlertParameter1[1]' part to be dynamic so that I don't have to use an 'if begin ... end' statement for all possible values of @int.
How can I achive this?
Best regards,
Coen van Dijk

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

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

paul_ramster Ten Centuries Points: 1382 More actions · Answer 1

declare @int int = 1;

declare @alert varchar(max) = '{0}{1}';

declare @N1 xml = '<AlertParameters><AlertParameter1>Param1</AlertParameter1><AlertParameter2>Param2</AlertParameter2></AlertParameters>'

declare @param varchar(50)

select @param = 'AlertParameter'+cast(@int+1 as char(1))

select @alert = replace(@alert,'{' + CAST(@int AS CHAR(1)) + '}',@N1.value('/AlertParameters[1]/*[local-name()=sql:variable("@param")][1]','varchar(max)'))

Jacob Sebastian's blog is an invaluable resource of XQuery samples: http://beyondrelational.com/blogs/jacob/archive/2008/06/26/xquery-labs-a-collection-of-xquery-sample-scripts.aspx

jonsborn Grasshopper Points: 12 More actions · Answer 2

You can use the <template> declaration to create XPath expressions, SQL queries or added syntax strings dynamically for any use in a mashup. This may ascertain activating expressions aural one mashup or it can be used to pass dynamic input to another, generic mashup script.

psd to magento