从db2中使用SQL从XML Clob中提取数据 [英] Extract data from XML Clob using SQL from db2

问题描述

我想从表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屋！