EXTRACT

说明

该函数用于得到一个xpath表达式作用于xml数据上产生的结果，该结果是一个节点集合。（兼容Oracle）

语法

extract ::=

extract::=EXTRACT(xmltype_instance, xpath_string)

参数

XMLType_instance

用于指定XMLType实例

Xpath_string

用于指定XML节点路径

返回值

Xpath作用于xml数据后得到的节点集

注释

该函数暂不支持指定namespace

在pl中，extract可以通过变量调用：xmldata.extract(XPath_string)

示例

示例1：

SQL> select extract(xmltype('<aa><bb>afeafe</bb></aa>'),'/aa/bb | /aa') from dual;
                extract
-----------------------------------------
<aa><bb>afeafe</bb></aa><bb>afeafe</bb>
(1 row)

示例2：在pl中通过变量调用

create or replace function extract_test(xmlVar xmltype, xpathStr
varchar2) return xmltype is
resultXml xmltype;
begin
resultXml := xmlVar.extract(xpathStr);
return resultXml;
end;
/
SQL> select extract_test(xmltype('<aa><bb>afeafe</bb></aa>'),
'/aa/bb |/aa') from dual;
            EXTRACT_TEST
------------------------------------------
<aa><bb>afeafe</bb></aa><bb>afeafe</bb>
(1 row)

示例3：

Create TABLE EMPLOYEES
(
  id     NUMBER,
  data   XMLTYPE
);

Insert INTO EMPLOYEES
VALUES (1, xmltype ('<Employees>
<Employee emplid="1111" type="admin">
    <firstname>John</firstname>
    <lastname>Watson</lastname>
    <age>30</age>
    <email>johnwatson@sh.com</email>
</Employee>
<Employee emplid="2222" type="admin">
    <firstname>Sherlock</firstname>
    <lastname>Homes</lastname>
    <age>32</age>
    <email>sherlock@sh.com</email>
</Employee>
<Employee emplid="3333" type="user">
    <firstname>Jim</firstname>
    <lastname>Moriarty</lastname>
    <age>52</age>
    <email>jim@sh.com</email>
</Employee>
<Employee emplid="4444" type="user">
    <firstname>Mycroft</firstname>
    <lastname>Holmes</lastname>
    <age>41</age>
    <email>mycroft@sh.com</email>
</Employee>
</Employees>'));

SQL> SELECT EXTRACT(data,'/Employees/Employee') node FROM EMPLOYEES;
                     NODE
------------------------------------------------------
<Employee emplid="1111" type="admin">
     <firstname>John</firstname>
     <lastname>Watson</lastname>
     <age>30</age>
     <email>johnwatson@sh.com</email>
 </Employee><Employee emplid="2222" type="admin">
     <firstname>Sherlock</firstname>
     <lastname>Homes</lastname>
     <age>32</age>
     <email>sherlock@sh.com</email>
 </Employee><Employee emplid="3333" type="user">
     <firstname>Jim</firstname>
     <lastname>Moriarty</lastname>
     <age>52</age>
     <email>jim@sh.com</email>
 </Employee><Employee emplid="4444" type="user">
     <firstname>Mycroft</firstname>
     <lastname>Holmes</lastname>
     <age>41</age>
     <email>mycroft@sh.com</email>
 </Employee>
(1 row)