XMLEXISTS query is not giving the desired output in DB2

Question

I am trying to query one XML column in DB2 but not getting the desired output. I want to fetch all the records where Name='Inder'

Below is the structure of my database -

Table Name - CUSTOMER

Column Name - XML_DATA

Structure of XML -

<ns2:CustomerRequest xmlns:ns2=http://en.nomura.customer.submit>

<CustomerData>

    <Name>Inder</Name>

and this is the query which i am trying -

SELECT * FROM CUSTOMER WHERE XMLExists ('$d//CustomerData[Name="Inder"]' passing XML_DATA as "d")

Below is the error - Code: -16011, SQL State: 10507] The result of an intermediate step expression in an XQuery path expression contains an atomic value. Error QName=err:XPTY0019.. SQLCODE=-16011, SQLSTATE=10507, DRIVER=4.22.29

[Code: -727, SQL State: 56098] An error occurred during implicit system action type "2". Information returned for the error includes SQLCODE "-16011", SQLSTATE "10507" and message tokens "10507".. SQLCODE=-727, SQLSTATE=56098, DRIVER=4.22.29

Can anyone please help me with the query

What type is XML_DATA ? if a string type you have to XMLPARSE it first — nfgl
– nfgl, Commented Mar 26 at 8:45

Mark Barinstein · Accepted Answer · 2025-03-26 13:03:05Z

0

You must cast your XML document string data representation to the XML data type in the XMLEXISTS predicate.

SELECT XML_DATA
FROM (VALUES 
'<CustomerData>
    <Name>Inder</Name>
</CustomerData>'
) CUSTOMER(XML_DATA)
WHERE XMLExists ('$d//CustomerData[Name="Inder"]' passing 
XMLPARSE(DOCUMENT XML_DATA)  -- Correct
--XML_DATA  -- Wrong
as "d")

The result is:

XML_DATA
<CustomerData><Name>Inder</Name></CustomerData>

fiddle

answered Mar 26 at 13:03

Mark Barinstein

12.8k2 gold badges11 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

XMLEXISTS query is not giving the desired output in DB2

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related