/* Formatted on 2016/5/23 13:58:17 (QP5 v5.256.13226.35538) */
DELETE FROM s_org
WHERE sog_id = 0;
--数据准备
INSERT INTO s_org (sog_id, sog_remark)
VALUES (0, xmltype ('<?xml version="1.0" encoding="UTF-8"?>
<root>
<cont>
<username>twb0</username>
<atime>2016-05-20 16:30:00</atime>
<detail>xxxxx0</detail>
</cont>
<cont>
<username>twb1</username>
<atime>2016-05-21 16:30:00</atime>
<detail>xxxxx1</detail>
</cont>
<cont>
<username>twb2</username>
<atime>2016-05-22 16:30:00</atime>
<detail>xxxxx2</detail>
</cont>
</root>'));
--查询出某个节点的所有数据
SELECT XMLQUERY ('/root/cont/username'
PASSING t.sog_remark RETURNING CONTENT)
FROM s_org t
WHERE t.sog_id = 0;
--以列表方式显示节点数据
SELECT t.sog_id,
x.username,
x.detail,
TO_DATE (x.atime, 'yyyy-mm-dd hh24:Mi:ss') replyTime
FROM s_org t,
XMLTABLE (
'/root/cont'
PASSING t.sog_remark
COLUMNS username VARCHAR2 (40) PATH 'username',
detail VARCHAR2 (200) PATH 'detail',
atime VARCHAR2 (19) PATH 'atime') x
WHERE T.SOG_ID = 0 AND XMLEXISTS ('/root/cont' PASSING t.sog_remark)
ORDER BY replytime DESC;
--检索某个节点的数据
SELECT t.sog_id, x.*
FROM s_org t,
XMLTABLE (
'/root/cont'
PASSING t.sog_remark
COLUMNS username VARCHAR (40) PATH 'username',
detail VARCHAR (200) PATH 'detail',
atime VARCHAR2 (19) PATH 'atime') x
WHERE T.SOG_ID = 0 AND x.username = 'twb0';
--增加节点
UPDATE s_org t
SET t.sog_remark =
INSERTCHILDXML (
t.sog_remark,
'/root',
'cont',
xmltype (
'<cont><username>twb3</username><detail>xxxxx3</detail><atime>2016-05-25 16:30:00</atime></cont>'))
WHERE T.SOG_ID = 0;
UPDATE s_org t
SET T.SOG_REMARK =
APPENDCHILDXML (
t.sog_remark,
'/root',
xmltype (
'<cont><username>twb3</username><detail>xxxxx3</detail><atime>2016-05-24 16:30:00</atime></cont>'))
WHERE T.SOG_ID = 0;
--修改某个节点数据
UPDATE s_org t
SET t.sog_remark =
UPDATEXML (t.sog_remark,
'/root/cont/username[text()="twb3"]/text()',
'testname')
WHERE T.SOG_ID = 0
AND XMLEXISTS ('/root/cont/username[text()="twb3"]'
PASSING t.sog_remark);
--删除指定的节点
UPDATE s_org t
SET T.SOG_REMARK =
DELETEXML (t.sog_remark,
'/root/cont[username="testname"]')
WHERE T.SOG_ID = 0
AND XMLEXISTS ('/root/cont/username[text()="testname"]'
PASSING t.sog_remark);
SELECT t.sog_id, t.sog_remark
FROM s_org t
WHERE t.sog_id = 0;
DELETE FROM s_org
WHERE sog_id = 0;
--数据准备
INSERT INTO s_org (sog_id, sog_remark)
VALUES (0, xmltype ('<?xml version="1.0" encoding="UTF-8"?>
<root>
<cont>
<username>twb0</username>
<atime>2016-05-20 16:30:00</atime>
<detail>xxxxx0</detail>
</cont>
<cont>
<username>twb1</username>
<atime>2016-05-21 16:30:00</atime>
<detail>xxxxx1</detail>
</cont>
<cont>
<username>twb2</username>
<atime>2016-05-22 16:30:00</atime>
<detail>xxxxx2</detail>
</cont>
</root>'));
--查询出某个节点的所有数据
SELECT XMLQUERY ('/root/cont/username'
PASSING t.sog_remark RETURNING CONTENT)
FROM s_org t
WHERE t.sog_id = 0;
--以列表方式显示节点数据
SELECT t.sog_id,
x.username,
x.detail,
TO_DATE (x.atime, 'yyyy-mm-dd hh24:Mi:ss') replyTime
FROM s_org t,
XMLTABLE (
'/root/cont'
PASSING t.sog_remark
COLUMNS username VARCHAR2 (40) PATH 'username',
detail VARCHAR2 (200) PATH 'detail',
atime VARCHAR2 (19) PATH 'atime') x
WHERE T.SOG_ID = 0 AND XMLEXISTS ('/root/cont' PASSING t.sog_remark)
ORDER BY replytime DESC;
--检索某个节点的数据
SELECT t.sog_id, x.*
FROM s_org t,
XMLTABLE (
'/root/cont'
PASSING t.sog_remark
COLUMNS username VARCHAR (40) PATH 'username',
detail VARCHAR (200) PATH 'detail',
atime VARCHAR2 (19) PATH 'atime') x
WHERE T.SOG_ID = 0 AND x.username = 'twb0';
--增加节点
UPDATE s_org t
SET t.sog_remark =
INSERTCHILDXML (
t.sog_remark,
'/root',
'cont',
xmltype (
'<cont><username>twb3</username><detail>xxxxx3</detail><atime>2016-05-25 16:30:00</atime></cont>'))
WHERE T.SOG_ID = 0;
UPDATE s_org t
SET T.SOG_REMARK =
APPENDCHILDXML (
t.sog_remark,
'/root',
xmltype (
'<cont><username>twb3</username><detail>xxxxx3</detail><atime>2016-05-24 16:30:00</atime></cont>'))
WHERE T.SOG_ID = 0;
--修改某个节点数据
UPDATE s_org t
SET t.sog_remark =
UPDATEXML (t.sog_remark,
'/root/cont/username[text()="twb3"]/text()',
'testname')
WHERE T.SOG_ID = 0
AND XMLEXISTS ('/root/cont/username[text()="twb3"]'
PASSING t.sog_remark);
--删除指定的节点
UPDATE s_org t
SET T.SOG_REMARK =
DELETEXML (t.sog_remark,
'/root/cont[username="testname"]')
WHERE T.SOG_ID = 0
AND XMLEXISTS ('/root/cont/username[text()="testname"]'
PASSING t.sog_remark);
SELECT t.sog_id, t.sog_remark
FROM s_org t
WHERE t.sog_id = 0;