How to eliminate unnecessary namespace declarations in SQL-expressed XML?

Question

I am fine-tuning a web application that calls SOAP services supported by SQL stored procedure calls. Typically, stored procs generate XML, which becomes part of the SOAP response, and that XML has many redundant xmlns namespace declarations. In pathological cases, this may be 30% or more of the encoded XML char, measured in bytes, for example:

<GetFooResponse xmlns="http://service.url/"> <GetFooResult> <FooItems xmlns="http://www.thisisalongishurl.com/schema12345/version12345"> <Item xmlns="http://www.thisisalongishurl.com/schema12345/version12345" data="foo" /> <Item xmlns="http://www.thisisalongishurl.com/schema12345/version12345" data="foo" /> <Item xmlns="http://www.thisisalongishurl.com/schema12345/version12345" data="foo" /> <Item xmlns="http://www.thisisalongishurl.com/schema12345/version12345" data="foo" /> <Item xmlns="http://www.thisisalongishurl.com/schema12345/version12345" data="foo" /> </FooItems > </GetFooResult> </GetFooResponse> 

The SQL that I use to generate XML usually follows this pattern:

 WITH XMLNAMESPACES(DEFAULT 'http://www.thisisalongishurl.com/schema12345/version12345') SELECT [Name], [Value] FROM [Foo] FOR XML PATH('Item'), TYPE, ROOT('FooItems'); 

Is there a way to avoid generating redundant xmlns namespace declarations for each XML element element?

thanks.

Answer 1

 WITH XMLNAMESPACES( 'http://www.thisisalongishurl.com/schema12345/version12345' AS short, DEFAULT 'http://service.url/' ), [Foo] ([short:Name], [short:Value]) AS ( SELECT 'testName', 'testValue' ) SELECT * FROM [Foo] FOR XML PATH('Item'), TYPE, ROOT('FooItems') 

returns

 <FooItems xmlns="http://service.url/" xmlns:short="http://www.thisisalongishurl.com/schema12345/version12345"> <Item> <short:Name>testName</short:Name> <short:Value>testValue</short:Value> </Item> </FooItems>