4 Replies Latest reply: Sep 1, 2012 3:28 AM by 905160 RSS

    XML Parsing error

    905160
      Hi, I am getting the below error
      step 1: create table test_dept(dno number, dname varchar2(20))
      
      insert into test_dept values(10,'10-Sourcing dept');
      insert into test_dept values(20,'20-Audting dept');
      insert into test_dept values(30,'30-Computer dept');
      
      
      select * from test_dept;
      DNO     DNaME
      10     10-Sourcing dept
      30     30-Computer dept
      20     20-Audting dept
      
      step 2:
      create table test_web (name varchar2(60), xml_col xmltype) ;
      step 3:
      create sequence test1_seq start with 100 increment by 1
      
      step 4: 
      test.out has the following contents (it is not .xml file) and i dont have <?xml version="1.0" ?> tag at the begining
      
      <File>
        <File_Type>Type1</File_Type> 
      <File_Header_Record>
          <file_num>WP10</file_num> 
       </File_Header_Record>
      <Transaction>
        <Transaction_Type>TR 1</Transaction_Type> 
         <Amount> 
          <Amounts>100</Amounts>
         </Amount> 
      <Depts>
        <Dept_Type>Sourcing</Dept_Type> 
        <Dept>10</Dept> 
      </Depts>
      </Transaction>
      <Transaction>
        <Transaction_Type>TR 2</Transaction_Type> 
         <Amount> 
          <Amounts>200</Amounts>
         </Amount> 
      <Depts>
        <Dept_Type>Auditing</Dept_Type> 
        <Dept>20</Dept> 
      </Depts>
      </Transaction>
      <Transaction>
        <Transaction_Type>TR 3</Transaction_Type> 
         <Amount> 
          <Amounts>300</Amounts>
         </Amount> 
      <Depts>
        <Dept_Type>Computer</Dept_Type> 
        <Dept>30</Dept> 
      </Depts>
      </Transaction>
      </File>
      
      step 5:
      
      DECLARE
         v_check_file_exist   BOOLEAN;
         v_file_length        NUMBER;
         v_block_size         NUMBER;
         v_file_path          VARCHAR2 (100);
         v_file_name          VARCHAR2 (100);
         v_file_type           UTL_FILE.FILE_TYPE;
         v_str                varchar2 (32760);
         v_cnt      number;
      v_seq number;
      --check whether file exists and has data in it
      BEGIN
         v_file_path := '/usr/tmp';
         v_file_name := 'test.xml.out';
         -- initailise the variables
         v_cnt := 0;
         UTL_FILE.FGETATTR (v_file_path,
                            v_file_name,
                            v_check_file_exist,
                            v_file_length,
                            v_block_size);
         if v_check_file_exist
         then
          DBMS_OUTPUT.put_line (' File exists');
            IF v_file_length > 0 AND v_block_size > 0
            THEN
                   BEGIN
                     select test1_seq.nextval into v_seq from dual;
                       insert into test_web values(v_seq,v_file_name);
                    --  
                  EXCEPTION
                     WHEN NO_DATA_FOUND
                     THEN
                         DBMS_OUTPUT.put_line ('No data found '||SQLERRM);
                     WHEN OTHERS
                     THEN
                        DBMS_OUTPUT.put_line ('others '||SQLERRM);
                  END;
            commit;
            ELSE
               DBMS_OUTPUT.put_line ('No Data in  File');
            END IF;
         ELSE
            DBMS_OUTPUT.put_line (' File Not Available');
         END IF;
      END;
      /
      
      I am getting below error 
      
      
      File exists
      others ORA-31011: XML parsing failed
      ORA-19202: Error occurred in XML
      processing
      LPX-00210: expected '<' instead of 't'
      Error at line 1
      
      My requirement is to replace the 
      
      1. contents of the file WP10 from   <file_num>WP10</file_num>  
      with sequence value 'WP101' because sequence is starting at 100
      
      2. Replace all Dept tag  <Dept>30</Dept>  with Dname value from test_dept table
      
      for example 
      
        <Dept>10</Dept>  should be replaced with <Dept>10-Sourcing dept</Dept>
      
      update the 
      
      when i say select * from test_web;
      
      It should contain this row 
      
      101          <File>
        <File_Type>Type1</File_Type> 
      <File_Header_Record>
          <file_num>WP101</file_num> 
       </File_Header_Record>
      <Transaction>
        <Transaction_Type>TR 1</Transaction_Type> 
         <Amount> 
          <Amounts>100</Amounts>
         </Amount> 
      <Depts>
        <Dept_Type>Sourcing</Dept_Type> 
        <Dept>10-Sourcing dept</Dept> 
      </Depts>
      </Transaction>
      <Transaction>
        <Transaction_Type>TR 2</Transaction_Type> 
         <Amount> 
          <Amounts>200</Amounts>
         </Amount> 
      <Depts>
        <Dept_Type>Auditing</Dept_Type> 
        <Dept>20-Audting dept</Dept> 
      </Depts>
      </Transaction>
      <Transaction>
        <Transaction_Type>TR 3</Transaction_Type> 
         <Amount> 
          <Amounts>300</Amounts>
         </Amount> 
      <Depts>
        <Dept_Type>Computer</Dept_Type> 
        <Dept>30-Computer dept</Dept> 
      </Depts>
      </Transaction>
      </File>
      Appreciate your help
        • 1. Re: XML Parsing error
          AlexAnd
          for a start
          >
          step 4:
          test.out has the following contents (it is not .xml file) and i dont have <?xml version="1.0" ?> tag at the begining

          <File>
          <File_Type>Type1</File_Type>
          <File_Header_Record>
          <file_num>WP10</file_num>
          </File_Header_Record>
          <Transaction>
          <Transaction_Type>TR 1</Transaction_Type>
          <Amount>
          <Amounts>100</Amounts>
          </Amount>
          <Depts>
          <Dept_Type>Sourcing</Dept_Type>
          <Dept>10</Dept>
          </Depts>
          </Transaction>
          <Transaction>
          <Transaction_Type>TR 2</Transaction_Type>
          <Amount>
          <Amounts>200</Amounts>
          </Amount>
          <Depts>
          <Dept_Type>Auditing</Dept_Type>
          <Dept>20</Dept>
          </Depts>
          </Transaction>
          <Transaction>
          <Transaction_Type>TR 3</Transaction_Type>
          <Amount>
          <Amounts>300</Amounts>
          </Amount>
          <Depts>
          <Dept_Type>Computer</Dept_Type>
          <Dept>30</Dept>
          </Depts>
          </Transaction>
          </File>
          >
          for me it's xml content and parse as xml :)



          >
          v_file_path := '/usr/tmp';
          v_file_name := 'test.xml.out';
          -- initailise the variables
          v_cnt := 0;
          UTL_FILE.FGETATTR (v_file_path,
          v_file_name,
          v_check_file_exist,
          v_file_length,
          v_block_size);
          if v_check_file_exist
          then
          >
          v_file_path it's oracle directory; not os path. look at http://psoug.org/reference/utl_file.html

          >
          insert into test_web values(v_seq,v_file_name);
          >
          bad

          may be
          SQL>  create table test_web (name varchar2(60), xml_col xmltype) ;
           
          Table created
           
          SQL> create sequence test1_seq start with 100 increment by 1;
           
          Sequence created
           
          SQL> 
          SQL> insert into test_web (name, xml_col)
            2    select test1_seq.nextval, xmltype(bfilename('MYDIR','test.out'), nls_charset_id('AL32UTF8'))
            3     from dual;
           
          1 row inserted
           
          SQL> 
          'test.xml.out';
          >
          looks like apps server output :)
          • 2. Re: XML Parsing error
            odie_63
            I am getting below error

            ORA-31011: XML parsing failed
            ORA-19202: Error occurred in XML
            processing
            LPX-00210: expected '<' instead of 't'
            Error at line 1
            Of course you are, you're trying to insert the file name into the XMLType column, not the actual content of the file ;)
            As Alex said, create a directory object pointing to your folder and use it to access the file.

            You have not mention any database version yet. Can you do that? (SELECT * FROM v$version)


            In the meantime, here's a possible solution for your requirement (tested on 11.2) :
            1) Insert the file content into the table :
            SQL> insert into test_web (name, xml_col)
              2  values( test1_seq.nextval, xmltype(bfilename('TEST_DIR','test.out'), nls_charset_id('AL32UTF8')) )
              3  ;
             
            1 row inserted
             
            SQL> set long 5000
            SQL> select * from test_web;
             
            NAME     XML_COL
            -------- --------------------------------------------------------------------------------
            101      <File>
                       <File_Type>Type1</File_Type>
                       <File_Header_Record>
                         <file_num>WP10</file_num>
                       </File_Header_Record>
                       <Transaction>
                         <Transaction_Type>TR 1</Transaction_Type>
                         <Amount>
                           <Amounts>100</Amounts>
                         </Amount>
                         <Depts>
                           <Dept_Type>Sourcing</Dept_Type>
                           <Dept>10</Dept>
                         </Depts>
                       </Transaction>
                       <Transaction>
                         <Transaction_Type>TR 2</Transaction_Type>
                         <Amount>
                           <Amounts>200</Amounts>
                         </Amount>
                         <Depts>
                           <Dept_Type>Auditing</Dept_Type>
                           <Dept>20</Dept>
                         </Depts>
                       </Transaction>
                       <Transaction>
                         <Transaction_Type>TR 3</Transaction_Type>
                         <Amount>
                           <Amounts>300</Amounts>
                         </Amount>
                         <Depts>
                           <Dept_Type>Computer</Dept_Type>
                           <Dept>30</Dept>
                         </Depts>
                       </Transaction>
                     </File>
             
            2) Update the XML document via an XSL transformation :
            SQL> update test_web t
              2  set t.xml_col =
              3         xmltransform(
              4           t.xml_col
              5         , xmltype(
              6  q'!<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
              7    <xsl:output method="xml"/>
              8    <xsl:param name="new_fn"/>
              9    <xsl:template match="@*|node()">
             10      <xsl:copy>
             11        <xsl:apply-templates select="@*|node()"/>
             12      </xsl:copy>
             13    </xsl:template>
             14    <xsl:template match="Dept">
             15      <xsl:copy>
             16        <xsl:value-of select="document(concat('/oradb/DEV/TEST_DEPT/ROW[DNO=',.,']/DNAME'))"/>
             17      </xsl:copy>
             18    </xsl:template>
             19    <xsl:template match="file_num">
             20      <xsl:copy>
             21        <xsl:value-of select="$new_fn"/>
             22      </xsl:copy>
             23    </xsl:template>
             24  </xsl:stylesheet>!'
             25           )
             26         , 'new_fn="''WP' || t.name || '''"'
             27         )
             28  where t.name = 101
             29  ;
             
            1 row updated
             
            Please note that I hardcoded my testing schema ("DEV") in this expression, you'll have to replace it by your own :
            <xsl:value-of select="document(concat('/oradb/DEV/TEST_DEPT/ROW[DNO=',.,']/DNAME'))"/>
            3) Checking...
            SQL> select * from test_web;
             
            NAME     XML_COL
            -------- --------------------------------------------------------------------------------
            101      <?xml version="1.0" encoding="UTF-8"?>
                     <File>
                       <File_Type>Type1</File_Type>
                       <File_Header_Record>
                         <file_num>WP101</file_num>
                       </File_Header_Record>
                       <Transaction>
                         <Transaction_Type>TR 1</Transaction_Type>
                         <Amount>
                           <Amounts>100</Amounts>
                         </Amount>
                         <Depts>
                           <Dept_Type>Sourcing</Dept_Type>
                           <Dept>10-Sourcing dept</Dept>
                         </Depts>
                       </Transaction>
                       <Transaction>
                         <Transaction_Type>TR 2</Transaction_Type>
                         <Amount>
                           <Amounts>200</Amounts>
                         </Amount>
                         <Depts>
                           <Dept_Type>Auditing</Dept_Type>
                           <Dept>20-Audting dept</Dept>
                         </Depts>
                       </Transaction>
                       <Transaction>
                         <Transaction_Type>TR 3</Transaction_Type>
                         <Amount>
                           <Amounts>300</Amounts>
                         </Amount>
                         <Depts>
                           <Dept_Type>Computer</Dept_Type>
                           <Dept>30-Computer dept</Dept>
                         </Depts>
                       </Transaction>
                     </File>
             
            • 3. Re: XML Parsing error
              905160
              Yes, correct it is apps output....Thanks AlexAnd and Odie, I will test this out and update you....Sorry just realized i was giving file name...My database is down now, If not i would have tested it immediately.

              Thanks a ton.....
              • 4. Re: XML Parsing error
                905160
                Thanks a lot Odie, It worked fine....