7 Replies Latest reply: Oct 31, 2013 12:12 PM by Jason_(A_Non) RSS

    Parse the XML

    936666

      How to parse the XML

      Scenarios:

      1) If no value is give to a user defined XML then i am getting error as

      Select * from TABLE(FN_XMLInfo(XMLTYPE('')));

      SQL Error: ORA-19032: Expected XML tag , got no content

      2) If an invalid XML is passed then

      Select * from TABLE(FN_XMLInfo(XMLTYPE('12')));

      SQL Error: ORA-31011: XML parsing failed

      ORA-06512: at "SYS.XMLTYPE", line 310

      *Cause:    XML parser returned an error while trying to parse the document.

      *Action:   Check if the document to be parsed is valid.

       

      how to validate XML inside the user defined function and return the error message.

       

      Thanks!

        • 1. Re: Parse the XML
          odie_63

          Hi,

           

          What is your function supposed to do and return ?

          Is this an attempt to parse the XML and pipe the values back in relational form ?

           

          What's the db version?

          • 2. Re: Parse the XML
            936666

            My function returns a table.

             

            CREATE TYPE type_a AS OBJECT (

              Row_num    NUMBER(5)    ,

              D_Type  VARCHAR2(50) ,

              Y_Type VARCHAR2(50) );

             

            CREATE TYPE type_table IS TABLE OF type_a; 

             

            CREATE OR REPLACE FUNCTION FN_XMLInfo ( F_inputXML xmltype)

            RETURN type_table AS

              l_tab  type_table;

            BEGIN

            with t as ( select(F_inputXML) as xml from dual)

            select type_a( Rownum, x.D_type,x.Y_Type)

            bulk collect into  l_tab from t ,xmltable('TableEntry'

                               passing t.xml

                               columns D_Type varchar2(50) path '/TableEntry/D_type',

                                Y_Type varchar2(50) path '/TableEntry/Y_type'

                                 ) x;

            RETURN    l_tab;               

            end;  

             

            how to handle null and error in the above function.

            Oracle version :PL/SQL Release 11.2.0.1.0

            • 3. Re: Parse the XML
              odie_63

              Sorry to be blunt but your function is pointless. It just adds an unnecessary PL/SQL layer, and it's not even pipelined.

              Are you using the base query in so many places that you have to use that wrapper function ?

               

              That being said...

              Since you're supposed to pass a valid XMLType instance to the function, then obviously there's nothing you can do to handle the error in the function itself.

              You have to either validate the XML wellformedness before passing it to the function, or not passing XMLType at all but a CLOB instead, that way constructing the XMLType instance is left to the function, where you'd then be able to handle those exceptions and act accordingly.

              • 4. Re: Parse the XML
                936666

                Thanks for your suggestion how to rewrite my code in a better manner, why you are insisting to have pipelined I have to split and read the values.

                • 5. Re: Parse the XML
                  Jason_(A_Non)

                  Because PIPELINED is required for a standalone function to be used with the TABLE function

                  PL/SQL Optimization and Tuning

                  (points to the Creating Pipelined Table Functions section)

                   

                  His original question is still valid.  Why are you doing it the way you showed when it simply adds extra layers that are not necessary and clutter up the code as you showed it to us?

                  • 6. Re: Parse the XML
                    odie_63

                    Jason_(A_Non) wrote:

                     

                    Because PIPELINED is required for a standalone function to be used with the TABLE function

                    This statement is ambiguous, technically, we don't need the function to be declared pipelined to use the TABLE operator (OP's current implementation works that way).

                    However, as the link explains, a pipelined function doesn't have to build the whole collection in PGA memory to start returning rows to the caller, hence providing a great deal of optimization.

                    • 7. Re: Parse the XML
                      Jason_(A_Non)

                      Ahhhh.

                      Never used it before and all the examples I've ever seen have had it and just did a quick search/read of the documentation to come up with that link.  I supposed I've always seen them used together for exactly the reason you state.  The entire result set is not built first in memory and then transferred, it is returned as each row becomes available (is retrieved).