Personally if I was working with an xml structure like that and had to populate a number of tables, then I would consider shredding it to a single temp table first and then work with data from the temp table. Shredding xml is expensive so you should minimise how often you query the xml.

Here is an example query that takes your example xml and then creates a single table from it. I haven't extracted all of the columns, but you should be fill in the gaps yourself by looking at the code. The main thing to remember is that for each repeating xml node i.e. <FirstChild> and <SecondChild> etc, you'll need a CROSS APPLY. You could adapt this to put the data into a temp table first and then insert/update your other tables from the temp table.

hope it helps..

SELECT mas.c.value('(MasterNo/text())[1]', 'varchar(20)') AS MasterNo

, mas.c.value('(Id/text())[1]', 'varchar(20)') AS Id

, fir.c.value('(FirstChildNo/text())[1]', 'varchar(20)') AS FirstChildNo

, fir.c.value('(ClaimNO/text())[1]', 'varchar(20)') AS ClaimNO

, sec.c.value('(Code/text())[1]', 'int') AS Code

, sec.c.value('(Demo/text())[1]', 'int') AS Demo

, thi.c.value('(ID/text())[1]', 'int') AS ThirdChild_ID

, thi.c.value('(ChildNo/text())[1]', 'varchar(20)') AS ThirdChild_ChildNo

, fort.c.value('(Quantity/text())[1]', 'int') AS FourthChild_Quantity

, fort.c.value('(ID/text())[1]', 'int') AS FourthChild_ID

, frtno.c.value('(Number/text())[1]', 'int') AS FourthChildNo_Number

, frtno.c.value('(Ref/text())[1]', 'varchar(20)') AS FourthChildNo_Ref

FROM @xml.nodes('/Master') mas(c)

CROSS APPLY mas.c.nodes('FirstChild') fir(c)

CROSS APPLY mas.c.nodes('SecondChild') sec(c)

CROSS APPLY mas.c.nodes('ThirdChild') thi(c)

CROSS APPLY thi.c.nodes('FourthChild') fort(c)

CROSS APPLY fort.c.nodes('FourthChildNo') frtno(c)