tag:blogger.com,1999:blog-38515953097681282502024-02-20T10:10:18.494-07:00A_Non On XMLOracle's SQL/XML and the XML DBJason Hhttp://www.blogger.com/profile/02172936625260505714noreply@blogger.comBlogger26125tag:blogger.com,1999:blog-3851595309768128250.post-14916772548770041112013-10-15T10:54:00.001-06:002013-10-15T10:54:29.163-06:00Implicit Uppercase in XMLTableIf you review the documentation for <a href="http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions253.htm#sthref1822" target="_blank">XMLTable</a>, you may notice that within the COLUMNS clause, the PATH clause is optional. The documentation states<br />
<blockquote class="tr_bq">The optional PATH clause specifies that the portion of the XQuery result that is addressed by XQuery expression string is to be used as the column content. If you omit PATH, then the XQuery expression column is assumed.</blockquote>The example to show what the assumed PATH looks like after being added is: <br />
<blockquote class="tr_bq">COLUMNS xyz PATH 'XYZ'</blockquote>Does that mean that the PATH is always the upper case version of the column name? Let's see with a simple example. I used the WITH clause simply to simulate a table for this example.<br />
<div class="code"><pre>WITH INPUT_XML AS(
SELECT XMLTYPE('<Document>
<node1>1</node1>
<Node1>2</Node1>
<NODE1>3</NODE1></Document>') as XML_COL from dual
)
-- Care about the below
SELECT xt.*
from INPUT_XML ix,
XMLTABLE('Document'
PASSING ix.XML_COL
COLUMNS
node1 VARCHAR(1)
) xt;
NODE1
-----
3
</pre></div><br />
So it looks like it, but what about if we enclose the name in double quotes to make it case sensitive?<br />
<div class="code"><pre>WITH INPUT_XML AS(
SELECT XMLTYPE('<Document>
<node1>1</node1>
<Node1>2</Node1>
<NODE1>3</NODE1></Document>') as XML_COL from dual
)
-- Care about the below
SELECT xt.*
from INPUT_XML ix,
XMLTABLE('Document'
PASSING ix.XML_COL
COLUMNS
"node1" VARCHAR(1)
) xt;
node1
-----
1
</pre></div><br />
So the answer is, Oracle will uppercase the column name, unless you make the name case sensitive by enclosing it in double quotes. I believe the best solution is to always include the PATH clause so you can be explicit on which node from the XML is needed to ensure a correct mapping/extraction. Don't leave it up to the people after you to guess what the code is doing and why it is not retrieving a value from the XML.<br />
Jason Hhttp://www.blogger.com/profile/02172936625260505714noreply@blogger.com0tag:blogger.com,1999:blog-3851595309768128250.post-6900126114178141412013-09-17T10:46:00.001-06:002013-09-17T10:46:43.436-06:00Passing XMLType By ReferenceThis post was inspired by the Daily PL/SQL Quiz for 22 August 2013 over on the <a href="http://www.plsqlchallenge.com/" target="_blank">PL/SQL Challenge</a>. That quiz was testing concepts related to setting OUT parameters in a called procedure, but one of the quizzes was performing some *evil*. The procedure was changing a global VARCHAR2 variable that had also been passed to the procedure as an IN parameter. When the procedure looked at the local IN variable, it saw the change. This got me to wondering whether this would apply to an XMLType variables as well.<br />
Per <a href="http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/subprograms.htm#LNPLS659" target="_blank">Subprogram Parameter Modes</a> and <a href="http://docs.oracle.com/cd/E11882_01/appdev.112/e17126/subprograms.htm#CHDGBIEC" target="_blank">Subprogram Parameter Aliasing with Parameters Passed by Reference</a> it should, but why not test it to be sure. So I setup a simple test on 11.1 to verify it<br />
<br />
<div class="code">
<pre>Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
SQL> set serverout on
SQL> DECLARE
2 g_xmltype XMLTYPE;
3
4 FUNCTION return_col_value(i_xml IN XMLTYPE)
5 RETURN VARCHAR2
6 IS
7 l_value VARCHAR2(10);
8 BEGIN
9 g_xmltype := XMLTYPE('<root><c1>b</c1></root>');
10 SELECT c1
11 INTO l_value
12 FROM XMLTable('/root'
13 PASSING i_xml
14 COLUMNS
15 c1 VARCHAR2(10) PATH 'c1');
16
17 RETURN l_value;
18 END return_col_value;
19 BEGIN
20 g_xmltype := XMLTYPE('<root><c1>a</c1></root>');
21 dbms_output.put_line(return_col_value(g_xmltype));
22 END;
23 /
b
PL/SQL procedure successfully completed
</pre>
</div>
<br />
Note that line 9 sets the global variable and the SELECT on line 10 reads from the local IN variable instead. The function returns the value that was set on line 9 instead of what was set on line 20. Had the function not been able to see the assignment on line 9, then the function would have returned "a" as that is what the XML looked like on line 20 when the function was called. This confirms that the XMLType variable was passed by reference, as a by value passing would not have seen the change made on line 9.<br />
<br />
You may be thinking, well all that was in the same anonymous block of code so it was easy for Oracle to figure that out and treat it by reference. Following is another example, that would be more common to this situation, where the XMLType variable resides outside the block of code that is the function or block of code calling the function.<br />
<br />
<div class="code">
<pre>SQL> CREATE OR REPLACE PACKAGE hold_value
2 IS
3 g_xmltype XMLTYPE;
4 END;
5 /
Package created
SQL> CREATE OR REPLACE FUNCTION return_col_value(i_xml IN XMLTYPE)
2 RETURN VARCHAR2
3 IS
4 l_value VARCHAR2(10);
5 BEGIN
6 hold_value.g_xmltype := XMLTYPE('<root><c1>b</c1></root>');
7 SELECT c1
8 INTO l_value
9 FROM XMLTable('/root'
10 PASSING i_xml
11 COLUMNS
12 c1 VARCHAR2(10) PATH 'c1');
13
14 RETURN l_value;
15 END return_col_value;
16 /
Function created
SQL> BEGIN
2 hold_value.g_xmltype := XMLTYPE('<root><c1>a</c1></root>');
3 dbms_output.put_line(return_col_value(hold_value.g_xmltype));
4 END;
5 /
b
PL/SQL procedure successfully completed
</pre>
</div>
<br />
Same result as before so the change made on line 6 was seen in the SELECT statement and returned by the function, even though the package variable was modified and not the local variable the SELECT statement was using. So as above, Oracle is passing XMLType variables defined as IN by reference instead of by value. This is in agreement with Oracle documention.<br />
<br />
One final note. Don't do something like this in production code. This would be called a nasty side-effect and one not obvious unless you look closely at the code. Jason Hhttp://www.blogger.com/profile/02172936625260505714noreply@blogger.com0tag:blogger.com,1999:blog-3851595309768128250.post-69364505082589171142013-05-09T09:46:00.002-06:002013-05-09T09:46:59.518-06:00XMLType DOMDocument and pointersRan across this post on the OTN Forums last week.<br />
<a href="https://forums.oracle.com/forums/thread.jspa?threadID=2530938&tstart=0">Dynamically adding attribute based on PL/SQL function</a><br />
The part that interested me was Odie's second post. If you look closely at his second post (first example) he basically does<br />
======================= <br />
xmltype := XML_text<br />
DOMDocument := New Document (xmltype)<br />
... modify DOMDocument<br />
Free DomDocument<br />
Output xmltype.getClobVal<br />
======================= <br />
So with that example, he shows that even though the DOMDocument variable is being modified, the changes still show up in the XMLtype variable. The only way that can be done is through the use of a pointer the two variables are using to reference the same memory. Very interesting to me as it was something new.Jason Hhttp://www.blogger.com/profile/02172936625260505714noreply@blogger.com0tag:blogger.com,1999:blog-3851595309768128250.post-63094423082244510332012-06-13T14:45:00.000-06:002012-06-13T14:45:23.416-06:00XMLTYPE and DBMS_REDEFINITIONThis post is based on the following OTN XML DB thread: <a href="https://forums.oracle.com/forums/thread.jspa?threadID=2394094&tstart=0">Migrating XMLType from STORE AS CLOB to STORE AS BINARY XML</a>.<br/>
<br/>
When Oracle first introduced the XMLType datatype (9.2.0.3), the default storage mechanism for it was a CLOB (aka BasicFile LOB). Note: I'm ignoring Object Relational storage since it is a table based format instead of a single column. With the release of 11.1.0.6, Oracle added a new storage type of SECUREFILE BINARY XML but left the default storage as CLOB. With the release of 11.2.0.2, Oracle changed the default storage to be SECUREFILE BINARY XML (<a href="http://docs.oracle.com/cd/E11882_01/appdev.112/e10492/whatsnew.htm">What's New in Oracle XML DB?</a>).<br/><br/>
So what are the options for going from CLOB to BINARY XML storage? One is via DBMS_REDEFINITION. This may be the best option as it allows for the redefinition of the table without any system downtime. The downside to this option is that you need to have free space equal to or greater than the size of the new table as both the old and new will exist at the same time in the system. Assuming you can, here is what a simple example looks like (borrowed from myself from the OTN thread)
<div class="code"><pre>SQL> CREATE TABLE HOLDS_XML -- old table structure
2 (n_col NUMBER(5) NOT NULL PRIMARY KEY,
3 xml_col XMLTYPE)
4 XMLTYPE xml_col STORE AS BASICFILE CLOB;
Table created
SQL> INSERT INTO holds_xml VALUES
2 ('1',XMLTYPE('<root>val1</root>'));
1 row inserted
SQL> COMMIT;
Commit complete
SQL> CREATE TABLE HOLDS_XML_TMP -- new table structure
2 (n_col NUMBER(5) NOT NULL PRIMARY KEY,
3 xml_col XMLTYPE)
4 XMLTYPE xml_col STORE AS SECUREFILE BINARY XML;
Table created
SQL> exec DBMS_REDEFINITION.start_redef_table('JASON', 'HOLDS_XML', 'HOLDS_XML_TMP');
PL/SQL procedure successfully completed
SQL> exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE('JASON', 'HOLDS_XML', 'HOLDS_XML_TMP');
PL/SQL procedure successfully completed
SQL> exec DBMS_REDEFINITION.finish_redef_table('JASON', 'HOLDS_XML', 'HOLDS_XML_TMP');
PL/SQL procedure successfully completed
SQL> SELECT dbms_metadata.get_ddl('TABLE','HOLDS_XML')
2 FROM dual;
DBMS_METADATA.GET_DDL('TABLE',
--------------------------------------------------------------------------------
snipped...
XMLTYPE COLUMN "XML_COL" STORE AS SECUREFILE BINARY XML (
...snipped</pre></div>
As you can see from the above DDL, the column is now a SECUREFILE BINARY XML. This also means that all the XML in that column has been converted to the more efficient (and smaller storage requirements) BINARY XML.<br/><br/>
While researching this post, I came across someone mentioning the use of an ALTER TABLE statement to switch the storage formats. Here is what happens on 11.1.0.6 if we use the above table with a row in it.
<div class="code"><pre>SQL> ALTER TABLE JASON.HOLDS_XML
2 MODIFY (XML_COL)
3 XMLTYPE COLUMN XML_COL STORE AS SECUREFILE BINARY XML;
Table altered
SQL> SELECT dbms_metadata.get_ddl('TABLE','HOLDS_XML')
2 FROM dual;
DBMS_METADATA.GET_DDL('TABLE',
--------------------------------------------------------------------------------
snipped...
XMLTYPE COLUMN "XML_COL" STORE AS BASICFILE CLOB (
...snipped</pre></div>
So the command completed successfully but did nothing still the column still uses CLOB storage. It was worth a try at least.
<br/><br/>
There are also the standard methods of
<ul><li>Exporting data, dropping table, creating new table, importing data</li>
<li>Exporting data, creating new table, importing data, dropping old table, renaming new table</li>
<li>Create new table, Insert Select From old table, dropping old table, renaming table</li>
</ul>
I'm sure there are plenty others, but all those method involve re-doing grants and privileges for the new table. Each has a place though, depending upon the situation and environment.<br/><br/>
The downside to this is that once the table contains a SECUREFILE BINARY XML based XMLType column, you can no longer use DBMS_REDEFINITION as shown by
<div class="code"><pre>SQL> CREATE TABLE HOLDS_XML -- new table structure
2 (n_col NUMBER(5) NOT NULL PRIMARY KEY,
3 xml_col XMLTYPE)
4 XMLTYPE xml_col STORE AS SECUREFILE BINARY XML;
Table created
SQL> exec dbms_redefinition.can_redef_table( 'JASON', 'HOLDS_XML' );
begin dbms_redefinition.can_redef_table( 'JASON', 'HOLDS_XML' ); end;
ORA-12090: cannot online redefine table "JASON"."HOLDS_XML"
ORA-06512: at "SYS.DBMS_REDEFINITION", line 139
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1766
ORA-06512: at line 2
</pre></div>
The ORA-12090 means <blockquote>An attempt was made to online redefine a table that is either a clustered table, AQ table, temporary table, IOT overflow table or table with FGA/RLS enabled.</blockquote>
I searched on My Oracle Support but did not turn up any reason why a BINARY XML storage would cause this issue. Hopefully it is a limitation Oracle removes in the future.Jason Hhttp://www.blogger.com/profile/02172936625260505714noreply@blogger.com0tag:blogger.com,1999:blog-3851595309768128250.post-62842049025141618452012-05-10T15:31:00.000-06:002012-05-10T15:31:37.136-06:00I think this post <a href="https://forums.oracle.com/forums/thread.jspa?threadID=2387358&tstart=0">xml query hungs up with large xml response from utl_http request</a> on the OTN Forums and the response nicely sums up what I mentioned in my <a href="http://anononxml.blogspot.com/2011/09/options-for-slow-performance-parsing_21.html">Options for Slow Performance Parsing Large XML</a> series. If you want good performance for parsing large XML that is generated external to Oracle and you are on 11g then you need to INSERT the data into an XMLTYPE column stored as securefile binary xml and then query against that data.<br/>Jason Hhttp://www.blogger.com/profile/02172936625260505714noreply@blogger.com0tag:blogger.com,1999:blog-3851595309768128250.post-45189123508759514022012-05-10T15:10:00.000-06:002012-05-10T15:10:54.125-06:00XMLElement and namespacesThis post was inspired by the following thread in the OTN forum
<a href="https://forums.oracle.com/forums/thread.jspa?threadID=2386147&tstart=0">Formatting data to xml consumes more time</a>. I did some Google searching and did not turn up any decent hits on the topic (or my searching was bad for the day) so I decided to do a quick write up on it.<br/><br/>
The first place to start for learning how to generate XML data from an Oracle DB is of course the documentation. This can be found at <a href="http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb13gen.htm">Generating XML Data from the Database</a>.<br/><br/>
If you look through that document, there are not many references to namespaces and namespace prefixes. In fact, only Example 18-6 shows how to add a namespace to a node, but not how to assign a prefix to that node. I created the following example to show how to add a namespace and namespace prefix to nodes in XML.<br/>
<div class="code"><pre>SELECT XMLElement("t:global-instance",
XMLAttributes('your.namespace.uri.here' AS
"xmlns:t"),
XMLElement("t:child1",'c1'),
XMLForest('c2' AS "t:child2",
'c3' AS "t:child3"),
XMLElement(evalname('t1:' || dummy), 'c4')).getClobVal() rslt
FROM dual;</pre></div>which generates<div class="code"><pre><t:global-instance xmlns:t="http://your.namespace.here">
<t:child1>c1</t:child1>
<t:child2>c2</t:child2>
<t:child3>c3</t:child3>
<t1:X>c4</t1:X>
</t:global-instance></pre></div><br/>
That shows the basics for adding a prefix when using XMLElement and XMLForest. If you are using 10.2.0.3 or later, you can also use EvalName to generate the node name instead of hard-coding it as earlier versions required. In that case, I showed how to include the namespace prefix for EvalName as well. Yes it really is that simple. You just type in the prefix you want to use.Jason Hhttp://www.blogger.com/profile/02172936625260505714noreply@blogger.com0tag:blogger.com,1999:blog-3851595309768128250.post-56955928073380033442011-09-21T14:22:00.001-06:002011-09-21T14:24:59.867-06:00Options for Slow Performance Parsing Large XML - Part 3The final piece to this chapter. In my previous post <a href="http://anononxml.blogspot.com/2011/09/options-for-slow-performance-parsing.html">Options for Slow Performance Parsing Large XML - Part 2</a>, I showed how registering a schema and having that process create a table could be used to increase the parsing speed of XML compared to parsing that XML via PL/SQL. Now I'm going to look at whether using a table without an associated schema will make any difference on the parsing/inserting speeds.<br /><br />
I again used the following block of code for running my tests
<div class="code"><pre>declare
l_result_xml XMLTYPE;
l_ns_xml XMLTYPE;
l_start NUMBER;
l_end NUMBER;
begin
-- Build up the XML
-- Has 2100+ row nodes
...snipped...
l_start := dbms_utility.get_time;
INSERT INTO LOAD_TEMP_RESULT VALUES l_result_xml;
l_end := dbms_utility.get_time;
dbms_output.put_line('INSERT INTO TEMP TIME in sec: ' || to_char((l_end - l_start) / 100));
l_start := dbms_utility.get_time;
INSERT INTO import_queue
(...20 columns...)
SELECT CBIQ_ID_SEQ, CBRH_ID_SEQ,
...18 columns ...
FROM LOAD_TEMP_RESULT ltr,
XMLTABLE('/resultset/row'
PASSING ltr.object_value -- column alias name
COLUMNS
CBIQ_ID_SEQ NUMBER(11) PATH 'CBIQ_ID_SEQ',
CBRH_ID_SEQ NUMBER(11) PATH 'CBRH_ID_SEQ',
... 18 more columns ...);
l_end := dbms_utility.get_time;
dbms_output.put_line('INSERT INTO TIME in sec: ' || to_char((l_end - l_start) / 100));
rollback;
end;</pre></div><br />
The only difference I changed was the CREATE TABLE statement for LOAD_TEMP_RESULT. The following table summarizes the various CREATE TABLE statements I used and the resulting times, where TEMP TIME is the output for "INSERT INTO TEMP TIME in sec:" and TIME is the output for "INSERT INTO TIME in sec:"<br />
<table border="2" cellspacing="0" cellpadding="7">
<tr>
<th>DDL</th>
<th>TEMP TIME (sec)</th>
<th>TIME (sec)</th>
</tr>
<tr>
<td>CREATE TABLE load_temp_result OF XMLTYPE;</td>
<td>0.05</td>
<td>16</td>
</tr>
<tr>
<td>CREATE TABLE load_temp_result OF XMLTYPE XMLTYPE STORE AS BASICFILE CLOB;</td>
<td>0.09</td>
<td>15.82</td>
</tr>
<tr>
<td>CREATE TABLE load_temp_result OF XMLTYPE XMLTYPE STORE AS SECUREFILE CLOB;</td>
<td>2.36</td>
<td>15.46</td>
</tr>
<tr>
<td>CREATE TABLE load_temp_result OF XMLTYPE XMLTYPE STORE AS BASICFILE BINARY XML;</td>
<td>0.27</td>
<td>0.63</td>
</tr>
<tr>
<td>CREATE TABLE load_temp_result OF XMLTYPE XMLTYPE STORE AS SECUREFILE BINARY XML;</td>
<td>0.24</td>
<td>0.63</td>
</tr>
</table><br/>
As I am using 11.1.0.6, the default behavior for this version of Oracle is to store XMLTypes as a CLOB. This is confirmed by the fact that the first row (default storage) matches most closely with the second row (explicit storage). Based on the results, it is easy to see that even creating a XMLType table (or a XMLType column) where the XMLType is stored as BINARY XML is well worth looking at in terms of performance when validation is not needed.<br /><br />
I'm not going to go into a discussion on the differences between BASICFILE and SECUREFILE, but the highlights of what SECUREFILE offers can be found at <a href="http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28393/adlob_what.htm#BABBDGGJ">LOB Features Introduced in Oracle Database 11g Release 1</a>. Oracle recommends that SECUREFILE be used for as I have picked up from the <a href="https://forums.oracle.com/forums/forum.jspa?forumID=34">XML DB forum on OTN</a>.<br /><br />
To recap the results from the previous two threads on this topic, <br />
<ul>
<li>Using XMLTable in a SQL statement to parse a PL/SQL XMLType variable took 1032 seconds (16+ minutes).</li>
<li>Using PL/SQL to loop through each node in the XML and parse the information out and then INSERT that into a table took 4 seconds.</li>
<li>Using a registered schema and associated table took 0.5 seconds.</li>
</ul>
For the given test case I was using (approx 2100 row nodes in the XML) on the system I was using (11.1.0.6 on a RedHat OS), doing a schema registration that creates an associated table just edged out using a Binary XML XMLType based table at 0.87 seconds. This should be due to the additional information that Oracle knows about the XML based off the schema. As this was also the first version that BINARY XML existed in, it could be partially due to that fact as well. Maybe if I get access to a newer version of Oracle, I could test that theory.Jason Hhttp://www.blogger.com/profile/02172936625260505714noreply@blogger.com2tag:blogger.com,1999:blog-3851595309768128250.post-22867339979624479172011-09-16T15:50:00.001-06:002011-12-06T09:48:16.120-07:00Options for Slow Performance Parsing Large XML - Part 2As I covered previously in <a href="http://anononxml.blogspot.com/2011/05/options-for-slow-performance-parsing.html">Options for Slow Performance Parsing Large XML</a>, I was looking for a PL/SQL only solution given the business requirements to parse an XML with just over 2100 repeating nodes and insert information from each node into a table in the DB for additional processing. The first attempt using only XMLTable to parse the PL/SQL XMLType variable took over 1000 seconds (16+ minutes). The second attempt parsed the XML by looping through it in PL/SQL and using a FORALL to insert the data into the table. This took just over 4 seconds.<br />
<br />
That was fine for my situation, but what if you need something faster? As I previously said<br />
<i>One option is to register a schema in the database, create a table based off that schema, store the XML in there and parse it that way (if still needed).</i><br />
<br />
To verify the answer, I took the long way to get to the above just to see what happens. I started by creating a simple schema for the XML, doing a basic schema registration, converting the XML to a schema based XML and parsing that PL/SQL XMLType. The basics of that approach were<br />
<br />
<div class="code"><pre>DECLARE
l_schema CLOB;
BEGIN
l_schema := '<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb">
<xs:element name="resultset" type="resultSetType" xdb:defaultTable="LOAD_TEMP_RESULT"/>
<xs:complexType name="resultSetType">
<xs:sequence>
<xs:element name="row" type="rowType" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="rowType">
<xs:sequence>
...16 elements of xs:int, xs:string, xs:short, xs:date ...
</xs:sequence>
</xs:complexType>
</xs:schema>';
dbms_xmlSchema.registerSchema('blog_pref.xsd', l_schema, gentypes => false, gentables => false);
END;/
declare
l_result_xml XMLTYPE;
l_ns_xml XMLTYPE;
l_start NUMBER;
l_end NUMBER;
begin
-- Build up the XML
-- Has 2100+ row nodes
...snipped...
l_start := dbms_utility.get_time;
l_ns_xml := l_result_xml.createSchemaBasedXML('blog_pref.xsd');
INSERT INTO import_queue
(...20 columns...)
SELECT CBIQ_ID_SEQ, CBRH_ID_SEQ,
...18 columns ...
FROM XMLTABLE('/resultset/row'
PASSING l_ns_xml
COLUMNS
CBIQ_ID_SEQ NUMBER(11) PATH 'CBIQ_ID_SEQ',
CBRH_ID_SEQ NUMBER(11) PATH 'CBRH_ID_SEQ',
... 18 more columns ...);
l_end := dbms_utility.get_time;
dbms_output.put_line('INSERT INTO TIME in sec: ' || to_char((l_end - l_start) / 100));
rollback;
end;</pre></div>
The time for a run was<br />
<b>INSERT INTO TIME in sec: 1024.09</b><br /><br />
This was still 16+ minutes so no improvement at all. I wasn't expecting any improvement in overall time because <br />
A) The schema had been registered without creating any types or tables<br />
B) The code is still parsing a PL/SQL XMLType variable.<br /><br />
The only difference now is that the XML is associated to a schema but the object itself is still simply a PL/SQL XMLType variable. The next approach was to use the default schema registration. The only change to the above was to use<br />
<div class="code"><pre>dbms_xmlSchema.registerSchema('blog_pref.xsd', l_schema);</pre></div>
so that any needed tables/types would be generated. Running the above code to put the data into the import_queue table resulted in a run of<br />
<b>INSERT INTO TIME in sec: 1057.13</b><br /><br />
This was still 16+ minutes so no improvement at all. I wasn't expecting any improvement in overall time because I had only removed condition A) from above above and condition B) was still in effect.<br />
To remove condition B), I rewrote the code to insert into the table created during the registerSchema process and then read from that table using XMLTable to parse the XML. I used xdb:defaultTable="LOAD_TEMP_RESULT" in the schema to name the table that Oracle created. The code ended up looking like
<div class="code"><pre>declare
l_result_xml XMLTYPE;
l_ns_xml XMLTYPE;
l_start NUMBER;
l_end NUMBER;
begin
-- Build up the XML
-- Has 2100+ row nodes
...snipped...
l_start := dbms_utility.get_time;
INSERT INTO LOAD_TEMP_RESULT VALUES l_result_xml;
l_end := dbms_utility.get_time;
dbms_output.put_line('INSERT INTO TEMP TIME in sec: ' || to_char((l_end - l_start) / 100));
l_start := dbms_utility.get_time;
INSERT INTO import_queue
(...20 columns...)
SELECT CBIQ_ID_SEQ, CBRH_ID_SEQ,
...18 columns ...
FROM LOAD_TEMP_RESULT ltr,
XMLTABLE('/resultset/row'
PASSING ltr.object_value -- column alias name
COLUMNS
CBIQ_ID_SEQ NUMBER(11) PATH 'CBIQ_ID_SEQ',
CBRH_ID_SEQ NUMBER(11) PATH 'CBRH_ID_SEQ',
... 18 more columns ...);
l_end := dbms_utility.get_time;
dbms_output.put_line('INSERT INTO TIME in sec: ' || to_char((l_end - l_start) / 100));
rollback;
end;</pre></div>
The time for a run broke down as<br />
<b>INSERT INTO TEMP TIME in sec: .31<br />
INSERT INTO TIME in sec: .17</b><br /><br />
So the time Oracle spent parsing the XML into the temporary table and then retrieving the data from that and inserting it into the import_queue table was less than 0.5 seconds for 2100 rows. That was a very good improvement over 4 seconds, though it does require the registration of a schema and a table assocaited to that schema.<br />
Still to be covered was the other option I mentioned of<br />
<i>store the XML as binary XML (available in 11g).</i>
Hopefully I get to that soon.Jason Hhttp://www.blogger.com/profile/02172936625260505714noreply@blogger.com0tag:blogger.com,1999:blog-3851595309768128250.post-33628294671232870812011-06-30T11:52:00.002-06:002011-09-02T15:02:36.408-06:00XMLTable and outer join methodsWhile assisting with a question on the OTN forums <a href="https://forums.oracle.com/forums/thread.jspa?threadID=1074185&tstart=0">(Using XML clob in loop)</a><br />
<br />
I had to pause briefly to remember how to do outer joins when passing optional (aka child may not exist) information from one XMLTable to another XMLTable.<br />
<br />
The syntax I first used was the proprietary Oracle method of using (+). So the FROM clause looked something like<br />
<pre>XMLTABLE(...COLUMNS ... xmlfrag XMLTYPE PATH ... ) x,
XMLTABLE(... PASSING x.xmlfrag ...) (+) y</pre>
So now the query returns data from "x" even in there was no data in "y". Then I got to wondering what the ANSI standard for writing that XMLTable join would be. Turns out it is just a simple<br />
<pre>XMLTABLE(...COLUMNS ... xmlfrag XMLTYPE PATH ... ) x
LEFT OUTER JOIN
XMLTABLE(... PASSING x.xmlfrag ...) y
ON 1=1</pre>
The ON clause exists because some type of join condition is required for a LEFT/RIGHT OUTER JOIN. Normally a 1=1 would cause a Cartesian join between the two tables but in this case it doesn't because the join conditions are already defined as the second XMLTable is joined to the first XMLTable via the PASSING clause.<br />
<br />
Pretty simple.
<br /><br />
* <b>Caution</b> *<br />
Be wary when using the ANSI JOIN syntax on older versions of Oracle. Here is the reason why <a href="https://forums.oracle.com/forums/thread.jspa?threadID=2277218&tstart=0">Problem with XMLTABLE and LEFT OUTER JOIN </a>. I know I've seen talk/blogs regarding bugs with Oracle's implementation of the ANSI JOIN syntax in certain situations. As with any SQL statement, you always need to verify your output.Jason Hhttp://www.blogger.com/profile/02172936625260505714noreply@blogger.com0tag:blogger.com,1999:blog-3851595309768128250.post-54155472016502268702011-05-18T10:52:00.006-06:002011-09-13T14:28:11.925-06:00Options for Slow Performance Parsing Large XMLFirst let me state that this is a situational only solution and not a general solution to all performance problems when parsing large XML and inserting the data into the DB. I was brought onto a project to code the PL/SQL portion of a business process that would be kicked off a few times a year via a screen. This process would call a remote web service to see if a set of data had been updated. If that data had been updated since the last time, the code would call the web service again to retrieve all the information in XML format. This information would be parsed and stored into a table in the DB and a few automatic rules would be applied to update the data before the process finished and the user would need to perform manual steps to finish the larger process.<br /><br />
The XML that was being returned from the web service was in a simple format of
<div class="code"><pre><resultset>
<row>
...up to 16 nodes...
</row>
<row>
...up to 16 nodes...
</row>
</resultset></pre></div><br />
There were some mandatory nodes and many optional nodes. At the time of testing, there were 2,183 Row nodes being returned. The coding/testing was being done on 11.1.0.6.<br /><br />
Being lazy, I went the simple XMLTable method for parsing the XML and storing it into the table. This was done via the basic
<div class="code"><pre><span style="font-weight: bold;">INSERT INTO ...
SELECT ...
FROM XMLTABLE(... PASSING l_result_xml ...);</span></pre></div>
where l_result_xml was defined as an XMLType.<br /><br />
During my testing, the total run time of this was taking 1459 seconds. While this was a seldom run process, it was still too slow to give to the client. Given that this PL/SQL code included a web service call, a MERGE and an UPDATE statement, I wasn't sure where the slowdown was so I used<br />
<span style="font-weight: bold;">dbms_utility.get_time;</span><br /><br />
to get some basic timing information. The time for a run broke down as<br /><br />
<span style="font-weight: bold;">HTTP TIME in sec: 30.88<br />INSERT INTO TIME in sec: 1031.99<br />MERGE TIME in sec: 0.03<br />UPDATE TIME in sec: 0.15</span><br /><br />
The time spent dealing with the web service seemed reasonable and the MERGE/UPDATE time were great given the amount of SQL involved in them. The above INSERT INTO was killing performance though. Having a really good guess, I did an explain plan on the SQL statement and saw the dreaded<br />
<span style="font-weight: bold;">COLLECTION ITERATOR PICKLER FETCH</span><br /><br />
in the explain plan. All those memory operations on the XML were killing performance as expected.<br /><br />
For several reasons, registering a schema was not high on the list of options for better performance. My next approach was then to manually parse the XML in PL/SQL into memory and then use a FORALL to store that into the database. For that approach I setup a structure like (simplified for posting)
<div class="code"><pre>TYPE import_q_rec IS RECORD (statute dbms_sql.Varchar2_Table,
end_dt dbms_sql.Date_Table);
l_import_q_tab import_q_rec;
l_result_xml XMLTYPE;
WHILE l_result_xml.Existsnode('/ResultSet/Row[' || To_Char(l_row_index) || ']') > 0
LOOP
l_temp_nd := l_result_xml.Extract('/ResultSet/Row[' || To_Char(l_row_index) || ']');
l_import_q_tab.statute(l_row_index) := f_extractxmltypestrval(l_temp_nd, '/Row/STATUTE/text()', NULL);
l_row_index := l_row_index + 1;
END LOOP;
FORALL i IN 1..l_row_index-1
INSERT INTO import_queue
VALUES
(l_import_q_tab.statute(i), l_import_q_tab.descr(i));</pre></div><br />
This structure loops through each Row node in the XML and parses out the children nodes that exist. As some nodes were optional, the hidden logic within f_extractxmltypestrval is used to not throw errors if the desired node did not exist.<br /><br />
Once all this information was parsed and loaded into memory, the FORALL would store it into the table.<br /><br />
The first clean run through the code showed it was noticeably faster as the total run time was 37 seconds. The time for a run broke down as<br /><br />
<span style="font-weight: bold;">HTTP TIME in sec: 32.43<br />PARSE XML TIME in sec: 3.59<br />FORALL TIME in sec: 0.47<br />MERGE TIME in sec: 0.06<br />UPDATE TIME in sec: 0.27</span><br /><br />
So the time spent parsing the XML and storing it into the table for the first run was nearly 1032 seconds and the above way took approximately 4 seconds. That was a good enough in terms of performance in regards to run frequency to call this method good.<br /><br />
So what did this show? When Oracle has to parse large amounts of XML that is stored as a CLOB or a PL/SQL XMLType variable, performance will not be great (at best). This is confirmed by Mark Drake via <a href="http://forums.oracle.com/forums/thread.jspa?threadID=2163750&tstart=0">this XMLDB thread</a>. If you are looking for performance increases, then you have to go other options. The above is one option that worked good for the situation at hand. One option is to register a schema in the database, create a table based off that schema, store the XML in there and parse it that way (if still needed). A second option would be to store the XML as binary XML (available in 11g). How those compare to the pure PL/SQL approach is a good question and hopefully I get around to looking into those soon.<br /><br />
Edit: June 9, 2011<br />
As stated above, my testing was done on 11.1.0.6. In <a href="http://forums.oracle.com/forums/thread.jspa?threadID=2234474&tstart=0">this OTN thread</a>, see the answer from Mark (mdrake) regarding some internal performance changes made in 11.1.0.7 that would alter my original results. It should make the results faster so I'd be interested to see how much faster.Jason Hhttp://www.blogger.com/profile/02172936625260505714noreply@blogger.com1tag:blogger.com,1999:blog-3851595309768128250.post-44509898490228850082011-01-13T13:25:00.005-07:002011-01-13T15:55:09.269-07:00Memory Leaks, part 2While keeping up on the OTN forums, I saw one post that was working with DOMDocuments and used .freeNode. Having a system in production that doesn't use .freeNode, I wondered whether this was part of the known small memory leak that an OS session has over time. So I went back to my previous post of <a href="http://anononxml.blogspot.com/2010/09/memory-leaks.html">Memory Leaks</a> and revisited and expanded it to see whether not using .freeNode was causing memory leaks.<br /><br /><span style="font-weight:bold;">Systems:</span><br />The following was tested on two different machines. Both were running the same version of Redhat Linux. The older machine was 10.2.0.4 and 4 Gigs of memory. The newer machine has 11.1.0.6 and 8 Gigs of memory. The processor is better of course too.<br /><br /><span style="font-weight:bold;">Test procedure:</span><br />I ran the following updated script three times on both systems. On the older system, I ran it 300,000 times. On the newer system, I ran it 600,000 times (which still ran faster than the older system). You can see by the comments what two lines of code I added in run #2 and the one line of code I added in run #3. After doing each run, I would close that window/session and start a new one. I'm using PL/SQL Developer and have it setup so each window runs as a separate database session.<br /><br /><div class="code"><pre>DECLARE <br /> l_dom_doc dbms_xmldom.DOMDocument;<br /> l_node dbms_xmldom.domnode; -- added run 2<br /><br /> PROCEDURE p_local_open_close IS<br /> BEGIN<br /> l_dom_doc := dbms_xmldom.newDOMDocument;<br /> l_node := dbms_xmldom.makenode(dbms_xmldom.createelement(l_domdoc, 'root')); -- added run 2<br /> dbms_xmldom.freeNode(l_node); -- added run 3<br /> dbms_xmldom.FreeDocument(l_dom_doc);<br /> END p_local_open_close;<br /><br />BEGIN<br /> DBMS_APPLICATION_INFO.SET_MODULE(module_name => 'user_domDocument_mem_leak_test',<br /> action_name => 'SLEEPING');<br /> dbms_lock.sleep(20);<br /> /* Sleeps so have time to find OS PID via<br /> select s.sid, s.process, s.serial#, s.module, s.action, p.spid os_pid<br /> from gv$session s,<br /> gv$process p<br /> where s.module = 'user_domDocument_mem_leak_test'<br /> and s.paddr = p.addr;<br /> */<br /> dbms_application_info.set_action('RUNNING');<br /> FOR i IN 1..300000 -- adjust as needed for your system<br /> LOOP<br /> p_local_open_close;<br /> END LOOP;<br /> dbms_application_info.set_action('COMPLETE');<br /> <br />END;</pre></div><br /><br />To determine memory usage, I was using top to see what the VIRT (total virtual memory) for the OS session was. The SQL in the script shows how to get the OS session based on the Oracle session that was running. If your version of top doesn't support top -p <pid_value>, another way as my coworker turned up is ps -opid,vsz -p <pid_value>. This shows the PID and VSZ (Virtual Size) columns. I'm sure there are other ways too.<br /><br /><span style="font-weight:bold;">Run results:</span><br />Runs #2 and #3 were identical in memory usage to run #1. For the 10.2.0.4 system, VIRT would start out at/around 438m and increase to 470m after the run. For the 11.1.0.6 system, memory would start at 1181m and stay there.<br /><br /><span style="font-weight:bold;">Conclusion:</span><br />It would appear that using dbms_xmldom.freeNode is completely optional as Oracle handles the memory from this correctly. This is determined by the fact that memory usage from the first run, which had no dbms_xmldom.domnode in it, was exactly the same as the second and third runs.<br /><br /><span style="font-weight:bold;">Additional observation:</span><br />I was expecting that memory usage on the 10.2.0.4 system to remain a constant like it did on the 11.1.0.6 system. As my previous post said, the fix was applied to a 10.2.0.3 system and has since been upgraded to 10.2.0.4. As 10.2.0.4 was released sometime in early 2008 (or so it appears) it is highly possible the patch applied to 10.2.0.3 was not part of the 10.2.0.4 release. Looks like I will have to verify this on the client's system and see about getting this fixed if it still occurs on their system as well.Jason Hhttp://www.blogger.com/profile/02172936625260505714noreply@blogger.com0tag:blogger.com,1999:blog-3851595309768128250.post-17229797300546994062010-12-30T11:33:00.002-07:002010-12-30T12:01:10.339-07:00Methods to parse XML per Oracle versionOne of the items I've noted while hanging out in the <a href="http://forums.oracle.com/forums/forum.jspa?forumID=34">XML DB forum</a> or the general <a href="http://forums.oracle.com/forums/category.jspa?categoryID=51&start=0">XML forum</a> is that many people are still using the old extract and extractValue methods to parse XML via SQL statements.<br /><br />Starting with 11.2, Oracle has deprecated <a href="http://download.oracle.com/docs/cd/E14072_01/server.112/e10592/functions058.htm">extract</a> and <a href="http://download.oracle.com/docs/cd/E14072_01/server.112/e10592/functions059.htm">extractValue</a>. As you can see from the Oracle documentation, Oracle suggest you use XMLQuery in place of extract and either XMLTable or XMLCast/XMLQeury in place of extractValue.<br /><br />So what method should you use in SQL to parse XML? It depends upon your version of Oracle of course.<br /><br /><span style="font-weight: bold;">Oracle version: 8i - 9.0.x.x</span><br />There was no option that I can recall or could find. All the parsing of XML that I've done in 8i was via the xmldom package.<br /><br /><span style="font-weight: bold;">Oracle version: 9.2.x.x - 10.1.x.x</span><br />This is were Oracle introduced extract, extractValue and TABLE(XMLSequence(extract())) for dealing with repeating nodes.<br /><br /><span style="font-weight: bold;">Oracle version: 10.2.x.x</span><br />Oracle introduced <a href="http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions228.htm#SQLRF06232">XMLTable</a> as a replacement for the previous methods since it could handle all three methods for extracting data from XML. At that point, Oracle stopped enhancing extract/extractValue in terms of performance and focused on XMLTable. In 10.2.0.1 and .2, XMLTable was implemented via Java and in .3 it was moved into the kernel so performance from .3 onwards should be better than the older 9.2 / 10.1 methods. If not, feel free to open a ticket with Oracle support. Apparently Oracle also introduced <a href="http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions224.htm#SQLRF06209">XMLQuery</a> as well but I've never heard of many using that in 10.2<br /><br /><span style="font-weight: bold;">Oracle version: 11.1.x.x - 11.2.x.x</span><br />Oracle still has XMLTable and XMLQuery as I pointed out above, but also added in <a href="http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/functions224.htm#SQLRF20012">XMLCast</a> as a way to cast the output of XMLQuery into a desired datatype.<br /><br />So when coding, please try to pick the parsing approach that works with the version of Oracle being used. It's good for your job skills and Oracle provides better support for current functionality than deprecated functionality.Jason Hhttp://www.blogger.com/profile/02172936625260505714noreply@blogger.com0tag:blogger.com,1999:blog-3851595309768128250.post-80497905671080143582010-11-12T14:29:00.000-07:002011-09-02T14:50:50.308-06:00Text Literal StructureIn a previous post on <a href="http://anononxml.blogspot.com/2010/09/xmltypexmltransform-and-parameters.html">XMLType/XMLTransform and parameters</a>, I'd discovered the q'{}' structure from the OTN forum post, but I didn't know much more about it. I wasn't sure what to go searching for so I left it as an interesting tidbit to figure out later.<br />
<br />
Just about two months after that post (on Nov 4, 2010 to be exact), I encountered this syntax structure in a <a href="http://www.plsqlchallenge.com/">PL/SQL Challenge</a> quiz. To see the actual quiz, you will need to register (free) on the site. If you are into learning more about the database from the SQL and PL/SQL side of things in a simple quiz that takes a few minutes of time each day, I strongly suggest signing up and participating. You can find his blog discussion of this particular quiz at <a href="http://plsql-challenge.blogspot.com/2010/11/quote-character-q-and-4-november-quiz.html">The quote character (q) and the 4 November quiz</a>.<br />
<br />
Using his wording as a starting point to search the Oracle documentation, I soon came across <a href="http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements003.htm#SQLRF00218">Text Literals</a>. Somewhere along the way I saw that this was a 10g (10.1 I believe) addition. I went from 8.1 to 10.2, so it explains why I missed it.<br />
<br />
It's pretty simple to use. Ignoring the national character version, you start with<br />
q''<br />
Within that, you put your delimiters. As the documentation says,<br />
<span style="font-style: italic;">If the opening quote_delimiter is one of [, {, <, or (, then the closing quote_delimiter must be the corresponding ], }, >, or ). In all other cases, the opening and closing quote_delimiter must be the same character.</span><br />
so we can setup structures such as
<ul>
<li> q'[]'</li>
<li>q'<>'</li>
<li>q'##'</li>
</ul>
and within our quote delimiter, we simply put the desired text. Some examples:<br />
<div class="code"><pre>BEGIN
dbms_output.put_line(q'{Here be the text with a ' in it}');
dbms_output.put_line(q'*Here be the text with a ' in it*');
dbms_output.put_line(q'#Random words put here#');
dbms_output.put_line(q'^Here be the text with a ' in it^');
END;</pre></div>
<br />
which produces<br />
<div class="code"><pre>Here be the text with a ' in it
Here be the text with a ' in it
Random words put here
Here be the text with a ' in it</pre></div>
<br />
Makes it a lot easier to put a single quote into a string than using double single quotes.<br />
<br />
* <b>Caveat</b> *<br />
Apparently the Text Literal process has issues with strings that contain 'n', where n' is at the end of the string. By that I mean the following will all run successfully<div class="code"><pre>select q'[help'n ']'
from dual;
select q'['na']'
from dual;
select q'['a']'
from dual;</pre></div>but the following all return an ORA-01756: quoted string not properly terminated
<div class="code"><pre>select q'[help'n']'
from dual;
select q'['an']'
from dual;
select q'['n']'
from dual;
</pre></div>
I've seen this error on both 10.2.0.4 and 11.1.0.6. I can't find anything in MOS yet on it.Jason Hhttp://www.blogger.com/profile/02172936625260505714noreply@blogger.com0tag:blogger.com,1999:blog-3851595309768128250.post-78917346622348062092010-10-22T14:06:00.003-06:002010-10-22T14:55:27.228-06:00XMLTable with a ParameterI found this question on the OTN forums, <a href="http://forums.oracle.com/forums/thread.jspa?threadID=1773157&tstart=0">Problem with XMLTABLE with parameters</a>, interesting because<br />a) I knew there was a way to accomplish that<br />b) I couldn't quickly turn it up via Google till I got the right keywords.<br /><br />After seeing the solution that I ended up borrowing, I first checked the Oracle documentation on <a href="http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions228.htm#SQLRF06232">XMLTable</a> for 10.2 to verify this was valid. As you can see by the documentation, the XML_passing_clause does allow for multiple expressions.<br /><br />Curious as to see how this worked for different data types and on 10g and 11g, I created the following setup.<br /><br /><div class="code"><pre>Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0<br /><br />SQL><br />SQL> CREATE TABLE XMLT<br /> 2 (xmlcol XMLType)<br /> 3 XMLTYPE column xmlcol store AS BINARY XML;<br /><br />Table created<br /><br />SQL><br />SQL> INSERT INTO XMLT<br /> 2 VALUES<br /> 3 ('<Root><br /> 4 <Object><br /> 5 <ObjectID>ID_1</ObjectID><br /> 6 <ObjectName>Name 1</ObjectName><br /> 7 <ObjectValue>1</ObjectValue><br /> 8 <ObjectDate>1980-01-01</ObjectDate><br /> 9 </Object><br />10 <Object><br />11 <ObjectID>ID_2</ObjectID><br />12 <ObjectName>Name 2</ObjectName><br />13 <ObjectValue>2</ObjectValue><br />14 </Object><br />15 </Root>');<br /><br />1 row inserted<br /><br />SQL> commit;<br /><br />Commit complete</pre></div><br />sys_xmlgen works on both 10g and 11g for these scenarios. With this, everything is treated as a simple string so you just need to ensure the string you pass in matches up to the string in the XML.<br /><div class="code"><pre>SQL> SELECT xt.ObjID, xt.ObjName<br /> 2 FROM XMLT,<br /> 3 XMLTABLE('/Root/Object[ObjectID=$objID]'<br /> 4 PASSING XMLT.xmlcol,<br /> 5 sys_xmlgen('ID_1') as "objID"<br /> 6 COLUMNS<br /> 7 ObjID VARCHAR2(6) PATH 'ObjectID',<br /> 8 ObjName VARCHAR2(10) PATH 'ObjectName') xt;<br /><br />OBJID OBJNAME<br />------ ----------<br />ID_1 Name 1<br /><br />SQL> SELECT xt.ObjID, xt.ObjName<br /> 2 FROM XMLT,<br /> 3 XMLTABLE('/Root/Object[ObjectValue=$objVal]'<br /> 4 PASSING XMLT.xmlcol,<br /> 5 sys_xmlgen('2') as "objVal"<br /> 6 COLUMNS<br /> 7 ObjID VARCHAR2(6) PATH 'ObjectID',<br /> 8 ObjName VARCHAR2(10) PATH 'ObjectName') xt;<br /><br />OBJID OBJNAME<br />------ ----------<br />ID_2 Name 2<br /><br />SQL> SELECT xt.ObjID, xt.ObjName<br /> 2 FROM XMLT,<br /> 3 XMLTABLE('/Root/Object[ObjectDate=$objDt]'<br /> 4 PASSING XMLT.xmlcol,<br /> 5 sys_xmlgen('1980-01-01') as "objDt"<br /> 6 COLUMNS<br /> 7 ObjID VARCHAR2(6) PATH 'ObjectID',<br /> 8 ObjName VARCHAR2(10) PATH 'ObjectName') xt;<br /><br />OBJID OBJNAME<br />------ ----------<br />ID_1 Name 1</pre></div><br />Another way to do it in 11g is to use CAST to convert a string to the appropriate data type. I go overboard with the Date related queries. I will explain afterwards.<br /><div class="code"><pre>SQL> SELECT xt.ObjID, xt.ObjName<br /> 2 FROM XMLT,<br /> 3 XMLTABLE('/Root/Object[ObjectID=$objID]'<br /> 4 PASSING XMLT.xmlcol,<br /> 5 cast('ID_1' AS VARCHAR2(4)) as "objID"<br /> 6 COLUMNS<br /> 7 ObjID VARCHAR2(6) PATH 'ObjectID',<br /> 8 ObjName VARCHAR2(10) PATH 'ObjectName') xt;<br /><br />OBJID OBJNAME<br />------ ----------<br />ID_1 Name 1<br /><br />SQL> SELECT xt.ObjID, xt.ObjName<br /> 2 FROM XMLT,<br /> 3 XMLTABLE('/Root/Object[ObjectValue=$objVal]'<br /> 4 PASSING XMLT.xmlcol,<br /> 5 CAST('2' AS NUMBER) as "objVal"<br /> 6 COLUMNS<br /> 7 ObjID VARCHAR2(6) PATH 'ObjectID',<br /> 8 ObjName VARCHAR2(10) PATH 'ObjectName') xt;<br /><br />OBJID OBJNAME<br />------ ----------<br />ID_2 Name 2<br /><br />SQL> SELECT xt.ObjID, xt.ObjName<br /> 2 FROM XMLT,<br /> 3 XMLTABLE('/Root/Object[ObjectDate=$objDt]'<br /> 4 PASSING XMLT.xmlcol,<br /> 5 CAST('01-JAN-1980' AS DATE) as "objDt"<br /> 6 COLUMNS<br /> 7 ObjID VARCHAR2(6) PATH 'ObjectID',<br /> 8 ObjName VARCHAR2(10) PATH 'ObjectName') xt;<br /><br />OBJID OBJNAME<br />------ ----------<br />ID_1 Name 1<br /><br />SQL> SELECT xt.ObjID, xt.ObjName<br /> 2 FROM XMLT,<br /> 3 XMLTABLE('/Root/Object[ObjectDate=$objDt]'<br /> 4 PASSING XMLT.xmlcol,<br /> 5 CAST(DATE '1980-01-01' AS DATE) as "objDt"<br /> 6 COLUMNS<br /> 7 ObjID VARCHAR2(6) PATH 'ObjectID',<br /> 8 ObjName VARCHAR2(10) PATH 'ObjectName') xt;<br /><br />OBJID OBJNAME<br />------ ----------<br />ID_1 Name 1<br /><br />SQL> SELECT xt.ObjID, xt.ObjName<br /> 2 FROM XMLT,<br /> 3 XMLTABLE('/Root/Object[ObjectDate=$objDt]'<br /> 4 PASSING XMLT.xmlcol,<br /> 5 DATE '1980-01-01' as "objDt"<br /> 6 COLUMNS<br /> 7 ObjID VARCHAR2(6) PATH 'ObjectID',<br /> 8 ObjName VARCHAR2(10) PATH 'ObjectName') xt;<br /><br />OBJID OBJNAME<br />------ ----------<br />ID_1 Name 1<br /><br />SQL> SELECT xt.ObjID, xt.ObjName<br /> 2 FROM XMLT,<br /> 3 XMLTABLE('/Root/Object[ObjectDate=$objDt]'<br /> 4 PASSING XMLT.xmlcol,<br /> 5 TO_DATE('01-01-1980', 'MM-DD-YYYY') as "objDt"<br /> 6 COLUMNS<br /> 7 ObjID VARCHAR2(6) PATH 'ObjectID',<br /> 8 ObjName VARCHAR2(10) PATH 'ObjectName') xt;<br /><br />OBJID OBJNAME<br />------ ----------<br />ID_1 Name 1<br /><br />SQL> SELECT xt.ObjID, xt.ObjName<br /> 2 FROM XMLT,<br /> 3 XMLTABLE('/Root/Object[ObjectDate=$objDt]'<br /> 4 PASSING XMLT.xmlcol,<br /> 5 '1980-01-01' as "objDt"<br /> 6 COLUMNS<br /> 7 ObjID VARCHAR2(6) PATH 'ObjectID',<br /> 8 ObjName VARCHAR2(10) PATH 'ObjectName') xt;<br /><br />OBJID OBJNAME<br />------ ----------<br />ID_1 Name 1<br /><br />SQL> SELECT xt.ObjID, xt.ObjName<br /> 2 FROM XMLT,<br /> 3 XMLTABLE('/Root/Object[ObjectDate=$objDt]'<br /> 4 PASSING XMLT.xmlcol,<br /> 5 TO_DATE('01-01-1980', 'MM-DD-YYYY') as "objDt"<br /> 6 COLUMNS<br /> 7 ObjID VARCHAR2(6) PATH 'ObjectID',<br /> 8 ObjName VARCHAR2(10) PATH 'ObjectName') xt;<br /><br />OBJID OBJNAME<br />------ ----------<br />ID_1 Name 1</pre></div><br />With my setup, Oracle knows nothing about the XML in terms of data types so 2 and 1980-01-01 are simply strings to it. So, even though I was CASTing a string to a DATE, Oracle is converting the value back to a string during the XPath evaluation. (Note: This is not confirmed but highly suspected).<br /><br />So why is using CAST good? It comes in useful when you have a schema registered within Oracle and the XMLType is based off of the schema. This provides Oracle with data type information and is especially true for Object Relational Storage where it is used to create data types for columns. Oracle knows the data type of the node so you want to pass in the same data type using an explicit conversion. This is the basic reasoning why you don't want to compare a number to a string. Oracle will implicitly convert one to the other and can cause issues it if encounters a string with "a1" in it that fails to convert to a number.. That is the purpose of CAST, so you can tell Oracle that a given parameter is a number or a date to avoid the implicit conversion.<br /><br />Gotchas<br /><ul><li>Running the above XMLTable with CAST SQL Statements will result in an ORA000932: inconsistent datatypes: expected - got <whatever><br /></li></ul>Jason Hhttp://www.blogger.com/profile/02172936625260505714noreply@blogger.com0tag:blogger.com,1999:blog-3851595309768128250.post-26436210298397839282010-09-20T14:18:00.002-06:002010-09-20T14:33:55.540-06:00Creating XML via the DBJust some ways to produce XML from an Oracle DB using only SQL and PL/SQL. I'm sure this list is incomplete so I will expand it as I remember others. This is just what was coming to mind before writing this entry<br /><ul><li>SQL/XML (XMLElement, XMLForest, XMLAgg)</li><li>DBMS_XMLDOM</li><li>XQUERY</li><li>DBMS_XMLGEN</li><li>DBMS_XMLQuery</li><li>TRANSFORM/XMLTransform</li><li>CreateXML/XMLType</li></ul><br />Basically this serves as a reminder of what to cover in the future.Jason Hhttp://www.blogger.com/profile/02172936625260505714noreply@blogger.com0tag:blogger.com,1999:blog-3851595309768128250.post-68561290654945319352010-09-13T10:43:00.002-06:002010-09-13T12:01:39.112-06:00XMLType/XMLTransform and parametersWhile spending time in the OTN XML DB forums, I ran across yet another thread where I learned something new. The thread was <a href="http://forums.oracle.com/forums/thread.jspa?threadID=1124243&tstart=0">Add Namespaces via XQuery to an XML Instance </a>. Besides relearning I still have a lot to learn about XQuery, I learned that the XMLTransform SQL operator accepts a third parm. This makes sense given the two operations, the PL/SQL .transform and SQL XMLTransform, perform the same task. If you look at the 11.2 documentation for <a href="http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/functions254.htm#SQLRF06171">XMLTransformation</a>, it still shows it accepts only two parms. The <a href="http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/t_xml.htm#i1009783">XMLType.Transform</a> function shows a third parm, parammap, which is a VARCHAR2.<br /><br />Given my lack of understanding of the third parm in general, I decided to research it. From what little I was able to turn up, it is described in more detail in Metalink. I am currently without Metalink access so I'm left to wonder as well what is said. I was able to piece together the following examples of a few ways to send in multiple parms.<br /><div class="code"><pre>Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 <br /> <br />SQL> set serveroutput on;<br />SQL> <br />SQL> DECLARE<br /> 2 l_xml XMLTYPE := XMLTYPE('<root/>');<br /> 3 l_xsd XMLTYPE;<br /> 4 l_new_xml XMLTYPE;<br /> 5 l_val1 VARCHAR2(5) := 'val1';<br /> 6 l_val2 VARCHAR2(5) := 'val 2';<br /> 7 BEGIN<br /> 8 l_xsd := XMLTYPE('<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" <br /> xmlns:fo="http://www.w3.org/1999/XSL/Format" <br /> exclude-result-prefixes="fo"><br /> 9 <xsl:output method="xml"/><br /> 10 <br /> 11 <xsl:param name="par1"/><br /> 12 <xsl:param name="par2"/><br /> 13 <br /> 14 <xsl:template match="/"><br /> 15 <output><br /> 16 <param1><xsl:value-of select="$par1"/></param1><br /> 17 <param2><xsl:value-of select="$par2"/></param2><br /> 18 </output><br /> 19 </xsl:template><br /> 20 </xsl:stylesheet>');<br /> 21 <br /> 22 dbms_output.put_line('#1'); -- hard coded strings<br /> 23 l_new_xml := l_xml.transform(l_xsd, 'par1="''val1''" par2="''val 2''"');<br /> 24 dbms_output.put_line(l_new_xml.getStringVal());<br /> 25 <br /> 26 dbms_output.put_line('#2'); -- hard coded strings<br /> 27 l_new_xml := l_xml.transform(l_xsd, q'{par1="'val1'" par2="'val 2'"}');<br /> 28 dbms_output.put_line(l_new_xml.getStringVal());<br /> 29 <br /> 30 dbms_output.put_line('#3'); -- passing in variables<br /> 31 l_new_xml := l_xml.transform(l_xsd, 'par1="''' || l_val1 || '''" par2="''' || l_val2 || '''"');<br /> 32 dbms_output.put_line(l_new_xml.getStringVal());<br /> 33 <br /> 34 dbms_output.put_line('#4'); -- hard coded string/number<br /> 35 l_new_xml := l_xml.transform(l_xsd, 'par1="''val1''" par2="2"');<br /> 36 dbms_output.put_line(l_new_xml.getStringVal());<br /> 37 <br /> 38 dbms_output.put_line('#5'); -- hard coded string/number<br /> 39 l_new_xml := l_xml.transform(l_xsd, q'{par1="'val1'" par2="2"}');<br /> 40 dbms_output.put_line(l_new_xml.getStringVal());<br /> 41 END;<br /> 42 /<br /> <br />#1<br /><output><br /><param1>val1</param1><br /><param2>val 2</param2><br /></output><br /><br />#2<br /><output><br /><param1>val1</param1><br /><param2>val 2</param2><br /></output><br /><br />#3<br /><output><br /><param1>val1</param1><br /><param2>val 2</param2><br /></output><br /><br />#4<br /><output><br /><param1>val1</param1><br /><param2>2</param2><br /></output><br /><br />#5<br /><output><br /><param1>val1</param1><br /><param2>2</param2><br /></output><br /> <br />PL/SQL procedure successfully completed</pre></div><br />As the Oracle documentation says, the parameters are a "string of name=value pairs". What the online documentation leaves out are examples regarding usage.<br /><br />So what can we learn from the working examples above?<br /><ul><br /><li/>If the value is a string, the right side formatted is as "'value'". Note it is wrapped with a leading/trailing '. Depending upon how you setup your parameter, this may mean the use of two single quotes to insert one single quote in the resolved string.<br /><li/>If the value is a number, the right side formatted is as "value". No leading/trailing ' is needed. You can treat the number as a string but then the above rule applies.<br /><li/>Spaces are allowed within a value.<br /><li/>Everything can be treated as a string or number that is being passed into the .xsl<br /><li/>A space, one or more, is used to separate pairs.<br /><li/>The common error reported when not following the above noticed rules was an "ORA-31020: The operation is not allowed, Reason: Invalid XSL Parameter or its Value". I did see a few others, including killing my session as the OTN forum thread noted when randomly throwing data in the third parm.<br /></ul>I'm not sure where the q'{}' structure comes from but it means no need for putting double single quotes around the string values as shown in #2 and #5.<br /><br />As that forum post shows, and the below example as well, you can pass the third parameter into XMLTransform and it will work appropriately.<br /><div class="code"><pre>SQL> SELECT XMLSerialize(DOCUMENT<br /> 2 XMLTransform(XMLTYPE('<root/>'),<br /> 3 XMLTYPE('<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"<br /> 4 xmlns:fo="http://www.w3.org/1999/XSL/Format"<br /> 5 exclude-result-prefixes="fo"><br /> 6 <xsl:output method="xml"/><br /> 7 <br /> 8 <xsl:param name="par1"/><br /> 9 <xsl:param name="par2"/><br /> 10 <br /> 11 <xsl:template match="/"><br /> 12 <output><br /> 13 <param1><xsl:value-of select="$par1"/></param1><br /> 14 <param2><xsl:value-of select="$par2"/></param2><br /> 15 </output><br /> 16 </xsl:template><br /> 17 </xsl:stylesheet>'),<br /> 18 'par1="''val1''" par2="2"')<br /> 19 AS CLOB INDENT) xsl_output<br /> 20 FROM dual;<br /> <br />XSL_OUTPUT<br />--------------------------------------------------------------------------------<br /><?xml version="1.0" encoding="ISO-8859-1"?><br /><output><br /> <param1>val1</param1><br /> <param2>2</param2><br /></output></pre></div><br />You can replace the third parameter with any of the examples shown above, assuming you setup variable for #3, and the results will be identical to that shown above. Note: I simply used XMLSerialize to format the output similar to how the PL/SQL code formatted it.<br /><br />So now we know the basics for how to pass parameters to stylesheet and that you can do it with XMLTransform as well, even though the documentation doesn't specify it.Jason Hhttp://www.blogger.com/profile/02172936625260505714noreply@blogger.com1tag:blogger.com,1999:blog-3851595309768128250.post-73397186626910384312010-09-03T15:00:00.004-06:002011-01-13T15:31:19.894-07:00Memory LeaksThe thought for this posting has been in my head for a while and then this week this post came up on the OTN XML DB Forums ... <a href="http://forums.oracle.com/forums/thread.jspa?threadID=1122322&tstart=0">XMLTYPE.createxml - how to free memory?</a><br /><br />I knew when working with DOMDocuments that there was a .FreeDocument() but I didn't know of a similar concept for XMLType. From the testing I did given what the poster was seeing, it looks to be a bug only when using a sys_refcursor as input when doing an XMLTYPE conversion. It could impact BFILENAME as well but I need to get around to testing that at some point.<br /><br />This brought back memories from a few years back of when I stumbled across a memory leak in the code one of our clients was using on 10.2.0.3. We knew we had memory leaks somewhere in the app code because we would need to restart our drivers (sessions that ran 24x7) every few days else the higher processing volume threads would eat up memory and kill the node. This was in a RAC so we got to watch the fail-over work as intended. Not something you want to do on a regular basis in a production system. We put in a process to stop/start those threads every so often to release memory while we tried to figure out where the leak was.<br /><br />At that time a task came up where I had to do something as a one time job, so I hacked together a script that used a lot of production code, tested it and finally let it loose in Production. I was using DBMS_APPLICATION_INFO to log job progress and I noticed that over time, the job would start slowing down. I could stop and restart the job and it would run fast and then start slowing down again. After a lot of research and time stripping out pieces of code and rerunning and not seeing any progress, I finally figured out the code was missing some dbms_xmldom.FreeDocument statements that the documentation tells you to use.<br /><br />Ah, the cause of the memory leak. Reviewed the code, plugged those in where missing, go back to testing my script and notice no difference. Huh? I stripped the script down even further till I had something like the following.<br /><div class="code"><pre>DECLARE <br /> l_dom_doc dbms_xmldom.DOMDocument;<br /><br /> PROCEDURE p_local_open_close IS<br /> BEGIN<br /> l_dom_doc := dbms_xmldom.newDOMDocument;<br /> dbms_xmldom.FreeDocument(l_dom_doc);<br /> END p_local_open_close;<br /><br />BEGIN<br /> DBMS_APPLICATION_INFO.SET_MODULE(module_name => 'user_domDocument_mem_leak_test',<br /> action_name => 'SLEEPING');<br /> dbms_lock.sleep(20);<br /> /* Sleeps so have time to find OS PID via<br /> select s.sid, s.process, s.serial#, s.module, s.action, p.spid os_pid<br /> from gv$session s,<br /> gv$process p<br /> where s.module = 'user_domDocument_mem_leak_test'<br /> and s.paddr = p.addr;<br /> */<br /> dbms_application_info.set_action('RUNNING');<br /> FOR i IN 1..700000 -- adjust as needed for your system<br /> LOOP<br /> p_local_open_close;<br /> END LOOP;<br /> dbms_application_info.set_action('COMPLETE');<br /> <br />END;</pre></div><br />Running that on both 10.2.0.1 (our Development at the time) and 10.2.0.3 (Test/Production) would result in memory usage growing consistently while the script was running. We had our client open a SR with Oracle and they did confirm it was a bug. At some point later, Oracle provided the patch to fix this bug and all was good. I think this fix was included in the .4 release but I've lost the bug number for reference purposes. I think it stemmed from some of the JAVA used under the covers by dbms_xmldom based on what I learned from Oracle's responses regarding .freeDocument on this SR and another I had open with them regarding side effects.<br /><br />One other interesting thing we saw is that the Development ran on RedHat Linux and Test/Production on SunOS. When the script finished running in Dev, the amount of memory that was used would be freed up even though the session/thread was still alive. On SunOS, we had to kill the session/thread before the memory would free up. I was always interested in whether that was caused by the .1 and .3 difference or the OS difference but we never had the opportunity to sync the version up until both went to .4Jason Hhttp://www.blogger.com/profile/02172936625260505714noreply@blogger.com0tag:blogger.com,1999:blog-3851595309768128250.post-49566385000503577352010-08-31T10:35:00.006-06:002010-08-31T13:28:07.748-06:00XML Parsing with Default Namespaces via XMLTableIn my previous post <a href="http://anononxml.blogspot.com/2010/08/xml-parsing-with-namespaces-via.html">XML Parsing with Namespaces via XMLTable</a> I covered parsing of XML where a namespace prefix existed for each namespace. What happens when you have to parse XML where some of the XML is in a default namespace? With XMLTable, it is simple as the XMLNamespace clause has a DEFAULT option to deal with this situation. Here is another round of simple example based off the ongoing example.<br /><div class="code"><pre>Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0<br /><br />SQL> CREATE TABLE your_table (xml_col XMLTYPE);<br /><br />Table created<br /><br />SQL><br />SQL> INSERT INTO your_table<br />2 VALUES<br />3 ('<employees xmlns="abc.com/123" b="xyz.net/456"><br />4 <emp><br />5 <name>Scott</name><br />6 <b:favorites><br />7 <b:color>red</b:color><br />8 <b:color>orange</b:color><br />9 </b:favorites><br />10 </emp><br />11 <emp><br />12 <name>John</name><br />13 <b:favorites><br />14 <b:color>blue</b:color><br />15 <b:color>green</b:color><br />16 </b:favorites><br />17 </emp><br />18 </employees>');<br /><br />1 row inserted<br /><br />SQL> commit;<br /><br />Commit complete</pre></div><br /><div class="code"><pre>Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0<br /><br />SQL><br />SQL> -- Method 3<br />SQL> SELECT xt.nam<br />2 FROM your_table yt,<br />3 XMLTable(XMLNamespaces(DEFAULT 'abc.com/123'),<br />4 'employees/emp'<br />5 PASSING yt.xml_col -- defines source of XMLType, can define a join<br />6 COLUMNS<br />7 nam VARCHAR2(20) PATH 'name') xt;<br /><br />NAM<br />--------------------<br />Scott<br />John<br />SQL> --<br />SQL> -- Method 4<br />SQL> -- declares a prefix for the default<br />SQL> SELECT xt.nam<br />2 FROM your_table yt,<br />3 XMLTable(XMLNamespaces('abc.com/123' AS "a"),<br />4 'a:employees/a:emp[2]'<br />5 PASSING yt.xml_col<br />6 COLUMNS<br />7 nam VARCHAR2(20) PATH 'a:name') xt;<br /><br />NAM<br />--------------------<br />John<br />SQL> --<br />SQL> -- Method 5.a<br />SQL> SELECT xt.color<br />2 FROM your_table yt,<br />3 XMLTable(XMLNamespaces(DEFAULT 'abc.com/123',<br />4 'xyz.net/456' AS "b"),<br />5 'employees/emp/b:favorites/b:color'<br />6 PASSING yt.xml_col<br />7 COLUMNS<br />8 color VARCHAR2(10) PATH '.') xt;<br /><br />COLOR<br />----------<br />red<br />orange<br />blue<br />green<br />SQL> --<br />SQL> -- Method 5.b<br />SQL> SELECT xt.nam, xt2.color<br />2 FROM your_table yt,<br />3 XMLTable(XMLNamespaces(DEFAULT 'abc.com/123',<br />4 'xyz.net/456' AS "b"),<br />5 'employees/emp'<br />6 PASSING yt.xml_col<br />7 COLUMNS<br />8 nam VARCHAR2(20) PATH 'name',<br />9 color_t XMLTYPE PATH 'b:favorites') xt, -- path to the node that repeats<br />10 XMLTable(XMLNamespaces('xyz.net/456' AS "b"),<br />11 'b:favorites/b:color'<br />12 PASSING xt.color_t -- define input XMLType as output of above, aka a join<br />13 COLUMNS<br />14 color VARCHAR2(10) PATH '.') xt2;<br /><br />SELECT xt.nam, xt2.color<br />FROM your_table yt,<br /> XMLTable(XMLNamespaces(DEFAULT 'abc.com/123',<br /> 'xyz.net/456' AS "b"),<br /> 'employees/emp'<br /> PASSING yt.xml_col<br /> COLUMNS<br /> nam VARCHAR2(20) PATH 'name',<br /> color_t XMLTYPE PATH 'b:favorites') xt, -- path to the node that repeats<br /> XMLTable(XMLNamespaces('xyz.net/456' AS "b"),<br /> 'b:favorites/b:color'<br /> PASSING xt.color_t -- define input XMLType as output of above, aka a join<br /> COLUMNS<br /> color VARCHAR2(10) PATH '.') xt2<br /><br />ORA-31011: XML parsing failed<br />ORA-19202: Error occurred in XML processing<br />LPX-00601: Invalid token in: '/oraxq_defpfx:emp/b:favorites'<br />SQL> --<br />SQL> -- Method 5.c<br />SQL> SELECT xt.nam, xt2.color<br />2 FROM your_table yt,<br />3 XMLTable(XMLNamespaces(DEFAULT 'abc.com/123',<br />4 'xyz.net/456' AS "b"),<br />5 'employees/emp'<br />6 PASSING yt.xml_col<br />7 COLUMNS<br />8 nam VARCHAR2(20) PATH 'name',<br />9 color_t XMLTYPE PATH 'b:favorites/b:color') xt, -- path to the node that repeats<br />10 XMLTable(XMLNamespaces('xyz.net/456' AS "b"),<br />11 '/b:color'<br />12 PASSING xt.color_t -- define input XMLType as output of above, aka a join<br />13 COLUMNS<br />14 color VARCHAR2(10) PATH '.') xt2;<br /><br />SELECT xt.nam, xt2.color<br />FROM your_table yt,<br /> XMLTable(XMLNamespaces(DEFAULT 'abc.com/123',<br /> 'xyz.net/456' AS "b"),<br /> 'employees/emp'<br /> PASSING yt.xml_col<br /> COLUMNS<br /> nam VARCHAR2(20) PATH 'name',<br /> color_t XMLTYPE PATH 'b:favorites/b:color') xt, -- path to the node that repeats<br /> XMLTable(XMLNamespaces('xyz.net/456' AS "b"),<br /> '/b:color'<br /> PASSING xt.color_t -- define input XMLType as output of above, aka a join<br /> COLUMNS<br /> color VARCHAR2(10) PATH '.') xt2<br /><br />ORA-31011: XML parsing failed<br />ORA-19202: Error occurred in XML processing<br />LPX-00601: Invalid token in: '/oraxq_defpfx:emp/b:favorites/b:color'<br /><br />SQL> </pre></div><br />Wait, what? Errors? I'll get back to that.<br />In Method 3, we declared the default namespace for the XML via<br />DEFAULT 'abc.com/123'<br />On the XML elements that reside in the default namespace, we do not need to include a prefix as the default namespace is assumed. XMLTable makes it that simple. You can still declare the namespace and assign it a prefix as Method 4 shows if you prefer or would need in situations where the XML has many different default namespaces.<br /><br />Now about Method 5.b and 5.c, where did that error come from? That would be a bug in the version of Oracle I am using. The SQL runs correctly on 10.2 and 11.2 at least. I can't speak for other versions of 11.1<br />What can we learn from this bug? We learn that when the DEFAULT namespace is declared within an XMLNamespaces clause that Oracle behind the scenes creates a prefix called oraxq_defpfx and applies it to the appropriate elements in the XQuery statement. <br /><br />You can work around this bug by declaring your own prefix for the namespace as shown in Method 4 and using that prefix in the XPath accordingly. I would avoid declaring any namespace prefixes that start with "ora" just to be safe. Final note, since "oraxq_defpfx" is an internal definition for the default namespace prefix, that name is subject to be changed at any time by Oracle for any reason.Jason Hhttp://www.blogger.com/profile/02172936625260505714noreply@blogger.com0tag:blogger.com,1999:blog-3851595309768128250.post-43033953475059708352010-08-25T11:03:00.004-06:002010-08-25T11:22:59.010-06:00XML Parsing with Namespaces via XMLTableIn <a href="http://anononxml.blogspot.com/2010/07/basic-xml-parsing-via-xmltable.html">Basic XML Parsing via XMLTable</a> I covered the basics for parsing simple XML stored in the database via SQL, but most XML has one or more namespaces associated with it. So how do you deal with that? With XMLTable it is simple. As the <a href="http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/functions240.htm#SQLRF06232">XMLTable</a> documentation shows, there is an XMLnamespaces_clause where this information can be provided. This translates into the XMLNamespaces(), parameter within the XMLTable syntax. The documentation doesn't show how to use it, but the XML DB FAQ Thread on the <a href="http://forums.oracle.com/forums/forum.jspa?forumID=34&start=0">OTN XML DB forum</a> does (see the "How do I declare namespace prefix mapping with XMLTable()?" thread). As before in the PL/SQL example showing namespaces, I've taken the previous XML and added two namespaces to it and updated the appropriate calls to make this example.<br /><br />The updated XML I used.<br /><div class="code"><pre>Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0<br /><br />SQL><br />SQL> CREATE TABLE your_table (xml_col XMLTYPE);<br /><br />Table created<br />SQL> INSERT INTO your_table<br />2 VALUES<br />3 ('<a:employees xmlns:a="abc.com/123" xmlns:b="xyz.net/456"><br />4 <a:emp><br />5 <a:name>Scott</a:name><br />6 <b:favorites><br />7 <b:color>red</b:color><br />8 <b:color>orange</b:color><br />9 </b:favorites><br />10 </a:emp><br />11 <a:emp><br />12 <a:name>John</a:name><br />13 <b:favorites><br />14 <b:color>blue</b:color><br />15 <b:color>green</b:color><br />16 </b:favorites><br />17 </a:emp><br />18 </a:employees>');<br /><br />1 row inserted<br />SQL> commit;<br /><br />Commit complete</pre></div><br /><div class="code"><pre>SQL> -- Method 3<br />SQL> SELECT xt.nam<br /> 2 FROM your_table yt,<br /> 3 XMLTable(XMLNamespaces('abc.com/123' AS "a"),<br /> 4 'a:employees/a:emp'<br /> 5 PASSING yt.xml_col -- defines source of XMLType, can define a join<br /> 6 COLUMNS<br /> 7 nam VARCHAR2(20) PATH 'a:name') xt;<br /><br />NAM<br />--------------------<br />Scott<br />John<br />SQL> --<br />SQL> -- Method 4<br />SQL> SELECT xt.nam<br /> 2 FROM your_table yt,<br /> 3 XMLTable(XMLNamespaces('abc.com/123' AS "a"),<br /> 4 'a:employees/a:emp[2]'<br /> 5 PASSING yt.xml_col<br /> 6 COLUMNS<br /> 7 nam VARCHAR2(20) PATH 'a:name') xt;<br /><br />NAM<br />--------------------<br />John<br />SQL> --<br />SQL> -- Method 5.a<br />SQL> SELECT xt.color<br /> 2 FROM your_table yt,<br /> 3 XMLTable(XMLNamespaces('abc.com/123' AS "a",<br /> 4 'xyz.net/456' AS "b"),<br /> 5 'a:employees/a:emp/b:favorites/b:color'<br /> 6 PASSING yt.xml_col<br /> 7 COLUMNS<br /> 8 color VARCHAR2(10) PATH '.') xt;<br /><br />COLOR<br />----------<br />red<br />orange<br />blue<br />green<br />SQL> --<br />SQL> -- Method 5.b<br />SQL> SELECT xt.nam, xt2.color<br /> 2 FROM your_table yt,<br /> 3 XMLTable(XMLNamespaces('abc.com/123' AS "a",<br /> 4 'xyz.net/456' AS "b"),<br /> 5 'a:employees/a:emp'<br /> 6 PASSING yt.xml_col<br /> 7 COLUMNS<br /> 8 nam VARCHAR2(20) PATH 'a:name',<br /> 9 color_t XMLTYPE PATH 'b:favorites') xt, -- path to the node that repeats<br />10 XMLTable(XMLNamespaces('xyz.net/456' AS "b"),<br />11 'b:favorites/b:color'<br />12 PASSING xt.color_t -- define input XMLType as output of above, aka a join<br />13 COLUMNS<br />14 color VARCHAR2(10) PATH '.') xt2;<br /><br />NAM COLOR<br />-------------------- ----------<br />Scott red<br />Scott orange<br />John blue<br />John green<br />SQL> --<br />SQL> -- Method 5.c<br />SQL> SELECT xt.nam, xt2.color<br /> 2 FROM your_table yt,<br /> 3 XMLTable(XMLNamespaces('abc.com/123' AS "a",<br /> 4 'xyz.net/456' AS "b"),<br /> 5 'a:employees/a:emp'<br /> 6 PASSING yt.xml_col<br /> 7 COLUMNS<br /> 8 nam VARCHAR2(20) PATH 'a:name',<br /> 9 color_t XMLTYPE PATH 'b:favorites/b:color') xt, -- path to the node that repeats<br />10 XMLTable(XMLNamespaces('xyz.net/456' AS "b"),<br />11 '/b:color'<br />12 PASSING xt.color_t -- define input XMLType as output of above, aka a join<br />13 COLUMNS<br />14 color VARCHAR2(10) PATH '.') xt2;<br /><br />NAM COLOR<br />-------------------- ----------<br />Scott red<br />Scott orange<br />John blue<br />John green<br /></pre></div><br /><br />which is exactly the same output as the previous example. This is what we expected. As always, you only need to pass along namespaces that are referenced directly in the XMLTable and not all the namespaces associated with the XML.<br /><br />Trouble shooting tips:<br /><ul><li>Don't forgot to include the comma after the closing parenthesis on the XMLNamespaces(), structure otherwise you'll get ORA-02000: missing , keyword<br /></li></ul>Jason Hhttp://www.blogger.com/profile/02172936625260505714noreply@blogger.com3tag:blogger.com,1999:blog-3851595309768128250.post-30271117885586751352010-07-20T16:16:00.007-06:002010-08-17T11:33:44.092-06:00Basic XML Parsing via XMLTableIn <a href="http://anononxml.blogspot.com/2010/05/one-question-that-comes-up-with-some.html">Basic XML Parsing via PL/SQL</a>, I looked at "How do I parse this XML?" when the XML resides in PL/SQL. Oftentimes, the XML resides in the database and so Oracle provides another option for parsing the XML, the <a href="http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/functions251.htm#SQLRF06232">XMLTable</a> command. This command became available with the release of 10.2. On versions <= 10.1, you have to use TABLE(xmlsequence(extract(...))). I might cover that at some point in the future but will see. While reading that documentation may make XMLTable seem complex, which it can be, it is very easy to use. Let us start with putting our XML into the DB via:<div class="code"><pre>Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0<br /><br />SQL><br />SQL> CREATE TABLE your_table<br /> 2 (xml_col XMLTYPE);<br /><br />Table created<br /><br />SQL><br />SQL> INSERT INTO your_table<br /> 2 VALUES<br /> 3 ('<employees><br /> 4 <emp><br /> 5 <name>Scott</name><br /> 6 <favorites><br /> 7 <color>red</color><br /> 8 <color>orange</color><br /> 9 </favorites><br />10 </emp><br />11 <emp><br />12 <name>John</name><br />13 <favorites><br />14 <color>blue</color><br />15 <color>green</color><br />16 </favorites><br />17 </emp><br />18 </employees>');<br /><br />1 row inserted<br /><br />SQL> commit;<br /><br />Commit complete</pre></div><br />Now let's get out the same data we did in the original PL/SQL example. For this we will only be doing Methods 3 - 5 since they cover Methods 1 and 2 as well.<br /><div class="code"><pre>SQL> -- Method 3<br />SQL> SELECT xt.nam<br /> 2 FROM your_table yt,<br /> 3 XMLTable('employees/emp'<br /> 4 PASSING yt.xml_col -- defines source of XMLType, can define a join<br /> 5 COLUMNS<br /> 6 nam VARCHAR2(20) PATH 'name') xt;<br /><br />NAM<br />--------------------<br />Scott<br />John<br /><br />SQL> --<br />SQL> -- Method 4<br />SQL> SELECT xt.nam<br /> 2 FROM your_table yt,<br /> 3 XMLTable('employees/emp[2]'<br /> 4 PASSING yt.xml_col<br /> 5 COLUMNS<br /> 6 nam VARCHAR2(20) PATH 'name') xt;<br /><br />NAM<br />--------------------<br />John<br /><br />SQL> --<br />SQL> -- Method 5.a<br />SQL> SELECT xt.color<br /> 2 FROM your_table yt,<br /> 3 XMLTable('employees/emp/favorites/color'<br /> 4 PASSING yt.xml_col<br /> 5 COLUMNS<br /> 6 color VARCHAR2(10) PATH '.') xt;<br /><br />COLOR<br />----------<br />red<br />orange<br />blue<br />green<br /><br />SQL> --<br />SQL> -- Method 5.b<br />SQL> SELECT xt.nam, xt2.color<br /> 2 FROM your_table yt,<br /> 3 XMLTable('employees/emp'<br /> 4 PASSING yt.xml_col<br /> 5 COLUMNS<br /> 6 nam VARCHAR2(20) PATH 'name',<br /> 7 color_t XMLTYPE PATH 'favorites') xt, -- path to the node that repeats<br /> 8 XMLTable('favorites/color'<br /> 9 PASSING xt.color_t -- define input XMLType as output of above, aka a join<br />10 COLUMNS<br />11 color VARCHAR2(10) PATH '.') xt2;<br /><br />NAM COLOR<br />-------------------- ----------<br />Scott red<br />Scott orange<br />John blue<br />John green<br /><br />SQL> --<br />SQL> -- Method 5.c<br />SQL> SELECT xt.nam, xt2.color<br /> 2 FROM your_table yt,<br /> 3 XMLTable('employees/emp'<br /> 4 PASSING yt.xml_col<br /> 5 COLUMNS<br /> 6 nam VARCHAR2(20) PATH 'name',<br /> 7 color_t XMLTYPE PATH 'favorites/color') xt, -- path to the node that repeats<br /> 8 XMLTable('/color'<br /> 9 PASSING xt.color_t -- define input XMLType as output of above, aka a join<br />10 COLUMNS<br />11 color VARCHAR2(10) PATH '.') xt2;<br /><br />NAM COLOR<br />-------------------- ----------<br />Scott red<br />Scott orange<br />John blue<br />John green</pre></div><br />Methods 3 - 5.a here extract the same information as we did via PL/SQL.<br /><br />Breaking down the Oracle documentation syntax, I will cover <span style="font-weight: bold;">XMLnamespaces_clause</span> in another post. The <span style="font-weight: bold;">XQuery_string</span> can be a simple XPath as shown above or more complex XQuery statements to be demonstrated later. The <span style="font-weight: bold;">XML_passing_clause</span> is just the PASSING line where the input XML is defined. This can join the XMLTable to another table/XMLTable, a variable, or be XML generated right there. The <span style="font-weight: bold;">XML_table_column</span> section following COLUMNS simply lists how to find the information referenced by the XQuery_string. The information is relative to the results of the XQuery_string.<br /><br />Trouble shooting tips:<br /><ul><li>If you are unsure of what the XMLType looks like at some point within an XMLTable, simply create a column of XMLType and include it in the SELECT list. In example 5.c, adding xt.color_t to the select list allows us to see what Oracle was passing into the second XMLTable.</li></ul>Jason Hhttp://www.blogger.com/profile/02172936625260505714noreply@blogger.com3tag:blogger.com,1999:blog-3851595309768128250.post-87527511398420700682010-07-20T16:09:00.002-06:002010-07-20T16:15:29.743-06:00Oracle DocumentationOne thing I have noticed while being on the OTN forums is that not everyone knows about the online documentation for the version of Oracle they are using. Thankfully, Oracle has the handy <a href="http://www.oracle.com/technology/documentation/index.html">Oracle Documentation</a> to start from.Jason Hhttp://www.blogger.com/profile/02172936625260505714noreply@blogger.com0tag:blogger.com,1999:blog-3851595309768128250.post-13427928065679270152010-06-30T14:50:00.011-06:002010-07-20T16:03:42.272-06:00XML Parsing with Default Namespaces via PL/SQLIn my <a href="http://anononxml.blogspot.com/2010/06/xml-parsing-with-namespaces-via-plsql.html">previous post</a> I covered parsing of XML where a namespace prefix existed for each namespace. What happens when you have to parse XML where some of the XML is in a default namespace? Make up a namespace prefix. No W3C complaint parser will care about what the prefix is in the XML and the prefix in the XPath is as long as the URI (the stuff within the " quotes) is the same for the node in question. Here is a simple example based off the ongoing example<div class="code"><pre>DECLARE<br /> l_doc VARCHAR2(2000);<br /> l_domdoc dbms_xmldom.DOMDocument;<br /> l_nodelist dbms_xmldom.DOMNodeList;<br /> l_node dbms_xmldom.DOMNode;<br /> l_value VARCHAR2(30);<br /> l_a_ns VARCHAR2(30);<br /> l_both_ns VARCHAR2(60);<br /><br /> l_xmltype XMLTYPE;<br /> l_empx XMLTYPE;<br /> l_index PLS_INTEGER;<br /> l_col_ind PLS_INTEGER;<br />BEGIN<br /> l_doc := '<employees xmlns="abc.com/123" xmlns:b="xyz.net/456"><br /> <emp><br /> <name>Scott</name><br /> <b:favorites><br /> <b:color>red</b:color><br /> <b:color>orange</b:color><br /> </b:favorites><br /> </emp><br /> <emp><br /> <name>John</name><br /> <b:favorites><br /> <b:color>blue</b:color><br /> <b:color>green</b:color><br /> </b:favorites><br /> </emp><br /></employees>';<br /><br /> l_a_ns := 'xmlns:a="abc.com/123"'; -- assign random prefix to namespace<br /> -- assigning a different prefix to this namespace<br /> l_both_ns := l_a_ns || ' xmlns:c="xyz.net/456"'; -- separated by a space<br /><br /> l_xmltype := XMLTYPE(l_doc);<br /><br /> -- Method 4<br /> dbms_output.new_line;<br /> dbms_output.put_line('Method 4');<br /> l_value := l_xmltype.extract('/employees/emp[2]/name/text()', 'xmlns="abc.com/123"').getStringVal();<br /> dbms_output.put_line('Emp Name: '||l_value);<br /><br /> -- Method 5<br /> dbms_output.new_line;<br /> dbms_output.put_line('Method 5');<br /> l_index := 1;<br /> WHILE l_xmltype.Existsnode('/a:employees/a:emp[' || To_Char(l_index) || ']', l_a_ns) > 0<br /> LOOP<br /> l_empx := l_xmltype.extract('/a:employees/a:emp[' || To_Char(l_index) || ']', l_a_ns);<br /> l_col_ind := 1;<br /> WHILE l_empx.Existsnode('/a:emp/c:favorites/c:color[' || To_Char(l_col_ind) || ']', l_both_ns) > 0<br /> LOOP<br /> l_value := l_empx.extract('/a:emp/c:favorites/c:color[' || To_Char(l_col_ind) || ']/text()', l_both_ns).getStringVal();<br /> dbms_output.put_line('Color: '||l_value);<br /> l_col_ind := l_col_ind + 1;<br /> END LOOP;<br /> l_index := l_index + 1;<br /> END LOOP;<br />END;</pre></div><br />In the XML, we have a default namespace of "abc.com/123" for the elements and the previous "xyz.net/456" namespace. In the code, I randomly picked a prefix of "a" for the default namespace and so anytime an element in the default namespace is referenced, the corresponding prefix is used.<br /><br />As Method 4 shows, you could just provide the default namespace as is for the third parm and then there is no need to include a prefix in the XPath itself. I prefer to go the route of method 5 as I have encountered XML where the "default" namespace has changed several nodes deep in the XML. Since the default changes, you need to specify a namespace prefix to keep things in order with the namespace each node resides in.Jason Hhttp://www.blogger.com/profile/02172936625260505714noreply@blogger.com0tag:blogger.com,1999:blog-3851595309768128250.post-85481065952328156442010-06-28T22:04:00.002-06:002010-07-20T16:07:45.317-06:00XML Parsing with Namespaces via PL/SQLMy previous post covered the basics for parsing simple XML in PL/SQL, but most XML has one or more namespaces associated with it. So how do you deal with that? Simple pretty much. Oracle provides a third parm (usually it is the third) to pass namespace URI information along to the underlying parser. I've taken the previous XML and added two namespaces to it and updated the appropriate calls to make this example.<br /><div class="code"><pre>DECLARE<br />l_doc VARCHAR2(2000);<br />l_domdoc dbms_xmldom.DOMDocument;<br />l_nodelist dbms_xmldom.DOMNodeList;<br />l_node dbms_xmldom.DOMNode;<br />l_value VARCHAR2(30);<br />l_a_ns VARCHAR2(30);<br />l_both_ns VARCHAR2(60);<br /><br />l_xmltype XMLTYPE;<br />l_empx XMLTYPE;<br />l_index PLS_INTEGER;<br />l_col_ind PLS_INTEGER;<br />BEGIN<br />l_doc := '<a:employees xmlns:a="abc.com/123" xmlns:b="xyz.net/456"><br /> <a:emp><br /> <a:name>Scott</a:name><br /> <b:favorites><br /> <b:color>red</b:color><br /> <b:color>orange</b:color><br /> </b:favorites><br /> </a:emp><br /> <a:emp><br /> <a:name>John</a:name><br /> <b:favorites><br /> <b:color>blue</b:color><br /> <b:color>green</b:color><br /> </b:favorites><br /> </a:emp><br /></a:employees>';<br /><br />l_domdoc := dbms_xmldom.newDomDocument(l_doc);<br />l_a_ns := 'xmlns:a="abc.com/123"';<br />l_both_ns := l_a_ns || ' xmlns:b="xyz.net/456"'; -- separated by a space<br /><br />-- Method 1<br />dbms_output.put_line('Method 1');<br />-- third parm for namespace info<br />l_nodelist := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_domdoc),'/a:employees/a:emp/a:name', l_a_ns);<br />FOR cur_emp IN 0 .. dbms_xmldom.getLength(l_nodelist) - 1 LOOP<br /> l_node := dbms_xmldom.item(l_nodelist, cur_emp);<br /> l_value := dbms_xmldom.getnodevalue(dbms_xmldom.getfirstchild(l_node));<br /> dbms_output.put_line('Emp Name: '||l_value);<br />END LOOP;<br /><br />-- Method 2<br />dbms_output.new_line;<br />dbms_output.put_line('Method 2');<br />l_nodelist := dbms_xmldom.getelementsbytagname(l_domdoc, 'name'); -- This doesn't care about namespaces<br />-- get first item from list, could loop as shown above<br />l_node := dbms_xmldom.item(l_nodelist, 0);<br />l_value := dbms_xmldom.getnodevalue(dbms_xmldom.getfirstchild(l_node));<br />dbms_output.put_line('Emp Name: '||l_value);<br /><br />-- Done with DOMDocument examples, setup for XMLType based examples<br />dbms_xmldom.freeDocument(l_domdoc);<br />l_xmltype := XMLTYPE(l_doc);<br /><br />-- Method 3<br />dbms_output.new_line;<br />dbms_output.put_line('Method 3');<br />l_index := 1;<br />WHILE l_xmltype.Existsnode('/a:employees/a:emp[' || To_Char(l_index) || ']', l_a_ns) > 0<br />LOOP<br /> l_value := l_xmltype.extract('/a:employees/a:emp[' || To_Char(l_index) || ']/a:name/text()', l_a_ns).getStringVal();<br /> dbms_output.put_line('Emp Name: '||l_value);<br /> l_index := l_index + 1;<br />END LOOP;<br /><br />-- Method 4<br />dbms_output.new_line;<br />dbms_output.put_line('Method 4');<br />l_value := l_xmltype.extract('/a:employees/a:emp[2]/a:name/text()', l_a_ns).getStringVal();<br />dbms_output.put_line('Emp Name: '||l_value);<br /><br />-- Method 5<br />dbms_output.new_line;<br />dbms_output.put_line('Method 5');<br />l_index := 1;<br />WHILE l_xmltype.Existsnode('/a:employees/a:emp[' || To_Char(l_index) || ']', l_a_ns) > 0<br />LOOP<br /> l_empx := l_xmltype.extract('/a:employees/a:emp[' || To_Char(l_index) || ']', l_a_ns);<br /> l_col_ind := 1;<br /> WHILE l_empx.Existsnode('/a:emp/b:favorites/b:color[' || To_Char(l_col_ind) || ']', l_both_ns) > 0<br /> LOOP<br /> l_value := l_empx.extract('/a:emp/b:favorites/b:color[' || To_Char(l_col_ind) || ']/text()', l_both_ns).getStringVal();<br /> dbms_output.put_line('Color: '||l_value);<br /> l_col_ind := l_col_ind + 1;<br /> END LOOP;<br /> l_index := l_index + 1;<br />END LOOP;<br />END;</pre></div>Running this script produces<br /><div class="code"><pre>Method 1<br />Emp Name: Scott<br />Emp Name: John<br /> <br />Method 2<br />Emp Name: Scott<br /> <br />Method 3<br />Emp Name: Scott<br />Emp Name: John<br /> <br />Method 4<br />Emp Name: John<br /> <br />Method 5<br />Color: red<br />Color: orange<br />Color: blue<br />Color: green<br /></pre></div> which is exactly the same output as the previous example. This is what we expected. The only call that did not have to change was the one for Method 2. DBMS_XMLDOM is pretty much ignores namespaces when performing operations so this does have useful situations. As Methods 1, 3, and 4 show, the namespace parm only needs to contain the URI information for namespaces referenced in the Xpath, not in the XML. All the namespaces information can be passed along, but it is not needed. Pretty simple, excluding the whole "wait, what namespace is that node in again?" issue.Jason Hhttp://www.blogger.com/profile/02172936625260505714noreply@blogger.com1tag:blogger.com,1999:blog-3851595309768128250.post-29765932076263302422010-05-28T14:00:00.006-06:002010-07-20T16:08:39.897-06:00Basic XML Parsing via PL/SQLOne 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.<br /><div class="code"><pre>DECLARE<br />l_doc VARCHAR2(2000);<br />l_domdoc dbms_xmldom.DOMDocument;<br />l_nodelist dbms_xmldom.DOMNodeList;<br />l_node dbms_xmldom.DOMNode;<br />l_value VARCHAR2(30);<br /><br />l_xmltype XMLTYPE;<br />l_empx XMLTYPE;<br />l_index PLS_INTEGER;<br />l_col_ind PLS_INTEGER;<br />BEGIN<br />l_doc := '<employees><br /> <emp><br /> <name>Scott</name><br /> <favorites><br /> <color>red</color><br /> <color>orange</color><br /> </favorites><br /> </emp><br /> <emp><br /> <name>John</name><br /> <favorites><br /> <color>blue</color><br /> <color>green</color><br /> </favorites><br /> </emp><br /></employees>';<br /><br />l_domdoc := dbms_xmldom.newDomDocument(l_doc);<br /><br />-- Method 1<br />dbms_output.put_line('Method 1');<br />l_nodelist := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_domdoc),'/employees/emp/name');<br />FOR cur_emp IN 0 .. dbms_xmldom.getLength(l_nodelist) - 1 LOOP<br /> l_node := dbms_xmldom.item(l_nodelist, cur_emp);<br /> l_value := dbms_xmldom.getnodevalue(dbms_xmldom.getfirstchild(l_node));<br /> dbms_output.put_line('Emp Name: '||l_value);<br />END LOOP;<br /><br />-- Method 2<br />dbms_output.new_line;<br />dbms_output.put_line('Method 2');<br />l_nodelist := dbms_xmldom.getelementsbytagname(l_domdoc, 'name');<br />-- get first item from list, could loop as shown above<br />l_node := dbms_xmldom.item(l_nodelist, 0);<br />l_value := dbms_xmldom.getnodevalue(dbms_xmldom.getfirstchild(l_node));<br />dbms_output.put_line('Emp Name: '||l_value);<br /><br />-- Done with DOMDocument examples, setup for XMLType based examples<br />dbms_xmldom.freeDocument(l_domdoc);<br />l_xmltype := XMLTYPE(l_doc);<br /><br />-- Method 3<br />dbms_output.new_line;<br />dbms_output.put_line('Method 3');<br />l_index := 1;<br />WHILE l_xmltype.Existsnode('/employees/emp[' || To_Char(l_index) || ']') > 0<br />LOOP<br /> l_value := l_xmltype.extract('/employees/emp[' || To_Char(l_index) || ']/name/text()').getStringVal();<br /> dbms_output.put_line('Emp Name: '||l_value);<br /> l_index := l_index + 1;<br />END LOOP;<br /><br />-- Method 4<br />dbms_output.new_line;<br />dbms_output.put_line('Method 4');<br />l_value := l_xmltype.extract('/employees/emp[2]/name/text()').getStringVal();<br />dbms_output.put_line('Emp Name: '||l_value);<br /><br />-- Method 5<br />dbms_output.new_line;<br />dbms_output.put_line('Method 5');<br />l_index := 1;<br />WHILE l_xmltype.Existsnode('/employees/emp[' || To_Char(l_index) || ']') > 0<br />LOOP<br /> l_empx := l_xmltype.extract('/employees/emp[' || To_Char(l_index) || ']');<br /> l_col_ind := 1;<br /> WHILE l_empx.Existsnode('/emp/favorites/color[' || To_Char(l_col_ind) || ']') > 0<br /> LOOP<br /> l_value := l_empx.extract('/emp/favorites/color[' || To_Char(l_col_ind) || ']/text()').getStringVal();<br /> dbms_output.put_line('Color: '||l_value);<br /> l_col_ind := l_col_ind + 1;<br /> END LOOP;<br /> l_index := l_index + 1;<br />END LOOP;<br />END;<br /></pre></div><br />Running this script produces<br /><div class="code"><pre>Method 1<br />Emp Name: Scott<br />Emp Name: John<br /> <br />Method 2<br />Emp Name: Scott<br /> <br />Method 3<br />Emp Name: Scott<br />Emp Name: John<br /> <br />Method 4<br />Emp Name: John<br /> <br />Method 5<br />Color: red<br />Color: orange<br />Color: blue<br />Color: green<br /></pre></div><br />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.<br /><br />Methods 1 and 2 use the old style to parse the XML.<br />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.<br />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.<br /><br />Methods 3 through 5 use XMLType to parse the XML.<br />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.<br />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"<br />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.<br /><br />Gotchas:<br /><ul><li>/text() refers to the text contents of the given node. Leaving this off returns the node itself (an XMLType).</li></ul><ul><li>.getStringVal() and others, along with many examples can be found in the <a href="http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14259/xdb04cre.htm#i1024803">XMLType Operations</a><span style="text-decoration: underline;"> </span>section within the online Oracle Documentation.<br /></li></ul><ul><li>"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()<br /></li></ul>Jason Hhttp://www.blogger.com/profile/02172936625260505714noreply@blogger.com8tag:blogger.com,1999:blog-3851595309768128250.post-65910934034751870592010-05-23T21:26:00.001-06:002010-08-17T11:45:39.226-06:00XMLType - What is it?While pondering my first meaningful post regarding what it should include and be limited to, I began pondering what an XMLType really is. A CLOB or VARCHAR2 is stored as characters. Numbers are stored as bits and bytes. Dates are an internal Oracle representation. An XMLType though is simply <a href="http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10577/t_xml.htm#ARPLS369">a system-defined opaque type</a> per 11.2 documentation. Depending upon where the XMLType is used, it could be stored in various formats. In the database, it could be represented as a CLOB, binary XML, or object-relational storage. How PL/SQL represents an XMLType is unclear. <br /><br />Oracle provides many functions to access the data in an XMLType, whether a member function such as .extract or via XMLTable which uses an XMLType. Since the introduction of XMLType in 9.2, the definition in the Oracle documentation has changed slightly over time. Whether this was just documentation clarification or a associated with an underlying structure change is a guess. I suspect the latter given Oracle continues to introduce new DB storage options in 11g. <br /><br />So for now, I will accept an XMLType as any other basic data type within in Oracle, but I will still continue to wonder how it is represented in PL/SQL and how I can figure it out besides a memory dump. Future post will touch on how to access the data in a XMLType, whether in PL/SQL or the DB along with ways to build XML. For how XML can be stored in the DB, I defer to <a href="http://www.liberidu.com/blog/">Marco's blog</a> on that subject where possible.<br /><br />Information on <a href="http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14289/dciwhatis.htm">Data Cartridges</a> (10.2 documentation), as mentioned by Marco in the comment.Jason Hhttp://www.blogger.com/profile/02172936625260505714noreply@blogger.com1