Hi,
I have data in a CLOB column which I need to turn to an XML attribute. The data may contain CR, LF, and tabs - all of which the XML should be preserving in a normalized/linearized manner (I.e using , etc.)
I tried using XMLROOT - it removes the newline altogether.
I tried using XMLSERIALIZE - when done with VERSION clause it also removes the newline, when without the VERSION it keeps the newline but not normalized.
Not using these preserves the newline - but not normalized.
Is there a built in way to normalize the XML - or must I use nested replace ?
Examples below.
Thanks in advance.
SQL> set long 20000
SQL> set lines 1000
SQL> set pages 1000
SQL> select * from v$version
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
5 rows selected.
SQL> -- as XMLTYPE, newline (chr(10)) is preserved, but not normalized
SQL> select XMLELEMENT("A",
XMLATTRIBUTES(1 as ID, 2 ||chr(10)||3 as "TXT")
)
from dual
XMLELEMENT("A",XMLATTRIBUTES(1ASID,2||CHR(10)||3AS"TXT"))
---------------------------------------------------------
<A ID="1" TXT="2
3"></A>
1 row selected.
SQL> -- as CLOB, newline (chr(10)) is preserved, but not normalized
SQL> select XMLELEMENT("A",
XMLATTRIBUTES(1 as ID, 2 ||chr(10)||3 as "TXT")
).getclobval()
from dual
XMLELEMENT("A",XMLATTRIBUTES(1ASID,2||CHR(10)||3AS"TXT")).GETCLOBVAL()
----------------------------------------------------------------------
<A ID="1" TXT="2
3"></A>
1 row selected.
SQL> -- with XMLROOT - newline is removed
SQL> select XMLROOT(
XMLELEMENT("A",
XMLATTRIBUTES(1 as ID, 2 ||chr(10)||3 as "TXT")
),
VERSION '1.0')
from dual
XMLROOT(XMLELEMENT("A",XMLATTRIBUTES(1ASID,2||CHR(10)||3AS"TXT")),VERSION'1.0')
-------------------------------------------------------------------------------
<?xml version="1.0"?>
<A ID="1" TXT="2 3"/>
1 row selected.
SQL> --with XMLSERIALIZE without VERSION clause - newline is preserved but not normalized
SQL> select XMLSERIALIZE(DOCUMENT
XMLELEMENT("A",
XMLATTRIBUTES(1 as ID, 2 ||chr(10)||3 as "TXT")
)
)
from dual
XMLSERIALIZE(DOCUMENTXMLELEMENT("A",XMLATTRIBUTES(1ASID,2||CHR(10)||3AS"TXT")))
-------------------------------------------------------------------------------
<A ID="1" TXT="2
3"></A>
1 row selected.
SQL> --with XMLSERIALIZE with VERSION clause - newline is removed
SQL> select XMLSERIALIZE(DOCUMENT
XMLELEMENT("A",
XMLATTRIBUTES(1 as ID, 2 ||chr(10)||3 as "TXT")
)
VERSION '1.0')
from dual
XMLSERIALIZE(DOCUMENTXMLELEMENT("A",XMLATTRIBUTES(1ASID,2||CHR(10)||3AS"TXT"))VERSION'1.0')
-------------------------------------------------------------------------------------------
<A ID="1" TXT="2 3"/>
1 row selected.
SQL> --doing replace works, but seems "un-natural" and very inefficient
SQL> select replace(
XMLELEMENT("A",
XMLATTRIBUTES(1 as ID, 2 ||chr(10)||3 as "TXT")
),
chr(10),' ')
from dual
REPLACE(XMLELEMENT("A",XMLATTRIBUTES(1ASID,2||CHR(10)||3AS"TXT")),CHR(10),' ')
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<A ID="1" TXT="2 3"></A>
1 row selected.