Reading XML in table
I have a stored procedure which is passed XML as a parameter and then I have to read that xML and do some updates. The format of the XML is 

    <Requests>
        <RequestReference>R12345</RequestReference>
        <Inventory InventoryId="1" InventoryName="I1">
            <RequestCodes>
                <Code>AAA</Code>
            </RequestCodes>
        </Inventory>
        <Inventory InventoryId="2" InventoryName="I2">
            <RequestCodes>
                <Code>BBB</Code>
                <Code>CCC</Code>
            </RequestCodes>
        </Inventory>
        <Inventory InventoryId="3" InventoryName="I3">
            <RequestCodes>
                <Code>DDD</Code>
                <Code>EEE</Code>
                <Code>FFF</Code>
            </RequestCodes>
        </Inventory>
    </Requests>


Now I need to read the XML in a table like

    RequestReference	InventoryId	InventoryName	        Code
    R12345	                  1	          I1	        AAA
    R12345	                  2           I2	        BBB
    R12345	                  2	          I2	        CCC
    R12345	                  3	          I3	        DDD
    R12345	                  3	          I3	        EEE
    R12345	                  3	          I3	        FFF

I am trying something like 

    SELECT 
    	T.Item.value('@InventoryId', 'VARCHAR(3)')	AS InventoryId,
        T.Item.value('@InventoryName', 'VARCHAR(3)')	AS InventoryName,
        T.Item.value('RequestCodes[1]/Code[1]', 'VARCHAR(5)')	AS Code
    FROM   
       @xmlDoc.nodes('Requests/Inventory') 
       AS T(Item)

This seems to be working but not producing the desired results completely.
Reading XML in table
===

I have a stored procedure which is passed XML as a parameter and then I have to read that xML and do some updates. The format of the XML is 

    <Requests>
        <RequestReference>R12345</RequestReference>
        <Inventory InventoryId="1" InventoryName="I1">
            <RequestCodes>
                <Code>AAA</Code>
            </RequestCodes>
        </Inventory>
        <Inventory InventoryId="2" InventoryName="I2">
            <RequestCodes>
                <Code>BBB</Code>
                <Code>CCC</Code>
            </RequestCodes>
        </Inventory>
        <Inventory InventoryId="3" InventoryName="I3">
            <RequestCodes>
                <Code>DDD</Code>
                <Code>EEE</Code>
                <Code>FFF</Code>
            </RequestCodes>
        </Inventory>
    </Requests>


Now I need to read the XML in a table like

    RequestReference	InventoryId	InventoryName	        Code
    R12345	                  1	          I1	        AAA
    R12345	                  2           I2	        BBB
    R12345	                  2	          I2	        CCC
    R12345	                  3	          I3	        DDD
    R12345	                  3	          I3	        EEE
    R12345	                  3	          I3	        FFF

I am trying something like 

    SELECT 
    	T.Item.value('@InventoryId', 'VARCHAR(3)')	AS InventoryId,
        T.Item.value('@InventoryName', 'VARCHAR(3)')	AS InventoryName,
        T.Item.value('RequestCodes[1]/Code[1]', 'VARCHAR(5)')	AS Code
    FROM   
       @xmlDoc.nodes('Requests/Inventory') 
       AS T(Item)

This seems to be working but not producing the desired results completely.