I am looking to insert records from xml into two different tables. For example

<Root>
    <A>
        <AValue>1</AValue>
        <Children>
            <B>
                <BValue>2</BValue>
            </B>
        </Children>
    </A>
</Root>

Would insert a record into table A

AID  AValue
#    1

also insert a record into table B

BID AID                    BValue
#   #(Same as AID Above)   2

I have this

DECLARE @idoc INT
DECLARE @doc NVARCHAR(MAX)

SET @doc = '
<Root>
    <A>
        <AValue>1</AValue>
        <Children>
            <B>
                <BValue>2</BValue>
            </B>
        </Children>
    </A>
</Root>
'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

CREATE TABLE #A
    (
      AID INT IDENTITY(1, 1) ,
      AValue INT
    )

INSERT  INTO #A
        SELECT  *
        FROM    OPENXML (@idoc, '/Root/A',2)
                WITH (AValue INT
                      )  

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

Thanks!