XMLExists在DB2中为复杂数据选择

我的数据库中有这个xml：

<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>

我试着用XMLExists执行SELECT语句，但是没有结果。我需要的是搜索<payload>标记，在那些json主体中，orderIdentifierCode值。
这里是我的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;

相反，这一个工作得很好：

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;

我开始认为第一个没有很好的格式，或者由于标记<payload>中的复杂值而缺少了一些东西。
猜猜看？
先谢谢你

您必须先获取JSON值，然后再使用它。
根据数据库代码页运行下面的语句。

仅限UTF-8数据库

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'

所有数据库编码

您必须在非Unicode数据库中使用旧版的SYSTOOLS.JSON2BSON函数。
更好的方法是不使用SYSTOOLS JSON函数，所以，如果你有UTF-8数据库，最好使用上面的语句。
看起来，SYSIBM.JSON_TABLE在非unicode数据库中不起作用。

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'

XMLExists在DB2中为复杂数据选择

1条答案

相关问题

热门标签

最新问答