Create Oracle XML view

Question

I have an Oracle table that stores a lot of data as XML in CLOBs. For instance, it maps status integers to customized status names. Here's some simplified XML:

<metadata>
    <states>
        <state status="0" label="New" />
        <state status="1" label="Data-Entry" />
        <state status="25" label="Approve-Data" /> 
    </states>
</metadata>

I wrote a query to extract said XML from the database and determine status name given a status integer of 25:

select id As METADATA_ID, EXTRACTVALUE(xmltype(XML_STRING), '/metadata/states/state[@status="25"]/@label') from metadata;

Result: 342, "Approve-Data". So far, so good. The query above creates a list of all the different labels for that state. What I'd like to do, though, is to create a view with the following columns:

METADATA_ID   |   STATUS_NUM   |    LABEL
342               0                 "New"
342               1                 "Data-Entry"
342               25                "Approve-Data"
674               3                 "Something-Else"
674               6                 "More-Data"

That way I could look up all the different mappings for the different metadata IDs and perform normal Oracle joins. Is there a way to do it, given that there's gaps in the status numbers?

Answer 1

Good news everyone! I found a solution using XMLTABLE (please pardon my arbitrary capitalization practices):

select 
  m.id as metadata_id, x.*
FROM
  metadata m,
  XMLTABLE('/metadata/states/state'
       PASSING xmltype(m.xml_string)
       COLUMNS status_id INTEGER PATH '@status',
               status_label VARCHAR(32) PATH '@label') x;