This discussion is archived
4 Replies Latest reply: Sep 1, 2012 1:28 AM by 905160 RSS

XML Parsing error

905160 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks a lot Odie, It worked fine....

Legend

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