Topic
1 reply Latest Post - ‏2014-10-22T07:22:43Z by MatthiasNicola
MatthewDonald
MatthewDonald
1 Post
ACCEPTED ANSWER

Pinned topic [DB2 LUW] XML use in predicates

‏2014-03-07T05:02:40Z |

I have an XML feed I need to process.  Each XML document contains data on related issues for the previous 30 days, so the only way to determine any changed or new issue is to compare the current and previous documents.  A little googling leads to this blog entry from the pureXML team, which states:

XML comparison rules
 
The SQL XML type cannot be compared with any other type, including the XML type itself, using SQL comparison operators. XML can only be tested using one of the following two predicates:
 
    xml-expr IS NULL, or xml-expr IS NOT NULL
    theXMLEXISTS predicate, you can use XPath operators within XMLEXISTS.
 
If you need to compare an XML atomic value extracted from an XML document using XPath with an SQL value, you need to use XMLCAST to cast to an SQL type. XMLCAST can be used to cast a value from XML to SQL, or from SQL to XML. Casting from XML to SQL, such as XMLCAST(XMLQUERY('/purchaseOrder/@PONum' PASSING X) as INTEGER), is its main usage, as casting from SQL to XML is done by DB2 implicitly in most cases, such as in parameter passing of XMLQUERY, XMLEXISTS, and XMLTABLE.

So both XML and SQL columns can be compared, subject to some common sense rules (like with like).

So I tried this

SELECT ...
FROM ...
WHERE xmlcol = XMLPARSE(DOCUMENT 'xml-text')

This query isn't intended to be efficient - efficiency will be added later - it's simply meant to compare an XML document (the previous value) with a text-formatted XML document (the new value).

The result of the query was SQLCODE=-401 SQLSTATE=42818.  Checking the message reference gives the following explanation:

The operation using operator cannot be processed for one of the following reasons:
  •     The data types of the operands must be comparable and compatible but there is at least one pair of types that is not comparable or compatible.
  •     The data type of an operand is XML which cannot be compared (with itself or any other type).
  •     The data type of an operand is DATALINK which cannot be compared (with itself or any other type).
  •     The data type of an operand is a structured type which cannot be compared (with itself or any other type).
The error text is stating that XML operands cannot be compared at all.  So which is true - the pureXML team blog or the error message?  What am getting wrong?

Matthew

  • MatthiasNicola
    MatthiasNicola
    321 Posts
    ACCEPTED ANSWER

    Re: [DB2 LUW] XML use in predicates

    ‏2014-10-22T07:22:43Z  in response to MatthewDonald

    Ok, better late than never, here is an answer!

     

    >> So which is true - the pureXML team blog or the error message?

    Both are correct ! :-)

    The blog post referenced above starts by saying that "The XML type cannot be compared with any other type, including the XML type itself, using SQL comparison operators."  And this is exactly where the error message above is saying too.

     

    The comparison in your sample is: WHERE xmlcol = XMLPARSE(DOCUMENT 'xml-text')

    In this comparison, the left-hand side and right-hand side operands have data type XML, but you cannot use the SQL equality comparison operator "=" to compare something that is of type XML.

    The blog post referenced above goes on to explain that you could extract individual values from the XML document, convert these values to SQL data types such as Integer, Varchar, Date, etc, and then compare those values. But, that's not what you want in your example.

     

    If you want to compare entire XML documents with each other, the XQuery function fn:deep-equal is the right tool for the job:

    http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.5.0/com.ibm.db2.luw.xml.doc/doc/xqrfndpe.html

     

    Here is a rough example (from memory, not tested):

    
    SELECT xmlcol
    FROM mytable
    WHERE XMLEXISTS('$XMLCOL[fn:deep-equal( . , $input)]'
             passing  XMLPARSE(DOCUMENT 'xml-text...' AS "input" )
    

     

    Does that help?

    Matthias