SQL Server WITH XMLNAMESPACES dynamic declaration

I have the following query:

;WITH XMLNAMESPACES (
    'Bob' AS b
)
SELECT 'Test' AS [@b:Test]
FOR XML PATH('root')

However I'd like the name sp开发者_运维技巧ace 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:

-- 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')

Is there any way to have the name space definition dynamic?

Thanks,

Tom

You should use dynamic SQL:

DECLARE @d NVARCHAR(30)='Fred'

;WITH XMLNAMESPACES (
    'UniqueValue' AS b
)
SELECT @ns=(
SELECT  'Test' AS [@b:Test]
FOR XML PATH('root'))

SELECT CAST(REPLACE(@ns, 'UniqueValue',@D) AS XML)

I've actually switched to using the FOR XML EXPLICIT mode to control the output - http://msdn.microsoft.com/en-us/library/ms189068(v=SQL.100).aspx - as I was using nested XML fragments and getting empty xmlns attributes. This also allowed me to set the namespace dynamically without place holders and string manipulation.