[RESOLVED] SQL Server 2008 R2 - Default XML Namespace

[wild_bill]

I would like to add a namespace to the root element. I've got this code

;WITH XMLNAMESPACES(default 'urn:blah')
select
 'hello' as 'Element1',
 'world' as 'Element2'
FOR XML RAW(''), ROOT('TEST'), ELEMENTS

which gives me the namespace at every node.

<TEST xmlns="urn:blah">
  <Element1 xmlns="urn:blah">hello</Element1>
  <Element2 xmlns="urn:blah">world</Element2>
</TEST>

I've also tried adding the namespace through the xml modify method

declare @result xml = (
select
 'hello' as 'Element1',
 'world' as 'Element2'
FOR XML RAW(''), ROOT('TEST'), ELEMENTS)

SET @result.modify('insert attribute xmlns {"urn:x12:schemas:005:010:834A1A1:BenefitEnrollmentAndMaintenance"} into (/*)[1]')

but that gives me an error

XQuery [modify()]: Cannot use 'xmlns' in the name expression of computed attribute constructor.

Any ideas? I REALLY would like to not implement string manipulation.