xmldb2containsxmlindex

SQL Query on DB2. contains function on XML column

My test table has two columns, one is message_xml of type XML and another is company_names of type VARCHAR. I would like to search for a word in each XML row and if the XML has it then export that XML.

Here are 2 samples of xml stored in DB2:

<breakfast_menu>
  <food>
    <name>Belgian Waffles</name>
    <price>$5.95</price>
    <description>Two of our famous Belgian Waffles with plenty of real maple syrup</description>
    <calories>650</calories>
  </food>
  <food>
    <name>Homestyle Breakfast</name>
    <price>$6.95</price>
    <description>Two eggs, bacon or sausage, toast, and our ever-popular hash browns</description>
    <calories>950</calories>
  </food>
</breakfast_menu>

<breakfast_menu>
  <food>
    <name>Strawberry Belgian Waffles</name>
    <price>$7.95</price>
    <description>Light Belgian waffles </description>
    <calories>900</calories>
  </food>
  <food>
    <name>French Toast</name>
    <price>$4.50</price>
    <description>Thick slices of bread</description>
    <calories>600</calories>
  </food>
</breakfast_menu>

In these XML documents I want to find the word "bacon" (it can be anywhere in the XML) and just export that XML to a text file.

I tried using CONTAINS by first converting the XML to string but I get an error regarding VARCHAR.

xmlcast(test.message_xml as varchar(255)) as export_XML is the code I wrote to convert XML to string

[Error Code: -16061, SQL State: 10608] The value "429541527005540133404021548131000109999-12-312..." cannot be constructed as, or cast (using an implicit or explicit cast) to the data type "VARCHAR_255". Error QName=err:FORG0001.. SQLCODE=-16061, SQLSTATE=10608, DRIVER=4.15.82

Next I directly tried to use CONTAINS but I get an error regarding no text index was found. So, I tried to create an index but I get an error there too and the error reads

"An unexpected token "idx1" was found following "L) as ( create index". Expected tokens may include: "JOIN""

for the code: create index idx1 on test(message_xml) generate key using xmlpattern '/XML' as varchar(9999)

My code is: 

@export on;
@export set filename="D:\temp\searchResults.txt";
@set maxrows 10;
with Tempresult(export_xml)
as
(
create index idx1 on test(message_xml)
generate key using xmlpattern '/XML'
as varchar(9999)
select 
    test.message_xml as export_XML
from test where source_id = 14
and trans_timestamp between '2015-10-01' and '2016-04-30'
)
select
    export_XML
    from Tempresult
    //where LOCATE('bacon',export_XML) > 0;
    where CONTAINS(export_XML, ' "bacon" ') = 1;
@export off;

with the above code WITHOUT creating an index, I get this error:

[Error Code: -443, SQL State: 38799] Routine "*RCH_8K64" (specific name "") has returned an error SQLSTATE with diagnostic text "CTE0199 No text index corresponding to column "MESSAGE_XML"".. SQLCODE=-443, SQLSTATE=38799, DRIVER=4.15.82

I even tried to use LOCATE, CONVERT, CAST but of no use. Can someone please help me in solving this?

I think solution might be by converting XML to string and apply CONTAINS or LOCATE or create an index for the xml column. Please correct me if I am wrong.

Solution

  • I found a solution at the expense of some run time due to XMLSERIALIZE (I think).

    select message_xml AS export_xml from test where LOCATE('bacon',XMLSERIALIZE(xmlquery('$clam//breakfast_menu ' passing test.message_xml as "clam") as CLOB)) > 0

    For now this worked for me. Sometimes I am getting an error which reads:

    [Error Code: -433, SQL State: 22001] Value "et">0.00" is too long.. SQLCODE=-433, SQLSTATE=22001, DRIVER=4.15.82

    I couldn't figure out what this error is.