Parsing XML with special character as part of data [message #595352] |
Tue, 10 September 2013 09:26 |
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 #595399 is a reply to message #595353] |
Tue, 10 September 2013 22:20 |
|
Barbara Boehmer
Messages: 9104 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 "<=", 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 '<=', '<=')) 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, '<=', '<=');
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 #595512 is a reply to message #595416] |
Wed, 11 September 2013 08:03 |
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 |
|
Michel Cadot
Messages: 68732 Registered: March 2007 Location: Saint-Maur, France, https...
|
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
|
|
|