1 2 Previous Next 27 Replies Latest reply: Dec 10, 2012 8:40 AM by Marco Gralike RSS

    Pretty Print feature of the XMLType methods is broken in Oracle 11g

    domiq44
      Hello,

      I have found some problem in the Pretty Print feature with Oracle 11g !

      Here are the queries I used :
      SELECT * FROM v$version;
      SELECT XMLTYPE ('<myns:aaa xmlns:myns="a:b:c:d.1.0"><bbb><ccc>blablabla1</ccc><ddd>blablabla2</ddd></bbb></myns:aaa>').EXTRACT ('*') AS xml
        FROM DUAL;
      Information about my Oracle 10g :
      Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi 
      PL/SQL Release 10.2.0.4.0 - Production                           
      CORE     10.2.0.4.0     Production                                       
      TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio 
      NLSRTL Version 10.2.0.4.0 - Production                           
      It's okay with Oracle 10g, and I get this :
      <myns:aaa xmlns:myns="a:b:c:d.1.0">
        <bbb>
          <ccc>blablabla1</ccc>
          <ddd>blablabla2</ddd>
        </bbb>
      </myns:aaa>
      But using Oracle 11g :
      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production     
      PL/SQL Release 11.2.0.1.0 - Production                                           
      CORE     11.2.0.1.0     Production                                                       
      TNS for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production                
      NLSRTL Version 11.2.0.1.0 - Production                                           
      I get this ??? !!!
      <myns:aaa xmlns:myns="a:b:c:d.1.0"><bbb><ccc>blablabla1</ccc><ddd>blablabla2</ddd></bbb></myns:aaa>
      Any explanation as to why and how can I resolve this?

      I need a turn arround to get the good result.

      Thanks
        • 1. Re: Pretty Print feature of the XMLType methods is broken in Oracle 11g
          mdrake-Oracle
          Actaully extract(/*) was broken in 10gR2 since it pretty-printed. Extracting fragments (or in this case the entire document) should not introduce new nodes (even insignificant whitespace) into the result.

          You are looking for XMLSerialize() which allows you to control pretty printing of an XMLType. Note that the output of prettyprinting is never an XMLType

          eg

          XMLSERIALIZE(Document MYXML as CLOB INDENT SIZE = 2)
          • 2. Re: Pretty Print feature of the XMLType methods is broken in Oracle 11g
            domiq44
            Thanks for your answer mdrake :)

            I've seen this feature in the « Pretty-Printing of Results » section of this url : http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14259/xdb04cre.htm

            But what can I do with XMLSERIALIZE if this function does not accept XMLType ?

            I've done something like this :
            SELECT XMLSERIALIZE(Document XMLTYPE ('<myns:aaa xmlns:myns="a:b:c:d.1.0"><bbb><ccc>blablabla1</ccc><ddd>blablabla2</ddd></bbb></myns:aaa>') as CLOB INDENT SIZE = 2) as xml 
              FROM DUAL;
            And I've got that :
            <myns:aaa xmlns:myns="a:b:c:d.1.0">
              <bbb>
                <ccc>blablabla1</ccc>
                <ddd>blablabla2</ddd>
              </bbb>
            </myns:aaa>
            It's okay, but it's not a XMLType!
            • 3. Re: Pretty Print feature of the XMLType methods is broken in Oracle 11g
              mdrake-Oracle
              The whole point it that pretty printing is something you only need when displaying the XML... XML itself cannot be displayed, a serialized representation of the XML as text can be displayed. So you can do anything you need to do on the XMLType before displaying it, however when you want to display it you have to serialize it at text, either into a CLOB, BLOB or VARCHAR2 and that's when you would also pretty print it... In some ways it's a differnence that makes no difference, technically any time XML is printed it's no longer an XML Object, the text is a serialized representation of an XML object..

              Edited by: mdrake on Apr 22, 2010 7:24 AM
              • 4. Re: Pretty Print feature of the XMLType methods is broken in Oracle 11g
                domiq44
                Yes, I understand what you mean.
                You're right!

                Thanks mdrake ;)
                • 5. Re: Pretty Print feature of the XMLType methods is broken in Oracle 11g
                  domiq44
                  Hello,

                  I have some problem with this solution because I get an error when using it on Oracle 10g !!!
                  Error: ORA-00907: missing right parenthesis
                  It doesn't like the « INDENT » key word !!!

                  In fact, my code must be run on both platform (Oracle 10g and 11g).

                  How can I do ?

                  Thanks.
                  • 6. Re: Pretty Print feature of the XMLType methods is broken in Oracle 11g
                    domiq44
                    I have some problem to get it work on both Oracle 10g and 11g
                    • 7. Re: Pretty Print feature of the XMLType methods is broken in Oracle 11g
                      Marco Gralike
                      You could try to make you code dynamically based on the version.

                      case >
                      - If 10g no xmlserialize is needed
                      - If 11g and onwards use xmlserialize
                      - else ...
                      • 8. Re: Pretty Print feature of the XMLType methods is broken in Oracle 11g
                        domiq44
                        Hello Marco,

                        You talk about something like that for the version ?
                        SELECT COUNT (*)
                          FROM v$version
                         WHERE banner LIKE 'Oracle Database 11g Enterprise Edition%';
                        And then
                        SELECT XMLSERIALIZE(Document XMLTYPE ('<myns:aaa xmlns:myns="a:b:c:d.1.0"><bbb><ccc>blablabla1</ccc><ddd>blablabla2</ddd></bbb></myns:aaa>') as CLOB INDENT SIZE = 2) as xml 
                          FROM DUAL;
                        or
                        SELECT XMLSERIALIZE(Document XMLTYPE ('<myns:aaa xmlns:myns="a:b:c:d.1.0"><bbb><ccc>blablabla1</ccc><ddd>blablabla2</ddd></bbb></myns:aaa>') as CLOB) as xml 
                          FROM DUAL;
                        depend on the result ?
                        • 9. Re: Pretty Print feature of the XMLType methods is broken in Oracle 11g
                          Marco Gralike
                          There are probably better examples on the internet out there, for example, what if its a 11g standard edition database ???

                          For example (http://articles.techrepublic.com.com/5100-10878_11-6123583.html) apparently there is a simple package nowadays: DBMS_DB_VERSION.VERSION
                          EXEC DBMS_OUTPUT.PUT_LINE (DBMS_DB_VERSION.VERSION);
                          And probably I would choose for something like EXECUTE IMMEDIATE or the likes...but...I guess you got my drift...

                          Edited by: Marco Gralike on May 20, 2010 8:56 PM
                          • 10. Re: Pretty Print feature of the XMLType methods is broken in Oracle 11g
                            Marco Gralike
                            ...and then by accident you see the following code popping up in front of your nose...
                            BEGIN
                                $IF DBMS_DB_VERSION.VER_LE_10_1 $THEN
                                   DBMS_OUTPUT.PUT_LINE('This package is only working on Oracle RDBMS versions 10.2, 11.1, and 11.2.');
                                   RAISE_APPLICATION_ERROR(-31061, 'Unsupported Oracle RDBMS version');
                                $ELSIF DBMS_DB_VERSION.VER_LE_10_2 $THEN
                                   DBMS_OUTPUT.PUT_LINE('INSTALLING SCRIPT FOR VERSION 10.2.');
                                $ELSIF DBMS_DB_VERSION.VER_LE_11_1 $THEN
                                   DBMS_OUTPUT.PUT_LINE('INSTALLING SCRIPT FOR VERSION 11.1.');
                                $ELSIF DBMS_DB_VERSION.VER_LE_11_2 $THEN
                                   DBMS_OUTPUT.PUT_LINE('INSTALLING SCRIPT FOR VERSION 11.2.');
                                $ELSE
                                   DBMS_OUTPUT.PUT_LINE('This package is only working on Oracle RDBMS versions 10.2, 11.1, and 11.2.');
                                   RAISE_APPLICATION_ERROR(-31061, 'Unsupported Oracle RDBMS version'); 
                                $END
                            END;
                            /
                            • 12. Re: Pretty Print feature of the XMLType methods is broken in Oracle 11g
                              User800295-Oracle
                              Hi, I'm looking for a replacement in 11g of the .extract(/*) as I'm not able to have the XMLSERIALIZE working as I want.

                              If you run:
                              SELECT XMLELEMENT("test",
                              XMLELEMENT("test2", NULL),
                              XMLELEMENT("test3", NULL)).EXTRACT('/*')
                              FROM dual

                              The result is not pretty printed in 11g but it was in earlier versions:

                              <test><test2></test2><test3></test3></test>

                              The .EXTRACT('/*') is being used in several queries in our code, so my question is: is there a good replacement for the .EXTRACT('/*') to have the query above pretty printing ?

                              Thanks, Veggie
                              • 13. Re: Pretty Print feature of the XMLType methods is broken in Oracle 11g
                                odie_63
                                Hi,
                                I'm looking for a replacement in 11g of the .extract(/*) as I'm not able to have the XMLSERIALIZE working as I want.
                                XMLSerialize is the replacement.
                                What's exactly the issue you have with it?

                                Is it related to empty elements?
                                SQL> SELECT XMLSerialize(document
                                  2           XMLElement("test",
                                  3             XMLElement("test2", NULL),
                                  4             XMLElement("test3", NULL)
                                  5           )
                                  6           as clob indent
                                  7         )
                                  8  FROM dual
                                  9  ;
                                
                                XMLSERIALIZE(DOCUMENTXMLELEMENT("TEST",XMLELEMENT("TEST2",NULL),XMLELEMENT("TEST
                                --------------------------------------------------------------------------------
                                <test>
                                  <test2/>
                                  <test3/>
                                </test>
                                
                                {code}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                • 14. Re: Pretty Print feature of the XMLType methods is broken in Oracle 11g
                                  mdrake-Oracle
                                  Let me be clear. The fact that extract() pretty printed at all was a very serious performance bug. the current behavior is correct and extract has been depricated

                                  If you want to format your XML while serializing it in order to make it easier for humans to read it you must use xmlserialize. the output from xmlserialize can be pretty printed. note that computers do not care wether or not the XML is formatted so the overhead of pretty printing should only be incurred when a human needs to view the output using a editor or viewer that cannot format the XML itself

                                  Edited by: mdrake on Feb 13, 2012 11:32 AM
                                  1 2 Previous Next