title: Insert Into Two SQL Tables From XML Maintaining Relationship
body: <p>I am looking to insert records from xml into two different tables.
  For example</p>
  
  <pre><code>&lt;Root&gt;
      &lt;A&gt;
          &lt;AValue&gt;value&lt;/AValue&gt;
          &lt;Children&gt;
              &lt;B&gt;
                  &lt;BValue&gt;2&lt;/BValue&gt;
              &lt;/B&gt;
          &lt;/Children&gt;
      &lt;/A&gt;
      &lt;A&gt;
          &lt;AValue&gt;value&lt;/AValue&gt;
          &lt;Children&gt;
              &lt;B&gt;
                  &lt;BValue&gt;3&lt;/BValue&gt;
              &lt;/B&gt;
          &lt;/Children&gt;
      &lt;/A&gt;
  &lt;/Root&gt;
  </code></pre>
  
  <p>Would insert a record into table A
  Assuming the identity is starting at 1 </p>
  
  <pre><code>AID  AValue
  1    value
  2    value
  </code></pre>
  
  <p>also insert a record into table B</p>
  
  <pre><code>BID AID  BValue
  1   1    2
  2   2    3
  </code></pre>
  
  <p>I have this</p>
  
  <pre><code>DECLARE @idoc INT
  DECLARE @doc NVARCHAR(MAX)
  
  SET @doc = '
  &lt;Root&gt;
      &lt;A&gt;
          &lt;AValue&gt;value&lt;/AValue&gt;
          &lt;Children&gt;
              &lt;B&gt;
                  &lt;BValue&gt;2&lt;/BValue&gt;
              &lt;/B&gt;
          &lt;/Children&gt;
      &lt;/A&gt;
       &lt;A&gt;
          &lt;AValue&gt;value&lt;/AValue&gt;
          &lt;Children&gt;
              &lt;B&gt;
                  &lt;BValue&gt;3&lt;/BValue&gt;
              &lt;/B&gt;
          &lt;/Children&gt;
      &lt;/A&gt;
  &lt;/Root&gt;
  '
  
  EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
  
  CREATE TABLE #A
      (
        AID INT IDENTITY(1, 1) ,
        AValue varchar(100)
      )
  
  INSERT  INTO #A
          SELECT  *
          FROM    OPENXML (@idoc, '/Root/A',2)
                  WITH (AValue varchar(100)
                        )  
  
  CREATE TABLE #B
      (
        BID INT IDENTITY(1, 1) ,
        AID INT ,
        BValue INT
      )
  
  INSERT  INTO #B
          SELECT  *
          FROM    OPENXML (@idoc, '/Root/A/Children/B',2)
                  WITH (
                  AID INT,
                  BValue INT
                        )
  
  SELECT  *
  FROM    #A
  SELECT  *
  FROM    #B
  
  DROP TABLE #A
  DROP TABLE #B
  exec sp_xml_removedocument @idoc
  </code></pre>
  
  <p>Thanks!</p>