Component Workshop Ltd

The code snippet below shows the Oracle XML functions in action. It is the beset way to understand how it works:

CREATE OR REPLACE
FUNCTION get_accounts_by_email_address(xml_string clob) RETURN SYS_REFCURSOR
IS

  return_data SYS_REFCURSOR;
  xml_data XMLTYPE;

BEGIN

  xml_data := XMLType.CreateXML(xml_string);

  OPEN return_data FOR
    SELECT DISTINCT a.*
      FROM Accounts a
      INNER JOIN Contacts c
        ON a.AccountId = c.AccountId
      INNER JOIN table(xmlsequence(extract(xml_data, '/EmailAddresses/EmailAddress'))) emailaddresses
        ON extract(value(emailaddresses), '//EmailAddress/text()').getStringVal() = c.EmailAddress;

  RETURN return_data;

END

We pass an XML fragment in as a clob. In this example, the fragment would look like this:

<EmailAddresses>
  <EmailAddress>hubert@pretend.com</EmailAddress>
  <EmailAddress>marjory@acme.com</EmailAddress>
  <EmailAddress>kenneth@fish.net</EmailAddress>
</EmailAddresses>

Our database has an Accounts table that can be joined to the Contacts table in a one-to-many relationship. Thus, if we have an email address, we can match it to an account. The method of passing XML in to a procedure gives us a good way of querying an arbitrary number of values in a single request -- we could write a function to return an account for a single address very easily, but we would need to call it n-times for n-addresses.

In the function header we define the variable xml_data as XMLTYPE. This is Oracles native XML type. We initialize the XML document by calling xml_data := XMLType.CreateXML(xml_string) and passing it our clob. Now we can directly query the XML as if it were a table, and join the results (or do any other typical database task, such as INSERT, DELETE or UPDATE based on the XML values). You could create a cursors and loop through the XML nodes if you wanted.

The important code is in the last INNER JOIN:

INNER JOIN table(xmlsequence(extract(xml_data, '/EmailAddresses/EmailAddress'))) emailaddresses
  ON extract(value(emailaddresses), '//EmailAddress/text()').getStringVal() = c.EmailAddress;

We turn EmailAddress XML nodes in to rows of data by selecting the nodes using an XPath query through the extract() function, and then passing it first to xmlsequence() (to create individual rows for each result) and then table() (so the results can be queries using JOIN or ON). In our join caluse we must extract() the value of the XML node before we can compare it to the table we're joining to. The extract method takes the XMLTYPE instance as the first parameter, which in our case is our new table created by the first line (and given the alias emailaddresses), and an XPath query as the second value. An optional third parameter lets you specify the namespace.

I hope you find this article useful. The Oracle documentation isn't bad, and you should definitely check it out if you're writing XML functions in Oracle. The main reason for this article is that there is very little code that actually demonstrates, in simple examples, how to string the various XML functions together. If you have any feedback or would like to see anything else, please get in touch (through the contact page) and let us know.