title: How do I create parametrized XPath queries in SQL server?
body: <p>I am trying to write a parametrized query in SQL server that uses a parameter value as part of the XPath, however it does not seem to work the way I would expect it to.  Here is my sample:</p>
  
  <pre><code>create table ##example (xmltest xml)
  
  declare @LanguagePath varchar(75)
  
  set @LanguagePath = '(/languages/language[@id="en-US"])[1]'
  insert into ##example 
  values ('&lt;languages&gt;
              &lt;language id="en-US"&gt;c&lt;/language&gt;
              &lt;language id="es-ES"&gt;c&lt;/language&gt;
          &lt;/languages&gt;')
  
  insert into ##example 
  values ('&lt;languages&gt;
              &lt;language id="en-US"&gt;b&lt;/language&gt;
              &lt;language id="es-ES"&gt;b&lt;/language&gt;
          &lt;/languages&gt;')
  
  
  insert into ##example 
  values ('&lt;languages&gt;
              &lt;language id="en-US"&gt;a&lt;/language&gt;
              &lt;language id="es-ES"&gt;a&lt;/language&gt;
          &lt;/languages&gt;')
  
  --This is a working statement:
  --select *  from ##example 
  --order by xmltest.value('(/languages/language[@id="en-US"])[1]', 'varchar')
  
  
  declare @SQL nvarchar(4000)
  set @SQL = '
  select *  from ##example 
  order by xmltest.value(@LanguagePath1, ''varchar'')
  '
  
  exec sp_executesql @SQL, N'@LanguagePath1 varchar(75)',  @LanguagePath1 = @LanguagePath;
  
  drop table ##example 
  </code></pre>
  
  <p>This code results in the error:
  The argument 1 of the xml data type method "value" must be a string literal.</p>
  
  <p>Any ideas on how I can get this to work?  I would like to try to make my xpath query safe from SQL injection.</p>