Home » Developer & Programmer » JDeveloper, Java & XML » Parsing XML with special character as part of data (Oracle 11.2.0.3.0 EE )
Parsing XML with special character as part of data [message #595352] Tue, 10 September 2013 09:26 Go to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Hi guys, I'm using following select to parse XML as table ( Oracle 11.2.0.3.0 EE Linux and Windows)

SELECT a.id, a.name, a.description
  FROM ( XMLTABLE('//ROW' 
         PASSING (SELECT xmltype('<ROWSET>
                         <ROW><ID>1111</ID><Name>Name1</Name><DESCRIPTION>AAA</DESCRIPTION></ROW>
                         <ROW><ID>2222</ID><Name>Name2</Name><DESCRIPTION>BBB</DESCRIPTION></ROW>
                       </ROWSET>') txml
                  FROM dual) 
            COLUMNS id VARCHAR2(20) PATH '/ROW/ID',
                    name VARCHAR2(30) PATH '/ROW/Name',
                    description VARCHAR2(50) PATH '/ROW/DESCRIPTION') ) a


However, when DESCRIPTION contains "<" as part of it - I'm receiving an error:

SELECT a.id, a.name, a.description
  FROM ( XMLTABLE('//ROW' 
         PASSING (SELECT xmltype('<ROWSET>
                         <ROW><ID>1111</ID><Name>Name1</Name><DESCRIPTION>AAA</DESCRIPTION></ROW>
                         <ROW><ID>2222</ID><Name>Name2</Name><DESCRIPTION>B <= B</DESCRIPTION></ROW>
                       </ROWSET>') txml
                  FROM dual) 
            COLUMNS id VARCHAR2(20) PATH '/ROW/ID',
                    name VARCHAR2(30) PATH '/ROW/Name',
                    description VARCHAR2(50) PATH '/ROW/DESCRIPTION') ) a


Quote:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00231: Invalid character 61 ("=") found in Name or Nmtoken


I'm receiving a XML from some external source and can not change it.

Any ideas how to process it?

Michael
Re: Parsing XML with special character as part of data [message #595353 is a reply to message #595352] Tue, 10 September 2013 09:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I'm receiving a XML from some external source and can not change it.


Yes you can change it, anyway you receive it... and you have no other choice.

Regards
Michel
Re: Parsing XML with special character as part of data [message #595399 is a reply to message #595353] Tue, 10 September 2013 22:20 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
If you can identify all of the unacceptable things that make it invalid XML and they can be replaced without invalidating the rest of the XML, then you can use the REPLACE function to fix it or create a separate function and use that. I have demonstrated both methods below, replacing "<=" with "&lt;=", using the xml/html entity for "<".

SCOTT@orcl12c> SET DEFINE OFF SCAN OFF
SCOTT@orcl12c> COLUMN name FORMAT A5
SCOTT@orcl12c> COLUMN description FORMAT A11
SCOTT@orcl12c> SELECT a.id, a.name, a.description
  2  FROM   XMLTABLE
  3  	      ('//ROW'
  4  	       PASSING
  5  		 (SELECT XMLTYPE
  6  			   (REPLACE
  7  			     ('<ROWSET>
  8  				 <ROW><ID>1111</ID><Name>Name1</Name><DESCRIPTION>AAA</DESCRIPTION></ROW>
  9  				 <ROW><ID>2222</ID><Name>Name2</Name><DESCRIPTION>B <= B</DESCRIPTION></ROW>
 10  			       </ROWSET>',
 11  			       '<=', '&lt;=')) txml
 12  		  FROM	 dual)
 13  	       COLUMNS
 14  		 id	     VARCHAR2(20) PATH '/ROW/ID',
 15  		 name	     VARCHAR2(30) PATH '/ROW/Name',
 16  		 description VARCHAR2(50) PATH '/ROW/DESCRIPTION') a
 17  /

ID                   NAME  DESCRIPTION
-------------------- ----- -----------
1111                 Name1 AAA
2222                 Name2 B <= B

2 rows selected.


SCOTT@orcl12c> CREATE OR REPLACE FUNCTION fix_xml
  2    (p_clob IN CLOB)
  3    RETURN	  XMLTYPE
  4  AS
  5    v_clob	  CLOB := p_clob;
  6  BEGIN
  7    v_clob := REPLACE (v_clob, '<=', '&lt;=');
  8    RETURN XMLTYPE (v_clob);
  9  END fix_xml;
 10  /

Function created.

SCOTT@orcl12c> SHOW ERRORS
No errors.
SCOTT@orcl12c> SET DEFINE OFF SCAN OFF
SCOTT@orcl12c> COLUMN name FORMAT A5
SCOTT@orcl12c> COLUMN description FORMAT A11
SCOTT@orcl12c> SELECT a.id, a.name, a.description
  2  FROM   XMLTABLE
  3  	      ('//ROW'
  4  	       PASSING
  5  		 (SELECT fix_xml
  6  			   ('<ROWSET>
  7  			       <ROW><ID>1111</ID><Name>Name1</Name><DESCRIPTION>AAA</DESCRIPTION></ROW>
  8  			       <ROW><ID>2222</ID><Name>Name2</Name><DESCRIPTION>B <= B</DESCRIPTION></ROW>
  9  			     </ROWSET>') txml
 10  		  FROM	 dual)
 11  	       COLUMNS
 12  		 id	     VARCHAR2(20) PATH '/ROW/ID',
 13  		 name	     VARCHAR2(30) PATH '/ROW/Name',
 14  		 description VARCHAR2(50) PATH '/ROW/DESCRIPTION') a
 15  /

ID                   NAME  DESCRIPTION
-------------------- ----- -----------
1111                 Name1 AAA
2222                 Name2 B <= B

2 rows selected.

Re: Parsing XML with special character as part of data [message #595408 is a reply to message #595399] Wed, 11 September 2013 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is what I meant by "yes, you can change it". Wink

Regards
Michel
Re: Parsing XML with special character as part of data [message #595409 is a reply to message #595408] Wed, 11 September 2013 01:11 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Thanks,

Found it by myself (after Michel's hint).

Re: Parsing XML with special character as part of data [message #595415 is a reply to message #595409] Wed, 11 September 2013 02:04 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Wrapping the solution:

Eventually I took Barbara's proposal to use "private" function, but I used it to add CDATA tag for a problematic column instead of dealing with all possible problems.
Re: Parsing XML with special character as part of data [message #595416 is a reply to message #595415] Wed, 11 September 2013 02:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please post your solution.

Regards
Michel
Re: Parsing XML with special character as part of data [message #595512 is a reply to message #595416] Wed, 11 September 2013 08:03 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Full solution:

CREATE OR REPLACE FUNCTION ADD_CDATA_TAG2xml (p_clob IN CLOB, p_tag VARCHAR2) RETURN CLOB AS
    v_clob    CLOB := p_clob;
BEGIN
    v_clob := REPLACE (v_clob, '<' || p_tag || '>' , '<' || p_tag || '><![CDATA[');
    v_clob := REPLACE (v_clob, '</' || p_tag || '>' , ']]></' || p_tag || '>');
    RETURN v_clob;
END ADD_CDATA_TAG2xml;


SELECT a.id, a.name, a.description
   FROM   XMLTABLE
   	      ('//ROW'
   	       PASSING
   		 XMLTYPE(ADD_CDATA_TAG2xml
   			   ( '<ROWSET>
   			       <ROW><ID>1111</ID><Name>Name1</Name><DESCRIPTION>AAA</DESCRIPTION></ROW>
   			       <ROW><ID>2222</ID><Name>Name2</Name><DESCRIPTION>B < B</DESCRIPTION></ROW>
   			     </ROWSET>', 'DESCRIPTION' ) )
   	       COLUMNS
   		 id	     VARCHAR2(20) PATH '/ROW/ID',
   		 name	     VARCHAR2(30) PATH '/ROW/Name',
   		 description VARCHAR2(50) PATH '/ROW/DESCRIPTION') a
Re: Parsing XML with special character as part of data [message #595534 is a reply to message #595512] Wed, 11 September 2013 09:35 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks to let us know.

[To fill hole in my memory]
SQL> select ADD_CDATA_TAG2xml
  2  ( '<ROWSET>
  3      <ROW><ID>1111</ID><Name>Name1</Name><DESCRIPTION>AAA</DESCRIPTION></ROW>
  4      <ROW><ID>2222</ID><Name>Name2</Name><DESCRIPTION>B < B</DESCRIPTION></ROW>
  5    </ROWSET>', 'DESCRIPTION' ) val
  6  from dual;
VAL
--------------------------------------------------------------------------------------------
<ROWSET>
    <ROW><ID>1111</ID><Name>Name1</Name><DESCRIPTION><![CDATA[AAA]]></DESCRIPTION></ROW>
    <ROW><ID>2222</ID><Name>Name2</Name><DESCRIPTION><![CDATA[B < B]]></DESCRIPTION></ROW>
  </ROWSET>

SQL> SELECT a.id, a.name, a.description
  2     FROM   XMLTABLE
  3            ('//ROW'
  4             PASSING
  5        XMLTYPE(ADD_CDATA_TAG2xml
  6           ( '<ROWSET>
  7               <ROW><ID>1111</ID><Name>Name1</Name><DESCRIPTION>AAA</DESCRIPTION></ROW>
  8               <ROW><ID>2222</ID><Name>Name2</Name><DESCRIPTION>B < B</DESCRIPTION></ROW>
  9             </ROWSET>', 'DESCRIPTION' ) )
 10             COLUMNS
 11        id      VARCHAR2(20) PATH '/ROW/ID',
 12        name      VARCHAR2(30) PATH '/ROW/Name',
 13        description VARCHAR2(50) PATH '/ROW/DESCRIPTION') a
 14  /
ID                   NAME                           DESCRIPTION
-------------------- ------------------------------ --------------------------------------------------
1111                 Name1                          AAA
2222                 Name2                          B < B


Regards
Michel
Previous Topic: xml parse values
Next Topic: error when i run any page with view object
Goto Forum:
  


Current Time: Fri Mar 29 06:00:33 CDT 2024