Varchar2 to XML conversion [message #451520] |
Thu, 15 April 2010 03:30 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #451523 is a reply to message #451520] |
Thu, 15 April 2010 03:45 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #451541 is a reply to message #451540] |
Thu, 15 April 2010 04:40 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #453674 is a reply to message #451653] |
Fri, 30 April 2010 04:41 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 &)
In case of ampersand ('&') we can handle as below:
Select XMLType(replace('<RECORD COL1="EQUITY545734&<^*^>"/>','&','&'))
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 <)
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 #453696 is a reply to message #453689] |
Fri, 30 April 2010 05:44 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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>\2')
|
|
|
Re: Varchar2 to XML conversion [message #454882 is a reply to message #453696] |
Sat, 08 May 2010 01:24 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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="&>>>"/>','&','&'),'(".*)>(.*")','\1>\2') AS COL1
FROM dual;
O/P is like,
COL1 := <RECORD COL1="&>>>"/>
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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,'".*"'),'&','&'),'<','<'),'>','>') ||
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|