Home » SQL & PL/SQL » SQL & PL/SQL » Parsing XML multiple CDATA with & and withoutn & (Oracle 12c 2)
Parsing XML multiple CDATA with & and withoutn & [message #683947] Sun, 07 March 2021 02:38 Go to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member

Hi All,

I want to parse xml multiple CDATA with payload including & and without &. Please suggest if it is right way to handle the scenario.


--case1 (with "&" present in payload)
--replacing cdata and & with null working
--replacing cdata only not working 
--not replacing cdata not working

--case2 (with "&" not present in payload)
--replacing cdata and & with null working
--replacing cdata only working 
--not replacing cdata not working
set define off;
with temp as ( select '<![CDATA[<?xml version="1.0" encoding="UTF-8"?><Employee Dept="Finance" >
  <Address>
    <IsValid>Y</IsValid>
    <Address1><![CDATA[ST=SIKKIM,postalCode=737106,CN=LAXUMAN TAMANG,OU=RM AND DD,O=R M & D D GOVERNMENT OF SIKKIM,C=IN]]></Address1>
    <Address2><![CDATA[STREET=18,LAXMI NAGAR DISTRICT CENTER,ST=DELHI,postalCode=110092,C=IN]]></Address2>
  </Address>
</Employee>]]>' a from dual )
select xmltype(replace(REPLACE(REPLACE(a, '<![CDATA[', ''),
                               ']]>',
                               ''),'&','')) from temp;


Re: Parsing XML multiple CDATA with & and withoutn & [message #683948 is a reply to message #683947] Sun, 07 March 2021 06:16 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
with temp as (
              select '<![CDATA[<?xml version="1.0" encoding="UTF-8"?>
                        <Employee Dept="Finance" >
                          <Address>
                            <IsValid>Y</IsValid>
                            <Address1><![CDATA[ST=SIKKIM,postalCode=737106,CN=LAXUMAN TAMANG,OU=RM AND DD,O=R M & D D GOVERNMENT OF SIKKIM,C=IN]]></Address1>
                            <Address2><![CDATA[STREET=18,LAXMI NAGAR DISTRICT CENTER,ST=DELHI,postalCode=110092,C=IN]]></Address2>
                          </Address>
                        </Employee>]]>' a
                from  dual
             )
select  x.*
  from  temp,
        xmltable(
                 '/Employee'
                 passing xmltype(
                                 replace(
                                         replace(
                                                 replace(
                                                         a,
                                                         '<![CDATA['
                                                        ),
                                                 ']]>'
                                                ),
                                         '&',
                                         '&amp;'
                                        )
                                )
                 columns
                   dept     varchar2(10)  path '@Dept',
                   is_valid varchar2(1)   path 'Address/IsValid',
                   address1 varchar2(100) path 'Address/Address1',
                   address2 varchar2(100) path 'Address/Address2'
                ) x
/

DEPT       I
---------- -
ADDRESS1
--------------------------------------------------------------------------------
ADDRESS2
--------------------------------------------------------------------------------
Finance    Y
ST=SIKKIM,postalCode=737106,CN=LAXUMAN TAMANG,OU=RM AND DD,O=R M & D D GOVERNMEN
T OF SIKKIM,C=IN
STREET=18,LAXMI NAGAR DISTRICT CENTER,ST=DELHI,postalCode=110092,C=IN


SQL>
SY.
Re: Parsing XML multiple CDATA with & and withoutn & [message #683949 is a reply to message #683948] Sun, 07 March 2021 22:27 Go to previous messageGo to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member

Thank you SY for your answer.


If we need to manually replace it for parsing then why XML are get combined with CDATA.
Re: Parsing XML multiple CDATA with & and withoutn & [message #683951 is a reply to message #683949] Mon, 08 March 2021 04:17 Go to previous message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Because what you posted in not a valid XML. It has nested CDATA and nested CDATA isn't allowed in XML. Without CDATA nesting:

with temp as (
              select '<?xml version="1.0" encoding="UTF-8"?>
                        <Employee Dept="Finance" >
                          <Address>
                            <IsValid>Y</IsValid>
                            <Address1><![CDATA[ST=SIKKIM,postalCode=737106,CN=LAXUMAN TAMANG,OU=RM AND DD,O=R M & D D GOVERNMENT OF SIKKIM,C=IN]]></Address1>
                            <Address2><![CDATA[STREET=18,LAXMI NAGAR DISTRICT CENTER,ST=DELHI,postalCode=110092,C=IN]]></Address2>
                          </Address>
                        </Employee>' a
                from  dual
             )
select  x.*
  from  temp,
        xmltable(
                 '/Employee'
                 passing xmltype(a)
                 columns
                   dept     varchar2(10)  path '@Dept',
                   is_valid varchar2(1)   path 'Address/IsValid',
                   address1 varchar2(100) path 'Address/Address1',
                   address2 varchar2(100) path 'Address/Address2'
                ) x
/

DEPT       I
---------- -
ADDRESS1
--------------------------------------------------------------------------------
ADDRESS2
--------------------------------------------------------------------------------
Finance    Y
ST=SIKKIM,postalCode=737106,CN=LAXUMAN TAMANG,OU=RM AND DD,O=R M & D D GOVERNMEN
T OF SIKKIM,C=IN
STREET=18,LAXMI NAGAR DISTRICT CENTER,ST=DELHI,postalCode=110092,C=IN


SQL>
SY.
Previous Topic: Need help on Tabibitosan (start of group)
Next Topic: Beginner question. Update query with another SQL
Goto Forum:
  


Current Time: Thu Mar 28 14:47:38 CDT 2024