Sort a Whole XML Document using XSL

This is a variation of an identity transformation that sorts all nodes and attributes alphabetically.

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output indent="yes"/>
<xsl:output method="xml" omit-xml-declaration="no"/>

<xsl:template match="@*|node()" priority="1">
  <xsl:copy>
    <xsl:apply-templates select="@*|node()">
      <xsl:sort select="name()" data-type="text" order="ascending"/>
    </xsl:apply-templates>
  </xsl:copy>
</xsl:template>

</xsl:stylesheet>

How to Get XPath to an XML Node

There are several XSL templates floating around that get a human readable path to a node. However, I found none that cover the case when there are multiple nodes of the same element at the same level. I came up with a complicated solution that kept the output human readable, but the solution for something machine readable is actually simple.

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output indent="yes"/>
  <xsl:template match="@*|node()">
      <xsl:copy>        
        <xsl:attribute name="position"><xsl:call-template name="xpath.position"></xsl:call-template</xsl:attribute>
        <xsl:apply-templates select="@*|node()"/>
      </xsl:copy>
  </xsl:template>

  <xsl:template name="xpath.position">
    <xsl:param name="node" select="."></xsl:param> <!-- default value is current node -->
    <xsl:param name="path" select="''"></xsl:param> <!-- default value is empty string -->

    <!-- prepend the current position to $path, save it as next.path -->
    <xsl:variable name="next.path"><xsl:text>*[position()=</xsl:text><xsl:value-of select="$node/count(preceding-sibling::*)+1"/><xsl:text>]</xsl:text>
      <xsl:if test="$path != ''">/</xsl:if>
      <xsl:value-of select="$path"></xsl:value-of>
    </xsl:variable>

    <xsl:choose>
      <xsl:when test="$node/parent::*"> <!-- when there is a parent node -->
        <xsl:call-template name="xpath.position">
          <xsl:with-param name="node" select="$node/parent::*"></xsl:with-param>
          <xsl:with-param name="path" select="$next.path"></xsl:with-param>
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise>
        <xsl:text>/</xsl:text>
        <xsl:value-of select="$next.path"></xsl:value-of>
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>
  
</xsl:stylesheet>

Identity Transformation that Removes Empty Nodes

Sometimes its handy to remove XML nodes that have no content other than whitespace. The following XSL does exactly that.

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:template match="@*|node()">
    <xsl:if test=". != '' or ./@* != ''">
     <xsl:if test="not(contains(name(), 'temp'))">
      <xsl:copy>
        <xsl:apply-templates select="@*|node()"/>
      </xsl:copy>
     </xsl:if>
    </xsl:if>
  </xsl:template>
</xsl:stylesheet>

Oracle XML Queries and Namespaces

Oracle XML functions have trouble dealing with namespaces. It seems the best way it to simply strip them out but if you can't, you might try and use the optional namespace argument in many of the functions. Trouble is that Oracle's documentation doesn't actually explain what the syntax is.

The answer comes from Flavio Casetta at his oraclequirks blog:

select 
     extractValue(content,'/path/here','xmlns="http://www.w3.org/2005/Atom"') contentname 
   from 
     table

Parsing a Document into a DomDocument in JavaScript

XMLHttpRequest includes an attribute called responseXML besides responseText which should be populated when the returned data is XML. But actually often this doesn't work right because the headers are set right. Found this post by Darko in Google Groups about how to force parsing:

http://groups.google.com/group/comp.lang.javascript/browse_thread/thread/762990f37ae218a3/6e0d948e1820bf24?pli=1

I called this in my "showResponse" function called by prototype.js' Ajax.Request onSuccess event. showResponse(originalRequest) receives the original XMLHttpRequest object and you need to call Darko's code using originalRequest.

However of course it didn't work for me-- in Firefox, the script runs forever. Turns out there was actually an error parsing the doc, but there's no way to tell what it is.

Time to add error output, which is defined nicely here:
http://www.faqts.com/knowledge_base/entry/versions/index.phtml?aid=15302

Haven't finished this yet :)

Exporting Table Data as Oracle XML

If you are using dbms xmlsave, you might want to capture data from a table into Oracle's format as well. This is actually very straightforward with dbms_xmlgen.

SELECT dbms_xmlgen.getXml(
  'SELECT *
     FROM your_table_name_goes_here'
  , 0
  ) FROM dual;

Looping over a Set of Nodes with PL/SQL

Oracle's XMLType has the handy member function 'extract' which evaluates an XPath. Except it won't work if you XPath is a function call. So, if you want to count how many children there are of a node so you can iterate over them,

extract('count(/xpath/here/*)')

won't work. Here's the solution:

select count(*) into v_maxi from TABLE(XMLSequence(extract(p_xml,'/xpath/here')));               
  FOR v_i IN 0..v_maxi-1 LOOP
      v_xmlfrag := p_xml.extract('/xpath/here[count(preceding-sibling::*)='||v_i||']');
      v_text := v_xmlfrag.extract('/record/stuff/text()').getStringVal();
  END LOOP;

Insert, Append, or Update an XML element to an XML document using Oracle PL/SQL

Oracle allows you to quickly insert or append an XML element to a path inside an XML document using insertchildxml.

select
   insertchildxml(v_xmltype,
        '/my/xpath',
        lower('elementname'),
        XMLType('stuff'))
   from dual;

Gotchas: You must use a select statement. Argument 3 must match the element inside argument 4.

If you are modifying attributes, you can use updatexml-- but it is obnoxious because if the attribute doesn't exist, updatexml fails silently. So, if unsure, you need to user insertxml to add the attribute and if it throws an exception, then attempt an update. Like so:

   BEGIN
        select insertchildxml(v_xml, '//myxpath', '@myattribute',
               v_newvalue) into v_xml from dual;
   EXCEPTION -- if @myattribute attribute exists, insert will fail, so update instead
   WHEN OTHERS THEN
        select updatexml(v_xml, '//myxpath/@myattribute', v_newvalue)
               into v_xml from dual;
   END;

Extending DBMS.XMLSave to Insert into Multiple Tables

Oracle, through the DBMS.XMLSave package, is able to take XML in a specific format and insert it into normal relational tables. This package has two major limitations in my opinion: 1) The values of rows are NOT evaluated, so functions do not work and sequences don't work. 2) Only one target table can be specified.

I've written a procedure which wraps Oracle's format inside some higher level XML elements to allow insertion into multiple tables. My solution for sequences and functions is not very flexible, but it will work for specific applications, and I can make some recommendations on how to improve the flexibility.

Let's start with inserting into multiple tables. If you look at my previous post, you will see Oracle's format for the DBMS.XMLSave function. I wrap it in two higher level elements, records (the new root) and the record element which contains a rowset.

<records>
  <record table="tablenamegoeshere">
    <rowset>
      <row>
        <columnname>value</columnname>
      </row>
    </rowset>
  </record>
<records>

A stored procedure to handle these XML files must loop through each record and call Oracle's XMLSave on the rowset contained within. Each record element has an attribute, table, which specifies which table to insert into.

The next step is to implement sequences. We could take a cue from Hibernate and add an attribute to the column that uses a sequence. I didn't have time to implement this, so what I've done is update one column in each row with the results of an execute immediate. This assumes that there is only one sequence that needs updating and that it's name & and the containing column's name is based on the table attribute in the record element.

I use a similar procedure for function calls-- use XPath to update columns. The structure of the data is very rigid unfortunately because of this.

One gotcha -- make sure rowset and row elements are LOWER CASE. Oracle's procedures are case sensitive.

create or replace procedure PARSE_RECORD_XML(p_xml in xmltype, p_xmlid in integer) is
   v_i            INTEGER;
   v_j            INTEGER;
   v_func_arg    INTEGER;
   v_seqnum       INTEGER;
   v_func_result          VARCHAR2(50);
   v_xmlfrag      XMLTYPE;
   v_tablename    VARCHAR2(50);
   v_seqname      VARCHAR2(100);
   v_maxi         INTEGER;
   v_maxj         INTEGER;
  
begin
  select count(*) into v_maxi from TABLE(XMLSequence(extract(p_xml,'/records/record')));               
  FOR v_i IN 0..v_maxi-1 LOOP
      v_xmlfrag := p_xml.extract('/records/record[count(preceding-sibling::*)='||v_i||']');
      v_tablename := v_xmlfrag.extract('/record/@table').getStringVal();
      v_seqname := v_tablename || 'ID'; -- sequence name is a concatenation of table attribute and ID
     
      select count(*) into v_maxj from TABLE(XMLSequence(extract(v_xmlfrag,'/record/rowset/row')));               
 
      FOR v_j in 0..v_maxj-1 LOOP
        -- Get sequence number
        execute immediate 'SELECT ' || v_seqname || '.nextval from dual' into v_seqnum;
       
        -- Execute a function on some data
        v_func_result := v_xmlfrag.extract('/record/rowset/row[count(preceding-sibling::*)='||v_j||']/func_arg/text()').getStringVal();
       
        -- Insert the parent id
        select insertchildxml(v_xmlfrag, '/record/rowset/row[count(preceding-sibling::*)='||v_j||']', 'parentid',
                    XMLType('' || p_xmlid || '')) into v_xmlfrag from dual;
       
        -- Insert function result           
        select insertchildxml(v_xmlfrag, '/record/rowset/row[count(preceding-sibling::*)='||v_j||']', 'func_result',
                    XMLType('' || getMRN(v_patientid) || '')) into v_xmlfrag from dual;

        -- Insert sequence id
        select insertchildxml(v_xmlfrag, '/record/rowset/row[count(preceding-sibling::*)='||v_j||']', lower(v_seqname),
                    XMLType('<' || lower(v_seqname) || '>' || v_seqnum || '')) into v_xmlfrag from dual;
             
       
      END LOOP;
      InsertXML(v_xmlfrag.extract('/record/rowset'), v_tablename);
  END LOOP;
end PARSE_RECORD_XML;

Inserting XML data into an Oracle Table

Oracle has a package DBMS_XMLsave that can take an XML document and insert it into a normal relational table. The format for the XML document is

<ROWSET>
  <ROW>
    <COLUMNNAME>
      value
    </COLUMNNAME>
  </ROW>
</ROWSET>

The other argument to the save function is the table name. Two gotchas: 1) by default, the element names are case sensitive. I think this is a nightmare with table column names, so luckily there is a flag to disable case sensitivity (why do developer's inflict case sensitivity on each other?) 2) The format is very barebones. Attributes are ignored and you can't use functions within the values for columns. You can only insert into one table as well. I've created some code to get around this, which is the topic of another post.

At least, though, you CAN use XSLT to transform your document to something that Oracle can handle natively.

Here is a stored procedure that inserts such an XML document:

CREATE OR REPLACE PROCEDURE InsertXML (p_xml in xmltype, p_tablename in varchar2) is

   queryCtx DBMS_XMLsave.ctxType;

   rowsInserted INT;

BEGIN

   --Defines the query

   queryCtx := SYS.DBMS_XMLSave.newContext(p_tablename);
  
   DBMS_XMLSave.setIgnoreCase(queryCtx, 1);
  
   rowsInserted := DBMS_XMLSave.insertXML(queryCtx,p_xml.getCLOBval());
  
   DBMS_XMLSave.closeContext(queryCtx);
  
   commit;

END;

Passing XML stored in a String into Oracle Stored Procs

For stored procedures, you can pass XML data stored in a java.lang.String into a procedure via a CLOB type. To cofigure you Oracle callablestatement, use setObject on the java.lang.String. It's harder to pass in XMLType because it's an opaque type and requires some fiddling. In your stored procedure, just create an XMLType from the input CLOB via code like v_arg := XMLType(p_arg);

If your XML is not stored in a String, I don't know what to say since I tend to grab XML from a request and it comes out as a String.

Counting the number of nodes in an XMLType

Newer Oracle versions can use a function called XMLTable, but <10.2 have to use another method:

select count(*) into number_of_items from table(xmlsequence((the_xml.extract('/*/mynode', 'xmlns="http://purl.org/rss/1.0/" xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#"') )));

XMLType extract on XML with namespaces

Guess what, using extract('/mynode') in Oracle will not return mynode if the XML file has namespaces that Oracle doesn't recognize. The solution? use extract('/mynode','paste all your namespace declarations in here'). So for example:

extract('/mynode','xmlns="http://stupidideas.net/stupid" xmlns:dumb="http://dumb.com/dumber"')

See also:

http://pbarut.blogspot.com/2006/11/xmltype-and-namespace-problem.html

Saving and Loading an XMLType to/from Oracle from Java

First off, you will need ojdbc14.jar, xmlparsenv2.jar, and xdb.jar. You probably already have ojdbc14.jar if you are doing Oracle work, but the other two I had to download.

Lets get our imports going.

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import oracle.jdbc.OracleTypes;

Now, you'll have to create a class, and inside it put this save function. My variable XMLdata is just a string I grabbed from the POST to this Java.

 public void save() {
  Connection conn = null;
  CallableStatement callableStatement = null;
  ResultSet rs = null;
  DataSource dataSource = null;
  InitialContext ctx = null; 
  System.out.println("[save] Starting");
  try {
   ctx = new InitialContext();
   dataSource = (DataSource)ctx.lookup("java:comp/env/yourdatasourcename");
   conn = dataSource.getConnection();
   System.out.println("[save] Fetching");
   callableStatement = conn.prepareCall("{call SAVE(?,?,?,?)}");
   System.out.println("[save] prepare call done, setting values"); 
   callableStatement.setInt(1, var1);
   callableStatement.setString(2, var2);
   callableStatement.setString(3, var3);
   callableStatement.setObject(4, XMLdata); // note that I use setObject instead
   rs = callableStatement.executeQuery();
   System.out.println("[save] Executed the query");
   
   System.out.println("[save] inserted XML data: "+XMLdata);
   rs.close();
   callableStatement.close();
   conn.close();
   System.out.println("[save] Done"); 
  } catch (SQLException e) {
   System.out.println("[save] Saving the form blew up with an SQLException");
   e.printStackTrace();
  } catch (Exception e) {
   System.out.println("[save] Saving the form blew up with an Exception");
   e.printStackTrace();
  }
   
 }

The SAVE stored procedure just takes an integer, two varchar2s, and an XMLtype as input. Nothing exciting here. Let's move on to the trickier part: loading.

 public void load() {
  Connection conn = null;
  CallableStatement callableStatement = null;
  ResultSet rs = null;
  DataSource dataSource = null;
  InitialContext ctx = null; 
  System.out.println("[load] Starting");
  try {
   ctx = new InitialContext();
System.out.println("got context");
   dataSource = (DataSource)ctx.lookup("java:comp/env/yourdatasourcenamehere");
if(dataSource==null) 
System.out.println("Got the datasource but it's null");
else
System.out.println("Got the datasource and it's not null");
   conn = dataSource.getConnection();
System.out.println("Got the connection");
   callableStatement = conn.prepareCall("{call LOAD(?,?,?,?)}");
   System.out.println("[load] prepare call done, setting values"); 
   callableStatement.setInt(1, InstanceId);
   callableStatement.setString(2, ProcessName);
   callableStatement.setString(3, TracingTag);
   callableStatement.registerOutParameter(4, OracleTypes.OPAQUE,"SYS.XMLTYPE"); 
   System.out.println("[load] Registered XMLType out parameter");
   rs = callableStatement.executeQuery();
   System.out.println("[load] Executed the query");
   oracle.xdb.XMLType xml = oracle.xdb.XMLType.createXML((oracle.sql.OPAQUE)callableStatement.getObject(4));

   System.out.println("[load] Got XML data: "+xml.getStringVal());
   rs.close();
   callableStatement.close();
   conn.close();
   System.out.println("[load] Done"); 
  } catch (SQLException e) {
   System.out.println("[load] Loading the form blew up with an SQLException");
   e.printStackTrace();
  } catch (Exception e) {
   System.out.println("[load] Loading the form blew up with an Exception");
   e.printStackTrace();
  }
   
 }

The key to loading is to first register an opaque out parameter. There are ways to use other types, but I found that subsequent manipulations of the returned data required starting with this type. You can get an XMLType, but then you need to use the connection in Oracle's libraries, and that breaks other stuff... so on. So use this: callableStatement.registerOutParameter(4, OracleTypes.OPAQUE,"SYS.XMLTYPE");

Opaque can store all sorts of Oracle types. To actually manipulate the XML in a useful way, you have to store it in an XMLType. To do that, create a new XMLType and then, using a cast, assign the opaque variable to it. Like so:

oracle.xdb.XMLType xml = oracle.xdb.XMLType.createXML((oracle.sql.OPAQUE)callableStatement.getObject(4));

That's the quick one line version. A little more readable is the following:

  Object xmlobject = callableStatement.getObject(4);
  oracle.sql.OPAQUE xmlopaque= (oracle.sql.OPAQUE) xmlobject;
  oracle.xdb.XMLType xml = oracle.xdb.XMLType.createXML(xmlopaque);

The nice thing about oracle.xdb.XMLType is that it behaves just like an XMLType in Oracle. So you can use the functions you're used to from PL/SQL. getStringVal() returns the string value of the XML for example.

SQL Query to select/update text from XML columns

Here's how to get data:

select * FROM
  table_with_xml w
WHERE
  w.XMLCOLUMN.EXTRACT('/xpath/goes/here/text()').getstringval() = 'test';

And how to update:

UPDATE TABLE_WITH_XML
  SET XMLCOLUMN =  UPDATEXML(XMLCOLUMN,
     '/xpath/goes/here/text()', 'a new value')
  WHERE
     xml_id = p_xml_id; 

Quick XSL Primer

XSL uses a set of rules to transform an XML document into something else. You can set up these rules by creating templates. Templates match tags in the XML, and contain text that should replace the tag. It's like a complex search and replace.

The basic building block of XSL is the xsl:template tag. It has one principal attribute, match, that takes an XPath query.

As part of the body text of the template tag, you will use xsl:apply-templates, which continues processing nodes further down in the XML tree. It has one attribute, select, which behaves like match.

In the examples below, replace 'node' with the tag you use in your xml. For example, if you have a tag called person:
<person>Guido</person>

Node is 'person'.

Here's a few basic queries:
//node
match, anywhere in the document, tags with the name node

/node
match, just at the root, tags with the name node

/node1//node2
match any tag with the name node2 that is underneath node1, which is at the root

.
select the current node

*
match all nodes at the current level of processing

/*
match any tag at the root

/*[@school='engineering']
match all nodes at the root with an attribute school that is set to equal 'engineering'

/*[name()='seanDavis']
this would be the same as //seanDavis while this example seems pointless look at the next one

/*[count(name()='voijinOklobja')=0]
this would match all tags in the root, unless there are tags with the name voijinOklobja. You should have a corresponding rule for when the count > 0 if you do this.

/person/children//*[count(*)=0]
select all leaf nodes underneath /person/children

/person/property[count(*/*)>0]
select all nodes under /person/property that have children (no guarantees that their children are leaves though)

/person/name[text()='Guido']
select all person tags which have a child tag which contains just 'Guido'.

/person[position()=last()]
select the last person tag in the root

/person[position()<last()]
select all but the last person tag in the root of the document (you might need to literally use

& l t ; [remove the spaces] rather than < 

)

/person[./property/*/name()='height']
select only people who have a property named height present

Example:

<xsl:template match="">10]">
    filthy rich
    <xsl:apply-templates/>
</xsl:template>

xsl:for-each

I found that I was processing a lot of lists, and inserting commas and such between list items. Apply-templates doesn't work very well because you can't insert text around each match. So you need to use xsl:for-each. This takes one attribute, select, and works pretty much like apply-templates.

<!-- process all but the last element -->
<xsl:for-each select="*[position()<last()]">
      <xsl:apply-templates select="."/> and
</xsl:for-each>

<!-- process the last element -->
<xsl:apply-templates select="*[position()=last()]">

xsl:value-of
When you are inside a template, you may want to print a value using the same XPath that you use to do matching and selections. Just use the xsl:value-of tag

xsl:value-of select="..."

name() prints out the name of the node inside the <> in your XML
@attributename prints an attribute
text() prints the text inside the braces
variables prefixed by $

xsl:variable
Sometimes you need variables. Here's how to create one, set its value, then display it:

<xsl:variable
            name="hag"
            select="name()"/>
 <xsl:value-of select="$hag"/>

xsl:call-template
You can call a template from another template, so long as the template you are calling also has a unique name attribute. The name can't contain variables, etc (so this is not so useful for inheritance):

<xsl:call-template name="blah"/>

xsl:template and xsl:next-match and priorities
I needed to use inheritance right away. Unfortunately with XSL 1.0 and MSXML 3, you can't. But Saxon supports XSL 2.0, which contains next-match. What this does is that you can have multiple templates that match the same nodes. Set a priority attribute for each (lower numbers have a lower priority) and put xsl:next-match in the higher level priorities, and they will be evaluated in order. For example...

<xsl:template match="/*" priority="1001">
    You have created an animal named <xsl:value-of select="name()">
    <xsl:next-match/>
</xsl:template>

<xsl:template match="/dog" priority="1">
    Woof woof
</xsl:template>

Will say 'You have created an animal named ...' for each node in the root. If you happen to have a node named 'dog', it will also say 'Woof woof' after the above text.

Using Saxon to Transform XML with XSL stylesheets

While you can just drop XML into Internet Explorer, the MSXML v3 parses IE 6 uses is really quite awful. With Firefox, I couldn't figure out how to view the outputted text.

1. Download saxon8.jar
2. Make sure Java is installed (I think the JRE will work fine)
3. Create an XML file
4. Create an XSL file
5. Run saxon like so from the directory that saxon8.jar is in:
   

   java -jar saxon8.jar xmlxslt\test.xml xmlxslt\test.xsl