Find the xml node value without knowing the position of an xml node from xml file using SQL query.

To find the xml node value without knowing the position of an xml node from xml file using SQL query.

Given 1 sample xml file which consist MyNode. Here I want to get the value of MyNode which may be placed in different position.

DECLARE @xml XML

SET @xml='<RootNode>

<SubNodes>

      <Node1>Node 1 </Node1>

      <Node2>Node 2 </Node2>

      <Node3>

            <MyNode> My node value </MyNode>

       </Node3>

 </SubNodes>

</RootNode>'

SELECT Nod.value('local-name(.)', 'varchar(50)') AS 'NodeName',

         Nod.value('.', 'varchar(50)') AS 'Node Value'

         FROM @xml.nodes('//*') AS xmlFile(Nod) WHERE Nod.value('local-name(.)', 'varchar(50)')='MyNode'

The output look like