XML query output question

RocketSauce
RocketSauce used Ask the Experts™
on
I am toying around with 2 solutions to an xml report I have to generate.  1 way is via XML in SQL 2005.

I have this query below.  

with xmlnamespaces ('uri' as tns)
select id as 'tns:id',
field1 as 'tns:field1',
field2 as 'tns:field2',
field3 as 'tns:field3',
field4 as 'tns:field4'
from reporting.xmlreport
where pid = 17591
for xml path('tns:TPO'), root('tns:Claim')



The output is fine except for the very first xml element

<tns:Claim xmlns:tns="uri">

This element should simply be
<tns:Claim >

How is this done?  I know its a problem for how I am using the xml namespaces, but I can't find a way to use the namespace and not have that xmlns:tns="uri" part included.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Software Developer
Top Expert 2009
Commented:
Well the concept of a namespace is actually giving it a name.  If you want the generic SQL XML name space use this
WITH XMLNAMESPACES ('urn:schemas-microsoft-com:xml-sql' as sql)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial