Memory dump: Work with XMLField in Oracle (part 2)

Posted by in Software

TL;DR: Here I present a SQL example from creating the table to performing a SELECT.

Table of Contents

  1. Part 1 – Prerequisites
  2. Part 2 – Create a table and perform a SELECT
  3. Part 3 – User Python to insert data
  4. Part 4 – references

Table and XMLTYPE

Once we’ve seen that we have XMLTYPE available, we can proceed to create a simple table.

The table structure

My problem required for a table with the following structure:

  1. An identifier (key) – 20 character long string
  2. An article field (nullable) – XML content
  3. A field with citations of the article (nullable) – XML content

The SQL statement to create the table is as follows:

This is straight-forward. However, as you can see, the table is quite dumb and it doesn’t have any XSDs attached to its contents.

Adding a record

Adding a record can be done via a simple SQL insert. However you need to make sure you escape all quotes… A simple example which adds a record with the ID and CITEDBY fields is:

An XML structure

Once we have added several records, we can perform queries on them. As you can see from the example above, the CITEDBY field has a very simple structure:

  • A count of citations and
  • A list of document IDs that refer the current document

The XML from the example above is:

We can therefore perform some interesting queries. For example, we can display a table containing the article ID and the number of citations for that article. The query would be:

This requires some explanations:

  • I want to query the ARTICLES_XML_DATA with an alias of articles. I will need the field ID from it.
  • For every record, I want to PASS articles.CITEDBY,
  • Get the content from XPATH of cited-by/count,
  • Place it in a VARCHAR2(30) as a column named NUMBER and
  • Refer to this result (table with a single column) as citations.

As you can see, the result looks very muck like a select on an inner join.

The result is something like:

Glorious!


A little experiment: If you find this post and ad below useful, please check the ad out :-)