Skip to Main Content
  • Questions
  • Infidelity when storing XMLType data elements (spaces)

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Jacek.

Asked: March 27, 2019 - 11:52 pm UTC

Answered by: Chris Saxon - Last updated: April 08, 2019 - 7:17 am UTC

Category: SQL - Version: 11g R2-19c

Viewed 1000+ times

You Asked

When saving XMLType data into table.
The data fidelity is is not maintained for fields containing only spaces (see LiveSQL).

Before save, spaces are present in <COL> </COL>
After reading the saved data, spaces are gone and we see empty tag <COL/>

I do understand that there might be infidelity in terms of exact XML structure (element ordering etc.) but I would expect the attribute values to be always preserved.

Except for CLOB storage do you see any other way to force saving of XMLType with full data-fidelity?


CLOB storage gives fidelity but is absolutely a no-go die to the performance of XML-> CLOB-> XML conversion.



with LiveSQL Test Case:

and we said...

There are various bugs related to this issue. Though no fixes I can see!

So if you want to see this change I suggest raising a bug detailing the impact it has.

In the meantime you can work around this issue by setting the xml:space attribute to preserve.

Or put whitespace only values inside CDATA:

create table xmltab of xmltype;

insert into xmltab values(
  xmltype('<a>   </a>')
);
insert into xmltab values(
  xmltype('<b xml:space=''preserve''>   </b>')
);
insert into xmltab values(
  xmltype('<c><![CDATA[   ]]></c>')
);

select * from xmltab;

SYS_NC_ROWINFO$                    
<a/>
                              
<b xml:space="preserve">   </b>
   
<c><![CDATA[   ]]></c>

and you rated our response

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

&#x20;

March 28, 2019 - 8:52 pm UTC

Reviewer: Mikhail Velikikh from Dublin, Ireland

Just another way to put spaces:
SQL> create table xmltab of xmltype;

Table created.

SQL>
SQL> set def ^
SQL>
SQL> insert into xmltab values (xmltype('<a>&#x20;&#x20;&#x20;&#x20;&#x20;</a>'));

1 row created.

SQL> select * from xmltab;

SYS_NC_ROWINFO$
--------------------------------------------------------------------------------
<a>     </a>

Chris Saxon

Followup  

March 29, 2019 - 4:57 pm UTC

Good suggestion.

Workaround works nicely on top level TAG

March 29, 2019 - 1:46 pm UTC

Reviewer: Jacek Gebal from Ireland

Thanks Chris,
This is really life-saving answer.
As we need to store "any cursor" as XML, we will implement the solution like this:
declare 
  l_cur sys_Refcursor; 
  l_ctx        number; 
  l_xml        xmltype; 
begin 
  open l_cur for  
    select ' '  as col from dual union all 
    select '  a  ' from dual union all 
    select ' a ' from dual union all 
    select '  ' from dual union all 
    select cast(null as varchar2(2)) from dual; 
 
    l_ctx := dbms_xmlgen.newContext(l_cur); 
    dbms_xmlgen.setNullHandling(l_ctx, dbms_xmlgen.NULL_ATTR); 
    dbms_xmlgen.setMaxRows(l_ctx, 100);    
    l_xml := dbms_xmlgen.getxmltype(l_ctx);
    /*workaround for spaces - start*/
    l_xml := xmltype( replace(l_xml.getClobVal(),'<ROWSET','<ROWSET xml:space=''preserve'''));

    /*workaround for spaces - end*/
 
    dbms_output.put_line(l_xml.getClobVal()); 
    insert into xmltab values (l_xml); 
    select * into l_xml from xmltab; 
    dbms_output.put_line(l_xml.getClobVal()); 
    rollback; 
end; 


Let me know if you see any better/less-hacky way of doing this.

Thanks
Jacek
Chris Saxon

Followup  

March 29, 2019 - 4:56 pm UTC

Thanks.

The only other feasible approach I see is using escape characters like Mikhail suggests above.

Whether this is better/less hacky is a decision for the reader ;)

XMLTABLE Follow same issue

March 29, 2019 - 7:59 pm UTC

Reviewer: Lukasz W from UK

Hi Chris,
I've been working with Jacek on issue.
Following on that,data saved into xmltype using xml:space attribute into table seems to working.
Unfortunately reading data back is behave very inconsistent.
When there is an tag with a whitespace inside XMLTABLE treat that as null (trim whitespace?) However if there is an value that has whitespace at the end e.g. 't ' it read that as expected.
Is that expected behavior ?
I have attached a LiveSQL example:

https://livesql.oracle.com/apex/livesql/s/h6ahy0vlhykp7k7xu0ua0xmw4

Chris Saxon

Followup  

April 02, 2019 - 10:16 am UTC

Adding the preserve option to every tag keeps the spaces in the tags. But XMLTable still returns null for me:

CREATE TABLE test( 
    item_data   XMLTYPE, 
    id          NUMBER 
)XMLTYPE COLUMN item_data STORE AS SECUREFILE BINARY XML;

INSERT INTO test( 
    item_data, 
    id 
)VALUES( 
    '<ROWSET xml:space="preserve"> <ROW> <T1>  </T1>  </ROW> </ROWSET> ', 
    4 
);

INSERT INTO test( 
    item_data, 
    id 
)VALUES( 
    '<ROWSET xml:space="preserve"><ROW><T1 xml:space="preserve">  </T1> </ROW> </ROWSET> ', 
   5 
);

set long 1000
SELECT 
    ucd."T1"   AS "T1", 
    id, 
    ucd.item_data
FROM 
    test x, 
    XMLTABLE('/ROWSET/ROW' PASSING x.item_data COLUMNS item_data XMLTYPE PATH '*',"T1" VARCHAR2(4000)PATH 'T1')ucd;

T1       ID   ITEM_DATA                           
<null>      4 <T1></T1>
                          
<null>      5 <T1 xml:space="preserve">  </T1>


I think you're going to have to take this one up with support.

.. and a bay leaf?

April 01, 2019 - 12:51 pm UTC

Reviewer: Racer I.

Hi,

You probably need

<T1 xml:space="preserve">  </T1> 


on each node.

regards,

Sadly didnt work

April 02, 2019 - 11:41 am UTC

Reviewer: A reader

Unfortunately didn't work.
I did some reading and it can be related to Xquery engine itself. Unfortunately Oracle XML doesn't support boundary preserve space which is suggested way of dealing with insignificant white space by XQuery.
Chris Saxon

Followup  

April 02, 2019 - 4:09 pm UTC

:(

Is using CDATA or escape characters viable for you?

Lukasz W

April 03, 2019 - 6:45 pm UTC

Reviewer: Lukasz W

I don't think so.
I don't know about the data as they are passed into procedure as sys_refcursor and using dbms_xmlgen to generate XML. At that point I have some knowledge of whats in data set. I have a data in xmltype that I'm writing into table ( xml:space preserve on root ROWSET helps with binary storage) replace of whitespace would have to happen at that stage but there is no way to tell what TAGS (columns) are part of XML to run regexp and replace every space is not really an option.
I cant think on any way at that point.

Chris Saxon

Followup  

April 08, 2019 - 7:17 am UTC

The backtracking suggested by Racer below might help - I'm not aware of a better solution.

cheering the (xml)stripper

April 05, 2019 - 11:52 am UTC

Reviewer: Racer I.

Hi,

> Unfortunately Oracle XML doesn't support boundary preserve space

May the OP could sue Oracle for infidelity (to the spec) ;)

https://docs.oracle.com/en/database/oracle/oracle-database/19/adxdb/xquery-and-XML-DB.html#GUID-2DBF9D49-9C20-45F8-BB11-CB68DAF4F702

4.8.1.2 XQuery Features Not Supported by Oracle XML DB
...
XQuery prolog boundary-space declaration.

So with heavy hacking :

SELECT 
    ucd."T1" AS "T1", 
    id, 
    RTRIM(ucd.item_data, CHR(10)) inp,
    REGEXP_REPLACE(RTRIM(ucd.item_data, CHR(10)), '<[^>]*>', '') new,
    length(REGEXP_REPLACE(RTRIM(ucd.item_data, CHR(10)), '<[^>]*>', '')) newl
FROM 
    test x, 
    XMLTABLE('/ROWSET/ROW' PASSING x.item_data COLUMNS item_data XMLTYPE PATH '*',"T1" VARCHAR2(4000)PATH 'T1') ucd;

regards,

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.