创建表:
CREATE TABLE STXML
(
ID NUMBER NOT NULL,
NAME VARCHAR2(256 BYTE) NOT NULL,
SEX CHAR(1 BYTE) NOT NULL,
AGE NUMBER NOT NULL,
XMLDOC SYS.XMLTYPE
)
查询
SELECT * FROM (
SELECT id,
XMLQUERY(
'for
$j in /Person
return
<Tr class="tr_moveout">
<td>
{$j/Id/text()}
</td>
<td>
{$j/Name/text()}
</td>
<td>
{$j/Age/text()}
</td>
<td>
{if ($j/Sex = "1")
then
"男"
else if($j/Sex = "2")
then
"女"
else
"未知"
}
</td>
</Tr>
'
PASSING xmldoc RETURNING CONTENT).getStringVal() xcontent FROM stxml
) WHERE xcontent IS NOT NULL;
更新XML节点:
UPDATE stxml medr
set medr.xmldoc = updateXML(medr.xmldoc,'/Person/Name/text()','改改改')
where medr.id = 1;
UPDATE stxml medr
set medr.xmldoc = updateXML(medr.xmldoc,'/Person/Name/text()','更改一下名字',
'/Person/Age/text()',99)
where XMLExists('$p/Person[Age="99"]'
PASSING xmldoc AS "p")
删除XML节点:
UPDATE stxml
SET xmldoc =
DELETEXML (xmldoc, '/Person/History/Disease/DiseaseItem[DiseaseName="糖尿病"]')
WHERE XMLExists('$p/Person[Name="测试数据"]'
PASSING xmldoc AS "p")