xmltype

来源：互联网发布：淘宝店铺货源编辑：程序博客网时间：2024/06/06 01:45

1、AppendChildXML(XMLTYPE,指定节点,子节点) 增加子节点
declare
testXML XMLTYPE;
outXML XMLTYPE;
begin
testXML:=XMLTYPE.CREATEXML(
'<patients>'||
'<patient><id>1</id><name>张三</name></patient>'||
'<patient><id>2</id><name>李四</name></patient>'||
'</patients>'
);
dbms_output.put_line(to_char(testxml.getClobval()));
select appendchildxml(testxml,'/patients/patient',xmltype.createxml('<sex>男</sex>')) into outxml from dual;
dbms_output.put_line(to_char(outxml.getClobval()));
end;

输出:
testXML:<patients><patient><id>1</id><name>张三</name></patient><patient><id>2</id><name>李四</name></patient></patients>
outXML:<patients><patient><id>1</id><name>张三</name><sex>男</sex></patient><patient><id>2</id><name>李四</name><sex>男</sex></patient></patients>

2、DeleteXML(XMLTYPE,指定节点) 删除指定节点
declare
testXML XMLTYPE;
outXML XMLTYPE;
begin
testXML:=XMLTYPE.CREATEXML(
'<patients>'||
'<patient><id>1</id><name>张三</name></patient>'||
'<patient><id>2</id><name>李四</name></patient>'||
'</patients>'
);
dbms_output.put_line('testXML:'||to_char(testxml.getClobval()));
select DeleteXML(testxml,'/patients/patient/name') into outxml from dual;
dbms_output.put_line('outXML:'||to_char(outxml.getClobval()));
end;
输出:
testXML:<patients><patient><id>1</id><name>张三</name></patient><patient><id>2</id><name>李四</name></patient></patients>
outXML:<patients><patient><id>1</id></patient><patient><id>2</id></patient></patients>

3、ExistsNode(XMLTYPE,指定节点) 判断指定节点是否存在,存在返回1 否则返回0
declare
testXML XMLTYPE;
outXML XMLTYPE;
n_Count Number;
begin
testXML:=XMLTYPE.CREATEXML(
'<patients>'||
'<patient><id>1</id><name>张三</name></patient>'||
'<patient><id>2</id><name>李四</name></patient>'||
'</patients>'
);
dbms_output.put_line('testXML:'||to_char(testxml.getClobval()));
select Existsnode(testxml,'/patients/patient/name') into n_Count from dual;
dbms_output.put_line(n_Count);
select DeleteXML(testxml,'/patients/patient/name') into outxml from dual;
select Existsnode(outxml,'/patients/patient/name') into n_Count from dual;
dbms_output.put_line(n_Count);
-- dbms_output.put_line('outXML:'||to_char(outxml.getClobval()));
end;
输出:
testXML:<patients><patient><id>1</id><name>张三</name></patient><patient><id>2</id><name>李四</name></patient></patients>
1
0

4、ExtractValue(xmltype,指定节点) 只返回一个节点的值
declare
testXML XMLTYPE;
outXML XMLTYPE;
strValue varchar2(100);
begin
testXML:=XMLTYPE.CREATEXML(
'<patients>'||
'<patient1><id>1</id><name sex="男">张三</name></patient1>'||
'<patient2><id>2</id><name>李四</name></patient2>'||
'</patients>'
);
dbms_output.put_line('testXML:'||to_char(testxml.getClobval()));
select ExtractValue(testxml,'/patients/patient2/name') into strValue from dual;
--dbms_output.put_line(to_char(outxml.getClobval()));
dbms_output.put_line(strvalue);
end;

end;
输出:
testXML:<patients><patient1><id>1</id><name>张三</name></patient1><patient2><id>2</id><name>李四</name></patient2></patients>
李四

5、Extract(XMLTYPE,指定节点) 返回指定节点所组成的XMLTYPE
declare
testXML XMLTYPE;
outXML XMLTYPE;
strValue varchar2(100);
begin
testXML:=XMLTYPE.CREATEXML(
'<patients>'||
'<patient><id>1</id><name sex="男">张三</name></patient>'||
'<patient><id>2</id><name>李四</name></patient>'||
'</patients>'
);
dbms_output.put_line('testXML:'||to_char(testxml.getClobval()));
select Extract(testxml,'/patients/patient') into outXML from dual;
dbms_output.put_line('outXML'||to_char(outxml.getClobval()));
end;
输出:
testXML:<patients><patient><id>1</id><name sex="男">张三</name></patient><patient><id>2</id><name>李四</name></patient></patients>

outXML<patient><id>1</id><name sex="男">张三</name></patient><patient><id>2</id><name>李四</name></patient>

6、XMLCOLATTVAL(value1,value2,.....) 返回一个以column值格式的XMLTYPE对象

SQL> select xmlcolattval(a.DEPTNO AS "编码",a.dname).getstringval() from scott.dept a;
XMLCOLATTVAL(A.DEPTNOAS"编码",
--------------------------------------------------------------------------------
<column name = "编码">10</column><column name = "DNAME">ACCOUNTING</column>
<column name = "编码">20</column><column name = "DNAME">RESEARCH</column>
<column name = "编码">30</column><column name = "DNAME">SALES</column>
<column name = "编码">40</column><column name = "DNAME">OPERATIONS</column>

7、XMLELEMENT("NODE_NAME",[xmlattributes(attribute_vale as attribute_alais)],NODE_VALUE) 返回一个XMLTYPE对象

SQL> SELECT XMLELEMENT("ROOT",XMLCOLATTVAL(a.DEPTNO AS "编码",a.DNAME as "名称")).GETSTRINGVAL() FROM SCOTT.DEPT a;
XMLELEMENT("ROOT",XMLCOLATTVAL
--------------------------------------------------------------------------------
<ROOT><column name = "编码">10</column><column name = "名称">ACCOUNTING</column></RO
<ROOT><column name = "编码">20</column><column name = "名称">RESEARCH</column></ROOT
<ROOT><column name = "编码">30</column><column name = "名称">SALES</column></ROOT>
<ROOT><column name = "编码">40</column><column name = "名称">OPERATIONS</column></RO

SQL> select xmlelement("root",xmlattributes(deptno as 部门号),dname).getstringval() from scott.dept;
XMLELEMENT("ROOT",XMLATTRIBUTE
--------------------------------------------------------------------------------
<root 部门号="10">ACCOUNTING</root>
<root 部门号="20">RESEARCH</root>
<root 部门号="30">SALES</root>
<root 部门号="40">OPERATIONS</root>

8、XMLConcat(xmltype1,xmltype2,......) :连接多个xmltype对象

declare
xmlname xmltype;
xmlsex xmltype;
xmlage xmltype;
xmlpatient xmltype;
begin
select xmlelement("name",'张三') into xmlname from dual ;
select xmlelement("sex",'男') into xmlsex from dual ;
select xmlelement("age",'13') into xmlage from dual ;
select xmlelement("patient",xmlconcat(xmlname,xmlsex,xmlage)) into xmlpatient from dual;
dbms_output.put_line(xmlpatient.getStringval());
end;

输出:

9、xmlforest(column1,column2,....) 将多个列组合成一个xmltype对象

SQL> select xmlelement("dept",xmlforest(a.deptno,a.dname,a.loc)).getstringval() from scott.dept a;
XMLELEMENT("DEPT",XMLFOREST(A.
--------------------------------------------------------------------------------
<dept><DEPTNO>10</DEPTNO><DNAME>ACCOUNTING</DNAME><LOC>NEW YORK</LOC></dept>
<dept><DEPTNO>20</DEPTNO><DNAME>RESEARCH</DNAME><LOC>DALLAS</LOC></dept>
<dept><DEPTNO>30</DEPTNO><DNAME>SALES</DNAME><LOC>CHICAGO</LOC></dept>
<dept><DEPTNO>40</DEPTNO><DNAME>OPERATIONS</DNAME><LOC>BOSTON</LOC></dept>

10、数据类型转换

getStringVal()

getClobVal()

getBlobVal()

getNumberVal()

0 0