SQL Server 插入父/子xml数据

<parents>
 <parent>
    <id>1</id>
    <name>Parent 1</name>
   <children>
     <child>
        <id>1</id>
        <name>Child 1</name>
      </child>
     <child>
        <id>2</id>
        <name>Child 2</name>
      </child>
    </children>
  </parent>
 <parent>
    <id>2</id>
    <name>Parent 2</name>
   <children>
     <child>
        <id>3</id>
        <name>Child 3</name>
      </child>
    </children>
  </parent>
</parents>

DECLARE @xmlData XML
SET @xmlData = '<parents>
 <parent>
    <id>1</id>
    <name>Parent 1</name>
   <children>
     <child>
        <id>1</id>
        <name>Child 1</name>
      </child>
     <child>
        <id>2</id>
        <name>Child 2</name>
      </child>
    </children>
  </parent>
 <parent>
    <id>2</id>
    <name>Parent 2</name>
   <children>
     <child>
        <id>3</id>
        <name>Child 3</name>
      </child>
    </children>
  </parent>
</parents>'

INSERT INTO ParentTable (ParentID, ParentName)
SELECT
    T.c.value('id[1]', 'INT') AS ParentID,
    T.c.value('name[1]', 'VARCHAR(50)') AS ParentName
FROM @xmlData.nodes('/parents/parent') T(c)

INSERT INTO ChildTable (ChildID, ChildName, ParentID)
SELECT
    T.c.value('id[1]', 'INT') AS ChildID,
    T.c.value('name[1]', 'VARCHAR(50)') AS ChildName,
    P.ParentID
FROM @xmlData.nodes('/parents/parent/children/child') T(c)
    JOIN ParentTable P ON T.c.value('id[1]', 'INT') = P.ParentID