Set attribute on root element with for xml path clause

25 Aug 2006 7:03 PM

PCTC_IT

I'm trying to set an attribute on the root element in my slect for xml path
statement.
Here's my query:

WITH XMLNAMESPACES('http://www.foo.com/' as n0)
SELECT 'john' as name, '222 mystreet' as address
for xml PATH ('MyPerson') ,root('n0:Root')

which returns this:
<n0:Root xmlns:n0="http://www.foo.com/">
<MyPerson>
    <name>john</name>
    <address>222 mystreet</address>
</MyPerson>
</n0:Root>

But i want this:
<n0:Root xmlns:n0="http://www.foo.com/" GroupID="555">
<MyPerson>
    <name>john</name>
    <address>222 mystreet</address>
</MyPerson>
</n0:Root>

I also tried this:
WITH XMLNAMESPACES('http://www.foo.com/' as n0)
SELECT 555 '@GroupID',
(
SELECT 'john' as name, '222 mystreet' as address
for xml PATH ('MyPerson'),type
)
for XML PATH ('n0:root')

Which almost got me there, but re-declared my namespace:
<n0:root xmlns:n0="http://www.foo.com/" GroupID="555">
<MyPerson xmlns:n0="http://www.foo.com/">
    <name>john</name>
    <address>222 mystreet</address>
</MyPerson>
</n0:root>

There has got to be a simple way to add a attribute to the root element.
Thanks in advance.

John

26 Aug 2006 1:37 AM

PCTC_IT

I posted this in the wrong newsgroup. Someone posted an answer to it in
sqlserver.xml group.

Show quote

"PCTC_IT" wrote:

> I'm trying to set an attribute on the root element in my slect for xml path
> statement.
> Here's my query:
>
> WITH XMLNAMESPACES('http://www.foo.com/' as n0)
> SELECT 'john' as name, '222 mystreet' as address
> for xml PATH ('MyPerson') ,root('n0:Root')
>
> which returns this:
> <n0:Root xmlns:n0="http://www.foo.com/">
>   <MyPerson>
>     <name>john</name>
>     <address>222 mystreet</address>
>   </MyPerson>
> </n0:Root>
>
> But i want this:
> <n0:Root xmlns:n0="http://www.foo.com/" GroupID="555">
>   <MyPerson>
>     <name>john</name>
>     <address>222 mystreet</address>
>   </MyPerson>
> </n0:Root>
>
> I also tried this:
> WITH XMLNAMESPACES('http://www.foo.com/' as n0)
> SELECT 555 '@GroupID',
> (
> SELECT 'john' as name, '222 mystreet' as address
> for xml PATH ('MyPerson'),type
> )
> for XML PATH ('n0:root')
>
> Which almost got me there, but re-declared my namespace:
> <n0:root xmlns:n0="http://www.foo.com/" GroupID="555">
>   <MyPerson xmlns:n0="http://www.foo.com/">
>     <name>john</name>
>     <address>222 mystreet</address>
>   </MyPerson>
> </n0:root>
>
> There has got to be a simple way to add a attribute to the root element.
> Thanks in advance.
>
> John
>