Using a variable as an xpath value in xml.query

• SQL Espo

  SSCrazy

  Points: 2678

  More actions

  April 30, 2009 at 6:05 am

  #384476

  I am trying to use a variable as the xpath when I do a query statement on an XML document in SQL Server 2008. I have tried

  DECLARE @STR varchar(8000)

  SET @STR = 'ACORD/InsuranceSvc/PersAutoPolicyAddRq/Producer/ProducerInfo/ContractNumber'

  select @myDoc.query(@str)

  which returns

  Msg 8172, Level 16, State 1, Line 11

  The argument 1 of the XML data type method "query" must be a string literal.

  and I have tried

  DECLARE @STR varchar(8000)

  SET @STR = 'ACORD/InsuranceSvc/PersAutoPolicyAddRq/Producer/ProducerInfo/ContractNumber'

  select @myDoc.query('sql:variable("@str")')

  which returns

  ACORD/InsuranceSvc/PersAutoPolicyAddRq/Producer/ProducerInfo/ContractNumber

  Does anyone have any ideas on how I can do it?

  Thanks!

  Michael

• WayneS

  SSC Guru

  Points: 95461

  More actions

  April 30, 2009 at 5:05 pm

  #987085

  The only thing that comes to mind is dynamic sql.

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2