title: How can I iterate through all nodes in XML - Sql2008
body: <p>How can I iterate through all nodes in XML document using T-SQL (SQL2008).
  I need to create table schemas for each distinct Node Path (elements) in the document.</p>
  
  <pre><code>declare @x xml='
  &lt;logins&gt;
    &lt;roles&gt;
      &lt;role name="Administrator" /&gt;
      &lt;role name="elUser" /&gt;
      &lt;role name="ElAdministrator"/&gt;
      &lt;role name="regionalManager" /&gt;
      &lt;role name="Rep"/&gt;
      &lt;role name="DiscountAdministrator" /&gt;
      &lt;role name="LoginAdmin"/&gt;
      &lt;groups&gt;
        &lt;group name="Administrators"&gt;
          &lt;role name="Administrator"/&gt;
          &lt;role name="elUser" /&gt;
          &lt;role name="ElAdministrator" /&gt;
          &lt;role name="Rep" /&gt;
          &lt;role name="regionalManager" /&gt;
          &lt;role name="DiscountAdministrator" /&gt;
          &lt;role name="LoginAdmin" /&gt;
        &lt;/group&gt;
      &lt;/groups&gt;
    &lt;/roles&gt;
    &lt;members&gt;
      &lt;member login="apeiris" ofgroup="Administrator"&gt;&lt;/member&gt;
    &lt;/members&gt;
  &lt;/logins&gt;'
  </code></pre>
  
  <p>Edited to include above,
  I am thinking of generic script to create /maintain schema, means that I do not know nodes before hand.</p>