MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE FUNCTION `ExVal`(ky text, xpath text) RETURNS text DETERMINISTIC
BEGIN
   RETURN CAST(ExtractValue(ky, xpath) AS CHAR);
END;
CREATE TABLE s1 (attributes TEXT);
INSERT INTO s1 VALUES
    ('<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>');
SELECT
    r.n AS r_n,
    v.n AS v_n,
    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;


r_n
v_n
role
value


1
3
Role_111


1
2
Role_111


1
1
Role_111
07771

2
3
Role_13


2
2
Role_13


2
1
Role_13
07771

3
3
Role_16


3
2
Role_16
07771

3
1
Role_16
07770

4
3
Role_37


4
2
Role_37


4
1
Role_37
07771

5
3
Role_5


5
2
Role_5


5
1
Role_5
07771

6
3
isValid


6
2
isValid


6
1
isValid



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%'
ORDER BY CAST(SUBSTRING(`role`, 6) AS UNSIGNED), `value`;


role
value


Role_5
07771

Role_13
07771

Role_16
07770

Role_16
07771

Role_37
07771

Role_111
07771

r_n	v_n	role	value
1	3	Role_111
1	2	Role_111
1	1	Role_111	07771
2	3	Role_13
2	2	Role_13
2	1	Role_13	07771
3	3	Role_16
3	2	Role_16	07771
3	1	Role_16	07770
4	3	Role_37
4	2	Role_37
4	1	Role_37	07771
5	3	Role_5
5	2	Role_5
5	1	Role_5	07771
6	3	isValid
6	2	isValid
6	1	isValid