Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2013

    Unanswered: xquery to get records where a record//text() contains/matches a word/pattern

    "DB2 v10.1.0.2", "s121127", "IP23393", and Fix Pack "2"
    DB2 Express-C
    Debian 6.0.6

    I have an XML column and I need to get records that contain specific elements (which I can already do) and records with text (but not tags) matching a given word or pattern (which I am having trouble with). The final goal is to select the xsltransform of the result.

    My current attempted solution is:

    XQUERY for $r in db2-fn:xmlcolumn('XMLCUSTOMER.INFO')
    		let $t := $r//text()
    		where fn:contains( fn:concat($t), "word")
    		return $r;
    but that results in the following error:
    SQL0440N No authorized routine named
    "" of type "FUNCTION" having compatible arguments was found. SQLSTATE=42884
    because fn:concat only works on atomic values

    XQUERY for $r in db2-fn:xmlcolumn('XMLCUSTOMER.INFO')
    		for $t in $r//text()
    		where fn:contains($t, "word")
    		return $r;
    results in too many results because the inner loop matches multiple times.

    Is there a simple way to get the records I'm looking for?
    Last edited by zjmarlow; 10-29-13 at 11:54. Reason: title grammar

  2. #2
    Join Date
    Aug 2012
    If I understand you correctly you can do this in simple sql.....example

    text is the column name of the clob field
    within the clob is xml data and one of the tags is called DeviceStated

    To get all the data within this tag you would first look for the tag with
    the above would bring you to the start of the tag. To get the data after the tag you need to count the character and use +14
    then you look for the end tag and count the characters again and take them away from each other,

    For function POSSTR it does not need to be a tag it can look for any string

    substr(TEXT,(POSSTR(TEXT,'<DeviceStated>')+14),(PO SSTR(TEXT,'</DeviceStated>')-14)-POSSTR(COMMENTTEXT,'<DeviceStated>')) END as varchar(255)) AS gotit

  3. #3
    Join Date
    Aug 2012
    if you want it to look only when it has a certain string you would have to put in something like the below

    		WHEN (POSSTR(TEXT,'<DeviceStated>')=0) THEN 'NA' 
    		substr(TEXT,(POSSTR(TEXT,'<DeviceStated>')+14),(POSSTR(TEXT,'</DeviceStated>')-14)-POSSTR(TEXT,'<DeviceStated>')) END as varchar(255))  AS GOTIT

  4. #4
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    Try this:
    xquery for $r in db2-fn:xmlcolumn('XMLCUSTOMER.INFO') 
               where fn:exists($r//node()[fn:contains(./text(),"word")]) 
               return $r
    "It does not work" is not a valid problem statement.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts