title: parsing xml doc with cursor within sql server stored procedure
body: <p>I have an xml document which i want to pass as a parameter to a stored procedure
  the xml looks like this</p>
  
  <pre><code>&lt;root&gt; 
      &lt;EMPLOYEE ID= 100&gt; 
       &lt;PERIOD&gt;AUG-2010&lt;/PERIOD&gt;
          &lt;earnings&gt; 
                 &lt;title&gt;BASIC&lt;/title&gt;
          &lt;amount&gt;2000&lt;/amount&gt;
          &lt;title&gt;HRA&lt;/title&gt;
          &lt;amount&gt;1000&lt;/amount&gt;
          &lt;title&gt;CONVEYANCE&lt;/title&gt;
          &lt;amount&gt;500&lt;/amount&gt;
          &lt;/earnings&gt; 
      &lt;/EMPLOYEE&gt; 
       &lt;EMPLOYEE ID= 101&gt; 
      &lt;PERIOD&gt;AUG-2010&lt;/PERIOD&gt;
            &lt;earnings&gt; 
                 &lt;title&gt;BASIC&lt;/title&gt;
          &lt;amount&gt;2000&lt;/amount&gt;
          &lt;title&gt;HRA&lt;/title&gt;
          &lt;amount&gt;400&lt;/amount&gt;
          &lt;title&gt;CONVEYANCE&lt;/title&gt;
          &lt;amount&gt;500&lt;/amount&gt;
          &lt;/earnings&gt; 
      &lt;/EMPLOYEE&gt; 
  
   &lt;EMPLOYEE ID= 102&gt; 
      &lt;PERIOD&gt;AUG-2010&lt;/PERIOD&gt;
            &lt;earnings&gt; 
                 &lt;title&gt;BASIC&lt;/title&gt;
          &lt;amount&gt;2000&lt;/amount&gt;
          &lt;title&gt;HRA&lt;/title&gt;
          &lt;amount&gt;800&lt;/amount&gt;
          &lt;title&gt;CONVEYANCE&lt;/title&gt;
          &lt;amount&gt;5000&lt;/amount&gt;
          &lt;/earnings&gt; 
   &lt;/EMPLOYEE&gt; 
  &lt;/root&gt;
  </code></pre>
  
  <p>I need to store the above information to 2 tables ie: payslipdetails and payheaddetails.
  I think I have to loop through the xml doc . the outer loop gives me the employee id and period then I insert into the payslipdetails table with those fields and then get into the inner loop and I want to insert the  payheaddetailswith the same employeeid and all his earnings deatls like</p>
  
  <pre><code>empid   title  amount
  100     basic   2000
  100     hra     1000
  100     conveyance   500
  </code></pre>
  
  <p>then I go to the outer loop and get the next employee id and repeat the same thing</p>
  
  <p>How can I go to the inner child xml any ways like openxml etc..??</p>