XQuery

• santhosh_ms3

  Default port

  Points: 1482

  More actions

  September 2, 2008 at 5:48 am

  #197108

  based on the @SPName (name of the StoredProcedure ) I'm trying to execute SQL query read from the XML document dynamically.. But I'm getting only NULL as a result..

  SET @Qry = @Qry + 'SELECT @var = ParamXML.value(''/ROOT[1]/TestCase[' + @TestCaseID + ']/Query[1]/@Expected'',''VARCHAR(MAX)'') '

  + 'FROM dbo.SPParamXML '

  + 'WHERE SPName = @SPName '

  EXEC sp_executesql @Qry, N'@var AS VARCHAR(MAX) OUT,

  @SPName AS VARCHAR(50)', @var OUT, @SPName

  is there any way to execute this scenario dynamically???

• Mark Cowne

  One Orange Chip

  Points: 26944

  More actions

  September 2, 2008 at 7:21 am

  #865972

  Why do you want to do this dynamically?

  Have you tried something like this?

  SELECT @var = ParamXML.value('/ROOT[1]/TestCase[position()=sql:variable("@TestCaseID")][1]/Query[1]/@Expected','VARCHAR(MAX)')

  FROM dbo.SPParamXML

  WHERE SPName = @SPName

  ____________________________________________________

  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