DB2数据库操作XMl字段

1.xml查询语句

 ① 查询xml中的单个节点内容

　　　select TM_PRM_OBJECT.ORG , TM_PRM_OBJECT.PARAM_CLASS , TM_PRM_OBJECT.PARAM_KEY , XMLCAST(xmlquery('$d/com.allinfinance.cps.param.def.AuthTxnCode/templateCode'

        PASSING XMLPARSE(DOCUMENT(param_object)) AS "d") AS VARCHAR(100)) , XMLCAST(xmlquery('$d/com.allinfinance.cps.param.def.AuthTxnCode/description'

        PASSING XMLPARSE(DOCUMENT(param_object)) AS "d")  AS VARCHAR(100))

        from BMP.TM_PRM_OBJECT where TM_PRM_OBJECT.PARAM_CLASS like '%AuthTxnCode%'

② 插入语句（插入内容是个查询语句）

       insert into TM_RULE_NAME_TEMP (PARAM_KEY,RULE_NAME)  select  PARAM_KEY, 　　 　　XMLCAST(xmlquery('$d/com.allinfinance.cps.param.def.AuthTemplateCode/authCheckPointEnabled/entry/com.allinfinance.cps.param.def.enums.AuthCheckPoint'   PASSING    XMLPARSE(DOCUMENT(param_object)) AS "d")  AS 　　 　　VARCHAR(20))

　　 from TM_PRM_OBJECT where param_class='com.allinfinance.cps.param.def.AuthTemplateCode'

③ 根据指定的xml节点的内容查询

　　select TM_PRM_OBJECT.PARAM_KEY from  TM_PRM_OBJECT  where param_class='com.allinfinance.bmp.param.def.Product' and   XMLEXISTS('$d/com.allinfinance.bmp.param.def.Product[brand="C"]'  　　PASSING 　　XMLPARSE(DOCUMENT(param_object)) AS "d")

2.xml字段更新语句

①插入节点

　　UPDATE  TM_PRM_OBJECT  set  PARAM_OBJECT = xml2clob(xmlquery('copy $new := $d

            modify do insert  ****（节点内容） as last into $new/com.allinfinance.cps.param.def.AuthTxnCode

　　return $new' PASSING XMLPARSE(DOCUMENT(param_object)) as "d"))

　　where PARAM_CLASS='com.allinfinance.cps.param.def.AuthTxnCode'

　　and XMLEXISTS('$d/com.allinfinance.cps.param.def.AuthTxnCode[templateCode="01551"]' PASSING XMLPARSE(DOCUMENT(param_object)) AS "d")

② 节点重命名（修改）

　　UPDATE TM_PRM_OBJECT set TM_PRM_OBJECT.PARAM_CLASS='com.allinfinance.bmp.param.def.InputChannel', PARAM_OBJECT = xml2clob(xmlquery('copy $new := $d

        modify  do  rename  $new/com.allinfinance.cps.param.def.InputChannel  as "com.allinfinance.bmp.param.def.InputChannel"      

        return $new' PASSING XMLPARSE(DOCUMENT(param_object)) as "d"))

     where PARAM_CLASS='com.allinfinance.cps.param.def.InputChannel'

3.xml节点删除（这里为shell脚本中写法，在数据库中要去掉转义符）

　　删除map中的一个元素

　　db2 "UPDATE TM_PRM_OBJECT set PARAM_OBJECT = xml2clob(xmlquery('copy \$new := \$d

            modify do delete  \$new/com.allinfinance.cps.param.def.AuthTemplateCode/authCheckPointEnabled/entry[com.allinfinance.cps.param.def.enums.AuthCheckPoint=\"Ass_019\"]

        return \$new' PASSING XMLPARSE(DOCUMENT(param_object)) as \"d\"))

        where PARAM_CLASS='com.allinfinance.cps.param.def.AuthTemplateCode' and PARAM_KEY in ('${bankTemplateCd}','${bankConfirmTemplateCd}','${cupTemplateCd}','${cupConfirmTemplateCd}')    and      　　　　　  　　　  　　XMLEXISTS('\$d/com.allinfinance.cps.param.def.AuthTemplateCode/authCheckPointEnabled/entry[com.allinfinance.cps.param.def.enums.AuthCheckPoint=\"Ass_019\"]' )"

　　

　　删除单独的一个节点

　　update tm_prm_object set tm_prm_object.PARAM_OBJECT=xml2clob(xmlquery('copy $new := $d modify do delete $new/com.allinfinance.cps.param.def.ProductCredit/postCurrCd

　　return $new' PASSING XMLPARSE(DOCUMENT(param_object)) AS "d")) where tm_prm_object.PARAM_CLASS='com.allinfinance.cps.param.def.ProductCredit' and param_key='000001';

转载于:https://www.cnblogs.com/cowboys/p/6418662.html