title: XML PATH ELEMENTS XSINIL without replicate xmlns:xsi
body: <p>I need to extract XML from table, with null value:</p>
  
  <pre><code>&lt;RoomNo xsi:nil="true" /&gt;
  </code></pre>
  
  <p>So I use the clause <strong>elements xsinil</strong>:</p>
  
  <pre><code>for xml path('root'), elements xsinil
  </code></pre>
  
  <p>My problem is in the extraction of a complex XML made with subqueries. The clause <strong>elements xsinil</strong> insert in every subquery the code <strong>xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"</strong></p>
  
  <p>If I remove the inner <strong>elements xsinil</strong> I lose the null fields.</p>
  
  <p>With this query:</p>
  
  <pre><code>    -- Test table
      CREATE TABLE #TestTable (StudentName VARCHAR(100), Course VARCHAR(100), Instructor VARCHAR(100), RoomNo VARCHAR(100))
      GO
      INSERT INTO #TestTable (StudentName, Course, Instructor, RoomNo)
      SELECT 'Mark', 'Algebra', 'Dr. James', '101'
      UNION ALL
      SELECT 'Mark', 'Maths', 'Dr. Jones', '201'
      UNION ALL
      SELECT 'Joe', 'Algebra', 'Dr. James', null
      UNION ALL
      SELECT 'Joe', 'Science', 'Dr. Ross', '301'
      UNION ALL
      SELECT 'Joe', 'Geography', 'Dr. Lisa', null
      UNION ALL
      SELECT 'Jenny', 'Algebra', 'Dr. James', '101'
      GO
  
  
      -- Xpath query
      select -- node STUDENT
          A.StudentName,
          (
              select -- node TEST
              Instructor as '@Instructor', Course as 'Course', RoomNo as 'RoomNo'
              from #TestTable B where B.StudentName = A.StudentName
              for xml path('test'), type, elements xsinil
              ) as 'node()'
      from (select distinct StudentName from #TestTable) A
      for xml path('student'), root('root'), type, elements xsinil
  
      -- end
      drop TABLE #TestTable
  </code></pre>
  
  <p>I got:</p>
  
  <pre><code>    &lt;root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;
        &lt;student&gt;
          &lt;StudentName&gt;Jenny&lt;/StudentName&gt;
          &lt;test xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" Instructor="Dr. James"&gt;
            &lt;Course&gt;Algebra&lt;/Course&gt;
            &lt;RoomNo&gt;101&lt;/RoomNo&gt;
          &lt;/test&gt;
        &lt;/student&gt;
        &lt;student&gt;
          &lt;StudentName&gt;Joe&lt;/StudentName&gt;
          &lt;test xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" Instructor="Dr. James"&gt;
            &lt;Course&gt;Algebra&lt;/Course&gt;
            &lt;RoomNo xsi:nil="true" /&gt;
          &lt;/test&gt;
          &lt;test xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" Instructor="Dr. Ross"&gt;
            &lt;Course&gt;Science&lt;/Course&gt;
            &lt;RoomNo&gt;301&lt;/RoomNo&gt;
          &lt;/test&gt;
          &lt;test xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" Instructor="Dr. Lisa"&gt;
            &lt;Course&gt;Geography&lt;/Course&gt;
            &lt;RoomNo xsi:nil="true" /&gt;
          &lt;/test&gt;
        &lt;/student&gt;
        &lt;student&gt;
          &lt;StudentName&gt;Mark&lt;/StudentName&gt;
          &lt;test xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" Instructor="Dr. James"&gt;
            &lt;Course&gt;Algebra&lt;/Course&gt;
            &lt;RoomNo&gt;101&lt;/RoomNo&gt;
          &lt;/test&gt;
          &lt;test xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" Instructor="Dr. Jones"&gt;
            &lt;Course&gt;Maths&lt;/Course&gt;
            &lt;RoomNo&gt;201&lt;/RoomNo&gt;
          &lt;/test&gt;
        &lt;/student&gt;
      &lt;/root&gt;
  </code></pre>
  
  <p>But I need:</p>
  
  <pre><code>    &lt;root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;
        &lt;student&gt;
          &lt;StudentName&gt;Jenny&lt;/StudentName&gt;
          &lt;test Instructor="Dr. James"&gt;
            &lt;Course&gt;Algebra&lt;/Course&gt;
            &lt;RoomNo&gt;101&lt;/RoomNo&gt;
          &lt;/test&gt;
        &lt;/student&gt;
        &lt;student&gt;
          &lt;StudentName&gt;Joe&lt;/StudentName&gt;
          &lt;test Instructor="Dr. James"&gt;
            &lt;Course&gt;Algebra&lt;/Course&gt;
            &lt;RoomNo xsi:nil="true" /&gt;
          &lt;/test&gt;
          &lt;test Instructor="Dr. Ross"&gt;
            &lt;Course&gt;Science&lt;/Course&gt;
            &lt;RoomNo&gt;301&lt;/RoomNo&gt;
          &lt;/test&gt;
          &lt;test Instructor="Dr. Lisa"&gt;
            &lt;Course&gt;Geography&lt;/Course&gt;
            &lt;RoomNo xsi:nil="true" /&gt;
          &lt;/test&gt;
        &lt;/student&gt;
        &lt;student&gt;
          &lt;StudentName&gt;Mark&lt;/StudentName&gt;
          &lt;test Instructor="Dr. James"&gt;
            &lt;Course&gt;Algebra&lt;/Course&gt;
            &lt;RoomNo&gt;101&lt;/RoomNo&gt;
          &lt;/test&gt;
          &lt;test Instructor="Dr. Jones"&gt;
            &lt;Course&gt;Maths&lt;/Course&gt;
            &lt;RoomNo&gt;201&lt;/RoomNo&gt;
          &lt;/test&gt;
        &lt;/student&gt;
      &lt;/root&gt;
  </code></pre>