title: TSQL - Dynamic query to retrieve the value of given xml element using provided XPATH
body: <p>I'm trying to write a SP in SQL Server 2008 where if the input is a valid XML and a XPATH i want to retrieve the value of that XPATH:</p>
  
  <p>For example if i have:</p>
  
  <pre><code>    &lt;soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"&gt;
          &lt;soapenv:Body&gt;
              &lt;genRetrieve xmlns:v1="http://xxxxxxxxxxxxxxxxxxxxx"&gt;
                  &lt;checkRetrieve&gt;
                      &lt;party&gt;
                          &lt;user&gt;
                              &lt;first&gt;JOHN&lt;/first&gt;
                              &lt;last&gt;SMITH&lt;/last&gt;
                          &lt;/user&gt;
                          &lt;media /&gt;
                      &lt;/party&gt;
                  &lt;/checkRetrieve&gt;
              &lt;/genRetrieve&gt;
          &lt;/soapenv:Body&gt;
      &lt;/soapenv:Envelope&gt;
  </code></pre>
  
  <p>And the XPATH is :</p>
  
  <pre><code>    /*[local-name() = "Envelope"][1]/*[local-name() = "Body"][1]/genRetrieve[1]/checkRetrieve[1]/party[1]/user[1]/first[1]
  </code></pre>
  
  <p>I need to grab "JOHN" and return it.</p>
  
  <p>I tried the below </p>
  
  <pre><code>    DECLARE @xml xml,
              @xpath nvarchar(max),
              @output nvarchar(100),
              @query nvarchar(max)
  
      SET @xml = @userINPUT
      SET xpath = '/*[local-name() = "Envelope"][1]/*[local-name() = "Body"][1]/genRetrieve[1]/checkRetrieve[1]/party[1]/user[1]/first[1]' +  '/text()'
      SET @output = ''    
      select @output = @xml.query('sql:variable("@xpath")')
  </code></pre>
  
  <p>But instead of the value i just get the full XPATH string i passed as argument in return. I also tried dynamic query:</p>
  
  <pre><code>    SET @query = 'SELECT @xml.query(N''' + @xpath + '/text()'')'
      print @query
      EXECUTE(@query)
  </code></pre>
  
  <p>But it gives "Must declare the scalar variable "@xml" erorr</p>