65535 node limit EXTRACTVALUE / XMLSEQUENCE functions.
681619Jan 26 2009 — edited Apr 19 2009
Oracle Version 10.2.0.3.0.
I am trying to load data from a large XML document into Oracle via a stored procedure. I am having problems with the EXTRACTVALUE / EXTRACTSEQUENCE functions in the Oracle XMLDB package, as they seem to have a limit on the number of nodes they can retrieve from an XML document. The code below will demonstrate.
First I setup an XMLTYPE table, and test stored procedure...
bq. h6. \\ -- Create table to hold XML document \\ CREATE TABLE XML_TEST OF XMLTYPE; \\ / \\ h6. -- Create stored procedure to insert the data \\ CREATE OR REPLACE PROCEDURE SP_XML_TEST ( pin_InputXML IN XMLTYPE, \\ pout_RowCount OUT INTEGER, \\ pout_MaxNum OUT INTEGER, \\ pout_MaxValue OUT VARCHAR2 \\ ) \\ IS \\ BEGIN \\ EXECUTE IMMEDIATE 'TRUNCATE TABLE XML_TEST'; \\ -- Insert XML document into the table \\ INSERT \\ INTO XML_TEST \\ VALUES ( pin_InputXML ); \\ {color:#000000} SELECT COUNT( EXTRACTVALUE(VALUE(x), {color}[{color:#000000}'/ROW/@num'{color}|mailto:'/ROW/@num']{color:#000000}) ), \\ MAX( TO_NUMBER(EXTRACTVALUE(VALUE(x), {color}[{color:#000000}'/ROW/@num'{color}|mailto:'/ROW/@num']{color:#000000})) ) \\ INTO pout_RowCount, \\ pout_MaxNum \\ FROM XML_TEST xmltab, \\ TABLE( XMLSEQUENCE( EXTRACT(xmltab.OBJECT_VALUE, '/ROWSET/ROW') ) ) x; \\ BEGIN \\ SELECT EXTRACTVALUE(VALUE(x), '/ROW/DATA') \\ INTO pout_MaxValue \\ FROM XML_TEST xmltab, \\ TABLE( XMLSEQUENCE( EXTRACT(xmltab.OBJECT_VALUE, '/ROWSET/ROW') ) ) x \\ WHERE TO_NUMBER(EXTRACTVALUE(VALUE(x), {color}[{color:#000000}'/ROW/@num'{color}|mailto:'/ROW/@num']{color:#000000})) = pout_MaxNum; \\ EXCEPTION{color} \\ WHEN NO_DATA_FOUND THEN \\ pout_MaxValue := 'NO DATA!'; \\ END; \\ END SP_XML_TEST; \\ /
I have a piece of C# code which generates large XML document in the below format, and calls the stored procedure passing the large XML as paramater pin_InputXML. The XML document looks like this...
bq. h6. \\ <?xml version="1.0"?> \\ <ROWSET> \\ <ROW num="1"> \\ <DATA>VALUE 1</DATA> \\ </ROW> \\ <ROW num="2"> \\ <DATA>VALUE 2</DATA> \\ </ROW> \\ <ROW num="3"> \\ <DATA>VALUE 3</DATA> \\ </ROW> \\ <ROW num="4"> \\ <DATA>VALUE 4</DATA> \\ </ROW> \\ etc... \\ </ROWSET>
If I call the stored procedure with pin_InputXML containing the following XML...
bq. h6. \\ <?xml version="1.0"?> \\ <ROWSET> \\ <ROW num="1"> \\ <DATA>VALUE 1</DATA> \\ </ROW> \\ (...) \\ <ROW num="65534"> \\ <DATA>VALUE 65534</DATA> \\ </ROW> \\ </ROWSET>
... the parameters are output as would be expected...
bq. h6. pout_RowCount: 65534 \\ pout_MaxNum: 65534 \\ pout_MaxValue: VALUE 65534
... and the same for XML with 1 extra <ROW> node...
bq. h6. <?xml version="1.0"?> \\ <ROWSET> \\ <ROW num="1"> \\ <DATA>VALUE 1</DATA> \\ </ROW> \\ (...) \\ <ROW num="65535"> \\ <DATA>VALUE 65535</DATA> \\ </ROW> \\ </ROWSET>
...results in...
bq. h6. pout_RowCount: 65535 \\ pout_MaxNum: 65535 \\ pout_MaxValue: VALUE 65535
However, as soon as I hit 65536 <ROW> nodes, the results look strange...
bq. h6. <?xml version="1.0"?> \\ <ROWSET> \\ <ROW num="1"> \\ <DATA>VALUE 1</DATA> \\ </ROW> \\ (...) \\ <ROW num="65536"> \\ <DATA>VALUE 65536</DATA> \\ </ROW> \\ </ROWSET> \\ pout_RowCount: 0 \\ pout_MaxNum: (NULL) \\ pout_MaxValue: NO DATA!
... and then 65537 <ROW> nodes seems to repeat the earlier values...
bq. h6. <?xml version="1.0"?> \\ <ROWSET> \\ <ROW num="1"> \\ <DATA>VALUE 1</DATA> \\ </ROW> \\ (...) \\ <ROW num="65537"> \\ <DATA>VALUE 65537</DATA> \\ </ROW> \\ </ROWSET>
... results in...
bq. h6. pout_RowCount: 1 \\ pout_MaxNum: 1 \\ pout_MaxValue: VALUE 1
It looks like Oracle has a limit of 65535 nodes in the XML document. If this is the limit, then that is a little frustrating, but workable. However, I am concerned that rather than return some sort of error, the EXTRACTVALUE and/or XMLSEQUENCE functions seem to just return incorrect data.
I am sure that the XML document is being stored in it's entirety in table XML_TEST, because I have tried changing the stored procedure as follows...
bq. h6. -- Create stored procedure to insert the data \\ CREATE OR REPLACE PROCEDURE SP_XML_TEST ( pin_InputXML IN XMLTYPE, \\ pout_RowCount OUT INTEGER, \\ pout_MaxNum OUT INTEGER, \\ pout_MaxValue OUT VARCHAR2 \\ ) \\ IS \\ v_XMLDoc XMLTYPE; \\ v_Result INTEGER; \\ BEGIN \\ EXECUTE IMMEDIATE 'TRUNCATE TABLE XML_TEST'; \\ -- Insert XML document into the table \\ INSERT \\ INTO XML_TEST \\ VALUES ( pin_InputXML ); \\ -- Select back into variable \\ SELECT OBJECT_VALUE \\ INTO v_XMLDoc \\ FROM XML_TEST; \\ -- Validate XMLDoc \\ {color:#ff0000} v_XMLDoc := v_XMLDoc.CREATESCHEMABASEDXML('http://urlofmyschema.com/mychema'); \\ v_Result := v_XMLDoc.ISSCHEMAVALID();{color} \\ {color:#000000} SELECT COUNT( EXTRACTVALUE(VALUE(x), {color}[{color:#000000}'/ROW/@num'{color}|mailto:'/ROW/@num']{color:#000000}) ), \\ MAX( TO_NUMBER(EXTRACTVALUE(VALUE(x), {color}[{color:#000000}'/ROW/@num'{color}|mailto:'/ROW/@num']{color:#000000})) ) \\ INTO pout_RowCount, \\ pout_MaxNum \\ FROM XML_TEST xmltab, \\ TABLE( XMLSEQUENCE( EXTRACT(xmltab.OBJECT_VALUE, '/ROWSET/ROW') ) ) x; \\ BEGIN \\ SELECT EXTRACTVALUE(VALUE(x), '/ROW/DATA') \\ INTO pout_MaxValue \\ FROM XML_TEST xmltab, \\ TABLE( XMLSEQUENCE( EXTRACT(xmltab.OBJECT_VALUE, '/ROWSET/ROW') ) ) x \\ WHERE TO_NUMBER(EXTRACTVALUE(VALUE(x), {color}[{color:#000000}'/ROW/@num'{color}|mailto:'/ROW/@num']{color:#000000})) = pout_MaxNum;{color} \\ EXCEPTION \\ WHEN NO_DATA_FOUND THEN \\ pout_MaxValue := 'NO DATA!'; \\ END; \\ END SP_XML_TEST; \\ /
... where 'http://urlofmyschema.com/mychema' contains the following XML schema...
bq. h6. \\ <?xml version="1.0"?> \\ {color:#000000} <xs:schema xmlns:xs="{color}[{color:#000000}http://www.w3.org/2001/XMLSchema{color}|http://www.w3.org/2001/XMLSchema]{color:#000000}">{color} \\ <!-- Definition of simple elements --> \\ <xs:element name="DATA"> \\ <xs:simpleType> \\ <xs:restriction base="xs:string"> \\ </xs:restriction> \\ </xs:simpleType> \\ </xs:element> \\ <!-- Definition for ROW --> \\ <xs:element name="ROW"> \\ <xs:complexType > \\ <xs:sequence minOccurs="1" maxOccurs="1"> \\ <xs:element ref="DATA" /> \\ </xs:sequence> \\ <xs:attribute name="num" type="xs:positiveInteger" use="required" /> \\ </xs:complexType> \\ </xs:element> \\ <!-- Definition for ROWSET --> \\ <xs:element name="ROWSET"> \\ <xs:complexType> \\ <xs:sequence> \\ {color:#ff0000} <xs:element ref="ROW" minOccurs="1" maxOccurs="65535" /> {color} \\ </xs:sequence> \\ </xs:complexType> \\ </xs:element> \\ </xs:schema>
Note that I have stipulted a maximum of 65535 <ROW> nodes in a <ROWSET>. Running this version of the stored procedure with 65536 <ROW> nodes produces the following ORA error...
ORA-30936: Maximum number (65535) of 'ROW' XML node elements exceeded
ORA-06512: at "SYS.XMLTYPE", line 360
ORA-06512: at "FRMDMDUSR.SP_XML_TEST", line 28
Hence it seems that Oracle is able to store and retrieve the XML document in an XMLTYPE table and variable without any problem, but something in the EXTRACTVALUE and/or XMLSEQUENCE functions have some limitation.
Anyway, any clarification or suggestions are greatly appreciated.