This discussion is archived
7 Replies Latest reply: Dec 18, 2012 1:45 AM by greg_c RSS

Extracting xml data from xml_type column producing strange behaviour

greg_c Newbie
Currently Being Moderated
Hi All,

I have at table which contains xml in an xml_type column. This is generated from an apex ajax insert which is split into chunks using ajaxRequest.AddArrayClob(). I am then using DBMS_LOB.WRITEAPPEND to pull everything back together again and then using XMLType() to make it XML. When I run the following select from this column:

SELECT
     x.t_id     AS t_id
FROM
     xml_test t
     ,XMLTable(
          '/*/associatedData'
          passing t.xmldoc
          columns
               t.id     xmltype path      'products/product/id/text()'
) x

the following is returned:

<!CDATA[34]]]]]> instead of <![CDATA[341]]> hence both a 1 missing and three additional ']' are being added.

If i get the xml from the database directly and put this into notepad, textpad, and other various editors then the XML is as expected (<![CDATA[341]]>)

What is interesting is that ajaxRequest.AddArrayClob() is splitting the clob at the point of <!CDATA[341]

Any help would be much appreciated

The select statement we are using is:



Running Oracle 10.2.0.4

Edited by: greg_c on Dec 13, 2012 4:45 AM
  • 1. Re: Extracting xml data from xml_type column producing strange behaviour
    odie_63 Guru
    Currently Being Moderated
    Hi Greg,

    Well, from what you say the query is probably OK.
    Most likely the code that splits the content is the cause. Could you show it?
  • 2. Re: Extracting xml data from xml_type column producing strange behaviour
    greg_c Newbie
    Currently Being Moderated
    Hi,

    It is standard ApEx 3.1.1 code we are using:

    htmldb_Get.prototype.AddArrayClob=function(pText,pFnumber){
              var lArray = $s_Split(pText,4000);
              this.AddArray(lArray,pFnumber)
              return this;
         }

    and

    function $s_Split(pString,pLength){
    var lArray = [];
         if (pString.length<=pLength) {
              lArray[lArray.length]=pString;
         } else {
              while (pString.length>4000) {
                   lArray[lArray.length]=pString.substr(0,4000);
                   pString = pString.substr(4000,pString.length-4000);
              }
              lArray[lArray.length]=pString.substr(0,4000);
         }
         return lArray;
    }

    Removing the <![CDATA[]]> from the whole XML document that is generated on the browser, seems to remove the problem, but I need CDATA as the XML document contains free text entered by users who could enter special characters.

    What is also interesting is that once the XML is in the table, if I do
    SELECT xmldoc FROM xml_table
    It returns '<id><![CDATA[341]]></id>'

    Its only once I do
    SELECT extract(xmldoc,'/*') FROM xml_table
    That I get '<id><![CDATA[34]]]]]></id>'


    Any help or advice you could give would be much appreciated.

    Kind Regards,

    Greg
  • 3. Re: Extracting xml data from xml_type column producing strange behaviour
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    The APEX team is heavily (STILL) using old deprecated code like extract/extractvalue/etc. I noticed this also in all their wrapped code (package/bodies/etc). If the problem is cause by APEX/xml,xql (old exract etc) then I am guessing you are with at least this APEX version, out of luck. I notified the APEX Dev team, and Sr Prod Manager, multiple times over the years regarding this, but AFAIK, I haven't seen any code changes in the latest versions yet...
  • 4. Re: Extracting xml data from xml_type column producing strange behaviour
    odie_63 Guru
    Currently Being Moderated
    The whole workflow is not entirely clear to me.

    What is getting split and why?

    Is it reproducible in PL/SQL only?
  • 5. Re: Extracting xml data from xml_type column producing strange behaviour
    greg_c Newbie
    Currently Being Moderated
    Hi,

    Apologies, but a little bit of context may have been more helpful initially!

    I have an ApEx application page which sends a sizable amount of XML back to the server via an AJAX call. Because this XML is too big for a standard SQL VARCHAR2(4000), we cannot use the standard way of sending the data, instead it has to split into smaller chunks. To do this we are using the AddArrayClob, which essentially takes the XML string and splits it up into chunks smaller than 4000 chars.

    On the server side, an ApEx Application Process populates these chunks into a PLSQL array of VARCHAR2s called wwv_flow.g_f01. I have written a function that loops through this array and uses dbms_lob.writeappend to pull all the chunks back together and write them to a clob. This clob is then returned and using XMLType it is converted into an XML document again. In a normal situation this XMLType is then processed, using EXTRACT and EXTRACTVALUE to get to the data, which is then inserted/updated in the database. However we noticed the above problem recently and started writing the CLOB and XMLType into a table before the processing as a way to try and debug what is going on.

    It is when we started doing the EXTRACT and EXTRACTVALUES on the XML and CLOB in the table to find out what the problem was that we noticed the changing of the XML when using either the method in my first post, or EXTRACT.

    Does this help to clear up what we are doing?

    Kind Regards,

    Greg
  • 6. Re: Extracting xml data from xml_type column producing strange behaviour
    odie_63 Guru
    Currently Being Moderated
    Thanks for the details.
    It is when we started doing the EXTRACT and EXTRACTVALUES on the XML and CLOB in the table to find out what the problem was that we noticed the changing of the XML when using either the method in my first post, or EXTRACT.
    So the CLOB looks OK before constructing the XMLType?
  • 7. Re: Extracting xml data from xml_type column producing strange behaviour
    greg_c Newbie
    Currently Being Moderated
    Hi,

    Apologies for the delay in getting back to you. Have been out of office for the last few days!

    Yep, the CLOB looks absolutely fine, and the XMLType looks fine too. When I do
    SELECT
    value(x)
    FROM
    TABLE(XMLSEQUENCE(EXTRACT(xmldoc,'/*'))) x;

    Thats when the changes happen!

    It's almost as if the EXTRACT is causing the problems!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points