title: How to fetch data from XML with different nodes as column in SQL Server
body: <p>I have a XML from which I am trying to select rows by the table name from the XML but the columns count is different on each node.</p>
  <p>Below is my XML:</p>
  <pre><code>&lt;tables&gt;
      &lt;table&gt;
        &lt;name&gt;Table1&lt;/name&gt;
         &lt;attr&gt;
           &lt;id&gt;1&lt;/id&gt;
           &lt;col1&gt;123&lt;/col1&gt;
            &lt;col2&gt;345&lt;/col2&gt;
         &lt;/attr&gt;
          &lt;attr&gt;
           &lt;id&gt;2&lt;/id&gt;
           &lt;col3&gt;123&lt;/col3&gt;
         &lt;/attr&gt;
          &lt;attr&gt;
           &lt;id&gt;4&lt;/id&gt;
            &lt;col2&gt;123&lt;/col2&gt;
         &lt;/attr&gt;
      &lt;/table&gt;
  &lt;/tables&gt; 
  </code></pre>
  <p>Now I am querying by below query:</p>
  <pre><code>SELECT T.N.VALUE('id','nvarchar(100)') as id,
         T.N.VALUE('col1','nvarchar(100)') as col1,
         T.N.VALUE('col2','nvarchar(100)') as col2,
         T.N.VALUE('col3','nvarchar(100)') as col3 
  FROM @input.nodes('/tables/table/attr') AS T(N) 
  CROSS APPLY @input.nodes('/tables/table/name') AS S(N) 
  WHERE S.N.value('name','varchar(100)')='Table1'
  </code></pre>
  <p>But I get an error</p>
  <blockquote>
  <p>XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'</p>
  </blockquote>
  <p>But I am expecting to get:</p>
  <pre><code>id | col1 | col2 | col3
  ---+------+------+------
   1 | 123  | 345  | NULL
   2 | NULL | NULL | 123
   4 | NULL | 123  | NULL
  </code></pre>