<ingredient quantity="1" unit="ounce">, <ingredient quantity="1" unit="ounce">....

declare @tbl as table(value xml)
insert into @tbl
select'<drink-recipe name="Fuzzy Navel">
<ingredients>
<ingredient quantity="1" unit="ounce">
Vodka
</ingredient>
<ingredient quantity="1" unit="ounce">
Peach schnapps
</ingredient>
<ingredient quantity="4" unit="ounce">
Orange juice
</ingredient>
</ingredients>
<preparation>
<step>
Pour ingredients into a highball glass almost filled with ice.
</step>
<step>
Stir.
</step>
</preparation>
</drink-recipe>
'

select x.i.value('@quantity','varchar(100)')as qunatity,
x.i.value('@unit','varchar(100)')as unit
from @tbl
cross apply value.nodes('/drink-recipe/ingredients/ingredient')as x(i)

quote:

---

Originally posted by CSK

Idera
Thank you for the reply. and sorry for the late reply. With this above query seems okay for me.

Thank you once again
-- Krishna

---