SELECT on XML table using fn:upper-case can return incorrect
results when the table has an XML index.
If you have the following conditions:
- Table with XML Column
- Index on this XML column and does not contain the upper-case
function in the path
- SELECT statement that has where condition on the XML column
that uses fn:upper-case
For example,
db2 -v "create table myXMLtable(id int, XMLcol XML)"
db2 -v "insert into myXMLtable values
(1,'<AddProtQuoteResponse><surname>Griffin</surname><test>test1<
/test></AddProtQuoteResponse>')"
db2 -v "select t.id, x.surname,x.surname1 from myXMLtable t,
xmltable (xmlnamespaces('http://www.example.org/DataTypeTest' as
\"inter\"), '\$i/*:AddProtQuoteResponse' passing t.XMLcol AS
\"i\" columns surname varchar(15) path 'surname', surname1
varchar(15) path 'surname/fn:upper-case(.)') as x where
x.surname1 = 'GRIFFIN'";
db2 -v "CREATE INDEX TSTIDX ON myXMLtable (XMLcol ASC) GENERATE
KEY USING XMLPATTERN '/*:AddProtQuoteResponse/surname' AS SQL
VARCHAR (255)"
db2 -v "select t.id, x.surname,x.surname1 from myXMLtable t,
xmltable (xmlnamespaces('http://www.example.org/DataTypeTest' as
\"inter\"), '\$i/*:AddProtQuoteResponse' passing t.XMLcol AS
\"i\" columns surname varchar(15) path 'surname', surname1
varchar(15) path 'surname/fn:upper-case(.)') as x where
x.surname1 = 'GRIFFIN'"; |
