Oracle xml实践

来源：互联网发布：宝宝树知乎编辑：程序博客网时间：2024/04/29 02:41

官方参考文档：XML DB Developer's Guide(11.2)

1、带有XMLType字段的表操作

-- Create tablecreate table T_XML_TEST(  id        NUMBER,  xml_value XMLTYPE);

--使用xmltype数据类型的静态方法createxml插入xml数据INSERT INTO T_XML_TEST(id, xml_value) VALUES (1, XMLType.CreateXML(       '<?xml version="1.0"?>         <Envelope>           <Body>              <sayHelloResponse>                 <return>94065中国 say: hello [axis2]</return>              </sayHelloResponse>         </Body>      </Envelope>''));

--查询带xmltype类型字段的表的方法select t.id, t.xml_value.getclobval() xml_value from T_XML_TEST t;

2、XMLTABLE函数使用，检索xml节点的值

（1）xml不带命名空间，如下xml：

<?xml version="1.0"?>  <Envelope>     <Body>        <sayHelloResponse>           <return>94065中国 say: hello [axis2]</return>        </sayHelloResponse>     </Body>  </Envelope>

示例：

SELECT t."return", t."return2"  FROM        XMLTABLE(XMLNAMESPACES('http://www.w3.org/2003/05/soap-envelope' as "soap",                 'http://ws.apache.org/axis2' as "tns"                 )                ,'/Envelope' PASSING Xmltype.createxml(                '<?xml version="1.0"?>                   <Envelope>                     <Body>                       <sayHelloResponse>                         <return>94065中国 say: hello [axis2]</return>                       </sayHelloResponse>                     </Body>                   </Envelope>'                )                COLUMNS                 "return" varchar2(100) PATH '/Envelope/Body/sayHelloResponse/return'                ,"return2" varchar2(100) PATH '/Envelope/Body/sayHelloResponse/return'                ) t;

输出结果：

（2）xml带有命名空间，如下xml：

<?xml version="1.0" encoding="UTF-8"?>  <soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:tns="http://ws.apache.org/axis2">    <soap:Body>      <tns:delete>        <tns:record_id>7352</tns:record_id>      </tns:delete>    </soap:Body>  </soap:Envelope>

示例：

SELECT t."return", t."return2"  FROM        XMLTABLE(XMLNAMESPACES('http://www.w3.org/2003/05/soap-envelope' as "soap",                 'http://ws.apache.org/axis2' as "tns"                 )                ,'/soap:Envelope' PASSING Xmltype.createxml(                '<?xml version="1.0" encoding="UTF-8"?>                  <soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:tns="http://ws.apache.org/axis2">                   <soap:Body>                    <tns:delete>                     <tns:record_id>7352</tns:record_id>                    </tns:delete>                   </soap:Body>                  </soap:Envelope>'                )                COLUMNS                 "return" varchar2(10) PATH '/soap:Envelope/soap:Body/tns:delete/tns:record_id'                ,"return2" varchar2(10) PATH '/soap:Envelope/soap:Body/tns:delete/tns:record_id'                ) t

输出结果：

（3）附加where条件

SELECT t.id, t.xml_value.getclobval(), t2."return"  FROM T_XML_TEST t,       XMLTABLE(XMLNAMESPACES('http://www.w3.org/2003/05/soap-envelope' as "soap",                 'http://ws.apache.org/axis2' as "tns"                 )                ,'/soap:Envelope' PASSING t.xml_value COLUMNS                "return" varchar2(10) PATH '/soap:Envelope/soap:Body/tns:delete/tns:record_id') t2  where t.id = 254;