PLSQL XML into table by tag and value

Question

In PL/SQL, is it possible to convert XML into a table split by tag and value without hardcoding any columns?

Example XML:

<responseObject>
    <error>That is not a valid request</error>
    <errorCode>A11</errorCode>
    <value>A</value>
</responseObject>

Example output:

Tag                        Value
------------------------------------------------------
error                      That is not a valid request
errorCode                  A11
value                      A

I managed to get specific values by declaring the COLUMNS and PATH in a XMLTABLE query by hardcoding error, but I would like to do this dynamically since the tags in the responseObject can vary. I am trying to put all of this into an associative array.

score 3 · Accepted Answer · answered May 04 '18 at 10:33

I think you may want something like this (works in Oracle 11g):

DECLARE
  lxml xmltype;
begin
  lxml := 
  xmltype('<responseObject>
             <error>That is not a valid request</error>
             <errorCode>A11</errorCode>
             <value>A</value>
           </responseObject>');

  FOR test IN (
    select tag,
           VALUE
    FROM   xmltable('responseObject/*'
             passing lxml
             columns
               tag VARCHAR2(128) path 'name()',
               VALUE VARCHAR2(128) path '.'
           ) t
  )
  LOOP
    dbms_output.put_line(test.tag || ' - ' || test.value);
  END LOOP;
end;

I basically adapter the answer provided here: https://stackoverflow.com/a/38225058/1271743

and made use of the name() function to retrieve the name of the XML Node

PLSQL XML into table by tag and value

1 Answers1