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 rulesThe 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 NULLtheXMLEXISTS 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).