Mike, this should do the trick. OPENXML method first followed by the .nodes() method.

declare @xml xml

set @xml = '<Products>

<Product ID="123" Name="Male Bicycle" Amount="1">

<Product ID="988" Name="Male Frame" Amount="1"/>

<Product ID="501" Name="Wheel" Amount="2">

<Product ID="450" Name="Spoke" Amount="50"/>

<Product ID="490" Name="Rim" Amount="1"/>

</Product>

</Product>

<Product ID="234" Name="Female Bicycle" Amount="1">

<Product ID="998" Name="Female Frame" Amount="1"/>

<Product ID="501" Name="Wheel" Amount="2">

<Product ID="450" Name="Spoke" Amount="50"/>

<Product ID="490" Name="Rim" Amount="1"/>

</Product>

</Product>

</Products>'

declare @Products table ( ProductID int, ProductName varchar(50) )

declare @BOM table ( ParentProductID int, ComponentProductID int, Amount int )

declare @idoc int

exec sp_xml_preparedocument @idoc output, @xml

insert into @Products ( ProductID, ProductName )

select distinct

ID

, Name

from OPENXML ( @idoc, '//Product', 2 )

with (

ID int '@ID'

, Name varchar(50) '@Name'

)

insert into @BOM ( ParentProductID, ComponentProductID, Amount )

select

ParentProductID

, ComponentProductID

, Amount

from OPENXML ( @idoc, '//Product', 2 )

with (

ParentProductID int '../@ID'

, ComponentProductID int '@ID'

, Amount int '@Amount'

)

exec sp_xml_removedocument @idoc

select * from @Products

select * from @BOM

delete @Products

delete @BOM

insert into @Products ( ProductID, ProductName )

select distinct

node.value ( '(@ID)[1]', 'int' )

, node.value ( '(@Name)[1]', 'varchar(50)' )

from @xml.nodes ( '//Product' ) p(node)

insert into @BOM ( ParentProductID, ComponentProductID, Amount )

select

node.value ( '(../@ID)[1]', 'int' )

, node.value ( '(@ID)[1]', 'int' )

, node.value ( '(@Amount)[1]', 'int' )

from @xml.nodes ( '//Product' ) p(node)

select * from @Products

select * from @BOM