Greetings All, I am having a particular issue with XmlExists searching for data that has an ampersand.
I am running 11g patchset 12.
Table being searched is using sys.xmltype to persist xml document.
DDL:
CREATE TABLE "XML_TEST"
(
AccountID RAW(16) NOT NULL ENABLE,
TenantID RAW(16) NOT NULL ENABLE,
CustomFields "SYS"."XMLTYPE"
) ;
DML:
declare clobVal clob;
BEGIN
-- INSERT #1
clobVal := '<?xml version="1.0" encoding="utf-8"?>
<ArrayOfCustomField xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<CustomField ReferencedID="3A887DE7B2FA4A19B9F66FCC19E02062">
<StringValue>Guitar</StringValue>
</CustomField>
<CustomField ReferencedID="014F2EFA42CF40829A9A5FB761759071">
<StringValue>Louis & Clark</StringValue>
</CustomField>
<CustomField ReferencedID="59690AD42A5B4C83B4917D024908F905">
<NumberValue>12.48</NumberValue>
</CustomField>
<CustomField ReferencedID="06675CD4B0FD4C769C3266260159EAC4">
<DateValue>1242394042</DateValue>
</CustomField>
</ArrayOfCustomField>';
INSERT INTO XML_TEST (AccountID, TenantID, CustomFields)
VALUES ('ADDA071176A94654A2BE2129A1ABFBF9', 'F068F6D54ED2DB11ABE500188B001DE0', xmltype(clobVal));
Here is the problem element:
<StringValue>Louis & Clark</StringValue>
When I run this query:
SELECT count(*)
FROM XML_TEST
WHERE XMLExists('/ArrayOfCustomField/CustomField[@ReferencedID="014F2EFA42CF40829A9A5FB761759071"]/StringValue[. = "Louis %26amp; Clark"]' PASSING CustomFields);
It does not return the expected result?
Not does this query:
SELECT count(*)
FROM XML_TEST
WHERE XMLExists('/ArrayOfCustomField/CustomField[@ReferencedID="014F2EFA42CF40829A9A5FB761759071"]/StringValue[. = "Louis & Clark"]' PASSING CustomFields);
Any and all help would be greatly appreciated.
L