locked
Using a variable as an xpath value in xml.query RRS feed

  • Question

  • 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
    Thursday, April 30, 2009 2:56 PM

Answers

  • Dynamic SQL:
    DECLARE @str varchar(8000)
    SET @str = 'select @myDoc.query(''ACORD/InsuranceSvc/PersAutoPolicyAddRq/Producer/ProducerInfo/ContractNumber'')'
    EXEC (@str)
    
    I know, it stinks.

    RBarryYoung
    • Proposed as answer by RBarryYoung Saturday, May 2, 2009 8:32 PM
    • Marked as answer by Jacob Sebastian Monday, May 25, 2009 5:28 PM
    Saturday, May 2, 2009 8:32 PM