title: SQL Server WITH XMLNAMESPACES dynamic declaration
body: <p>I have the following query:</p>
  
  <pre><code>;WITH XMLNAMESPACES (
      'Bob' AS b
  )
  SELECT 'Test' AS [@b:Test]
  FOR XML PATH('root')
  </code></pre>
  
  <p>However I'd like the name space definition to be dynamically picked up at runtime from a configuration table. I've tried the following but they don't appear to be valid SQL:</p>
  
  <pre><code>-- use a sub query
  ;WITH XMLNAMESPACES (
      (SELECT 'Fred') AS b
  )
  SELECT 'Test' AS [@b:Test]
  FOR XML PATH('root')
  
  -- declare a variable
  DECLARE @ns VARCHAR(10) = 'Fred'
  
  ;WITH XMLNAMESPACES (
      @ns AS b
  )
  SELECT 'Test' AS [@b:Test]
  FOR XML PATH('root')
  </code></pre>
  
  <p>Is there any way to have the name space definition dynamic?</p>
  
  <p>Thanks,<br>
  Tom</p>