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

DECLARE @xml xml = '<ProductID ProductNo="100086861"></ProductID>';
SELECT  @xml.value('/ProductID[1]/@ProductNo', 'int');