XMLExists Select in DB2 for complex data

Question

I have this xml on my db:

<requestXML>
  <headers>
    <name>Accept</name>
    <value>text/plain, application/json, application/*+json, */*</value>
  </headers>
  <method>PUT</method>
  <payload>{"timestamp":"1659167441820","orderIdentifierCode":"OC22004795","clientName":"XXXX","country":"XX","vatNumber":"XXXXXXXX","orderDate":"XX/XX/XXXX","orderState":"XX"}</payload>
<threadName>default task-xx</threadName>
<url>http://localhost:8080/service_name</url>
</requestXML>

I tried to perform a SELECT statement with XMLExists but with no results. What I need is to search into <payload> tag, inside those json body, the orderIdentifierCode value.

Here my SELECT:

SELECT * FROM EVDA.TETSIR_LOG_WS_PBE
    AS X
        WHERE XMLExists('$XML//requestXML/payload[orderIdentifierCode="OC22004795"]' passing X.T_RIC_XML AS "XML")
        AND D_TMS_REG BETWEEN '2022-07-27 00:00:00.000001' AND '2022-08-02 23:59:59.999999'
WITH UR;

This one, on the contrary, is working fine:

SELECT * FROM EVDA.TETSIR_LOG_WS_PBE
    AS X
        WHERE XMLExists('$XML//requestXML[method="PUT"]' passing X.T_RIC_XML AS "XML")
        AND D_TMS_REG BETWEEN '2022-07-27 00:00:00.000001' AND '2022-08-02 23:59:59.999999'
WITH UR;

I started to think that the first one is not well formatted or that something is missing due to complex value inside the tag <payload>.

Any guess?

Thanks in advance

Answer 1

You have to get the JSON value first and use it afterwards.
Run the statements below depending on the database codepage as is.

UTF-8 databases only

SELECT 
  XT.NAME
, XT.METHOD
--, JT.*
FROM 
(
VALUES XMLPARSE 
(
DOCUMENT '
<requestXML>
  <headers>
    <name>Accept</name>
    <value>text/plain, application/json, application/*+json, */*</value>
  </headers>
  <method>PUT</method>
  <payload>
    {
      "timestamp": "1659167441820"
    , "orderIdentifierCode": "OC22004795"
    , "clientName": "XXXX"
    , "country": "XX"
    , "vatNumber": "XXXXXXXX"
    , "orderDate": "XX/XX/XXXX"
    , "orderState": "XX"
    }
  </payload>
  <threadName>default task-xx</threadName>
  <url>http://localhost:8080/service_name</url>
</requestXML>
'
)
) X (T_RIC_XML)
CROSS JOIN XMLTABLE 
(
  '$XML/requestXML' PASSING X.T_RIC_XML AS "XML"
  COLUMNS 
    JV          CLOB (1K)       PATH 'payload'
  , NAME        VARCHAR (20)    PATH 'headers/name'
  , METHOD      VARCHAR (10)    PATH 'method'
) XT
/*
CROSS JOIN JSON_TABLE
(
  XT.JV, 'strict $' 
  COLUMNS
  (
    timestamp           VARCHAR(20)     PATH '$.timestamp'
  , orderIdentifierCode VARCHAR (20)    PATH '$.orderIdentifierCode'
  ) ERROR ON ERROR
) JT
WHERE JT.orderIdentifierCode = 'OC22004795'
*/
-- If you want to get other JSON columns
-- comment out the line below and uncomment the block above
WHERE JSON_VALUE (XT.JV, 'strict $.orderIdentifierCode' RETURNING VARCHAR (20)) = 'OC22004795'

All database encodings

You have to use older SYSTOOLS.JSON2BSON function in a non-unicode database.
The preferable way is not to use SYSTOOLS JSON functions. So, if you have UTF-8 database, it's better to use the above statement.
Seems, that SYSIBM.JSON_TABLE doesn't work in non-unicode databases.

SELECT 
  XT.NAME
, XT.METHOD
, JSON_VAL (SYSTOOLS.JSON2BSON (XT.JV), 'timestamp', 's:20')            AS timestamp
, JSON_VAL (SYSTOOLS.JSON2BSON (XT.JV), 'orderIdentifierCode', 's:20')  AS orderIdentifierCode
FROM 
(
VALUES XMLPARSE 
(
DOCUMENT '
<requestXML>
  <headers>
    <name>Accept</name>
    <value>text/plain, application/json, application/*+json, */*</value>
  </headers>
  <method>PUT</method>
  <payload>
    {
      "timestamp": "1659167441820"
    , "orderIdentifierCode": "OC22004795"
    , "clientName": "XXXX"
    , "country": "XX"
    , "vatNumber": "XXXXXXXX"
    , "orderDate": "XX/XX/XXXX"
    , "orderState": "XX"
    }
  </payload>
  <threadName>default task-xx</threadName>
  <url>http://localhost:8080/service_name</url>
</requestXML>
'
)
) X (T_RIC_XML)
CROSS JOIN XMLTABLE 
(
  '$XML/requestXML' PASSING X.T_RIC_XML AS "XML"
  COLUMNS 
    JV          CLOB (1K)       PATH 'payload'
  , NAME        VARCHAR (20)    PATH 'headers/name'
  , METHOD      VARCHAR (10)    PATH 'method'
) XT
WHERE JSON_VAL (SYSTOOLS.JSON2BSON (XT.JV), 'orderIdentifierCode', 's:20') = 'OC22004795'