How to select all inside node tag in XML via SQL

Question

Required Output - Green mark data i want in my sql dataset

Can anyone help me please?

I would like to select all the nodes in xml and get its Key-Value and Value in my dataset.

I have tried but I can only get specific tag result.

I have numeric-entity, list-entity and string-entity in my xml and I want to get values from each tag.

So far I can get only one entity value but I don't know how I can write OR condition or something in query to get result from all the xmls tag.

This is my xml and the query I have written:

--Query
WITH XMLNAMESPACES(DEFAULT 'urn:guru.cincom.com-Hierarchy')
SELECT t.ID
    
      ,T0.Color.value('@name','nvarchar(max)') AS EntityName
      ,T0.Color.value('(key-value/text())[1]','nvarchar(max)') AS EntityValue  
FROM ConfigurationDnaItem t
CROSS APPLY (SELECT CAST(t.Value AS XML)) As A(AllNodes)
CROSS APPLY A.AllNodes.nodes('/hierarchy/nodes/numeric-entity[not(values/value/key-value[text() = "N/A" or text() = "NA" or text() = "Nil"])]') AS T0(Color)
where t.id = 'BA84B6C7-B6F7-4FC9-9DD8-8C9022513A67';

Xml

enter image description here

<?xml version="1.0" encoding="utf-16"?>
<hierarchy name="DGTrailerDimensionH" xmlns="urn:guru.cincom.com-Hierarchy">
  <nodes>
    <numeric-entity name="UnitLength">
      <key-value>14500</key-value>
      <characteristics>
        <characteristic name="Value" type="list"><![CDATA[14500]]></characteristic>
        <characteristic name="DGName" type="string"><![CDATA[Overall Length]]></characteristic>
      </characteristics>
      <extended-properties />
    </numeric-entity>
    <list-entity name="UnitHeightList">
      <values>
        <value>
          <key-value><![CDATA[4300]]></key-value>
          <characteristics>
            <characteristic name="Value" type="list"><![CDATA[4300]]></characteristic>
            <characteristic name="Description" type="string"><![CDATA[4300]]></characteristic>
            <characteristic name="DGName" type="string"><![CDATA[Overall Height]]></characteristic>
          </characteristics>
        </value>
      </values>
      <extended-properties />
    </list-entity>
    <numeric-entity name="CoamingHeight">
      <key-value>25</key-value>
      <characteristics>
        <characteristic name="Value" type="list"><![CDATA[25]]></characteristic>
        <characteristic name="DGName" type="string"><![CDATA[Coaming Height above main]]></characteristic>
      </characteristics>
      <extended-properties />
    </numeric-entity>
    <list-entity name="TurntableHeightList">
      <values>
        <value>
          <key-value><![CDATA[1270]]></key-value>
          <characteristics>
            <characteristic name="Value" type="list"><![CDATA[1270]]></characteristic>
            <characteristic name="Description" type="string"><![CDATA[1270]]></characteristic>
            <characteristic name="DGName" type="string"><![CDATA[Skidplate Height]]></characteristic>
          </characteristics>
        </value>
      </values>
      <extended-properties />
    </list-entity>
    <numeric-entity name="ApertureHeight">
      <key-value>2725</key-value>
      <characteristics>
        <characteristic name="Value" type="list"><![CDATA[2725]]></characteristic>
        <characteristic name="DGName" type="string"><![CDATA[Aperture Height]]></characteristic>
      </characteristics>
      <extended-properties />
    </numeric-entity>
    <numeric-entity name="ApertureLength">
      <key-value>14200</key-value>
      <characteristics>
        <characteristic name="Value" type="list"><![CDATA[14200]]></characteristic>
        <characteristic name="DGName" type="string"><![CDATA[Aperture Length]]></characteristic>
      </characteristics>
      <extended-properties />
    </numeric-entity>
    <numeric-entity name="FifthWheelLocationNum">
      <key-value>0</key-value>
      <characteristics>
        <characteristic name="Value" type="list"><![CDATA[0]]></characteristic>
        <characteristic name="DGName" type="string"><![CDATA[Turntable Location]]></characteristic>
      </characteristics>
      <extended-properties />
    </numeric-entity>
    <numeric-entity name="SuspensionLocation">
      <key-value>9350</key-value>
      <characteristics>
        <characteristic name="Value" type="list"><![CDATA[9350]]></characteristic>
        <characteristic name="DGName" type="string"><![CDATA[Suspension Location]]></characteristic>
      </characteristics>
      <extended-properties />
    </numeric-entity>
    <numeric-entity name="SuspensionSpreadNum">
      <key-value>2700</key-value>
      <characteristics>
        <characteristic name="Value" type="list"><![CDATA[2700]]></characteristic>
        <characteristic name="DGName" type="string"><![CDATA[Suspension spread]]></characteristic>
      </characteristics>
      <extended-properties />
    </numeric-entity>
    <string-entity name="KingPinLocationStr">
      <key-value><![CDATA[1550]]></key-value>
      <characteristics>
        <characteristic name="Value" type="list"><![CDATA[1550]]></characteristic>
        <characteristic name="DGName" type="string"><![CDATA[King Pin Position]]></characteristic>
      </characteristics>
      <extended-properties />
    </string-entity>
    <numeric-entity name="RearApertureHeight">
      <key-value>0</key-value>
      <characteristics>
        <characteristic name="Value" type="list"><![CDATA[0]]></characteristic>
        <characteristic name="DGName" type="string"><![CDATA[Rear Aperture Height]]></characteristic>
      </characteristics>
      <extended-properties />
    </numeric-entity>
    <numeric-entity name="DropDeckHeightNum">
      <key-value>0</key-value>
      <characteristics>
        <characteristic name="Value" type="list"><![CDATA[0]]></characteristic>
        <characteristic name="DGName" type="string"><![CDATA[Drop Deck Height]]></characteristic>
      </characteristics>
      <extended-properties />
    </numeric-entity>
  </nodes>
</hierarchy>


  [1]: Out required - Green mark i want in data set.