1

Topic: Whether it is possible to prevent the repeated declaration namespace at an output xml?

Example:
[code]
if OBJECT_ID (' tempdb..#t ') is not null
drop table #t;
Create table #t
(i int identity (1,1) not null
,f1 nvarchar (255) Null
,f2 nvarchar (255) Null
,ii int Null
Constraint PK_i primary key clustered (i))
Insert into #t (f1, f2, ii)
Values (' nom 1. ', ' prim 1. ', Null)
, (' nom 1. ', ' prim 2. ', Null)
, (' nom 1. ', Null, Null)
, (' nom 2. ', Null, 1)
, (' nom 2.a ', Null, 1)
, (' nom 2.b ', Null, 1)
, (' nom 2.b ', ' prim 3. ', 1)
, (' nom 2. ', Null, 2)
Select *
,(select * from #t b
where a.i = b.ii
For xml path (' prim '), ELEMENTS XSINIL, type
)
from #t a
For xml path (' nom '), root (' book '), ELEMENTS XSINIL, type
The example generates here such :
[code]
<book xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance">
<nom>
<i> 1 </i>
<f1> nom 1. </f1>
<f2> prim 1. </f2>
<ii xsi:nil = "true"/>
<prim xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance">
<i> 4 </i>
<f1> nom 2. </f1>
<f2 xsi:nil = "true"/>
<ii> 1 </ii>
</prim>
<prim xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance">
<i> 5 </i>
<f1> nom 2.a </f1>
<f2 xsi:nil = "true"/>
<ii> 1 </ii>
</prim>
<prim xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance">
<i> 6 </i>
<f1> nom 2.b </f1>
<f2 xsi:nil = "true"/>
<ii> 1 </ii>
</prim>
<prim xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance">
<i> 7 </i>
<f1> nom 2.b </f1>
<f2> prim 3. </f2>
<ii> 1 </ii>
</prim>
</nom>
<nom>
<i> 2 </i>
<f1> nom 1. </f1>
<f2> prim 2. </f2>
<ii xsi:nil = "true"/>
<prim xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance">
<i> 8 </i>
<f1> nom 2. </f1>
<f2 xsi:nil = "true"/>
<ii> 2 </ii>
</prim>
</nom>
<nom>
<i> 3 </i>
<f1> nom 1. </f1>
<f2 xsi:nil = "true"/>
<ii xsi:nil = "true"/>
</nom>
<nom>
<i> 4 </i>
<f1> nom 2. </f1>
<f2 xsi:nil = "true"/>
<ii> 1 </ii>
</nom>
<nom>
<i> 5 </i>
<f1> nom 2.a </f1>
<f2 xsi:nil = "true"/>
<ii> 1 </ii>
</nom>
<nom>
<i> 6 </i>
<f1> nom 2.b </f1>
<f2 xsi:nil = "true"/>
<ii> 1 </ii>
</nom>
<nom>
<i> 7 </i>
<f1> nom 2.b </f1>
<f2> prim 3. </f2>
<ii> 1 </ii>
</nom>
<nom>
<i> 8 </i>
<f1> nom 2. </f1>
<f2 xsi:nil = "true"/>
<ii> 2 </ii>
</nom>
</book>
[/code]
Whether it is possible to suppress repeated declarations xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance" at level child ?

2

Re: Whether it is possible to prevent the repeated declaration namespace at an output xml?

iii2, so?

Select *
,(select * from #t b
where a.i = b.ii
For xml path (' prim '), type
)
from #t a
For xml path (' nom '), root (' book '), ELEMENTS XSINIL, type

3

Re: Whether it is possible to prevent the repeated declaration namespace at an output xml?

Here so
Select *
, cast ((select * from #t b where a.i = b.ii For xml path (' prim ')) as xml)
from #t a
For xml path (' nom '), root (' book '), ELEMENTS XSINIL, type

4

Re: Whether it is possible to prevent the repeated declaration namespace at an output xml?

Minamoto, aleks222, what artful!
In this case are not deduced null values in a branch prim.
And then, I a little not meant it. I thought, can there is a method to inform sql server that this  it is necessary to generate all in a context of this name space, to declare it (as that) in title, and further already to receive something such: <f2 xsi:nil = "true"/> at any nesting level.

5

Re: Whether it is possible to prevent the repeated declaration namespace at an output xml?

iii2;

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/2f7bdfbf-8e40-456b-84e8-195318649703/how-to-remove-namespaces-from-xml-tags-when-using-for-xml-option-with-xmlnamespaces?forum=transactsql wrote:

Duplicate namespaces when creating nested XML is a known problem with FOR XML. There is an existing connect (Microsoft bug) for it, which you can vote for here:
http://connect.microsoft.com/SQLServer/ … statements

6

Re: Whether it is possible to prevent the repeated declaration namespace at an output xml?

Minamoto, thanks, clearly.
The best variant - to hammer.
If absolutely secures - that banal  to beat the superfluous...
Wonderfully.

7

Re: Whether it is possible to prevent the repeated declaration namespace at an output xml?

iii2, well, as a whole, yes, considering that fact that it does not influence validity XML.