Andrew Hardy schrieb:
> Ko van der Sloot wrote:
>
>> Hello,
>> we encountered a very strange problem with searching in our XMLType
>> database.
>>
>> Our configuration is:
>> Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
>> With the Partitioning, OLAP and Oracle Data Mining options
>> JServer Release 9.2.0.4.0 - Production
>>
>> Below you will find a very simple example that demonstrates the problem.
>> It seems that a diacritic in an attribute confuses Oracle, and makes
>> some record "invisable". We expect the query to find both records in the
>> table.
>>
>> Please try this and confirm that the problem is real, or that we have
>> some misconception about Oracle. (and if so: what do we do wrong?)
>>
>> Also we are very much interested to know how Oracle 10 handles this.
>>
>> The input is UTF8 ,and NSL_LANG is set to American_america.AL32UTF8
>>
>> Here is the example script:
>> ================================================== ==
>> drop table my_table;
>>
>> create table my_table ( doc_id NUMBER, data_char xmltype );
>>
>> insert into my_table values( 1, XMLType(
>> '<document>
>> <au first="Rüdiger" id="1">Rüdiger</au>
>> </document>' ) );
>>
>> insert into my_table values( 2, XMLType(
>> '<document>
>> <au first="Ruediger" id="1">Rüdiger</au>
>> </document>' ) );
>>
>> create index my_table_idx on my_table(data_char) indextype is
>> ctxsys.context;
>>
>> select data_char from my_table where contains(data_char,'1 inpath(
>> //au/@id )' ) > 0;
>
> On my box:
>
> Connected to Oracle Database 10g Release 10.1.0.3.0
> Connected as hardya
>
>
> SQL>
>
> Table created
>
> 1 row inserted
>
> 1 row inserted
>
> Index created
>
> DATA_CHAR
> --------------------------------------------------------------------------------
>
> <document>
> <au first="R?diger" id="1">R?diger</au>
> </document>
>
> <document>
> <au first="Ruediger" id="1">R?diger</au>
> </document>
>
>
> SQL>
>
On my box:
scott@ORA92>
Table created.
1 row created.
1 row created.
Index created.
scott@ORA92> select data_char from my_table where contains(data_char,'1
inpath(
//au/@id )' ) > 0; 2
DATA_CHAR
--------------------------------------------------------------------------------
--------------------
<document>
<au first="Ruediger" id="1">Rüdiger</au>
</document>
<document>
Verify pls the character set of your database. If your character set
does contain 'ü', you should be able to see proper results.
Best regards
Maxim