problem extracing xml from clob with colon in tag
670810May 17 2010 — edited May 17 2010I'm having a problem extracting XML-data (from a CLOB) when there is a colon (:) in one/more tags.
-- Creating a function to use the CLOB-column as an XMLType-column:
CREATE OR REPLACE FUNCTION TO_XMLTYPE(COL CLOB)
RETURN XMLTYPE AS
BEGIN
RETURN XMLType(COL);
END;
/
-- Inserting some test data:
INSERT INTO XML_TEST VALUES ('<xml-fragment><header><mark1>1111</mark1></header><body><mark2>thisiswhatimlookingfor</mark2></body></xml-fragment>');
commit;
-- Fetching the data from the column:
SELECT REPLACE (REPLACE (VALUE (p), '<mark2>', ''),'</mark2>','') NAME FROM XML_TEST w
, TABLE(XMLSEQUENCE(EXTRACT(TO_XMLTYPE(CLOB_COLUMN),'/xml-fragment/body/mark2'))) p;
-- Working!! exactly what i was looking for:
NAME
--------------------------------------------------------------------------------
thisiswhatimlookingfor
Okay, lets try again with a colon in the tags.
delete from xml_test;
commit;
-- Inserting some test data, with colon in tag:
INSERT INTO XML_TEST VALUES ('<xml-fragment><header><mark:1>1111</mark:1></header><body><mark:2>thisiswhatimlookingfor</mark:2></body></xml-fragment>');
commit;
-- Fetching the data:
SQL>SELECT REPLACE (REPLACE (VALUE (p), '<mark:2>', ''),'</mark:2>','') NAME FROM XML_TEST w
, TABLE(XMLSEQUENCE(EXTRACT(TO_XMLTYPE(CLOB_COLUMN),'/xml-fragment/body/mark:2'))) p;
SQL> SELECT REPLACE (REPLACE (VALUE (p), '<mark:2>', ''),'</mark:2>','') NAME FROM XML_TEST w
, TABLE(XMLSEQUENCE(EXTRACT(TO_XMLTYPE(CLOB_COLUMN),'/xml-fragment/body/mark:2'))) p; 2
, TABLE(XMLSEQUENCE(EXTRACT(TO_XMLTYPE(CLOB_COLUMN),'/xml-fragment/body/mark:2'))) p
*
ERROR at line 2:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00110: Warning: invalid QName "mark:1" (not a Name)
Error at line 1
ORA-06512: at "SYS.XMLTYPE", line 254
ORA-06512: at "MYUSERNAME.TO_XMLTYPE", line 4
SQL>
Any idea how to solve this?