title: SQL Query on DB2. contains function on XML column
body: <p>My <code>test</code> table has two columns, one is <code>message_xml</code> of type <code>XML</code> and another is <code>company_names</code> of type <code>VARCHAR</code>. I would like to search for a word in each XML row and if the XML has it then export that XML.</p>
  
  <p>Here are 2 samples of xml stored in DB2:</p>
  
  <pre><code>&lt;breakfast_menu&gt;
    &lt;food&gt;
      &lt;name&gt;Belgian Waffles&lt;/name&gt;
      &lt;price&gt;$5.95&lt;/price&gt;
      &lt;description&gt;Two of our famous Belgian Waffles with plenty of real maple syrup&lt;/description&gt;
      &lt;calories&gt;650&lt;/calories&gt;
    &lt;/food&gt;
    &lt;food&gt;
      &lt;name&gt;Homestyle Breakfast&lt;/name&gt;
      &lt;price&gt;$6.95&lt;/price&gt;
      &lt;description&gt;Two eggs, bacon or sausage, toast, and our ever-popular hash browns&lt;/description&gt;
      &lt;calories&gt;950&lt;/calories&gt;
    &lt;/food&gt;
  &lt;/breakfast_menu&gt;
  
  &lt;breakfast_menu&gt;
    &lt;food&gt;
      &lt;name&gt;Strawberry Belgian Waffles&lt;/name&gt;
      &lt;price&gt;$7.95&lt;/price&gt;
      &lt;description&gt;Light Belgian waffles &lt;/description&gt;
      &lt;calories&gt;900&lt;/calories&gt;
    &lt;/food&gt;
    &lt;food&gt;
      &lt;name&gt;French Toast&lt;/name&gt;
      &lt;price&gt;$4.50&lt;/price&gt;
      &lt;description&gt;Thick slices of bread&lt;/description&gt;
      &lt;calories&gt;600&lt;/calories&gt;
    &lt;/food&gt;
  &lt;/breakfast_menu&gt;
  </code></pre>
  
  <p>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.</p>
  
  <p>I tried using <code>CONTAINS</code> by first converting the XML to string but I get an error regarding <code>VARCHAR</code>.</p>
  
  <p><code>xmlcast(test.message_xml as varchar(255)) as export_XML</code> is the code I wrote to convert XML to string</p>
  
  <blockquote>
    <p>[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</p>
  </blockquote>
  
  <p>Next I directly tried to use <code>CONTAINS</code> 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 </p>
  
  <blockquote>
    <p>"An unexpected token "idx1" was found following "L) as ( create
         index".  Expected tokens may include:  "JOIN""</p>
  </blockquote>
  
  <p>for the code: 
       create index idx1 on test(message_xml)
       generate key using xmlpattern '/XML'
       as varchar(9999)</p>
  
  <p>My code is: </p>
  
  <pre><code>@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) &gt; 0;
      where CONTAINS(export_XML, ' "bacon" ') = 1;
  @export off;
  </code></pre>
  
  <p>with the above code WITHOUT creating an index, I get this error:</p>
  
  <blockquote>
    <p>[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</p>
  </blockquote>
  
  <p>I even tried to use <code>LOCATE</code>, <code>CONVERT</code>, <code>CAST</code> but of no use. Can someone please help me in solving this?</p>
  
  <p>I think solution might be by converting XML to string and apply <code>CONTAINS</code> or <code>LOCATE</code> or create an index for the xml column. Please correct me if I am wrong.</p>