title: SQL Server : how to parse this xml into table columns?
body: <p>I am trying to parse below xml into table data. However, I am only getting one row. What do I need to do to get all the data? I have a <a href="http://www.sqlfiddle.com/#!3/57935/224" rel="nofollow noreferrer">link</a> to demonstrate this as well.</p>
  
  <pre><code>INSERT BATCHES (BatchID, RawXML)
  VALUES (1, '
      &lt;ParamData&gt;
          &lt;moduleRole&gt;
              &lt;moduleId&gt;1&lt;/moduleId&gt;
              &lt;bmRoleId&gt;4&lt;/bmRoleId&gt;
              &lt;moduleId&gt;2&lt;/moduleId&gt;
              &lt;bmRoleId&gt;8&lt;/bmRoleId&gt;
              &lt;moduleId&gt;3&lt;/moduleId&gt;
              &lt;bmRoleId&gt;255&lt;/bmRoleId&gt;
              &lt;moduleId&gt;8&lt;/moduleId&gt;
              &lt;bmRoleId&gt;4&lt;/bmRoleId&gt;
              &lt;moduleId&gt;16&lt;/moduleId&gt;
              &lt;bmRoleId&gt;4&lt;/bmRoleId&gt;
              &lt;moduleId&gt;64&lt;/moduleId&gt;
              &lt;bmRoleId&gt;4&lt;/bmRoleId&gt;
              &lt;moduleId&gt;128&lt;/moduleId&gt;
              &lt;bmRoleId&gt;4&lt;/bmRoleId&gt;
          &lt;/moduleRole&gt;
      &lt;/ParamData&gt;
  ');
  
  SELECT  
      b.BatchID,
      x.XmlCol.value('(moduleId)[1]','INT') AS moduleId,
      x.XmlCol.value('(bmRoleId)[1]','INT') AS bmRoleId
  FROM
      Batches b
  CROSS APPLY 
      b.RawXml.nodes('/ParamData/moduleRole') x(XmlCol);
  </code></pre>