从db2中使用SQL从XML Clob中提取数据 [英] Extract data from XML Clob using SQL from db2
本文介绍了从db2中使用SQL从XML Clob中提取数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想从表TRAPTABCLOB中使用sql提取Decision的值,该列具有存储为clob的XML的列testclob。 IN DB2
I want to extract the value of Decision using sql from table TRAPTABCLOB having column testclob with XML stored as clob. IN DB2
以下示例XML
<?xml version="1.0" encoding="UTF-8"?>
<DCResponse>
<Status>Success</Status>
<Authentication>
<Status>Success</Status>
</Authentication>
<ResponseInfo>
<ApplicationId>5701200</ApplicationId>
<SolutionSetInstanceId>
63a5c214-b5b5-4c45-9f1e-b839a0409c24
</SolutionSetInstanceId>
<CurrentQueue />
</ResponseInfo>
<ContextData>
<!--Decision Details Start-->
<Field key="SoftDecision">A</Field>
<Field key="**Decision**">1</Field>
<Field key="NodeNo">402</Field>
<Field key="NodeDescription" />
<!--Decision Details End-->
<!--Error Details Start-->
<Field key="ErrorResponse">
<Response>
<Status>[STATUS]</Status>
<ErrorCode>[ERRORCODE]</ErrorCode>
<ErrorDescription>[ERRORDESCRIPTION]</ErrorDescription>
<Segment>[SEGMENT]</Segment>
</Response>
</Field>
<Field key="ErrorCode">0</Field>
<Field key="ErrorDescription" />
</ContextData>
</DCResponse>
推荐答案
使用XMLTABLE()它产生一个表达式,可以用作子查询或加入表或其他SQL表达式。
One of the nice things about using XMLTABLE() is that it produces an expression that can be used as a subquery or joined to a table or another SQL expression.
SELECT x.decision
FROM traptabclob, XMLTABLE(
'$d/DCResponse/ContextData[1]' PASSING XMLPARSE(DOCUMENT testclob) AS "d"
COLUMNS
DECISION CHAR(1) PATH 'Field[@key="**Decision**"][1]'
) AS x
;
这篇关于从db2中使用SQL从XML Clob中提取数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文