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
<?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.
CodePudding user response：
Please try the following solution. It is following the same minimal reproducible example paradigm. You copy it to SSMS as-is, run it, and it is working.
Though I don't understand the XPath expression predicate:
[not(values/value/key-value[text() = ("N/A","NA","Nil")])]
Please clarify what you are trying to filter out.
SQL
-- DDL and sample data population, start
DECLARE @tbl TABLE (ID UNIQUEIDENTIFIER PRIMARY KEY, [value] NVARCHAR(MAX));
INSERT INTO @tbl (ID, value) VALUES
('BA84B6C7-B6F7-4FC9-9DD8-8C9022513A67'
, N'<?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>');
-- DDL and sample data population, end
WITH XMLNAMESPACES(DEFAULT 'urn:guru.cincom.com-Hierarchy')
SELECT t.ID
, p.value('@name','nvarchar(max)') AS EntityName
, p.value('(.//key-value/text())[1]','nvarchar(max)') AS EntityValue
, c.value('(characteristic[@name="Value"]/text())[1]','nvarchar(max)') AS char_Value
, c.value('(characteristic[@name="DGName"]/text())[1]','nvarchar(max)') AS char_GName
, c.value('(characteristic[@name="Description"]/text())[1]','nvarchar(max)') AS char_desc
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST(t.Value AS XML)) AS A(AllNodes)
CROSS APPLY A.AllNodes.nodes('/hierarchy/nodes/*[not(values/value/key-value[text() = ("N/A","NA","Nil")])]') AS t0(p)
CROSS APPLY t0.p.nodes('.//characteristics') AS t1(c)
WHERE t.id = 'BA84B6C7-B6F7-4FC9-9DD8-8C9022513A67';
Output
-------------------------------------- ----------------------- ------------- ------------ --------------------------- -----------
| ID | EntityName | EntityValue | char_Value | char_GName | char_desc |
-------------------------------------- ----------------------- ------------- ------------ --------------------------- -----------
| BA84B6C7-B6F7-4FC9-9DD8-8C9022513A67 | UnitLength | 14500 | 14500 | Overall Length | NULL |
| BA84B6C7-B6F7-4FC9-9DD8-8C9022513A67 | UnitHeightList | 4300 | 4300 | Overall Height | 4300 |
| BA84B6C7-B6F7-4FC9-9DD8-8C9022513A67 | CoamingHeight | 25 | 25 | Coaming Height above main | NULL |
| BA84B6C7-B6F7-4FC9-9DD8-8C9022513A67 | TurntableHeightList | 1270 | 1270 | Skidplate Height | 1270 |
| BA84B6C7-B6F7-4FC9-9DD8-8C9022513A67 | ApertureHeight | 2725 | 2725 | Aperture Height | NULL |
| BA84B6C7-B6F7-4FC9-9DD8-8C9022513A67 | ApertureLength | 14200 | 14200 | Aperture Length | NULL |
| BA84B6C7-B6F7-4FC9-9DD8-8C9022513A67 | FifthWheelLocationNum | 0 | 0 | Turntable Location | NULL |
| BA84B6C7-B6F7-4FC9-9DD8-8C9022513A67 | SuspensionLocation | 9350 | 9350 | Suspension Location | NULL |
| BA84B6C7-B6F7-4FC9-9DD8-8C9022513A67 | SuspensionSpreadNum | 2700 | 2700 | Suspension spread | NULL |
| BA84B6C7-B6F7-4FC9-9DD8-8C9022513A67 | KingPinLocationStr | 1550 | 1550 | King Pin Position | NULL |
| BA84B6C7-B6F7-4FC9-9DD8-8C9022513A67 | RearApertureHeight | 0 | 0 | Rear Aperture Height | NULL |
| BA84B6C7-B6F7-4FC9-9DD8-8C9022513A67 | DropDeckHeightNum | 0 | 0 | Drop Deck Height | NULL |
-------------------------------------- ----------------------- ------------- ------------ --------------------------- -----------