Try this. Worked for me
DECLARE @xmlVal as XML
SET @xmlVal ='<gain><defaultstate>
<state>FL</state>
<state>CO</state>
</defaultstate>
<id>kkkkk</id></gain>'
SELECT
Gain.DefaultState.value('../ID[1]','nvarchar(50)') Id,
Gain.DefaultState.value('state[1]','nvarchar(50)') State1,
Gain.DefaultState.value('State[1]','nvarchar(50)') State2
FROM
@xmlVal.nodes('Gain/DefaultState') as Gain(DefaultState)
Hope this helps , If yes then plz accept and vote the answer. Any queries / questions on this are always welcome.
Thanks & Regards
RDBurmon.Sr.Software Engineer