How about using OPENXML?
DECLARE @idoc int, @myXml xml
SET @myXml = '<Author xmlns:xsi="..." xmlns:xsd="..." version="1">
<Benefits>
<Benefit Name="HospitalizationLimit" Value="500000"/>
<Benefit Name="MedicalConsultationLimit" Value="12000"/>
<Benefit Name="LifeInsuranceCoverage" Value="1000000"/>
<Benefit Name="MonthlyTelephone" Value ="5000"/>
<Benefit Name="DependentMemberCount" Value="5"/>
...
</Benefits>
</Author>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @myXml
SELECT *
FROM OPENXML ( @idoc, '/Author/Benefits', 2 )
WITH (
HospitilizationLimit int './Benefit[@Name="HospitalizationLimit"]/@Value',
MedicalConsultationLimit int './Benefit[@Name="MedicalConsultationLimit"]/@Value'
)
EXEC sp_xml_removedocument @idoc
I find OPENXML to be fairly useful when translating XML to table data.
Hope this helps!
-Graham