Friday, May 28, 2010

Basic XML Parsing via PL/SQL

One question that comes up with some frequency on various OTN forums is "How do I parse this XML?" Depending upon where the XML is (PL/SQL or DB) and what version of Oracle, there are different options that can be used. In starting simple, I show some examples for parsing some basic XML via PL/SQL. This example shows some basic ways to parse XML.

In PL/SQL, you basically have two methods. The first, and older method, is via dbms_xmldom/dbms_xslprocessor and the second is via XMLType. The first method uses DOMDocuments, DOMNodes, DOMNodelist, etc to slowly break down the XML and parse/traverse it. This is based on the W3C definition of a DOM. The second method uses XPath 1.0 syntax to quickly parse an XMLType and return either another XMLType fragment or a basic data type. My preference for parsing XML is via an XMLType as the code seems cleaner to write, understand, and maintain.

Methods 1 and 2 use the old style to parse the XML.Method 1 used dbms_xmlprocessor to select all the nodes that match the XPath /employees/emp/name and then iterates over the list that is returned to print out all the names.Method 2 uses dbms_xmldom to get all the nodes called "name" and simply prints the first one. It too could easily loop through all the name nodes since both operations return a DOMNodeList.

Methods 3 through 5 use XMLType to parse the XML.Method 3 looks for and returns the same information as Method 1. This difference is that instead of returning a list of nodes that match the criteria, it goes out and gets each occurrence one at a time.Method 4 is like Method 2 in that it returns the contents of a specific node directly, in this case, the name of the second employee "emp[2]/name"Method 5 shows the name for each employee and their favorite colors. As the code shows, it extract each emp as an XMLType and then parses that XMLType to extract all the colors.

Gotchas:

/text() refers to the text contents of the given node. Leaving this off returns the node itself (an XMLType).

.getStringVal() and others, along with many examples can be found in the XMLType Operationssection within the online Oracle Documentation.

"ORA-30625: method dispatch on NULL SELF argument is disallowed" means your XPath returned returned nothing aka NULL and further operations failed. This is common when doing a .get*Val() on a .extract() that returned NULL. One way is to do a .ExistsNode before the .extract().get*Val()

8 comments:

The "Basic XML Parsing via PL/SQL" has been v.helpful. But there is one problem which i am facing. The XML which I am having has more than 2 namespaces in it and I am unable to Import the data from it. XML File :

FamilyNameValue

Please let me know how to tackle this situation. You could reply to me question in: http://forums.oracle.com/forums/thread.jspa?threadID=1110466&tstart=0

If you have a question specific to this article, you can post the question here. If you have a general question regarding parsing XML, you can also ask it at http://forums.oracle.com/forums/category.jspa?categoryID=51&start=0 or http://forums.oracle.com/forums/forum.jspa?forumID=34 for review by myself and others.

Hi,Here in the above example you have taken a xml value in l_doc varchar2 data type.But here my requirement is to take a xml file from a particular directory and save xml data in each db table columns.

Late reply: Look at using BFileName for one alternative for reading XML in from disk. Plenty of examples in the XML DB forums at https://forums.oracle.com/forums/forum.jspa?forumID=34 Just search for that keyword.

Arun,You may need to use the second parm of XMLType to properly handle the XML encoding. Stolen from a search on the XML DB forums:xmltype(bfilename('SAMPLE_DATA','file.xml'),nls_charset_id('AL32UTF8'))

About Me

I do what I do because I like it. I've had exposure to a lot of different products/technology over the time. I started with COBOL and flat files on a mainframe. I've used VSAM and DB2 there as well. Linux and Solaris have crossed my path several times. XML, XSLT and schemas have been friends for a while. SQL has seen me through MS Access, DB2 and Oracle. I've worked with 2 versions of DB2 and 3 of Oracle. There has been many other things as well, too small to mention.