Retrieving XML elements from XMLType Oracle

Question

Can someone help me with retrieving the data from XMLType column in Oracle?

drop table xml_analysis;
create table xml_analysis(id number,soft_attributes XMLType);
create table xml_softattributes(id number,soft_attributes varchar2(200));
INSERT INTO xml_analysis VALUES 
   (       1, XMLType(
              '<softattributes> 
               <attr1>ABC</attr1>
               <attr2>XYZ</attr2> 
               <attr3>PQR</attr3> 
               </softattributes>
                '));
   insert into xml_softattributes values(1,'attr1');
   insert into xml_softattributes values(1,'attr2');
   insert into xml_softattributes values(1,'attr3');

1. Table xml_analysis contains the xmltype column ,whose attributes i don't know
2. Table xml_softattributes contains list of softattributes (not the xpath),which are present in the xmltype column of the xml_analysis table
3. Tables are joined based on id

Now my issue is to retrieve data from the xml_analysis table dynamically using a table xml_softattributes,how can i do that ?

Output required

Softattribute Value 
=======================  
   attr1        ABC
   attr2        XYZ
   attr3        PQR

Possible solution i can think of is using a dynamic string and execute ,but i don't want a dynamic string query to retrieve the data .

Answer 1

You can use the combination of existsNode and extract functions as follows.

SELECT b.SOFT_ATTRIBUTES,
  CASE
    WHEN existsNode (a.soft_attributes ,'/*/'
      ||b.SOFT_ATTRIBUTES) = 1
    THEN a.soft_attributes.extract('/*/'
      ||b.SOFT_ATTRIBUTES
      ||'/text()').getStringVal()
  END value
FROM xml_analysis a,
  xml_softattributes b
WHERE a.id = b.id;

* is Used as a wildcard to match any child node. For example, /PO/*/STREET matches any street element that is a grandchild of the PO element.

Output:

attr1   ABC
attr2   XYZ
attr3   PQR