sqlxmlenumsdb2ibm-db2

Query XML by SQL


I have an XML column:

<xmlList> 
   <XMLEntity> 
      <sug>ACHER</sug>
   </XMLEntity>

  <XMLEntity> 
      <sug>DOA</sug>
   </XMLEntity>
</xmlList>

The sug can hold only a enum memeber(ACHER or DOA). I would like to check if there is a sug without one of these values.

In this way I get just the sug node where it is one of the enum values:

SELECT XMLSERIALIZE(XMLQUERY ('//xmlList/XMLEntity/sug[.="ACHER"]' passing 
KTOVET ) as char large object) as XXX ,

XMLSERIALIZE(XMLQUERY ('//xmlList/XMLEntity/sug[.="DOA"]' passing 
KTOVET ) as char large object) as YYY   

FROM "TABLE" 

I would like to get the sug nodes where the value is not one of the enums value. Possible? How can I get the sug nodes where its value is "ACHER"?


Solution

  • SELECT XMLSERIALIZE(XMLQUERY ('//xmlList/XMLEntity/sug[.!="ACHER" and .!="DOA"]'  
    passing KTOVET ) as char large object) as XXX
    
    FROM "TABLE"