[RESOLVED] SQL Server 2008 R2 - Default XML Namespace

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

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.

Code:

---

<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

Code:

---

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

Quote:

---

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

---

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

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

Code:

---

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;

---

Code:

---

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

---

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:

Code:

---

<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

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

here's what I came up with:

Code:

---

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

---

and the result is:

Code:

---

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

---

-tg

Quote:

---

Originally Posted by 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:

Code:

---

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

Code:

---

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

---

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.