title: Iterate through XML variable in SQL Server
body: <p>I have a XML variable in a stored procedure (SQL Server 2008), its sample value is</p>
  
  <pre><code>&lt;parent_node&gt;
     &lt;category&gt;Low&lt;/category&gt;
     &lt;category&gt;Medium&lt;/category&gt;
     &lt;category&gt;High&lt;/category&gt;
  &lt;/parent_node&gt;
  </code></pre>
  
  <p>I have to take each category and insert into table as a separate record. How to iterate in XML and take individual node value?</p>
  
  <p>If I want to call a stored procedure and send each category as input parameter, how we can do that? The stored procedure is legacy one, which accept only one category at at time. I am trying to do invoke procedure in this way. </p>
  
  <ol>
  <li>loop fetch single category from xml variable.</li>
  <li>invoke stored procedure with current category.</li>
  <li>move to next category.</li>
  <li>loop until list contain value.</li>
  </ol>
  
  <p>Any help will be appreciated.</p>