title: Parse xml in TSQL: how to loop throw child nodes?
body: <p>I'm developing a stored procedure for SQL Server 2012 express and developer edition with latest service pack.</p>
  
  <p>I want to parse an XML and insert its data into a table.</p>
  
  <p>This is my <code>XML</code>:</p>
  
  <pre><code>&lt;Parent ParentId="1" ParentCodeId="ASDAS121" ParentLevelId="1"&gt;
      &lt;Child ParentId="1" ChildCode="GF342" ChildLevel="0" Position="1"/&gt;
      &lt;Child ParentId="1" ChildCode="G1A42" ChildLevel="0" Position="2"/&gt;
      &lt;Child ParentId="1" ChildCode="GFS42" ChildLevel="0" Position="3"/&gt;
      &lt;Child ParentId="1" ChildCode="GF242" ChildLevel="0" Position="4"/&gt;
                          &lt;!-- N childs more --&gt;
      &lt;Child ParentId="1" ChildCode="DF342" ChildLevel="0" Position="N"/&gt;
  &lt;/Parent&gt;
  </code></pre>
  
  <p>This is my stored procedure code:</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[InsertXML]
      @xmlString NVARCHAR(MAX)
  AS
  BEGIN
      DECLARE @xml xml
  
      set nocount on
  
      -- Convert string data into XML.
      SET @xml = @XmlString
  
      DECLARE @hDoc int
  
      --Prepare input values as an XML document
      exec sp_xml_preparedocument @hDoc OUTPUT, @xmlData
  
      INSERT INTO [dbo].[AGGREGATIONS]
          SELECT ID_AGGREGATION, CODE, CODE_LEVEL
            FROM OPENXML(@hdoc, '/Parent', 1) WITH (ID_AGGREGATION bigint '@ParentId', CODE nvarchar(20) '@ParentCodeId', CODE_LEVEL tinyint '@ParentLevelId')
  
  -- I don't know how to continue
  
      INSERT INTO [dbo].[AGGREGATION_CHILDS]
          SELECT ID_AGGREGATION, CODE, CODE_LEVEL
            FROM OPENXML(@hdoc, '/Parent/Child', 1) WITH (ID_AGGREGATION bigint 'ParentId', CODE nvarchar(20) '@ChildCodeId', CODE_LEVEL tinyint '@ChildLevelId', POSITION int  '@Position')
  </code></pre>
  
  <p>How can I loop through all children? I don't know how many children will be.</p>