Below is the XML data present in column xmldata.

<Inquiry>
  <MemberCode>56</MemberCode>
  <MemberName>COMP</MemberName>
  <Date>16</Date>
</Inquiry>```

How to use  XMLGET to extract only values from inner tag<Inquiry>

SELECT TOP 100
KEY,
GET(b.VALUE,’@’)::STRING as name, –returns Inquiry
b.VALUE:”$”[1].”$”[1],–returns null
GET(XMLGET(b.VALUE, GET(b.VALUE,’@’)::STRING), ‘$’) as value1,–returns null
— XMLGET(VALUE,GET(b.VALUE,’@’)::STRING):”$” as value,–returns null
b.value as children,– returns full xmldata
b.this

        FROM
            (SELECT  
                KEY,
                VALUE
                ,this
            from
                 FULL_XML,
                LATERAL FLATTEN(XMLGET(XMLGET(XML_DATA,'Report'),'Inquiries'):"$") )b ;```

expected output for value column is
56COMP16