Read XML in SQL upto N level

DECLARE @foo XML

SELECT @foo = N'
<inv_feed>
  <item>
    <code>kba6_34</code>
    <inv_detail>
      <loc>Delhi</loc>
      <bucket>
        <name>Pre-Grn</name>
        <qty>20</qty>
      </bucket>
      <bucket>
        <name>Buffer Stock</name>
        <qty>10</qty>
      </bucket>
      <bucket>
        <name>Consignment</name>
        <qty>50</qty>
      </bucket>
      <bucket>
        <name>Bulk stock</name>
        <qty>10</qty>
      </bucket>
    </inv_detail>
    <inv_detail>
      <loc>Surat</loc>
      <bucket>
        <name>Pre-Grn</name>
        <qty>100</qty>
      </bucket>
      <bucket>
        <name>Buffer Stock</name>
        <qty>80</qty>
      </bucket>
      <bucket>
        <name>Consignment</name>
        <qty>70</qty>
      </bucket>
      <bucket>
        <name>Bulk stock</name>
        <qty>10</qty>
      </bucket>
    </inv_detail>
 
  </item>
  <item>
    <code>kcr3765_32</code>
    <inv_detail>
      <loc>Delhi</loc>
      <bucket>
        <name>Pre-Grn</name>
        <qty>20</qty>
      </bucket>
      <bucket>
        <name>Buffer Stock</name>
        <qty>40</qty>
      </bucket>
      <bucket>
        <name>Consignment</name>
        <qty>60</qty>
      </bucket>
      <bucket>
        <name>Bulk stock</name>
        <qty>10</qty>
      </bucket>
    </inv_detail>
    <inv_detail>
      <loc>Surat</loc>
      <bucket>
        <name>Pre-Grn</name>
        <qty>10</qty>
      </bucket>
      <bucket>
        <name>Buffer Stock</name>
        <qty>20</qty>
      </bucket>
      <bucket>
        <name>Consignment</name>
        <qty>40</qty>
      </bucket>
      <bucket>
        <name>Bulk stock</name>
        <qty>10</qty>
      </bucket>
    </inv_detail>

  </item>
</inv_feed>


'

SELECT
   CAST(CAST(x.item.query('data(code)')   AS  varchar(30))  AS varchar(30)),
  CAST(CAST(y.item.query('data(loc)')  AS  varchar(1000)) AS varchar(1000)),
    CAST(LEFT(CAST(z.item.query('data(name)') AS char(25)), 100) AS varchar(100)),
   CAST(z.item.query('data(qty)') AS varchar(25))
FROM
    @foo.nodes('/inv_feed/item') x(item)
    CROSS APPLY  
     x.item.nodes('./inv_detail') AS y(item)
  CROSS APPLY
     y.item.nodes('./bucket') AS z(item)
 
 
--SELECT
--    CAST(CAST(x.item.query('data(fish)') AS varchar(30)) AS float),
--    CAST(LEFT(CAST(x.item.query('data(bicycle)') AS char(25)), 10) AS smalldatetime),
--    CAST(x.item.query('data(foo)') AS varchar(25))
--FROM
--    @foo.nodes('harrys/harry') x(item)

--SELECT
--    CAST(CAST(y.item.query('data(fish)') AS varchar(30)) AS float),
--    CAST(LEFT(CAST(y.item.query('data(bicycle)') AS char(25)), 10) AS smalldatetime),
--    CAST(y.item.query('data(foo)') AS varchar(25))
--FROM
--    @foo.nodes('/harrys') x(item)
--    CROSS APPLY
--    x.item.nodes('./harry') AS y(item)