Using attribute value to select xml block

Site Discussion
1

Hi, in the below example, I need to fetch <Name> value for which the EcinRecordID=1042893. Please let me know how to achieve this

   <Finding EcinRecordID="1042893">
      <Name>Goal Length of Stay for the ORG</Name>
      <Selected Value="0" DisplayValue="No"/>
   </Finding>
   <Finding EcinRecordID="1042894">
      <Name>Goal Length of Stay for the GRG</Name>
      <Selected Value="1" DisplayValue="Yes"/>
      <NoteText>3 days</NoteText>
   </Finding>
</Findings>```
2

Like this:

DECLARE @x XML = 
'<Findings>
  <Finding EcinRecordID="1042893">
    <Name>Goal Length of Stay for the ORG</Name>
    <Selected Value="0" DisplayValue="No" />
  </Finding>
  <Finding EcinRecordID="1042894">
    <Name>Goal Length of Stay for the GRG</Name>
    <Selected Value="1" DisplayValue="Yes" />
    <NoteText>3 days</NoteText>
  </Finding>
</Findings>';

SELECT 
	c.value('./Name[1]','varchar(256)') AS [Name]
FROM
	@x.nodes('//Finding[@EcinRecordID="1042893"]')T(c)
1 Like