Filter XML SQL query result



I have an XML column:



<xmlList>
<XMLEntity>
<sug>ACHR</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 the sug node where they are not from enum values:



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

FROM "TABLE"


The result is OK, but wherever the sug is ACHER or DOA I get empty line. How can I avoid the empty lines from result?


No comments:

Post a Comment