extractvalue with linebreaks in xml-attributes
Hi,
we're trying to use some xmldb-features. Our statement is as follows:
select value(xmltab).extract('/bla/@attribut')
from table(xmlsequence(xmltype('<r><bla attribut="a
linebreak"/><bla attribut="test"/></r>').extract('/r/bla'))) xmltab;
this gives us a table with two rows: one with "test" which is fine and the other with "a linebreak". Unfortunately, the linebreak between "a" and "linebreak" is eliminated and replaced by a simple blank.
This problem is on 10g XE and on 11gR2. On 11g I tried to replace the statement using xmltable with no success (anyway that wouldn't help since we need 10g-compatibility).
Unfortunately, there's no way to change the XML that is givent to the xmlsequence-function.
So we tried to track down the issue:
--This statement doesn't works
select xmltype(xmltype('<bla attribut="a
linebreak"/>').extract('/bla').getStringVal()).extract('/bla/@attribut') from dual;
--Whereas this statement works as expected (giving me a linebreak in the result
select xmltype('<bla attribut="a
linebreak"/>').extract('/bla/@attribut') from dual;
So the problem seems to be that doing an extract on a xmltype of a xml with an attribute with a line-break causes the linebreak to vanish.
--First column is fine, second has no linebreak
select
xmltype('<bla attribut="ein'||CHR(13)||CHR(10)||'Umbruch"/>'),
xmltype('<bla attribut="ein'||CHR(13)||CHR(10)||'Umbruch"/>').extract('/bla/@attribut')
from dual;
Unfortunately, using xmlsequence and value() afterwards seems to do something similar. Is there any way to a statement with the same result than our xmlsequence statement while preserving the linebreaks? We could of cause do something like this:
select replace(value(xmltab).extract('/bla/@attribut'),'BLAAAA',CHR(13)||CHR(10))
from table(xmlsequence(xmltype(replace('<r><bla attribut="a
linebreak"/><bla attribut="test"/></r>','
','BLAAAA')).extract('/r/bla'))) xmltab;
But in reality our data is stored in a clob-xmltype-column and on 11g we have an xml-index for our paths on this column, speeding up our queries enormously. I Think this path-table-index wouldn't work any longer when we do a replacement on the whole column-data.
Any ideas how this problem could be solved? Or is this a database-bug?
Any help would be highly appreciated,
Best regards
Johannes Michler