Export data from dynamic XML hierarchy in SQL Server

I want to get the lowest value in the hierarchy in a dynamic XML file.

I try to write a dynamic expression for reading from XML file:

patdata.value('(*[local-name() = sql:column("DataMng.Root")]/*[local-name() = sql:column("textMng.FieldId")]/text())[1]', 'nvarchar(max)')

DataMng.Root= 'rc0036'
FieldId='s4356/L4356/F4357'

but I am getting **NULL **although there is value on this XML file.

When I write the following expression, I get the output I want:

patdata.value('(*[local-name() = sql:column("DataMng.Root")]/s4356/L4356/F4357/text())[1]', 'nvarchar(max)')

there is the XML file:

<rc0036>
 <F2739>9893</F2739>
 <F2740>12</F2740>
 <F2741>ד"ר נאג'ד יאסין</F2741>
 <F0242>16/04/2024</F0242>
 <F5453>16/04/2024</F5453>
 <F4301>16/04/2024</F4301>
 <F5454>מרינה</F5454>
 <F1209>מרינה</F1209>
 <F1903>מעקב זקיקים</F1903>
 <F0984>מרינה</F0984>
 <F0308>01/04/2024</F0308>
 <F0415>וגינלי</F0415>
 <F2966>טיפול</F2966>
 <s4356>
  <L4356 line="1">
   <F0418>16/04/2024</F0418>
   <F0462>16</F0462>
   <F0071>100</F0071>
   <F0987>בינוני</F0987>
   <F4357>5</F4357>
   <F4357>6</F4357>
   <F1617>מרינה</F1617>
   <F1013/>
  </L4356>
 </s4356>
</rc0036>

A minimal reproducible example is not provided. So, I am shooting from the hip.

Here is a couple of ways of doing it.

First method is a hack.

The 2nd method is much more reliable. It is using XQuery FLWOR expression.

SQL

DECLARE @xml XML = 
N'<rc0036>
 <F2739>9893</F2739>
 <F2740>12</F2740>
 <F0242>16/04/2024</F0242>
 <F5453>16/04/2024</F5453>
 <F4301>16/04/2024</F4301>
 <F5454>מרינה</F5454>
 <F1209>מרינה</F1209>
 <F1903>מעקב זקיקים</F1903>
 <F0984>מרינה</F0984>
 <F0308>01/04/2024</F0308>
 <F0415>וגינלי</F0415>
 <F2966>טיפול</F2966>
 <s4356>
  <L4356 line="1">
   <F0418>16/04/2024</F0418>
   <F0462>16</F0462>
   <F0071>100</F0071>
   <F0987>בינוני</F0987>
   <F4357>5</F4357>
   <F4357>6</F4357>
   <F1617>מרינה</F1617>
   <F1013/>
  </L4356>
 </s4356>
</rc0036>';

SELECT @xml.value('min(//*)', 'INT') AS result;

SELECT @xml.query('
    for $x in min(//*[xs:int(text()[1]) instance of xs:int])
    return $x
').value('.', 'INT') AS result;

SELECT REPLACE(@xml.query('data(/rc0036/s4356/L4356/F4357/text())')
    .value('.', 'VARCHAR(20)'), SPACE(1),',') AS result;

Output #1

Output #2