en inpath finds record, es inpath does not
949946Jul 17 2012 — edited Jul 17 2012I have an Oracle 11g database (11.2.0), and a table T with a column A of type CLOB. The CLOB contains XML data, which includes - among others - an element with a language code. The XML looks like this:
<tags><tag name="Status"><int>5</int></tag><tag name="Language"><string>en</string></tag></tags>
I want to find all records of T where the language is "es" (Spanish):
select * from T where contains(A, '{es} INPATH(//tag[@name="Language"]/string)')>0;
Now the funny thing is, this works with all language codes except es. "{en} INPATH" gives me the English record, "{pt} INPATH" gives me the Portuguese record, but "{es} INPATH" returns 0 results. Why?
The following SQL works absolutely fine, returning a record with lang=es:
select extractvalue(xmltype(A), '//tag[@name="Language"]/string') as lang from T where uuid='B4qgMkx_DKwAAAEwcVp5ztFY';
I performed both a length and an asciistr check on the extractvalue results: "es" is 2 ASCII characters, nothing more, nothing less, and by no means different from "en" or "pt". I'm clueless. Can you help?
Edited by: 946943 on Jul 17, 2012 10:20 AM