select * from XML

A few days ago, one of our programmers asked me how he can call a procedure from the database passing as a parameter an array of records. Oracle provides this feature on the server side by using, for example, collections of objects, but building such collections on the client side and passing them as parameters is not a trivial task at all (I don’t even know if this is possible with complex types, especially if we are taking into consideration that we use IBATIS to handle database connections, statements and so on, which of course makes this attempt more difficult). However, the first thing which came into my mind was the usage of XML. In the light of this approach, the client side has to build up a XML and pass it to the invoked procedure as a replacement of an array of records. This is not difficult as far as the client csharp code is concerned and, of course, is not a big deal to write a PL/SQL procedure which expects a CLOB parameter, which actually represents the XML content. However, being a SQL guy I must admit that I don’t feel very comfortable to manually parse the XML and to do a lot of DBMS_XML stuff in order to get the data from that XML. Instead, I would rather like to see the XML content as a regular table. Below is how you can do the trick:

  SELECT id, value
   FROM XMLTABLE('/ROWSET/ROW'
      PASSING xmltype('
      <ROWSET>
        <ROW>
          <ID>1</ID>
          <VALUE>aaa</VALUE>
        </ROW>
        <ROW>
          <ID>2</ID>
          <VALUE>bbb</VALUE>
        </ROW>
      </ROWSET>')
   COLUMNS 
           id INTEGER PATH 'ID',
           value VARCHAR2(30) PATH 'VALUE') src;

If you want to embed the above piece of code within a procedure with the XML taken as parameter then it’s just of matter of replacing the actual plain XML content with the corresponding CLOB parameter.

  SELECT id, value
   FROM XMLTABLE('/ROWSET/ROW'
      PASSING xmltype(p_clob_xml)
   COLUMNS 
           id INTEGER PATH 'ID',
           value VARCHAR2(30) PATH 'VALUE') src;

I have tested this on an Oracle 10.2.0.2 server. It might work on previous releases but I cannot offer any guarantees. Happy XMLing! :P