oracle xmltype简单操作_jc_benben的专栏_oracle xmltype 操作

create table T_XML
(
  xml_type XMLTYPE
)


DECLARE
  v_x_str CLOB;
  v_xml   XMLTYPE;
  v_title VARCHAR2(50);

begin
  v_x_str := '<?xml version="1.0" encoding="utf-16"?>
             <Info>
                    <Id>123</Id>
                    <Name>loge</Name>
             </Info>'; 
  v_xml := XMLTYPE(v_x_str); -- 生成XML
  insert into t_xml (xml_type) values (v_xml);
  select xmlelement(id_test, 123456) into v_xml from dual;
  insert into T_XML (xml_type) values (v_xml);
  commit;
  SELECT EXTRACTVALUE(VALUE(t), '/Info/Id')
    INTO v_title
    FROM T_XML x,
         TABLE(XMLSEQUENCE(EXTRACT(x.xml_type, '/Info'))) t; -- 提取XML节点值
  dbms_output.put_line(v_title);

END;


--然而也可以使用
select t.xml_type.extract('/Info/Id') from T_XML t;
select t.xml_type.extract('/Info/Id/text()') from T_XML t;
select t.xml_type.extract('/Info/Id/text()').getstringval() from T_XML t;
select extract(t.xml_type,'/Info/Id/text()').getstringval() from T_XML t;
select extractvalue(t.xml_type,'/Info/Id') from T_XML t
create table T_XML
(
  xml_type XMLTYPE
)


DEC