XMLQuery in DB2

Question

We have a CLOB column, in a DB2 database that contains XML. The query does not seem to like the embedded xmlns. I simplified the query down to this:

values xmlquery('$rf/Producers/FIRMS/FIRM/EIN/text()' passing xmlparse(document '<Producers xmlns="http://yummy.cupcakes.com"><FIRMS><FIRM><EIN>27</EIN></FIRM>/</FIRMS></Producers>') as "rf")

If I remove the xmlns embedded in Producers, it works. As is, it does not return anything, but does not throw an error. Like I said, this is simplified for troubleshooting. In reality, the document would be from a CLOB column in our DB. All th XML stored in the CLOBs contains the xmlns. Any suggestions on how to deal with this?

Answer 1

You need to declare a default namespace right in the XMLQUERY.

SELECT XMLQUERY (
  'declare default element namespace "http://yummy.cupcakes.com";
   ...

Reference link: https://www.ibm.com/docs/en/db2/11.1?topic=tutorial-lesson-6-querying-xml-data