By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE ConfigurationDnaItem(ID uniqueidentifier, Value xml);
INSERT ConfigurationDnaItem(ID, Value)
VALUES('1D09BFCB-AE0A-4346-835D-4BBCF2BBB503', N'<?xml version="1.0" encoding="utf-16"?>
<hierarchy name="DGPaintH" xmlns="urn:guru.cincom.com-Hierarchy">
<nodes>
<list-entity name="HubPaintDetail">
<values>
<value>
<key-value><![CDATA[AFL.9H1B]]></key-value>
<characteristics>
<characteristic name="Value" type="list"><![CDATA[AFL.9H1B]]></characteristic>
<characteristic name="Description" type="string"><![CDATA[Fleet Red(PPG Code: AFL.9H1B)]]></characteristic>
<characteristic name="JDE_ITEM_NUMBER" type="string"><![CDATA[]]></characteristic>
<characteristic name="PRICE_EX_FACTORY" type="numeric">0.00</characteristic>
<characteristic name="PRICE_RETAIL" type="numeric">0.00</characteristic>
<characteristic name="PRICE_DIFFERENTIAL" type="numeric">0.00</characteristic>
<characteristic name="OPTION_WEIGHT" type="numeric">0.00</characteristic>
<characteristic name="WEIGHT_DIFFERENTIAL" type="numeric">0.00</characteristic>
<characteristic name="QUANTITY" type="numeric">0</characteristic>
<characteristic name="Name" type="string"><![CDATA[]]></characteristic>
<characteristic name="CatCode1" type="string"><![CDATA[02]]></characteristic>
<characteristic name="CatCode2" type="string"><![CDATA[33]]></characteristic>
<characteristic name="CatCode3" type="string"><![CDATA[DP]]></characteristic>
</characteristics>
</value>
</values>
<extended-properties>
<extended-property name="ALTNAME" type="string"><![CDATA[Hubs]]></extended-property>
<extended-property name="CatCode1" type="string"><![CDATA[02]]></extended-property>
<extended-property name="CatCode2" type="string"><![CDATA[33]]></extended-property>
<extended-property name="CatCode3" type="string"><![CDATA[DP]]></extended-property>
</extended-properties>
</list-entity>
<list-entity name="DoorPaintDetail">
<values>
1 rows affected
WITH XMLNAMESPACES(DEFAULT 'urn:guru.cincom.com-Hierarchy')
SELECT t.ID
,T0.Color.value('(extended-properties/extended-property[@name = "ALTNAME"]/text())[1]', 'nvarchar(max)') AS TagName
,T0.Color.value('@name','nvarchar(max)') AS EntityName
,T0.Color.value('(values/value/key-value/text())[1]','nvarchar(max)') AS TagValue
FROM ConfigurationDnaItem t
CROSS APPLY (SELECT CAST(t.Value AS XML)) As A(AllNodes)
CROSS APPLY A.AllNodes.nodes('/hierarchy/nodes/list-entity[not(values/value/key-value/text() = "N/A")]') AS T0(Color)
where id='1D09BFCB-AE0A-4346-835D-4BBCF2BBB503'
| ID | TagName | EntityName | TagValue |
|---|---|---|---|
| 1d09bfcb-ae0a-4346-835d-4bbcf2bbb503 | Hubs | HubPaintDetail | AFL.9H1B |
| 1d09bfcb-ae0a-4346-835d-4bbcf2bbb503 | Door | DoorPaintDetail | AFL.DTT6 |
| 1d09bfcb-ae0a-4346-835d-4bbcf2bbb503 | Under | UnderPaintDetail | AFL.9H1B |
| 1d09bfcb-ae0a-4346-835d-4bbcf2bbb503 | Boxes | PaintFleetMasterBoxesList | AFL.9H1B |