库表结构:
create table T_REPORTTOTAL
(
F_ID NUMBER(20),
F_CODE VARCHAR2(100),
F_DATA SYS.XMLTYPE
)
F_DATA 是XMLTYPE字段
<?xml version="1.0" encoding="GBK"?>
<Excel>
<Sheets>
<Sheet index="0">
<Rows>
<Row index="7">
<Cells>
<Cell rowPoint="7" colPoint="A" type="label" rowHeadText="听力残疾班" colHeadText="听力残疾班" dataType="" rowSpan="1" colSpan="2" formula="">听力残疾班</Cell>
<Cell rowPoint="7" colPoint="C" type="label" rowHeadText="03" colHeadText="03" dataType="" rowSpan="1" colSpan="1" formula="">03</Cell>
<Cell rowPoint="7" colPoint="D" type="value" rowHeadText="听力残疾班" colHeadText="合计" dataType="int" rowSpan="1" colSpan="1" formula=""/>
</Cells>
</Row>
</Rows>
</Sheet>
</Sheets>
</Excel>当节点有值的情况下可以更新成功:
比如:
UPDATE t_reporttotal p
SET F_DATA = updateXML(F_DATA,
'/Excel/Sheets/Sheet/Rows/Row[@index="7"]/Cells/Cell[@colPoint="C"]/text()',
'6666')
可以把节点值03换成6666但是,当节点没有值的情况下就更新不成功了:
比如:
UPDATE t_reporttotal
SET F_DATA = updateXML(F_DATA,
'/Excel/Sheets/Sheet/Rows/Row[@index="7"]/Cells/Cell[@colPoint="D"]/text()',
'8888888')
create table T_REPORTTOTAL
(
F_ID NUMBER(20),
F_CODE VARCHAR2(100),
F_DATA SYS.XMLTYPE
)
F_DATA 是XMLTYPE字段
<?xml version="1.0" encoding="GBK"?>
<Excel>
<Sheets>
<Sheet index="0">
<Rows>
<Row index="7">
<Cells>
<Cell rowPoint="7" colPoint="A" type="label" rowHeadText="听力残疾班" colHeadText="听力残疾班" dataType="" rowSpan="1" colSpan="2" formula="">听力残疾班</Cell>
<Cell rowPoint="7" colPoint="C" type="label" rowHeadText="03" colHeadText="03" dataType="" rowSpan="1" colSpan="1" formula="">03</Cell>
<Cell rowPoint="7" colPoint="D" type="value" rowHeadText="听力残疾班" colHeadText="合计" dataType="int" rowSpan="1" colSpan="1" formula=""/>
</Cells>
</Row>
</Rows>
</Sheet>
</Sheets>
</Excel>当节点有值的情况下可以更新成功:
比如:
UPDATE t_reporttotal p
SET F_DATA = updateXML(F_DATA,
'/Excel/Sheets/Sheet/Rows/Row[@index="7"]/Cells/Cell[@colPoint="C"]/text()',
'6666')
可以把节点值03换成6666但是,当节点没有值的情况下就更新不成功了:
比如:
UPDATE t_reporttotal
SET F_DATA = updateXML(F_DATA,
'/Excel/Sheets/Sheet/Rows/Row[@index="7"]/Cells/Cell[@colPoint="D"]/text()',
'8888888')
'//fields/field[@Name="intro"]/test()',
'aaaaaa'),可以写成
updateXML(XMLTYPE(content),
'//fields/field[@Name="intro"]',
'<field Name="intro">aaaaaa</field>').getclobVal()