7 Replies Latest reply: Feb 28, 2013 6:52 AM by Devid RSS

    XML XQuery

    Devid
      Hi Experts,

      I am beginner in XML programming with PL/SQL.
      when i am going to extract data from XMLTYPE column then some error is showing as:

      ORA-19114: error during parsing the XQuery expression:
      ORA-06550: line 1, column 13:
      PLS-00201: identifier 'SYS.DBMS_XQUERYINT' must be declared
      ORA-06550: line 1, column 7:
      PL/SQL: Statement ignored


      my script is :



      create table test (xdata xmltype);

      insert into test(xdata)
      values (
      '<?xml version="1.0" encoding="UTF-8" ?>
      <data>
      <name>Vivek</name>
      </data>'
      );


      select * from test,
      xmltable('/data'
      passing xdata
      columns data_name varchar2(20) path '@name'
      );
        • 1. Re: XML XQuery
          ascheffer
          As far as I know xmltable doesn't work on a Oracle 10 XE database
          • 2. Re: XML XQuery
            Devid
            Thanks,

            Now it is working on 11g Enterprice, but other error is showing........

            ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence


            The script are :

            create table test (xdata xmltype);

            truncate table test;

            insert into test(xdata)
            values (
            '<?xml version="1.0" encoding="UTF-8" ?>
            <data>
            <name>Vivek</name>
            <name>Vivek</name>
            </data>'
            );


            select data_name from test,
            xmltable('/data'
            passing xdata
            columns data_name varchar2(20) path 'name'
            );



            Database Details:
            Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
            PL/SQL Release 11.2.0.2.0 - Production
            CORE     11.2.0.2.0     Production
            • 3. Re: XML XQuery
              Devid
              ....

              Edited by: Devid on Feb 28, 2013 6:06 PM
              • 4. Re: XML XQuery
                Devid
                ...
                • 5. Re: XML XQuery
                  odie_63
                  This should be better :
                  select data_name 
                  from test,
                       xmltable('/data/name'
                         passing xdata
                         columns data_name varchar2(20) path '.'
                  );
                  • 6. Re: XML XQuery
                    ascheffer
                    with test as
                    ( select xmltype( '<?xml version="1.0" encoding="UTF-8" ?>
                    <data>
                    <name>Vivek</name>
                    <name>Vivek2</name>
                    </data>' ) xdata
                    from dual
                    )
                    select data_name from test,
                    xmltable('/data/name'
                    passing xdata
                    columns data_name varchar2(20) path '.'
                    )
                    • 7. Re: XML XQuery
                      Devid
                      Thanks