Different behavior when using ELEMENTS XSINIL on SQL 2005 and SQL 2012

Question

I have a long-standing problem between SQL 2005 (9.0.5057) and SQL 2012 (11.0.3128). When I run the following sample SQL query on SQL 2005 and then SQL 2012, I get different results:

select
  0 'test1/@old', null 'test1',
  null 'test2/@old', 2 'test2',
  2 'test3/@old', 2 'test3',
  null 'test4/@old', null 'test4'
FOR XML PATH('Data'), ELEMENTS XSINIL

For SQL 2005, the result is:

<Data xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <test1 old="0" />
  <test2>2</test2>
  <test3 old="2">2</test3>
  <test4 xsi:nil="true" />
</Data>

For SQL 2012, the result is:

<Data xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <test1 old="0" xsi:nil="true" />
  <test2>2</test2>
  <test3 old="2">2</test3>
  <test4 xsi:nil="true" />
</Data>

Test1 is the problem. The behavior for SQL 2012 is correct, but I'm struggling to find a patch or work for it in SQL 2005.

I performed the same test on SQL Server 2008 R2 (10.50.2500), but I get the result of SQL 2005.

Am I missing something?

Answer 1

Am I missing something?

No, this seems like a bug in SQL Server that was fixed in SQL Server 2012, possibly in Service Pack 1.

SQLXML: xml path() xsinil: xsi: nil = "true"

SQL 2005.

, , - , .

XML, modify() Method (xml Data Type) insert (XML DML).

, , exist() (xml Data Type) , xsi:nil.

declare @X xml

set @X = (
         select
           0 'test1/@old', null 'test1',
           null 'test2/@old', 2 'test2',
           2 'test3/@old', 2 'test3',
           null 'test4/@old', null 'test4'
         for xml path('Data'), elements xsinil
         )

while @X.exist('//*[empty(text()) and empty(*) and empty(@xsi:nil)]') = 1
  set @X.modify('declare namespace xsi="http://www.w3.org/2001/XMLSchema-instance";
                 insert (attribute xsi:nil {"true"}) into 
                   (//*[empty(text()) and empty(*) and empty(@xsi:nil)])[1]')

select @X

//* XML.
[] .
empty(text()) node.
empty(*) .
empty(@xsi:nil) xsi:nil.