Parsing XML Files in SQL with Custom Aggregation Function
I was recently given a task to parse an XML file in SQL that contained the definition of an OBI RPD file, which had been exported using the biserverxmlgen command line utility. The purpose of this is for quick analysis of the RPD file and subsequent mapping of the data lineage.
I decided to use an external table so that the XML data can be presented to the database without the need of a load process in PL/SQL, and then use the XMLTABLE function to parse the data. This is fine, except the problem is that each line of the XML file appears as a single row in the external table. I would need to pass a CLOB data type containing the entire file contents to XMLTABLE for the XML to be parsed.
The LISTAGG function is not an option here, as it is limited by the size of VARCHAR2 in Oracle - the RPD XML file I am dealing with is 500KB in size. So after discussion with my colleague Petar Simic, I discovered a LISTAGG_CLOB function created by Connor McDonald which is (almost) perfect for my needs. The function works like LISTAGG but is capable of returning the results in a CLOB. The original code by Connor can be found here.
The code in LISTAGG_CLOB delimits each element in the listagg with a comma (,) by default, which is hard-coded in the function. I needed the element delimiter to be a space (or chr(10)) so that the XML would parse correctly. I had two options to work around this:
- Modify the function to hard-code a space as the delimiter character, or
- add an optional parameter to the function so the delimiter can be specified at run time, which defaults to a comma.
I chose the second option.
Technical details
The LISTAGG_CLOB function consists of three parts. A Type specification, a Type body and the function to use the Type.
Type specification
CREATE OR REPLACE TYPE listagg_clob_t AS OBJECT (
t_varchar2 VARCHAR2(32767),
t_delim VARCHAR2(255),
t_clob CLOB,
STATIC FUNCTION odciaggregateinitialize (
sctx IN OUT listagg_clob_t,
delim IN VARCHAR2 DEFAULT ','
) RETURN NUMBER,
MEMBER FUNCTION odciaggregateiterate (
self IN OUT listagg_clob_t,
a_val VARCHAR2
) RETURN NUMBER,
MEMBER FUNCTION odciaggregateterminate (
self IN OUT listagg_clob_t,
returnvalue OUT CLOB,
flags IN NUMBER
) RETURN NUMBER,
MEMBER FUNCTION odciaggregatemerge (
self IN OUT listagg_clob_t,
ctx2 IN OUT listagg_clob_t
) RETURN NUMBER
)
Note the new variable T_DELIM and its position on line 3. This becomes the second of three parameters to the Type - see the body code below. There is also a new default parameter in the prototype of the static function.
Type body
The code can now be modified to replace the original static comma delimiter with the newly introduced T_DELIM variable. Note the new parameter added to the listagg_clob_t call on line 7. This is passed into the T_DELIM variable of the type.
CREATE OR REPLACE TYPE BODY listagg_clob_t IS
STATIC FUNCTION odciaggregateinitialize (
sctx IN OUT listagg_clob_t,
delim IN VARCHAR2 DEFAULT ','
) RETURN NUMBER IS
BEGIN
sctx := listagg_clob_t(NULL, delim, NULL);
RETURN odciconst.success;
END;
--
MEMBER FUNCTION odciaggregateiterate (
self IN OUT listagg_clob_t,
a_val VARCHAR2
) RETURN NUMBER IS
PROCEDURE add_val (
p_val VARCHAR2
) IS
BEGIN
IF nvl(lengthb(self.t_varchar2), 0) + lengthb(p_val) <= 4000 THEN
IF self.t_varchar2 IS NULL THEN
self.t_varchar2 := self.t_varchar2 || p_val;
ELSE
self.t_varchar2 := self.t_varchar2
|| self.t_delim
|| p_val;
END IF;
ELSE
IF self.t_clob IS NULL THEN
dbms_lob.createtemporary(self.t_clob, TRUE, dbms_lob.call);
dbms_lob.writeappend(self.t_clob,
length(self.t_varchar2),
self.t_varchar2);
ELSE
dbms_lob.writeappend(self.t_clob,
length(self.t_varchar2),
self.t_delim || self.t_varchar2);
END IF;
self.t_varchar2 := p_val;
END IF;
END;
BEGIN
add_val(a_val);
RETURN odciconst.success;
END;
--
MEMBER FUNCTION odciaggregateterminate (
self IN OUT listagg_clob_t,
returnvalue OUT CLOB,
flags IN NUMBER
) RETURN NUMBER IS
BEGIN
IF self.t_clob IS NULL THEN
dbms_lob.createtemporary(self.t_clob,
TRUE,
dbms_lob.call);
END IF;
IF self.t_varchar2 IS NOT NULL THEN
dbms_lob.writeappend(self.t_clob,
length(self.t_varchar2),
self.t_varchar2);
END IF;
returnvalue := self.t_clob;
RETURN odciconst.success;
END;
--
MEMBER FUNCTION odciaggregatemerge (
self IN OUT listagg_clob_t,
ctx2 IN OUT listagg_clob_t
) RETURN NUMBER IS
BEGIN
IF self.t_clob IS NULL THEN
dbms_lob.createtemporary(self.t_clob,
TRUE,
dbms_lob.call);
END IF;
IF self.t_varchar2 IS NOT NULL THEN
dbms_lob.writeappend(self.t_clob,
length(self.t_varchar2),
self.t_varchar2);
END IF;
IF ctx2.t_clob IS NOT NULL THEN
dbms_lob.append(self.t_clob, ctx2.t_clob);
dbms_lob.freetemporary(ctx2.t_clob);
END IF;
IF ctx2.t_varchar2 IS NOT NULL THEN
dbms_lob.writeappend(self.t_clob,
length(ctx2.t_varchar2),
ctx2.t_varchar2);
ctx2.t_varchar2 := NULL;
END IF;
RETURN odciconst.success;
END;
--
END;
Aggregation function
Finally, the custom function listagg_clob with two parameters:
AGGthe column name containing the data to be aggregatedDELIMa custom delimiter of your choice. Defaulted to comma.
CREATE OR REPLACE FUNCTION listagg_clob (
agg VARCHAR2,
delim IN VARCHAR2 DEFAULT ','
) RETURN CLOB
PARALLEL_ENABLE
AGGREGATE USING listagg_clob_t;
Solving the problem
So now that I have a fast and reliable function to aggregate every row from a 500KB file, all I need to complete the build is the external table and the SQL to extract the data. Below is the external table definition.
create table RPD_DATA_XML_EXT
(
XMLDATA varchar2(3000)
)
organization external
(
type oracle_loader
default directory <import directory>
access parameters
(
RECORDS DELIMITED BY NEWLINE
NOLOGFILE
NOBADFILE
NODISCARDFILE
FIELDS TERMINATED BY '!~!' OPTIONALLY ENCLOSED BY '!~!' NOTRIM
)
location
(
'<file name>'
)
)
The SQL to parse the XML that is contained in multiple rows in the table is below. This is from the RPD export file I loaded into the import directory, and in this example, I am extracting a list of primary keys from the model.
And the results:
