OraFAQ Forum: SQL & PL/SQL » Parsing XML multiple CDATA with & and withoutn &

Home » SQL & PL/SQL » SQL & PL/SQL » Parsing XML multiple CDATA with & and withoutn & (Oracle 12c 2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Parsing XML multiple CDATA with & and withoutn & [message #683947]

Sun, 07 March 2021 02:38

sss111ind
Messages: 628
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;

Report message to a moderator

Re: Parsing XML multiple CDATA with & and withoutn & [message #683948 is a reply to message #683947]

Sun, 07 March 2021 06:16

Solomon Yakobson
Messages: 3211
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.

Report message to a moderator

Re: Parsing XML multiple CDATA with & and withoutn & [message #683949 is a reply to message #683948]

Sun, 07 March 2021 22:27

sss111ind
Messages: 628
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.

Report message to a moderator

Re: Parsing XML multiple CDATA with & and withoutn & [message #683951 is a reply to message #683949]

Mon, 08 March 2021 04:17

Solomon Yakobson
Messages: 3211
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.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 20 04:59:22 CDT 2023