mysql query to extractvalue from xml with wild search and iterate through the object to get the key & value

Question

Example:

<Attributes>
   <Map>
     <entry key="Role_111">
       <value>
         <List>
           <String>07771</String>
         </List>
       </value>
     </entry>
     <entry key="Role_13">
       <value>
         <List>
           <String>07771</String>
         </List>
       </value>
     </entry>
     <entry key="Role_16">
       <value>
         <List>
           <String>07770</String>
           <String>07771</String>
         </List>
       </value>
     </entry>
     <entry key="Role_37">
       <value>
         <List>
           <String>07771</String>
         </List>
       </value>
     </entry>
     <entry key="Role_5">
       <value>
         <List>
           <String>07771</String>
         </List>
       </value>
     </entry>
     <entry key="isValid" value="true"/>
     <entry key="usercode" value="001"/>
   </Map>
 </Attributes>

In this xml, I have extract value of all roles for the user and print them to get the key and value separately. They should be in separate column and diffrent row entry for each role.

I tried few ways to get the value but unable to do so, could anyone suggest how to get the values

I am able to get the value if I give the exact role but not all the roles and the value,

EXTRACTVALUE(s1.attributes, '/Attributes/Map/entry[@key="Role_111"]/value/List/String') as role

EXTRACTVALUE(s1.attributes, '/Attributes/Map/entry/descendant::*') as role

Answer 1

This solution is taken from this: http://sql-debug.blogspot.com/2012/05/extractvalue-only-constant-xpath.html

If you try to build up the xpath expression dynamically, MySQL throws an error:

Error Code: 1105. Only constant XPATH queries are supported

To work around this you can wrap ExtractValue() in a UDF:

DELIMITER $$

CREATE FUNCTION `ExVal`(ky text, xpath text) RETURNS text DETERMINISTIC
BEGIN
   RETURN CAST(ExtractValue(ky, xpath) AS CHAR);
END $$

DELIMITER ;

This query is very inefficient but it works:

SELECT
    ExVal(s1.attributes, CONCAT('/Attributes/Map/entry[', r.n, ']/@key')) AS `role`,
    ExVal(s1.attributes, CONCAT('/Attributes/Map/entry[', r.n, ']/value/List/String[', v.n, ']')) AS `value`
FROM s1
JOIN (
    SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
) r
JOIN (
    SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3
) v
HAVING `value` > '' AND `role` LIKE 'Role%';

Output:

role	value
Role_111	07771
Role_13	07771
Role_16	07771
Role_16	07770
Role_37	07771
Role_5	07771

The first derived table (r) is used for the indices for the <entry> elements, and the second derived table (v) for the <String> elements.

Here's a db<>fiddle