DECLARE @x xml

SET @X =
N'<toys>
    <myToy>
        <name>Train</name>
        <components>
            <part><name>engine</name><howMany>1</howMany></part>
            <part><name>coaches</name><howMany>10</howMany></part>
            <part><name>caboose</name><howMany>1</howMany></part>
            <part><name>tracks</name><howMany>125</howMany></part>
            <part><name>switches</name><howMany>8</howMany></part>
            <part><name>power controller</name><howMany>1</howMany></part>
        </components>
    </myToy>
    <myToy>
        <name>remote control car</name>
        <components>
            <part><name>remote control</name><howMany>1</howMany></part>
            <part><name>car</name><howMany>1</howMany></part>
            <part><name>batteries</name><howMany>8</howMany></part>
        </components>
    </myToy>
</toys>
';


SELECT
      [name] = T1.Toy.value('name[1]','VARCHAR(20)'),
      componentname = T2.Part.value('name[1]','VARCHAR(20)'),
      numberOfItems = T2.Part.value('howMany[1]','TINYINT')
FROM @x.nodes('/toys/myToy') T1(Toy)
CROSS APPLY 
T1.Toy.nodes('./components/part') T2(Part)


SELECT
      [name] = T2.Part.value('../../name[1]','VARCHAR(20)'),
      componentname = T2.Part.value('name[1]','VARCHAR(20)'),
      numberOfItems = T2.Part.value('howMany[1]','TINYINT')
FROM @x.nodes('/toys/myToy/components/part') T2(Part)