Like this:
DECLARE @myDoc xml
SET @myDoc = '<AllowanceDeduction>
<Deduction Name=" ROW 1" Amount="-99" />
<allowance Name="ROW 2" Amount="88" />
</AllowanceDeduction>'
select
@myDoc.value('(/AllowanceDeduction/allowance/@Name)[1]', 'varchar(10)' ) as allowanceName,
@myDoc.value('(/AllowanceDeduction/allowance/@Amount)[1]', 'int' ) as allowanceAmount,
@myDoc.value('(/AllowanceDeduction/Deduction/@Name)[1]', 'varchar(10)' ) as deductionName,
@myDoc.value('(/AllowanceDeduction/Deduction/@Amount)[1]', 'int' ) as deductionAmount
Still, please remember:
1) I tmight be urgent for you, but we all are here to help, and we do this in out free time
2) Never ever publish yout email address like this, if you don't like spams...