Home » Developer & Programmer » JDeveloper, Java & XML » Varchar2 to XML conversion (Oracle 10g)
Varchar2 to XML conversion [message #451520] Thu, 15 April 2010 03:30 Go to next message
harshalonline22
Messages: 74
Registered: March 2007
Location: Pune
Member
Hi,

Is there any functionality provided in oracle to convert Varchar2 data into XmlType ??

Below is the scnario :

Suppose i have tables as below ,
CREATE TABLE EXCPTN_VARCHAR
(
    EXCPTN_ID     NUMBER(38)     NOT NULL,
    EXCPTN_RECRD  VARCHAR2(4000)     NULL
);

CREATE TABLE EXCPTN_XML
(
    EXCPTN_ID     NUMBER(38)     NOT NULL,
    EXCPTN_RECRD  XMLTYPE     NULL
);


So when i am trying to insert rows in EXCPTN_XML
table using EXCPTN_VARCHAR table like,

 insert into EXCPTN_XML
 select  e.EXCPTN_ID,
         e.EXCPTN_RECRD
 from EXCPTN_VARCHAR e;


Then it is giving error :
ORA-31011 : XML Parsing Failed
ORA-19202 : Error occured in XML Processing

So, How can we convert Varchar2 to XmlType?

Best Regards,
Harshal N.
Re: Varchar2 to XML conversion [message #451522 is a reply to message #451520] Thu, 15 April 2010 03:44 Go to previous messageGo to next message
Michel Cadot
Messages: 60062
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-31011: XML parsing failed
 *Cause:   XML parser returned an error while trying to parse the document.
 *Action:  Check if the document to be parsed is valid.

There is no problem is your string is a valid XML.

Regards
Michel
Re: Varchar2 to XML conversion [message #451523 is a reply to message #451520] Thu, 15 April 2010 03:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You're getting that error because the values that you are inserting into the XMLType column are not valid xml.

Here's an example that you can run in SQL*Plus that shows the problem:
create table test_183 (col_1 xmltype);

insert into test_183 values ('<start><tag_1>a</tag_1><tag_2>b</tag_2></start>');

insert into test_183 values ('<start><tag_1>a</tag_1><tag_2>b</start>');


YOu can use XMLTYPE(<string>) to convert a varchar2 into an XMLTYPE, but it will only work if the string contains valid XML.
Re: Varchar2 to XML conversion [message #451540 is a reply to message #451523] Thu, 15 April 2010 04:36 Go to previous messageGo to next message
harshalonline22
Messages: 74
Registered: March 2007
Location: Pune
Member
This is what the data i have in EXCPTN_VARCHAR.EXCPTN_RECRD column.

<RECORD  UNQ_SOI_ID = "2241199" PRICE_SRC_BATCH_ID = "-1" AS_OF_DT = "2010-04-07 00:00:00" PRTY_NBR = " 2" CREATE_DT = "2010-04-07 13:51:28" />


Best Regards,
Harshal N.

[Updated on: Thu, 15 April 2010 04:39]

Report message to a moderator

Re: Varchar2 to XML conversion [message #451541 is a reply to message #451540] Thu, 15 April 2010 04:40 Go to previous messageGo to next message
Michel Cadot
Messages: 60062
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select xmltype('<RECORD  UNQ_SOI_ID = "2241199" PRICE_SRC_BATCH_ID = "-1" AS_OF_DT = "2010-04-07 00:00:00" PRTY_NBR = " 2" CREATE_DT = "2010-04-07 13:51:28" />') from dual;
XMLTYPE('<RECORDUNQ_SOI_ID="2241199"PRICE_SRC_BATCH_ID="-1"AS_OF_DT="2010-04-0700:00:00"PRTY_NBR="2"CREATE_DT="2010-04-0
------------------------------------------------------------------------------------------------------------------------
<RECORD  UNQ_SOI_ID = "2241199" PRICE_SRC_BATCH_ID = "-1" AS_OF_DT = "2010-04-07 00:00:00" PRTY_NBR = " 2" CREATE_DT = "
2010-04-07 13:51:28" />

1 row selected.

No problem.

Regards
Michel

[Updated on: Thu, 15 April 2010 04:42]

Report message to a moderator

Re: Varchar2 to XML conversion [message #451546 is a reply to message #451540] Thu, 15 April 2010 05:29 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There's something missing in your question.
If I run this:
CREATE TABLE EXCPTN_VARCHAR(EXCPTN_ID     NUMBER(38)     NOT NULL
                           ,EXCPTN_RECRD  VARCHAR2(4000));

CREATE TABLE EXCPTN_XML    (EXCPTN_ID     NUMBER(38)     NOT NULL
                           ,EXCPTN_RECRD  XMLTYPE);
                           
insert into excptn_varchar values (1,'<RECORD  UNQ_SOI_ID = "2241199" PRICE_SRC_BATCH_ID = "-1" AS_OF_DT = "2010-04-07 00:00:00" PRTY_NBR = " 2" CREATE_DT = "2010-04-07 13:51:28" />');   

 insert into EXCPTN_XML
 select  e.EXCPTN_ID,
         e.EXCPTN_RECRD
 from EXCPTN_VARCHAR e;
 
 commit;
then it works fine.

Do you have other rows in your table?
Re: Varchar2 to XML conversion [message #451560 is a reply to message #451541] Thu, 15 April 2010 07:28 Go to previous messageGo to next message
harshalonline22
Messages: 74
Registered: March 2007
Location: Pune
Member
Thanks. It is working.

Best Regards,
Harshal N.
Re: Varchar2 to XML conversion [message #451562 is a reply to message #451560] Thu, 15 April 2010 07:54 Go to previous messageGo to next message
Michel Cadot
Messages: 60062
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So what was the problem?

Regards
Michel
Re: Varchar2 to XML conversion [message #451650 is a reply to message #451562] Fri, 16 April 2010 00:11 Go to previous messageGo to next message
harshalonline22
Messages: 74
Registered: March 2007
Location: Pune
Member
There was a space between / and > which was causing problem.

Best Regardes,
HarsHal N.
Re: Varchar2 to XML conversion [message #451653 is a reply to message #451650] Fri, 16 April 2010 00:23 Go to previous messageGo to next message
Michel Cadot
Messages: 60062
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks, good to know it for a future question.

Regards
Michel
Re: Varchar2 to XML conversion [message #453674 is a reply to message #451653] Fri, 30 April 2010 04:41 Go to previous messageGo to next message
harshalonline22
Messages: 74
Registered: March 2007
Location: Pune
Member
Hi all,

I am geting error when running following sql :

Select XMLType('<RECORD COL1="EQUITY545734&<^*^>"/>') 
From dual;


Error:

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00242: invalid use of ampersand ('&') character (use &amp;)

In case of ampersand ('&') we can handle as below:

Select XMLType(replace('<RECORD COL1="EQUITY545734&<^*^>"/>','&','&amp;'))
From dual;


But then it is throwing error for '<' as well as for '>' like,
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00242: invalid use of less-than('<') character (use &lt;)

If we try to replace '<' then it will replace all occurrences of '<' and the query will not work for XMLType.

How to handle such scenario?

Best Regards,
Harshal N.
Re: Varchar2 to XML conversion [message #453680 is a reply to message #453674] Fri, 30 April 2010 05:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Clean the data and replace character like < with the appropriate markers before wrapping them in open and close tags.

Re: Varchar2 to XML conversion [message #453689 is a reply to message #453680] Fri, 30 April 2010 05:30 Go to previous messageGo to next message
harshalonline22
Messages: 74
Registered: March 2007
Location: Pune
Member
thats the only solution you find or is there any way we can handle it in Query??

Best Regards,
Harshal N.
Re: Varchar2 to XML conversion [message #453696 is a reply to message #453689] Fri, 30 April 2010 05:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If all of the < and > that you need to replace are going to be inside " delimited strings, you could do something like:
regexp_replace('<RECORD COL1="EQUITY545734&<^*<^>"/>','(".*)<(.*")','\1&gt\2')
Re: Varchar2 to XML conversion [message #454882 is a reply to message #453696] Sat, 08 May 2010 01:24 Go to previous messageGo to next message
harshalonline22
Messages: 74
Registered: March 2007
Location: Pune
Member
Hi,

I tried the provided regexp_replace but its not working for multiple occurences of < or >.

SELECT   
regexp_replace(replace('<RECORD COL1="&>>>"/>','&','&amp;'),'(".*)>(.*")','\1&gt;\2')  AS COL1
FROM dual;


O/P is like,

COL1 := <RECORD COL1="&amp;>>&gt;"/>


Best Regards,
Harshal N.
Re: Varchar2 to XML conversion [message #455056 is a reply to message #454882] Mon, 10 May 2010 01:22 Go to previous messageGo to next message
harshalonline22
Messages: 74
Registered: March 2007
Location: Pune
Member
If any one have any solution please revert.

Thanks in advance.

Best Regards,
Harshal N
Re: Varchar2 to XML conversion [message #455114 is a reply to message #455056] Mon, 10 May 2010 04:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Try this:
with src as (select '<RECORD COL1="&>>>"/>' col_1 from dual)
select regexp_substr(col_1,'^[^"]*') ||
       (replace(replace(replace(regexp_substr(col_1,'".*"'),'&','&amp;'),'<','&lt;'),'>','&gt;') ||
       regexp_substr(col_1,'[^"]*$'))
from src;

[Updated on: Mon, 10 May 2010 06:18] by Moderator

Report message to a moderator

Re: Varchar2 to XML conversion [message #455121 is a reply to message #455114] Mon, 10 May 2010 05:20 Go to previous messageGo to next message
harshalonline22
Messages: 74
Registered: March 2007
Location: Pune
Member
Hi JRowbottom,

Thanks a lot for solution. Its working now. However it would be great full if you can explain logic you have implemented for regexp_substr.

I thank you once again for all your effort.

Best Regards,
Harshal N.
Re: Varchar2 to XML conversion [message #473242 is a reply to message #455121] Thu, 26 August 2010 16:46 Go to previous messageGo to next message
harshalonline22
Messages: 74
Registered: March 2007
Location: Pune
Member
Hi,

I have a XML data with extra special character as below :

<RECORD ASK_PRICE = "40""1" CREATE_USR_ID = "asibm" /> 


This is causing XML parsing failed error.

I am trying to use following but it is not working,
select dbms_xmlgen.convert('<RECORD ASK_PRICE = "40""1" CREATE_USR_ID = "asibm" />',1) recrd
from dual;


Is there any built in function available to have the XML as,

<RECORD ASK_PRICE = "40&qout;&qout;"1" CREATE_USR_ID = "asibm" /> 


So that it will not fail XML parsing while extracting values from XML.

Thanks,
Harshal

Re: Varchar2 to XML conversion [message #475209 is a reply to message #473242] Mon, 13 September 2010 13:42 Go to previous message
harshalonline22
Messages: 74
Registered: March 2007
Location: Pune
Member
Can any one have any hint or solution, please?
Previous Topic: Passing array structure from java callable stmnt to a pl/sql stored procedure
Next Topic: DBMS_XMLSTORE not able to insert values for nested tags
Goto Forum:
  


Current Time: Fri Dec 26 21:11:36 CST 2014

Total time taken to generate the page: 0.09887 seconds