EDIT: I demonstrated my ignorance by confusing attribute with element, I updated my question. Sorry. To clarify, I'm looking for the existence of the element code in:


I have a bunch of schemas where there is a table that contains an XML column. There are a handful XML indexes on each column. I'm pretty sure that the XML-schemas differs between the SQL-schemas, nevertheless, the same XML-indexes exists in each schema. I suspect copy/paste and I can't seem to find any documentation for the XML-schemas. So I thought of a more pragmatic approach namely to loop through the XML columns and check whether each element exists or not. The tables have been around for a while, so every possible element should be there by now.

Now to the question, what's the best way to check for the existence of an element, regardless of value? Unfortunately, my brain is incompatible with XPATH/XQUERY, and I have to struggle every time to just convince my self that the query does what it is supposed to do. My current approach seems to work, but I'm curious if there is a better way. The cardinality of the smallest table is ~100.000 rows and the largest ~100.000.000 rows:

import ibm_db
import ibm_db_dbi
conn = ibm_db_dbi.connect("...")
c1 = conn.cursor()

for schema in [ "S1", "S2", ... ]:
    for attribute in [ "a1", "s2", ...]:
        sql = f"""
            select * from {schema}.ATOM_ENTRY
            WHERE XMLEXISTS('$d//*:{attribute}[fn:matches(text(), $p, "i")]' 
                PASSING ENTRY_XML AS "d", CAST('\S*' AS VARCHAR(128)) as "p")
            fetch first 1 rows only
            optimize for 1 rows"""
        exists = False
        for row in c1.fetchall():
            exists = True
        print(f"{schema}.ATOM_ENTRY -> {attribute} : {exists}")

Try this:

For attributes

SELECT COUNT(x.a2) cnt_a2, COUNT(x.a4) cnt_a4
  '<doc><a><b a2=""><c a2=""/></b></a></doc>'
, '<doc><a><b a3=""/></a></doc>'
) T (D)
    a2 XML PATH '//*[fn:exists(@a2)]'
  , a4 XML PATH '//*[fn:exists(@a4)]'
) X 
--XMLEXISTS('$doc//*[@a4 or @a5]' PASSING XMLPARSE(DOCUMENT T.D) AS "doc")
XMLEXISTS('$doc//*[@a2 or @a4]' PASSING XMLPARSE(DOCUMENT T.D) AS "doc")

The result is:

|CNT_A2     |CNT_A4     |
|1          |0          |

For elements with namespaces

SELECT COUNT(x.code1) cnt_code1, COUNT(x.code3) cnt_code3
  '<doc xmlns:conditionmessage="http://xyz.com"><conditionmessage:code1>MYCODE</conditionmessage:code1></doc>'
, '<doc xmlns:conditionmessage="http://xyz.com"><conditionmessage:code2>MYCODE</conditionmessage:code2></doc>'
) T (D)
  XMLNAMESPACES('http://xyz.com' AS "conditionmessage"), '$doc' PASSING XMLPARSE(DOCUMENT T.D) AS "doc"
    code1 XML PATH '//*[fn:exists(conditionmessage:code1)]'
  , code3 XML PATH '//*[fn:exists(conditionmessage:code3)]'
) X
WHERE XMLEXISTS('declare namespace conditionmessage = "http://xyz.com"; $doc//*[conditionmessage:code1 or conditionmessage:code3]' PASSING XMLPARSE(DOCUMENT T.D) AS "doc");

The result is:

|1          |0          |
| improve this answer | |
  • Thanks for the update, I tried with namespace and it is still a bit slower than my attempt, so I think I will just leave it as is. It took an hour to loop through the tables, but I don't expect to do this very often. – Lennart Apr 16 at 14:02
  • Did I get you right, that your N runs for N attributes is quicker in sum, than my single run for N attributes? – Mark Barinstein Apr 16 at 14:33
  • No, I did the same loop in both cases, 1 table and 4 attributes, sorry. I get the element from syscat.indexxmlpatterns.patterns and then check for this in the corresponding xml column to conclude whether the index is useless or not. I suppose I could use listagg, split the string in python and construct the query from there, but I have not tried that yet. – Lennart Apr 16 at 14:57
  • But I wrote my query specifically to run it only once for all attributes. Counts are indicators of if the corresponding attribute exists (0 - doesn't exist, <>0 - exists). If you know all the attribute names beforehand, you may modify my query using the corresponding number of codeX XML PATH... columns and specify OR'ing all of them in the XMLEXISTS predicate. So, you may run my query only once for all your attributes... – Mark Barinstein Apr 16 at 15:35
  • I tried it like: sql = f"""select count(x.a1), count(x.a2), count(x.a3), count(x.a4) from ( SELECT ENTRY_XML FROM {schema}.ATOM_ENTRY ) T(D) cross join XMLTABLE ('$doc//*' PASSING T.D as "doc" COLUMNS a1 XML PATH '//*[fn:exists(*:larosateskod)]', a2 XML PATH '//*[fn:exists(*:uuid)]', ...) x WHERE XMLEXISTS('$doc//*[*:larosateskod or *:uuid or ...]' PASSING T.D as "doc" )""" c1.execute(sql) but the problem is that it actually counts the matches (which takes some time). Result: (82544, 0, 82544, 0), time ~7sek. I'm really only interested if the element exists or not – Lennart Apr 16 at 19:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.