Generating a namespace prefix with For XML Explicit

Hello,
How do I get my FOR XML explicit to qualify the returned elements with a
namespace prefix?
I want:
<s1:NewOrderLine xmlns:s1 ="www.company.com">
<s1:Quantity>23</s1:Quantity>
</s1:NewOrderLine>
not:
<NewOrderLine>
<Quantity>23</Quantity>
<NewOrderLine>
Thanks -
SimonIn SQL Server 2000, you need to do it explicitly add them.
Something like (if you are using auto mode):
select a as "s1:a", 'uri:example' as "xmlns:s1"
from t as "s1:t"
for xml auto
if you want to make element-centric mappings, you will need to use the
EXPLICIT mode.
Note in SQL Server 2005, there will be an easier way to specify XML
namespaces using the WITH XMLNAMESPACES clause.
Best regards
Michael
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:418B0654-6075-4FB6-8C2C-845B65E1E221@.microsoft.com...
> Hello,
> How do I get my FOR XML explicit to qualify the returned elements with a
> namespace prefix?
> I want:
> <s1:NewOrderLine xmlns:s1 ="www.company.com">
> <s1:Quantity>23</s1:Quantity>
> </s1:NewOrderLine>
> not:
> <NewOrderLine>
> <Quantity>23</Quantity>
> <NewOrderLine>
> Thanks -
> Simon
>|||Thanks for the reply, Michael. I wonder if you can elaborate on the EXPLICIT
mode option - with the query below I can get these results, but as you can
see, what I don't have is the 'xmlns:s1 ="www.company.com" in the root
element. Is it possible to change the query below to get that, or,
alternately, is it possible to load this result into an XMLDocument and then
add the namespace declaration?
<ns:SimpleTest>
<ns:Customers>
<ns:CompanyName>Alfreds Futterkiste</ns:CompanyName>
</ns:Customers>
</ns:SimpleTest>
from query:
select 1 as Tag,
Null as Parent,
Null as [ns:Sample!1!!element],
Null as [ns:Customers!2!ns:CompanyName!element],
Null as [ns:Customers!2!ns:CustomerID!hide]
UNION ALL
select 2 as Tag,
1 as Parent ,
Null as [ns:Sample!1!!element],
[Customers1].[CompanyName] as [ns:Customers!2!ns:CompanyName!element],
[Customers1].[CustomerID] as [ns:Customers!2!ns:CustomerID!hide]
FROM
(select
[Customers].[CompanyName] as [CompanyName],
[Customers].[CustomerID] as [CustomerID]
from [Customers])
as [Customers1]
Order By [ns:Sample!1!!element] , [ns:Customers!2!ns:CustomerID!hide] , Tag
FOR XML EXPLICIT
"Michael Rys [MSFT]" wrote:

> In SQL Server 2000, you need to do it explicitly add them.
> Something like (if you are using auto mode):
> select a as "s1:a", 'uri:example' as "xmlns:s1"
> from t as "s1:t"
> for xml auto
> if you want to make element-centric mappings, you will need to use the
> EXPLICIT mode.
> Note in SQL Server 2005, there will be an easier way to specify XML
> namespaces using the WITH XMLNAMESPACES clause.
> Best regards
> Michael
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:418B0654-6075-4FB6-8C2C-845B65E1E221@.microsoft.com...
>
>|||Simon,
I think the trick you are looking for is to treat xmlns as an
attribute:
Here is an example based on your request:
select 1 as tag, null as parent,
'www.company.com' as [s1:NewOrderLine!1!xmlns:s1],
null as [s1:Quantity!2]
UNION
select 2 as tag, 1 as parent,
'www.company.com' as [s1:NewOrderLine!1!xmlns:s1],
23 as [s1:Quantity!2]
ORDER BY TAG,PARENT
FOR XML EXPLICIT
This would produce the following:
<s1:NewOrderLine xmlns:s1="www.company.com">
<s1:Quantity>23</s1:Quantity>
</s1:NewOrderLine>
HTH,
Todd
Simon wrote:
> Thanks for the reply, Michael. I wonder if you can elaborate on the
EXPLICIT
> mode option - with the query below I can get these results, but as
you can
> see, what I don't have is the 'xmlns:s1 ="www.company.com" in the
root
> element. Is it possible to change the query below to get that, or,
> alternately, is it possible to load this result into an XMLDocument
and then
> add the namespace declaration?
> <ns:SimpleTest>
> <ns:Customers>
> <ns:CompanyName>Alfreds Futterkiste</ns:CompanyName>
> </ns:Customers>
> </ns:SimpleTest>
> from query:
> select 1 as Tag,
> Null as Parent,
> Null as [ns:Sample!1!!element],
> Null as [ns:Customers!2!ns:CompanyName!element],
> Null as [ns:Customers!2!ns:CustomerID!hide]
> UNION ALL
> select 2 as Tag,
> 1 as Parent ,
> Null as [ns:Sample!1!!element],
> [Customers1].[CompanyName] as
[ns:Customers!2!ns:CompanyName!element],

> [Customers1].[CustomerID] as [ns:Customers!2!ns:CustomerID!hide]
> FROM
> (select
> [Customers].[CompanyName] as [CompanyName],
> [Customers].[CustomerID] as [CustomerID]
> from [Customers])
> as [Customers1]
> Order By [ns:Sample!1!!element] , [ns:Customers!2!ns:CustomerID!hide]
, Tag
> FOR XML EXPLICIT
> "Michael Rys [MSFT]" wrote:
>
the
with a