Greetings All,
I have a table that has the form:
CREATE TABLE "XML_TEST"
(
AccountID RAW(16) NOT NULL ENABLE,
TenantID RAW(16) NOT NULL ENABLE,
CustomFields "SYS"."XMLTYPE"
) ;
Here is the DML to populate this table:
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/>
</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));
-- INSERT #2
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="60A33C7BEB3245FB9D49F57E58A0856A">
<StringValue>Flute</StringValue>
</CustomField>
<CustomField ReferencedID="8DDF1D2A565D4515883BB90308439124">
<StringValue>Drum</StringValue>
</CustomField>
<CustomField ReferencedID="88FF49FAF75942EBABB13C84C647737F">
<NumberValue>321.56</NumberValue>
</CustomField>
<CustomField ReferencedID="F78F8772988F44A1873F3E79D1722BD1">
<DateValue>1252494042</DateValue>
</CustomField>
</ArrayOfCustomField>';
INSERT INTO XML_TEST (AccountID, TenantID, CustomFields)
VALUES ('1C9825F2EEC6419B9D77AB5A4CB87543', 'F068F6D54ED2DB11ABE500188B001DE0', xmltype(clobVal));
commit;
end;
/
If you look at the first insert statement there is a null value for ReferencedID = 014F2EFA42CF40829A9A5FB761759071 and element StringValue.
I want to construct an XQuery that will return this record but don't know how to do it.
Further, I am using XMLExists but when I try to specify null it pukes. Here is my attempt:
SELECT count(*)
FROM XML_TEST
WHERE XMLExists('/ArrayOfCustomField/CustomField\[@ReferencedID="014F2EFA42CF40829A9A5FB761759071"\]/StringValue\[. = NULL\]' PASSING CustomFields);
Any help would be greatly appreciated.
L