[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.

[wild_bill]

I also tried this which adds blank namespaces to the children, which is no good.

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

declare @root xml;
SET @root = '<TEST xmlns="urn:blah" />'

SET @root.modify('insert sql:variable("@result") into (/*)[1]')

select @root;

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

[techgnome]

At first I was trying to figure out what the end result what... then it clicked... what you're after is this as the XML:

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

Right?

LEt me dig through some of my stuff... I think I've done this before...

-tg

[techgnome]

actually... this turned out to be simple...
https://msdn.microsoft.com/en-us/library/ms177400.aspx

here's what I came up with:

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

and the result is:

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

-tg

[wild_bill]

At first I was trying to figure out what the end result what... then it clicked... what you're after is this as the XML:

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

Right?

LEt me dig through some of my stuff... I think I've done this before...

-tg

Close . . .

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

[wild_bill]

I ended up using the first example I posted. I think it is overkill to list the namespace in the child nodes, but the .NET classes I built to deserialize the XML didn't mind.