Oracle xmltype解决方案

/* 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;