Recently I was presented with a XML document containing tabular data that needed to be joined with several Oracle tables in order to produce a report.  While there are dozens of ways to go about this, I decided to take the opportunity to learn more about the XMLTable function.

XMLTable takes a XML document and evaluates an XQuery expression on that document outputting a pseudotable of rows.  XQuery is powerful extension to XPath that I’ve had the opportunity to work with on a project before (we needed an XQuery processor that runs on Protocol Buffers data at work).  This report however didn’t need anything more powerful than a simple XPath expression.

Typically XMLTable is used with a XML document already stored in the database.  I wanted to pass the XML document in as a parameter on my query.  So I passed in a CLOB, used XMLType to convert it, and used XMLTable to present a tabular data set that I could use to join with other tables in Oracle.

Here’s a bit of example code in Ruby:

require 'rubygems'
require 'oci8'

def make_xml
    xml = '';
    100.times do |i|
        xml += "<row><n>Nathan</n><a>123 Main</a><id>#{i+1}</id></row>"
    end
    "<rowset>#{xml}</rowset>"
end

sql = "
  SELECT
  *
  FROM xmltable(
      '/rowset/row'
      PASSING xmltype(:xml)
      COLUMNS
      name VARCHAR2(50) PATH 'n',
      address VARCHAR2(50) PATH 'a',
      account_id INTEGER PATH 'id'
  )
"

conn=OCI8.new('scott', 'tiger', 'testdb')

cursor = conn.parse(sql)
cursor.bind_param(':xml', OCI8::CLOB.new(conn, make_xml()))
cursor.exec()
while r = cursor.fetch()
    puts r.inspect
end

And now my XML document is a joinable pseudotable.