Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2014
    Location
    Long Island, New York
    Posts
    31

    Answered: XLM searching using XMLEXISTS question

    Hi all,
    does anyone know if there a way to use a wildcard when searching the content of an element?*
    This works:
    ****** XMLEXISTS (' $i/a/b/c[Name = "DOE, JOHN "]' passing i.AUDIT as "i")
    but I would like to do something like​
    ...[NAME LIKE 'DOE%'
    is this possible?
    I'm using db2 9.7 and 10.5 on AIX and Linux.*
    Thank you

  2. Best Answer
    Posted by mark.bb

    "Hi,

    Try this:
    Code:
    select x.name
    from (
    select xmlparse (document c) 
    from table(values 
      '<a><b><c><Name>DOE</Name></c></b></a>'
    , '<a><b><c><Name>JOHN</Name></c></b></a>'
    , '<a><b><c><Name>JAN</Name></c></b></a>'
    ) t(c)
    ) t(x)
    , xmltable('$i/a/b/c' passing t.x as "i" COLUMNS 
      NAME    VARCHAR(10)     PATH 'Name'
    ) x
    --where xmlexists('$i/a/b/c[Name="DOE" or Name="JAN"]' passing t.x as "i")
    where x.name like 'J%'
    ;
    "


  3. #2
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    294
    Provided Answers: 45
    Hi,

    Try this:
    Code:
    select x.name
    from (
    select xmlparse (document c) 
    from table(values 
      '<a><b><c><Name>DOE</Name></c></b></a>'
    , '<a><b><c><Name>JOHN</Name></c></b></a>'
    , '<a><b><c><Name>JAN</Name></c></b></a>'
    ) t(c)
    ) t(x)
    , xmltable('$i/a/b/c' passing t.x as "i" COLUMNS 
      NAME    VARCHAR(10)     PATH 'Name'
    ) x
    --where xmlexists('$i/a/b/c[Name="DOE" or Name="JAN"]' passing t.x as "i")
    where x.name like 'J%'
    ;
    Regards,
    Mark.

  4. #3
    Join Date
    Sep 2014
    Location
    Long Island, New York
    Posts
    31
    Thank you Mark. Here is my stored procedure. I did it a little different but your suggestion seems to work.

    CREATE OR REPLACE PROCEDURE GETNAMESEARCH (
    IN PATIENTNAME VARCHAR(100),
    IN FROMDATE TIMESTAMP,
    IN TODATE TIMESTAMP
    )
    RESULT SETS 1

    P1: BEGIN

    DECLARE patname VARCHAR(100);

    DECLARE namesearch CURSOR WITH RETURN FOR
    select ax.* from PATIENTFILE i,
    XMLTABLE('$ad/AuditMessage/eventIdentification' passing i.AuditMessage AS "ad"
    columns
    eventDateTime TIMESTAMP PATH '@EventDateTime',
    event VARCHAR(100) PATH 'EventID/@code',
    eventUser VARCHAR(30) PATH 'ActiveParticipant/@UserID',
    patientName VARCHAR(30) PATH 'ParticipantObjectIdentification/ParticipantObjectName',
    patientNumber VARCHAR(30) PATH 'ParticipantObjectIdentification/@ParticipantObjectID'
    ) AS ax
    WHERE
    eventdate BETWEEN FROMDATE AND TODATE and
    upper (ax.patientName) like patname fetch first 10 rows only;

    SET patname = PATIENTNAME;

    open namesearch;

    END P1

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
  •