CREATE TABLE SCOTT.EMP
( EMPNONUMBER(4,0),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4,0),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2,0),
CONSTRAINT PK_EMP PRIMARY KEY (EMPNO)
)
主要内容：将关系型结构化数据转化为XML文件
一、对象转化为XML文件
建立一个对象
CREATE OR REPLACE TYPE address_type AS OBJECT (
address_line1 VARCHAR2(100),
address_line2 VARCHAR2(100),
address_line3 VARCHAR2(100) );
declare
v_addr address_type;
v_xml xmltype;
begin
-- 建立一个对象实例
v_addr := address_type('n1', 'n2', 'n3');
--将对象实例转化为xml
v_xml := xmltype(v_addr);
-- 显示 xml 输出
dbms_output.put_line(v_xml.getstringval());
end;
输出结果：
<ADDRESS_TYPE>
<ADDRESS_LINE1>n1</ADDRESS_LINE1>
<ADDRESS_LINE2>n2</ADDRESS_LINE2>
<ADDRESS_LINE3>n3</ADDRESS_LINE3>
</ADDRESS_TYPE>
xmltype 将数据转化为XML的一个结点；
xmltype也是数据表的一种数据类型，如同varchar2数据类型一样。
getstringval()将一个xml结点或者xml数据类型类型转化为varchar2类型；
其它的转化函数还有
getCLOBVal()
getStringVal()
getNumberVal()
getBLOBVal(csid)
上述匿名过程告诉我们一个方法，可以将关系型数据转化为集合，然后就可以转化为XML文件。
实际上oracle提供了较多的函数，方便我们的转化工作。
xmlelement
将一个普通查询（非层次查询）的结果，转化为XML
select
xmlelement("name", ename).getstringval()
from scott.emp
1、 将一个字段转化为XML的一个节点
XMLELEMENT("NAME",ENAME)
-----------------------------
<name>SMITH</name>
<name>ALLEN</name>
<name>WARD</name>
<name>JONES</name>
<name>MARTIN</name>
……
xmlattributes
增加属性
SELECT
XMLElement("Deptno",
XMLATTRIBUTES(deptno AS dept_id),
XMLElement("Name", ename),
XMLElement("Date", hiredate)
). getstringval()
FROM scott.emp
<Deptno DEPT_ID="20"><Name>SMITH</Name><Date>1980-12-17</Date></Deptno>
<DeptnoDEPT_ID="30"><Name>ALLEN</Name><Date>1981-02-20</Date></Deptno>
<DeptnoDEPT_ID="30"><Name>WARD</Name><Date>1981-02-22</Date></Deptno>
<DeptnoDEPT_ID="20"><Name>JONES</Name><Date>1981-04-02</Date></Deptno>
……
但是上述XML节点不符合规范，不过没有关系，这个函数只是提供了一个转化方法
xmlforest
如果以已有的字段名称作为元素名称转化为XML文件
select xmlelement(
"emp",
xmlattributes(e.ename as "name"),
xmlforest(e.hiredate, e.deptno as "department_id")
).getstringval()
as "result"
from scott.emp e where e.deptno = 20;
<Emp name="SMITH">
<HIREDATE>1980-12-17</HIREDATE>
<department_id>20</department_id>
</Emp>
<Emp name="JONES">
<HIREDATE>1981-04-02</HIREDATE>
<department_id>20</department_id>
</Emp>
…..
利用对象将一对多的查询转化为xml
CREATEOR REPLACE TYPE emp_t AS OBJECT (
"@EMPNO" NUMBER(4), --@转化为属性
ENAMEVARCHAR2(10) --没有@转化为子结点
);
集合对象
CREATEOR REPLACE TYPE emplist_t AS TABLE OF emp_t;
CREATEOR REPLACE TYPE dept_t AS OBJECT (
"@DEPTNO"NUMBER(2),
DNAMEVARCHAR2(14),
EMP_LISTemplist_t
);
SELECT XMLElement("Department",
dept_t(deptno,
dname,
CAST(
MULTISET(
SELECT empno, ename
FROM scott.emp e
WHERE e.deptno = d.deptno) --多方
AS emplist_t))).getstringval()
AS deptxml
FROM scott.dept d
WHERE d.deptno = 20;
<Department>
<DEPT_T DEPTNO="20">
<DNAME>RESEARCH</DNAME>
<EMP_LIST>
<EMP_T EMPNO="7369">
<ENAME>SMITH</ENAME>
</EMP_T>
<EMP_T EMPNO="7566">
<ENAME>JONES</ENAME>
</EMP_T>
<EMP_T EMPNO="7788">
<ENAME>SCOTT</ENAME>
</EMP_T>
<EMP_T EMPNO="7876">
<ENAME>ADAMS</ENAME>
</EMP_T>
<EMP_T EMPNO="7902">
<ENAME>FORD</ENAME>
</EMP_T>
</EMP_LIST>
</DEPT_T>
</Department>
注意：不能对cursor直接转化
select
xmlelement("department",
xmlattributes(deptno as dept_id),
xmlelement(dname,dname),
xmlelement("emplist",
cursor(select empno,ename from scott.emp wheredeptno=d.deptno) )
)
from scott.dept d
where deptno=20
ORA-22902: 不允许 CURSOR 表达式
selectxmlforest(
dept_t(deptno,
dname,
cast (multiset (selectempno, ename
from scott.emp e
where e.deptno = d.deptno)
as emplist_t)) –将多方转化为集合对象
as"department").getstringval()
as deptxml
from scott.dept d
where deptno=20;
输出结果：
-----------------------------------------------------------
<departmentdeptno="20">
<dname>research</dname>
<emp_list>
<emp_tempno="7369">
<ename>smith</ename>
</emp_t>
<emp_tempno="7566">
<ename>jones</ename>
</emp_t>
<emp_tempno="7788">
<ename>scott</ename>
</emp_t>
<emp_tempno="7876">
<ename>adams</ename>
</emp_t>
<emp_tempno="7902">
<ename>ford</ename>
</emp_t>
</emp_list>
</department>
XMLConcat
拼接XML结点
SELECTXMLConcat(XMLSequenceType(
XMLType('<PartNo>1236</PartNo>'),
XMLType('<PartName>Widget</PartName>'),
XMLType('<PartPrice>29.99</PartPrice>'))).getCLOBVal()
AS"RESULT"
FROM DUAL;
<partno>1236</partno>
<partname>widget</partname>
<partprice>29.99</partprice>
xmlagg
分组累积运算
select
xmlagg(
xmlelement("deptno",
xmlattributes(deptno as dept_id),
xmlagg( xmlelement("name",ename))
)
). getstringval()
from scott.emp
where deptno in (20,10)
group by deptno
注意转成的XML文件没有ROOT节点。
<deptnoDEPT_ID="10">
<name>CLARK</name>
<name>KING</name>
<name>MILLER</name>
</deptno>
<deptnoDEPT_ID="20">
<name>SMITH</name>
<name>ADAMS</name>
<name>FORD</name>
<name>SCOTT</name>
<name>JONES</name>
</deptno>
select
xmlagg(
xmlelement("deptno",
xmlattributes(deptno as dept_id),
xmlagg(xmlelement("emps",
xmlattributes(empno as emp_id),
xmlelement("name",ename)
)))).getstringval()
from scott.emp
where deptno in (20,10)
group by deptno
<deptno DEPT_ID="10">
<emps EMP_ID="7782">
<name>CLARK</name>
</emps>
<emps EMP_ID="7839">
<name>KING</name>
</emps>
<emps EMP_ID="7934">
<name>MILLER</name>
</emps>
</deptno>
<deptno DEPT_ID="20">
<emps EMP_ID="7369">
<name>SMITH</name>
</emps>
<emps EMP_ID="7876">
<name>ADAMS</name>
</emps>
<emps EMP_ID="7902">
<name>FORD</name>
</emps>
<emps EMP_ID="7788">
<name>SCOTT</name>
</emps>
<emps EMP_ID="7566">
<name>JONES</name>
</emps>
</deptno>
试着比较以下语句
select
xmlagg(
xmlelement("emp",
xmlattributes(empno as emp_id),
xmlelement("name",ename),
xmlelement("dept_id",deptno)
)). getstringval()
from scott.emp
where deptno in (20,10)
<empEMP_ID="7369">
<name>SMITH</name>
<dept_id>20</dept_id>
</emp>
<empEMP_ID="7566">
<name>JONES</name>
<dept_id>20</dept_id>
</emp>
<empEMP_ID="7782">
<name>CLARK</name>
<dept_id>10</dept_id>
</emp>
<empEMP_ID="7788">
<name>SCOTT</name>
<dept_id>20</dept_id>
</emp>
<empEMP_ID="7839">
<name>KING</name>
<dept_id>10</dept_id>
</emp>
<empEMP_ID="7876">
<name>ADAMS</name>
<dept_id>20</dept_id>
</emp>
<empEMP_ID="7902">
<name>FORD</name>
<dept_id>20</dept_id>
</emp>
<empEMP_ID="7934">
<name>MILLER</name>
<dept_id>10</dept_id>
</emp>
XMLComment
注释
SELECTXMLComment('This is a comment') AS cmnt FROM DUAL;
<!--This is a comment-->
xmlcolattval
select
xmlelement(
"emp",
xmlattributes(e.ename as "fullname" ),
xmlcolattval(e.hiredate, e.sal as"salary")
).getstringval()
as "result"
from scott.emp e
where e.deptno = 30;
<emp fullname="ALLEN"><column name= "HIREDATE">1981-02-20</column><columnname = "salary">1600</column></emp>
<emp fullname="WARD"><column name ="HIREDATE">1981-02-22</column><columnname = "salary">1250</column></emp>
<emp fullname="MARTIN"><column name ="HIREDATE">1981-09-28</column><columnname = "salary">1250</column></emp>
<emp fullname="BLAKE"><column name ="HIREDATE">1981-05-01</column><columnname = "salary">2850</column></emp>
<emp fullname="TURNER"><column name ="HIREDATE">1981-09-08</column><columnname = "salary">1500</column></emp>
<emp fullname="JAMES"><column name ="HIREDATE">1981-12-03</column><columnname = "salary">950</column></emp>
select xmlelement("purchaseorder",
xmlelement("address",
xmlcdata('100 pennsylvaniaave.'),
xmlelement("city", 'washington, d.c.'))).getstringval() as result
from dual;
<purchaseorder>
<address>
<![CDATA[100 pennsylvaniaave.]]>
<city>washington,d.c.</city>
</address>
</purchaseorder>
select
value(tab).getStringVal() "SomeCol"
from
table (
XMLSequence(extract (
XMLType('<things>
<item>car</item>
<item>bottle</item>
<item>chair</item>
</things>'),
'/things/item')
)
) tab;
<item>car</item>
<item>bottle</item>
<item>chair</item>
SYS_XMLAgg
包装XML文件
selectsys_XMLAgg(value(em),XMLFormat('emps')).getstringval() as "xmltype"
from
table(
xmlsequence(cursor(select empno,ename fromscott.emp where deptno = 10))
) em;
<?xml version="1.0"?>
<emps>
<ROW>
<EMPNO>7782</EMPNO>
<ENAME>CLARK</ENAME>
</ROW>
<ROW>
<EMPNO>7839</EMPNO>
<ENAME>KING</ENAME>
</ROW>
<ROW>
<EMPNO>7934</EMPNO>
<ENAME>MILLER</ENAME>
</ROW>
</emps>
select
sys_xmlagg(
xmlelement("deptno",
xmlattributes(deptno as dept_id),
xmlagg(xmlelement("emps",
xmlattributes(empno as emp_id),
xmlelement("name", ename)
))),xmlformat('depts')). getstringval()
from scott.emp
where deptno in (20,10)
group by deptno
<?xmlversion="1.0"?>
<depts>
<deptno DEPT_ID="10">
<emps EMP_ID="7782">
<name>CLARK</name>
</emps>
<emps EMP_ID="7839">
<name>KING</name>
</emps>
<emps EMP_ID="7934">
<name>MILLER</name>
</emps>
</deptno>
<deptno DEPT_ID="20">
<emps EMP_ID="7369">
<name>SMITH</name>
</emps>
<emps EMP_ID="7876">
<name>ADAMS</name>
</emps>
<emps EMP_ID="7902">
<name>FORD</name>
</emps>
<emps EMP_ID="7788">
<name>SCOTT</name>
</emps>
<emps EMP_ID="7566">
<name>JONES</name>
</emps>
</deptno>
</depts>
以上介绍的一些XML函数，将一个查询结果转化为XML文件。Oracle还提供了一个pl/sql包，负责将查询结果转化为XML。
dbms_xmlgen的用法
declare
qryctx dbms_xmlgen.ctxhandle;
result clob;
begin
qryctx := dbms_xmlgen.newcontext('selectdeptno, dname from scott.dept d ');
-- set therow header to be employee
dbms_xmlgen.setrowtag(qryctx, 'dept');
-- get theresult
result :=dbms_xmlgen.getxml(qryctx);
dbms_output.put_line(result);
--closecontext
dbms_xmlgen.closecontext(qryctx);
end;
<?xml version="1.0"?>
<ROWSET>
<DEPT>
<DEPTNO>10</DEPTNO>
<DNAME>ACCOUNTING</DNAME>
</DEPT>
<DEPT>
<DEPTNO>20</DEPTNO>
<DNAME>RESEARCH</DNAME>
</DEPT>
<DEPT>
<DEPTNO>30</DEPTNO>
<DNAME>SALES</DNAME>
</DEPT>
<DEPT>
<DEPTNO>40</DEPTNO>
<DNAME>OPERATIONS</DNAME>
</DEPT>
<DEPT>
<DEPTNO>50</DEPTNO>
<DNAME>a</DNAME>
</DEPT>
</ROWSET>
declare
qryctx dbms_xmlgen.ctxhandle;
resultclob;
begin
-- get the query context;
qryctx := dbms_xmlgen.newcontext('select * from scott.dept');
-- set the maximum number of rows to be 2
dbms_xmlgen.setmaxrows(qryctx, 2); --转化2条数据组成一个文件
loop
-- get the result
result := dbms_xmlgen.getxml(qryctx);
-- if no rows were processed, then quit
exit whendbms_xmlgen.getnumrowsprocessed(qryctx) = 0;
-- do some processing with the lob data
-- here, we insert the results into a table.
-- you can print the lob out, output it to astream,
-- put it in a queue, or do any otherprocessing.
dbms_output.put_line(result);
endloop;
--close context
dbms_xmlgen.closecontext(qryctx);
end;
<?xmlversion="1.0"?>
<ROWSET>
<ROW>
<DEPTNO>10</DEPTNO>
<DNAME>ACCOUNTING</DNAME>
<LOC>NEW YORK</LOC>
</ROW>
<ROW>
<DEPTNO>20</DEPTNO>
<DNAME>RESEARCH</DNAME>
<LOC>DALLAS</LOC>
</ROW>
</ROWSET>
<?xml version="1.0"?>
<ROWSET>
<ROW>
<DEPTNO>30</DEPTNO>
<DNAME>SALES</DNAME>
<LOC>hhhhh</LOC>
</ROW>
<ROW>
<DEPTNO>40</DEPTNO>
<DNAME>OPERATIONS</DNAME>
<LOC>BOSTON</LOC>
</ROW>
</ROWSET>
<?xml version="1.0"?>
<ROWSET>
<ROW>
<DEPTNO>50</DEPTNO>
<DNAME>a</DNAME>
<LOC>b</LOC>
</ROW>
</ROWSET>
层次查询结果返回XML文件
create table t_clob(a clob)
declare
qryctx dbms_xmlgen.ctxhandle;
result xmltype;
begin
qryctx := dbms_xmlgen.newcontextfromhierarchy(
'select level,
xmlelement("employees",
xmlelement("enumber", empno),
xmlelement("name", ename),
xmlelement("salary", sal),
xmlelement("hiredate",hiredate)
)
from scott.emp
start with ename=''KING'' connect by prior empno=mgr
order siblings by hiredate'
);
result :=dbms_xmlgen.getxmltype(qryctx);
dbms_output.put_line('<resultnum rows>');
dbms_output.put_line(to_char(dbms_xmlgen.getnumrowsprocessed(qryctx)));
--得到处理的行数
--dbms_output.put_line('</resultnum rows>');
--dbms_output.put_line(result.getstringval());
insert into t_clob values (result.getstringval());
commit;
dbms_xmlgen.closecontext(qryctx);
end;
<?xml version="1.0"?>
<employees>
<enumber>7839</enumber>
<name>KING</name>
<salary>5000</salary>
<hiredate>1981-11-17</hiredate>
<employees>
<enumber>7566</enumber>
<name>JONES</name>
<salary>2975</salary>
<hiredate>1981-04-02</hiredate>
<employees>
<enumber>7902</enumber>
<name>FORD</name>
<salary>3000</salary>
<hiredate>1981-12-03</hiredate>
<employees>
<enumber>7369</enumber>
<name>SMITH</name>
<salary>800</salary>
<hiredate>1980-12-17</hiredate>
</employees>
</employees>
<employees>
<enumber>7788</enumber>
<name>SCOTT</name>
<salary>3000</salary>
<hiredate>1987-04-19</hiredate>
<employees>
<enumber>7876</enumber>
<name>ADAMS</name>
<salary>1100</salary>
<hiredate>1987-05-23</hiredate>
</employees>
</employees>
</employees>
<employees>
<enumber>7698</enumber>
<name>BLAKE</name>
<salary>2850</salary>
<hiredate>1981-05-01</hiredate>
<employees>
<enumber>7499</enumber>
<name>ALLEN</name>
<salary>1600</salary>
<hiredate>1981-02-20</hiredate>
</employees>
<employees>
<enumber>7521</enumber>
<name>WARD</name>
<salary>1250</salary>
<hiredate>1981-02-22</hiredate>
</employees>
<employees>
<enumber>7844</enumber>
<name>TURNER</name>
<salary>1500</salary>
<hiredate>1981-09-08</hiredate>
</employees>
<employees>
<enumber>7654</enumber>
<name>MARTIN</name>
<salary>1250</salary>
<hiredate>1981-09-28</hiredate>
</employees>
<employees>
<enumber>7900</enumber>
<name>JAMES</name>
<salary>950</salary>
<hiredate>1981-12-03</hiredate>
</employees>
</employees>
<employees>
<enumber>7782</enumber>
<name>CLARK</name>
<salary>2450</salary>
<hiredate>1981-06-09</hiredate>
<employees>
<enumber>7934</enumber>
<name>MILLER</name>
<salary>1300</salary>
<hiredate>1982-01-23</hiredate>
</employees>
</employees>
</employees>
By default, the ROWSET tag is NULL: there is no default ROWSET tag used to enclose the XML result. However, you canexplicitly set the ROWSET tag by using procedure
setRowSetTag(), as follows:
declare
qryctx dbms_xmlgen.ctxhandle;
result xmltype;
begin
qryctx := dbms_xmlgen.newcontextfromhierarchy('select level,
xmlelement("employees",
xmlelement("enumber", empno),
xmlelement("name", ename),
xmlelement("salary", sal),
xmlelement("hiredate", hiredate))
from scott.emp
start with ename=''KING'' connect by priorempno=mgr
order siblings by hiredate');
dbms_xmlgen.setrowsettag(qryctx,'mynum_hierarchy');
result := dbms_xmlgen.getxmltype(qryctx);
dbms_output.put_line('<resultnum rows>');
dbms_output.put_line(to_char(dbms_xmlgen.getnumrowsprocessed(qryctx)));
dbms_output.put_line('</result num rows>');
--dbms_output.put_line (result.getstringval());
insert into sqlx_display values (3, result);
commit;
dbms_xmlgen.closecontext(qryctx);
end;
绑定变量
declare
ctx number;
xmldoc clob;
begin
ctx := dbms_xmlgen.newcontext('select * from scott.emp where deptno =:no');
dbms_xmlgen.setbindvalue(ctx, 'no', '20');
xmldoc := dbms_xmlgen.getxml(ctx);
dbms_output.put_line(xmldoc);
dbms_xmlgen.closecontext(ctx);
exception
when others then
dbms_xmlgen.closecontext(ctx);
raise;
end;
绑定变量多次使用
declare
ctx number;
xmldoc clob;
begin
ctx := dbms_xmlgen.newcontext('select * from scott.empwhere deptno = :mdate');
dbms_xmlgen.setbindvalue(ctx, 'mdate', '10');
xmldoc := dbms_xmlgen.getxml(ctx);
dbms_output.put_line(xmldoc);
dbms_xmlgen.setbindvalue(ctx, 'mdate', '10');
xmldoc := dbms_xmlgen.getxml(ctx);
dbms_output.put_line(xmldoc);
dbms_xmlgen.closecontext(ctx);
exception
when others then
dbms_xmlgen.closecontext(ctx);
raise;
end;
<?xml version="1.0"?>
<ROWSET>
<ROW>
<EMPNO>7782</EMPNO>
<ENAME>CLARK</ENAME>
<JOB>MANAGER</JOB>
<MGR>7839</MGR>
<HIREDATE>09-6ÔÂ-81</HIREDATE>
<SAL>2450</SAL>
<DEPTNO>10</DEPTNO>
</ROW>
<ROW>
<EMPNO>7839</EMPNO>
<ENAME>KING</ENAME>
<JOB>PRESIDENT</JOB>
<HIREDATE>17-11ÔÂ-81</HIREDATE>
<SAL>5000</SAL>
<DEPTNO>10</DEPTNO>
</ROW>
<ROW>
<EMPNO>7934</EMPNO>
<ENAME>MILLER</ENAME>
<JOB>CLERK</JOB>
<MGR>7782</MGR>
<HIREDATE>23-1ÔÂ-82</HIREDATE>
<SAL>1300</SAL>
<DEPTNO>10</DEPTNO>
</ROW>
</ROWSET>
<?xmlversion="1.0"?>
<ROWSET>
<ROW>
<EMPNO>7782</EMPNO>
<ENAME>CLARK</ENAME>
<JOB>MANAGER</JOB>
<MGR>7839</MGR>
<HIREDATE>09-6ÔÂ -81</HIREDATE>
<SAL>2450</SAL>
<DEPTNO>10</DEPTNO>
</ROW>
<ROW>
<EMPNO>7839</EMPNO>
<ENAME>KING</ENAME>
<JOB>PRESIDENT</JOB>
<HIREDATE>17-11ÔÂ-81</HIREDATE>
<SAL>5000</SAL>
<DEPTNO>10</DEPTNO>
</ROW>
<ROW>
<EMPNO>7934</EMPNO>
<ENAME>MILLER</ENAME>
<JOB>CLERK</JOB>
<MGR>7782</MGR>
<HIREDATE>23-1ÔÂ-82</HIREDATE>
<SAL>1300</SAL>
<DEPTNO>10</DEPTNO>
</ROW>
</ROWSET>
多个绑定变量
declare
ctx number;
xmldoc clob;
begin
ctx := dbms_xmlgen.newcontext('select * from scott.emp
where deptno = :no
and sal > :mdate');
dbms_xmlgen.setbindvalue(ctx, 'no', '20');
dbms_xmlgen.setbindvalue(ctx, 'mdate', '1000');
xmldoc := dbms_xmlgen.getxml(ctx);
dbms_output.put_line(xmldoc);
dbms_xmlgen.closecontext(ctx);
exception
whenothers then
dbms_xmlgen.closecontext(ctx);
raise;
end;
参考游标
declare
ctx number;
maxrow number;
xmldoc clob;
refcur sys_refcursor;
begin
dbms_lob.createtemporary(xmldoc, true);
maxrow := 2;
openrefcur for 'select * from scott.empwhere rownum <= :1'
using maxrow;
ctx := dbms_xmlgen.newcontext(refcur);
-- xmldoc will have 2 rows
dbms_xmlgen.getxml(ctx, xmldoc, dbms_xmlgen.none);
dbms_output.put_line(xmldoc);
dbms_lob.freetemporary(xmldoc);
closerefcur;
dbms_xmlgen.closecontext(ctx);
end;
<?xml version="1.0"?>
<ROWSET>
<ROW>
<EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
<JOB>CLERK</JOB>
<MGR>7902</MGR>
<HIREDATE>17-12ÔÂ-80</HIREDATE>
<SAL>800</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
<ROW>
<EMPNO>7499</EMPNO>
<ENAME>ALLEN</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR>
<HIREDATE>20-2ÔÂ-81</HIREDATE>
<SAL>1600</SAL>
<COMM>300</COMM>
<DEPTNO>30</DEPTNO>
</ROW>
</ROWSET>
CREATE TABLEemp_tab(
emp_id NUMBERPRIMARY KEY,
name VARCHAR2(20),
dept_id NUMBER);
TINSERT INTO emp_tabVALUES(30, 'Scott', NULL);
INSERT INTO emp_tabVALUES(31, 'Mary', NULL);
INSERT INTO emp_tabVALUES(40, 'John', NULL);
COMMIT;
CREATE TABLEtemp_clob_tab(result CLOB);
declare
qryctx dbms_xmlgen.ctxhandle;
result clob;
begin
qryctx := dbms_xmlgen.newcontext('select * from emp_tabwhere name = :name');
-- set the row header to be employee
dbms_xmlgen.setrowtag(qryctx, 'employee');
-- drop nulls
dbms_xmlgen.setbindvalue(qryctx, 'name', 'scott');
dbms_xmlgen.setnullhandling(qryctx, dbms_xmlgen.drop_nulls);
result :=dbms_xmlgen.getxml(qryctx);
insert into temp_clob_tabvalues (result);
-- null attribute
dbms_xmlgen.setbindvalue(qryctx, 'name', 'mary');
dbms_xmlgen.setnullhandling(qryctx, dbms_xmlgen.null_attr);
result :=dbms_xmlgen.getxml(qryctx);
insert into temp_clob_tabvalues (result);
-- empty tag
dbms_xmlgen.setbindvalue(qryctx, 'name', 'john');
dbms_xmlgen.setnullhandling(qryctx, dbms_xmlgen.empty_tag);
result :=dbms_xmlgen.getxml(qryctx);
insert into temp_clob_tabvalues (result);
--close context
dbms_xmlgen.closecontext(qryctx);
end;
XML转化为关系型表数据
CREATE OR REPLACE TYPE address_type AS OBJECT (
address_line1 VARCHAR2(100),
address_line2 VARCHAR2(100),
address_line3 VARCHAR2(100) );
declare
v_xml XMLTYPE;
v_addr address_type;
BEGIN
-- create XML
v_xml := XMLTYPE('
<ADDRESS_TYPE>
<ADDRESS_LINE1>line1</ADDRESS_LINE1>
<ADDRESS_LINE2>line2</ADDRESS_LINE2>
<ADDRESS_LINE3>line3</ADDRESS_LINE3>
</ADDRESS_TYPE>
');
--convert xml to object
v_xml.toObject(v_addr);
-- display xml output
DBMS_OUTPUT.put_line('Address Line 1: ' || v_addr.address_line1 ||
', Address Line 2: ' || v_addr.address_line2 ||
', dress Line 3: ' || v_addr.address_line3);
END;
输出结果
Address Line 1: line1,
Address Line 2: line2,
Address Line 3: line3
CREATE TABLEdept_xml_tab OF XMLType;
INSERT INTOdept_xml_tab
VALUES(
XMLType('<Departmentdeptno="100">
<DeptName>Sports</DeptName>
<EmployeeList>
<Employeeempno="200"><Ename>John</Ename><Salary>33333</Salary>
</Employee>
<Employeeempno="300"><Ename>Jack</Ename><Salary>333444</Salary>
</Employee>
</EmployeeList>
</Department>'));
1 row created.
INSERT INTOdept_xml_tab
VALUES (
XMLType('<Departmentdeptno="200">
<DeptName>Sports</DeptName>
<EmployeeList>
<Employeeempno="400"><Ename>Marlin</Ename><Salary>20000</Salary>
</Employee>
</EmployeeList>
</Department>'));
1 row created.
COMMIT;
You can use SQL function XMLSequenceto un-nest the Employee list items as
top-level SQL rows:
SELECTextractValue(OBJECT_VALUE, '/Department/@deptno') AS deptno,
extractValue(value(em),'/Employee/@empno') AS empno,
extractValue(value(em),'/Employee/Ename') AS ename
FROM dept_xml_tab,
table(XMLSequence(extract(OBJECT_VALUE,
'/Department/EmployeeList/Employee')))em;
This returns the following:
DEPTNO EMPNO ENAME
---------------------------------
100 200 John
100 300 Jack
200 400 Marlin
CREATE TABLE tblControllerProjectsXML OF XMLType
insert into tblControllerProjectsXML
values
(xmltype(
'<Solution>
<Project ProjectID="5"Name="Unassigned TeamWork Project">
<DatabaseMonitoredDBID="2" />
<DatabaseMonitoredDBID="5" />
<DatabaseMonitoredDBID="13" />
<DatabaseMonitoredDBID="12" />
<DatabaseMonitoredDBID="14" />
<DatabaseMonitoredDBID="15" />
</Project>
<Project ProjectID="9"Name="Unassigned TeamWork Project">
<DatabaseMonitoredDBID="9" />
<DatabaseMonitoredDBID="10" />
<DatabaseMonitoredDBID="15" />
</Project>
</Solution>'
))
select * from
(
SELECT extractValue(value(d),'/Database/@MonitoredDBID') AS MonDBID,
extractValue(value(p), '/Project/@ProjectID') AS ProId
FROM tblControllerProjectsXML X,
TABLE(XMLSequence(extract(X.OBJECT_VALUE,'/Solution/Project/Database'))) d,
TABLE(XMLSequence(extract(X.OBJECT_VALUE,'/Solution/Project'))) p
) order by proid,mondbid desc
| MONDBID | PROID |
| 9 | 5 |
| 5 | 5 |
| 2 | 5 |
| 15 | 5 |
| 15 | 5 |
| 14 | 5 |
| 13 | 5 |
| 12 | 5 |
| 10 | 5 |
| 9 | 9 |
| 5 | 9 |
| 2 | 9 |
| 15 | 9 |
| 15 | 9 |
| 14 | 9 |
| 13 | 9 |
| 12 | 9 |
| 10 | 9 |
Oracle 关系型数据与XML数据之间的转化学习
CREATE TABLE SCOTT.EMP
( EMPNONUMBER(4,0),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4,0),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2,0),
CONSTRAINT PK_EMP PRIMARY KEY (EMPNO)
)
主要内容：将关系型结构化数据转化为XML文件
一、对象转化为XML文件
建立一个对象
CREATE OR REPLACE TYPE address_type AS OBJECT (
address_line1 VARCHAR2(100),
address_line2 VARCHAR2(100),
address_line3 VARCHAR2(100) );
declare
v_addr address_type;
v_xml xmltype;
begin
-- 建立一个对象实例
v_addr := address_type('n1', 'n2', 'n3');
--将对象实例转化为xml
v_xml := xmltype(v_addr);
-- 显示 xml 输出
dbms_output.put_line(v_xml.getstringval());
end;
输出结果：
<ADDRESS_TYPE>
<ADDRESS_LINE1>n1</ADDRESS_LINE1>
<ADDRESS_LINE2>n2</ADDRESS_LINE2>
<ADDRESS_LINE3>n3</ADDRESS_LINE3>
</ADDRESS_TYPE>
xmltype 将数据转化为XML的一个结点；
xmltype也是数据表的一种数据类型，如同varchar2数据类型一样。
getstringval()将一个xml结点或者xml数据类型类型转化为varchar2类型；
其它的转化函数还有
getCLOBVal()
getStringVal()
getNumberVal()
getBLOBVal(csid)
上述匿名过程告诉我们一个方法，可以将关系型数据转化为集合，然后就可以转化为XML文件。
实际上oracle提供了较多的函数，方便我们的转化工作。
xmlelement
将一个普通查询（非层次查询）的结果，转化为XML
select
xmlelement("name", ename).getstringval()
from scott.emp
1、 将一个字段转化为XML的一个节点
XMLELEMENT("NAME",ENAME)
-----------------------------
<name>SMITH</name>
<name>ALLEN</name>
<name>WARD</name>
<name>JONES</name>
<name>MARTIN</name>
……
xmlattributes
增加属性
SELECT
XMLElement("Deptno",
XMLATTRIBUTES(deptno AS dept_id),
XMLElement("Name", ename),
XMLElement("Date", hiredate)
). getstringval()
FROM scott.emp
<Deptno DEPT_ID="20"><Name>SMITH</Name><Date>1980-12-17</Date></Deptno>
<DeptnoDEPT_ID="30"><Name>ALLEN</Name><Date>1981-02-20</Date></Deptno>
<DeptnoDEPT_ID="30"><Name>WARD</Name><Date>1981-02-22</Date></Deptno>
<DeptnoDEPT_ID="20"><Name>JONES</Name><Date>1981-04-02</Date></Deptno>
……
但是上述XML节点不符合规范，不过没有关系，这个函数只是提供了一个转化方法
xmlforest
如果以已有的字段名称作为元素名称转化为XML文件
select xmlelement(
"emp",
xmlattributes(e.ename as "name"),
xmlforest(e.hiredate, e.deptno as "department_id")
).getstringval()
as "result"
from scott.emp e where e.deptno = 20;
<Emp name="SMITH">
<HIREDATE>1980-12-17</HIREDATE>
<department_id>20</department_id>
</Emp>
<Emp name="JONES">
<HIREDATE>1981-04-02</HIREDATE>
<department_id>20</department_id>
</Emp>
…..
利用对象将一对多的查询转化为xml
CREATEOR REPLACE TYPE emp_t AS OBJECT (
"@EMPNO" NUMBER(4), --@转化为属性
ENAMEVARCHAR2(10) --没有@转化为子结点
);
集合对象
CREATEOR REPLACE TYPE emplist_t AS TABLE OF emp_t;
CREATEOR REPLACE TYPE dept_t AS OBJECT (
"@DEPTNO"NUMBER(2),
DNAMEVARCHAR2(14),
EMP_LISTemplist_t
);
SELECT XMLElement("Department",
dept_t(deptno,
dname,
CAST(
MULTISET(
SELECT empno, ename
FROM scott.emp e
WHERE e.deptno = d.deptno) --多方
AS emplist_t))).getstringval()
AS deptxml
FROM scott.dept d
WHERE d.deptno = 20;
<Department>
<DEPT_T DEPTNO="20">
<DNAME>RESEARCH</DNAME>
<EMP_LIST>
<EMP_T EMPNO="7369">
<ENAME>SMITH</ENAME>
</EMP_T>
<EMP_T EMPNO="7566">
<ENAME>JONES</ENAME>
</EMP_T>
<EMP_T EMPNO="7788">
<ENAME>SCOTT</ENAME>
</EMP_T>
<EMP_T EMPNO="7876">
<ENAME>ADAMS</ENAME>
</EMP_T>
<EMP_T EMPNO="7902">
<ENAME>FORD</ENAME>
</EMP_T>
</EMP_LIST>
</DEPT_T>
</Department>
注意：不能对cursor直接转化
select
xmlelement("department",
xmlattributes(deptno as dept_id),
xmlelement(dname,dname),
xmlelement("emplist",
cursor(select empno,ename from scott.emp wheredeptno=d.deptno) )
)
from scott.dept d
where deptno=20
ORA-22902: 不允许 CURSOR 表达式
selectxmlforest(
dept_t(deptno,
dname,
cast (multiset (selectempno, ename
from scott.emp e
where e.deptno = d.deptno)
as emplist_t)) –将多方转化为集合对象
as"department").getstringval()
as deptxml
from scott.dept d
where deptno=20;
输出结果：
-----------------------------------------------------------
<departmentdeptno="20">
<dname>research</dname>
<emp_list>
<emp_tempno="7369">
<ename>smith</ename>
</emp_t>
<emp_tempno="7566">
<ename>jones</ename>
</emp_t>
<emp_tempno="7788">
<ename>scott</ename>
</emp_t>
<emp_tempno="7876">
<ename>adams</ename>
</emp_t>
<emp_tempno="7902">
<ename>ford</ename>
</emp_t>
</emp_list>
</department>
XMLConcat
拼接XML结点
SELECTXMLConcat(XMLSequenceType(
XMLType('<PartNo>1236</PartNo>'),
XMLType('<PartName>Widget</PartName>'),
XMLType('<PartPrice>29.99</PartPrice>'))).getCLOBVal()
AS"RESULT"
FROM DUAL;
<partno>1236</partno>
<partname>widget</partname>
<partprice>29.99</partprice>
xmlagg
分组累积运算
select
xmlagg(
xmlelement("deptno",
xmlattributes(deptno as dept_id),
xmlagg( xmlelement("name",ename))
)
). getstringval()
from scott.emp
where deptno in (20,10)
group by deptno
注意转成的XML文件没有ROOT节点。
<deptnoDEPT_ID="10">
<name>CLARK</name>
<name>KING</name>
<name>MILLER</name>
</deptno>
<deptnoDEPT_ID="20">
<name>SMITH</name>
<name>ADAMS</name>
<name>FORD</name>
<name>SCOTT</name>
<name>JONES</name>
</deptno>
select
xmlagg(
xmlelement("deptno",
xmlattributes(deptno as dept_id),
xmlagg(xmlelement("emps",
xmlattributes(empno as emp_id),
xmlelement("name",ename)
)))).getstringval()
from scott.emp
where deptno in (20,10)
group by deptno
<deptno DEPT_ID="10">
<emps EMP_ID="7782">
<name>CLARK</name>
</emps>
<emps EMP_ID="7839">
<name>KING</name>
</emps>
<emps EMP_ID="7934">
<name>MILLER</name>
</emps>
</deptno>
<deptno DEPT_ID="20">
<emps EMP_ID="7369">
<name>SMITH</name>
</emps>
<emps EMP_ID="7876">
<name>ADAMS</name>
</emps>
<emps EMP_ID="7902">
<name>FORD</name>
</emps>
<emps EMP_ID="7788">
<name>SCOTT</name>
</emps>
<emps EMP_ID="7566">
<name>JONES</name>
</emps>
</deptno>
试着比较以下语句
select
xmlagg(
xmlelement("emp",
xmlattributes(empno as emp_id),
xmlelement("name",ename),
xmlelement("dept_id",deptno)
)). getstringval()
from scott.emp
where deptno in (20,10)
<empEMP_ID="7369">
<name>SMITH</name>
<dept_id>20</dept_id>
</emp>
<empEMP_ID="7566">
<name>JONES</name>
<dept_id>20</dept_id>
</emp>
<empEMP_ID="7782">
<name>CLARK</name>
<dept_id>10</dept_id>
</emp>
<empEMP_ID="7788">
<name>SCOTT</name>
<dept_id>20</dept_id>
</emp>
<empEMP_ID="7839">
<name>KING</name>
<dept_id>10</dept_id>
</emp>
<empEMP_ID="7876">
<name>ADAMS</name>
<dept_id>20</dept_id>
</emp>
<empEMP_ID="7902">
<name>FORD</name>
<dept_id>20</dept_id>
</emp>
<empEMP_ID="7934">
<name>MILLER</name>
<dept_id>10</dept_id>
</emp>
XMLComment
注释
SELECTXMLComment('This is a comment') AS cmnt FROM DUAL;
<!--This is a comment-->
xmlcolattval
select
xmlelement(
"emp",
xmlattributes(e.ename as "fullname" ),
xmlcolattval(e.hiredate, e.sal as"salary")
).getstringval()
as "result"
from scott.emp e
where e.deptno = 30;
<emp fullname="ALLEN"><column name= "HIREDATE">1981-02-20</column><columnname = "salary">1600</column></emp>
<emp fullname="WARD"><column name ="HIREDATE">1981-02-22</column><columnname = "salary">1250</column></emp>
<emp fullname="MARTIN"><column name ="HIREDATE">1981-09-28</column><columnname = "salary">1250</column></emp>
<emp fullname="BLAKE"><column name ="HIREDATE">1981-05-01</column><columnname = "salary">2850</column></emp>
<emp fullname="TURNER"><column name ="HIREDATE">1981-09-08</column><columnname = "salary">1500</column></emp>
<emp fullname="JAMES"><column name ="HIREDATE">1981-12-03</column><columnname = "salary">950</column></emp>
select xmlelement("purchaseorder",
xmlelement("address",
xmlcdata('100 pennsylvaniaave.'),
xmlelement("city", 'washington, d.c.'))).getstringval() as result
from dual;
<purchaseorder>
<address>
<![CDATA[100 pennsylvaniaave.]]>
<city>washington,d.c.</city>
</address>
</purchaseorder>
select
value(tab).getStringVal() "SomeCol"
from
table (
XMLSequence(extract (
XMLType('<things>
<item>car</item>
<item>bottle</item>
<item>chair</item>
</things>'),
'/things/item')
)
) tab;
<item>car</item>
<item>bottle</item>
<item>chair</item>
SYS_XMLAgg
包装XML文件
selectsys_XMLAgg(value(em),XMLFormat('emps')).getstringval() as "xmltype"
from
table(
xmlsequence(cursor(select empno,ename fromscott.emp where deptno = 10))
) em;
<?xml version="1.0"?>
<emps>
<ROW>
<EMPNO>7782</EMPNO>
<ENAME>CLARK</ENAME>
</ROW>
<ROW>
<EMPNO>7839</EMPNO>
<ENAME>KING</ENAME>
</ROW>
<ROW>
<EMPNO>7934</EMPNO>
<ENAME>MILLER</ENAME>
</ROW>
</emps>
select
sys_xmlagg(
xmlelement("deptno",
xmlattributes(deptno as dept_id),
xmlagg(xmlelement("emps",
xmlattributes(empno as emp_id),
xmlelement("name", ename)
))),xmlformat('depts')). getstringval()
from scott.emp
where deptno in (20,10)
group by deptno
<?xmlversion="1.0"?>
<depts>
<deptno DEPT_ID="10">
<emps EMP_ID="7782">
<name>CLARK</name>
</emps>
<emps EMP_ID="7839">
<name>KING</name>
</emps>
<emps EMP_ID="7934">
<name>MILLER</name>
</emps>
</deptno>
<deptno DEPT_ID="20">
<emps EMP_ID="7369">
<name>SMITH</name>
</emps>
<emps EMP_ID="7876">
<name>ADAMS</name>
</emps>
<emps EMP_ID="7902">
<name>FORD</name>
</emps>
<emps EMP_ID="7788">
<name>SCOTT</name>
</emps>
<emps EMP_ID="7566">
<name>JONES</name>
</emps>
</deptno>
</depts>
以上介绍的一些XML函数，将一个查询结果转化为XML文件。Oracle还提供了一个pl/sql包，负责将查询结果转化为XML。
dbms_xmlgen的用法
declare
qryctx dbms_xmlgen.ctxhandle;
result clob;
begin
qryctx := dbms_xmlgen.newcontext('selectdeptno, dname from scott.dept d ');
-- set therow header to be employee
dbms_xmlgen.setrowtag(qryctx, 'dept');
-- get theresult
result :=dbms_xmlgen.getxml(qryctx);
dbms_output.put_line(result);
--closecontext
dbms_xmlgen.closecontext(qryctx);
end;
<?xml version="1.0"?>
<ROWSET>
<DEPT>
<DEPTNO>10</DEPTNO>
<DNAME>ACCOUNTING</DNAME>
</DEPT>
<DEPT>
<DEPTNO>20</DEPTNO>
<DNAME>RESEARCH</DNAME>
</DEPT>
<DEPT>
<DEPTNO>30</DEPTNO>
<DNAME>SALES</DNAME>
</DEPT>
<DEPT>
<DEPTNO>40</DEPTNO>
<DNAME>OPERATIONS</DNAME>
</DEPT>
<DEPT>
<DEPTNO>50</DEPTNO>
<DNAME>a</DNAME>
</DEPT>
</ROWSET>
declare
qryctx dbms_xmlgen.ctxhandle;
resultclob;
begin
-- get the query context;
qryctx := dbms_xmlgen.newcontext('select * from scott.dept');
-- set the maximum number of rows to be 2
dbms_xmlgen.setmaxrows(qryctx, 2); --转化2条数据组成一个文件
loop
-- get the result
result := dbms_xmlgen.getxml(qryctx);
-- if no rows were processed, then quit
exit whendbms_xmlgen.getnumrowsprocessed(qryctx) = 0;
-- do some processing with the lob data
-- here, we insert the results into a table.
-- you can print the lob out, output it to astream,
-- put it in a queue, or do any otherprocessing.
dbms_output.put_line(result);
endloop;
--close context
dbms_xmlgen.closecontext(qryctx);
end;
<?xmlversion="1.0"?>
<ROWSET>
<ROW>
<DEPTNO>10</DEPTNO>
<DNAME>ACCOUNTING</DNAME>
<LOC>NEW YORK</LOC>
</ROW>
<ROW>
<DEPTNO>20</DEPTNO>
<DNAME>RESEARCH</DNAME>
<LOC>DALLAS</LOC>
</ROW>
</ROWSET>
<?xml version="1.0"?>
<ROWSET>
<ROW>
<DEPTNO>30</DEPTNO>
<DNAME>SALES</DNAME>
<LOC>hhhhh</LOC>
</ROW>
<ROW>
<DEPTNO>40</DEPTNO>
<DNAME>OPERATIONS</DNAME>
<LOC>BOSTON</LOC>
</ROW>
</ROWSET>
<?xml version="1.0"?>
<ROWSET>
<ROW>
<DEPTNO>50</DEPTNO>
<DNAME>a</DNAME>
<LOC>b</LOC>
</ROW>
</ROWSET>
层次查询结果返回XML文件
create table t_clob(a clob)
declare
qryctx dbms_xmlgen.ctxhandle;
result xmltype;
begin
qryctx := dbms_xmlgen.newcontextfromhierarchy(
'select level,
xmlelement("employees",
xmlelement("enumber", empno),
xmlelement("name", ename),
xmlelement("salary", sal),
xmlelement("hiredate",hiredate)
)
from scott.emp
start with ename=''KING'' connect by prior empno=mgr
order siblings by hiredate'
);
result :=dbms_xmlgen.getxmltype(qryctx);
dbms_output.put_line('<resultnum rows>');
dbms_output.put_line(to_char(dbms_xmlgen.getnumrowsprocessed(qryctx)));
--得到处理的行数
--dbms_output.put_line('</resultnum rows>');
--dbms_output.put_line(result.getstringval());
insert into t_clob values (result.getstringval());
commit;
dbms_xmlgen.closecontext(qryctx);
end;
<?xml version="1.0"?>
<employees>
<enumber>7839</enumber>
<name>KING</name>
<salary>5000</salary>
<hiredate>1981-11-17</hiredate>
<employees>
<enumber>7566</enumber>
<name>JONES</name>
<salary>2975</salary>
<hiredate>1981-04-02</hiredate>
<employees>
<enumber>7902</enumber>
<name>FORD</name>
<salary>3000</salary>
<hiredate>1981-12-03</hiredate>
<employees>
<enumber>7369</enumber>
<name>SMITH</name>
<salary>800</salary>
<hiredate>1980-12-17</hiredate>
</employees>
</employees>
<employees>
<enumber>7788</enumber>
<name>SCOTT</name>
<salary>3000</salary>
<hiredate>1987-04-19</hiredate>
<employees>
<enumber>7876</enumber>
<name>ADAMS</name>
<salary>1100</salary>
<hiredate>1987-05-23</hiredate>
</employees>
</employees>
</employees>
<employees>
<enumber>7698</enumber>
<name>BLAKE</name>
<salary>2850</salary>
<hiredate>1981-05-01</hiredate>
<employees>
<enumber>7499</enumber>
<name>ALLEN</name>
<salary>1600</salary>
<hiredate>1981-02-20</hiredate>
</employees>
<employees>
<enumber>7521</enumber>
<name>WARD</name>
<salary>1250</salary>
<hiredate>1981-02-22</hiredate>
</employees>
<employees>
<enumber>7844</enumber>
<name>TURNER</name>
<salary>1500</salary>
<hiredate>1981-09-08</hiredate>
</employees>
<employees>
<enumber>7654</enumber>
<name>MARTIN</name>
<salary>1250</salary>
<hiredate>1981-09-28</hiredate>
</employees>
<employees>
<enumber>7900</enumber>
<name>JAMES</name>
<salary>950</salary>
<hiredate>1981-12-03</hiredate>
</employees>
</employees>
<employees>
<enumber>7782</enumber>
<name>CLARK</name>
<salary>2450</salary>
<hiredate>1981-06-09</hiredate>
<employees>
<enumber>7934</enumber>
<name>MILLER</name>
<salary>1300</salary>
<hiredate>1982-01-23</hiredate>
</employees>
</employees>
</employees>
By default, the ROWSET tag is NULL: there is no default ROWSET tag used to enclose the XML result. However, you canexplicitly set the ROWSET tag by using procedure
setRowSetTag(), as follows:
declare
qryctx dbms_xmlgen.ctxhandle;
result xmltype;
begin
qryctx := dbms_xmlgen.newcontextfromhierarchy('select level,
xmlelement("employees",
xmlelement("enumber", empno),
xmlelement("name", ename),
xmlelement("salary", sal),
xmlelement("hiredate", hiredate))
from scott.emp
start with ename=''KING'' connect by priorempno=mgr
order siblings by hiredate');
dbms_xmlgen.setrowsettag(qryctx,'mynum_hierarchy');
result := dbms_xmlgen.getxmltype(qryctx);
dbms_output.put_line('<resultnum rows>');
dbms_output.put_line(to_char(dbms_xmlgen.getnumrowsprocessed(qryctx)));
dbms_output.put_line('</result num rows>');
--dbms_output.put_line (result.getstringval());
insert into sqlx_display values (3, result);
commit;
dbms_xmlgen.closecontext(qryctx);
end;
绑定变量
declare
ctx number;
xmldoc clob;
begin
ctx := dbms_xmlgen.newcontext('select * from scott.emp where deptno =:no');
dbms_xmlgen.setbindvalue(ctx, 'no', '20');
xmldoc := dbms_xmlgen.getxml(ctx);
dbms_output.put_line(xmldoc);
dbms_xmlgen.closecontext(ctx);
exception
when others then
dbms_xmlgen.closecontext(ctx);
raise;
end;
绑定变量多次使用
declare
ctx number;
xmldoc clob;
begin
ctx := dbms_xmlgen.newcontext('select * from scott.empwhere deptno = :mdate');
dbms_xmlgen.setbindvalue(ctx, 'mdate', '10');
xmldoc := dbms_xmlgen.getxml(ctx);
dbms_output.put_line(xmldoc);
dbms_xmlgen.setbindvalue(ctx, 'mdate', '10');
xmldoc := dbms_xmlgen.getxml(ctx);
dbms_output.put_line(xmldoc);
dbms_xmlgen.closecontext(ctx);
exception
when others then
dbms_xmlgen.closecontext(ctx);
raise;
end;
<?xml version="1.0"?>
<ROWSET>
<ROW>
<EMPNO>7782</EMPNO>
<ENAME>CLARK</ENAME>
<JOB>MANAGER</JOB>
<MGR>7839</MGR>
<HIREDATE>09-6ÔÂ-81</HIREDATE>
<SAL>2450</SAL>
<DEPTNO>10</DEPTNO>
</ROW>
<ROW>
<EMPNO>7839</EMPNO>
<ENAME>KING</ENAME>
<JOB>PRESIDENT</JOB>
<HIREDATE>17-11ÔÂ-81</HIREDATE>
<SAL>5000</SAL>
<DEPTNO>10</DEPTNO>
</ROW>
<ROW>
<EMPNO>7934</EMPNO>
<ENAME>MILLER</ENAME>
<JOB>CLERK</JOB>
<MGR>7782</MGR>
<HIREDATE>23-1ÔÂ-82</HIREDATE>
<SAL>1300</SAL>
<DEPTNO>10</DEPTNO>
</ROW>
</ROWSET>
<?xmlversion="1.0"?>
<ROWSET>
<ROW>
<EMPNO>7782</EMPNO>
<ENAME>CLARK</ENAME>
<JOB>MANAGER</JOB>
<MGR>7839</MGR>
<HIREDATE>09-6ÔÂ -81</HIREDATE>
<SAL>2450</SAL>
<DEPTNO>10</DEPTNO>
</ROW>
<ROW>
<EMPNO>7839</EMPNO>
<ENAME>KING</ENAME>
<JOB>PRESIDENT</JOB>
<HIREDATE>17-11ÔÂ-81</HIREDATE>
<SAL>5000</SAL>
<DEPTNO>10</DEPTNO>
</ROW>
<ROW>
<EMPNO>7934</EMPNO>
<ENAME>MILLER</ENAME>
<JOB>CLERK</JOB>
<MGR>7782</MGR>
<HIREDATE>23-1ÔÂ-82</HIREDATE>
<SAL>1300</SAL>
<DEPTNO>10</DEPTNO>
</ROW>
</ROWSET>
多个绑定变量
declare
ctx number;
xmldoc clob;
begin
ctx := dbms_xmlgen.newcontext('select * from scott.emp
where deptno = :no
and sal > :mdate');
dbms_xmlgen.setbindvalue(ctx, 'no', '20');
dbms_xmlgen.setbindvalue(ctx, 'mdate', '1000');
xmldoc := dbms_xmlgen.getxml(ctx);
dbms_output.put_line(xmldoc);
dbms_xmlgen.closecontext(ctx);
exception
whenothers then
dbms_xmlgen.closecontext(ctx);
raise;
end;
参考游标
declare
ctx number;
maxrow number;
xmldoc clob;
refcur sys_refcursor;
begin
dbms_lob.createtemporary(xmldoc, true);
maxrow := 2;
openrefcur for 'select * from scott.empwhere rownum <= :1'
using maxrow;
ctx := dbms_xmlgen.newcontext(refcur);
-- xmldoc will have 2 rows
dbms_xmlgen.getxml(ctx, xmldoc, dbms_xmlgen.none);
dbms_output.put_line(xmldoc);
dbms_lob.freetemporary(xmldoc);
closerefcur;
dbms_xmlgen.closecontext(ctx);
end;
<?xml version="1.0"?>
<ROWSET>
<ROW>
<EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
<JOB>CLERK</JOB>
<MGR>7902</MGR>
<HIREDATE>17-12ÔÂ-80</HIREDATE>
<SAL>800</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
<ROW>
<EMPNO>7499</EMPNO>
<ENAME>ALLEN</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR>
<HIREDATE>20-2ÔÂ-81</HIREDATE>
<SAL>1600</SAL>
<COMM>300</COMM>
<DEPTNO>30</DEPTNO>
</ROW>
</ROWSET>
CREATE TABLEemp_tab(
emp_id NUMBERPRIMARY KEY,
name VARCHAR2(20),
dept_id NUMBER);
TINSERT INTO emp_tabVALUES(30, 'Scott', NULL);
INSERT INTO emp_tabVALUES(31, 'Mary', NULL);
INSERT INTO emp_tabVALUES(40, 'John', NULL);
COMMIT;
CREATE TABLEtemp_clob_tab(result CLOB);
declare
qryctx dbms_xmlgen.ctxhandle;
result clob;
begin
qryctx := dbms_xmlgen.newcontext('select * from emp_tabwhere name = :name');
-- set the row header to be employee
dbms_xmlgen.setrowtag(qryctx, 'employee');
-- drop nulls
dbms_xmlgen.setbindvalue(qryctx, 'name', 'scott');
dbms_xmlgen.setnullhandling(qryctx, dbms_xmlgen.drop_nulls);
result :=dbms_xmlgen.getxml(qryctx);
insert into temp_clob_tabvalues (result);
-- null attribute
dbms_xmlgen.setbindvalue(qryctx, 'name', 'mary');
dbms_xmlgen.setnullhandling(qryctx, dbms_xmlgen.null_attr);
result :=dbms_xmlgen.getxml(qryctx);
insert into temp_clob_tabvalues (result);
-- empty tag
dbms_xmlgen.setbindvalue(qryctx, 'name', 'john');
dbms_xmlgen.setnullhandling(qryctx, dbms_xmlgen.empty_tag);
result :=dbms_xmlgen.getxml(qryctx);
insert into temp_clob_tabvalues (result);
--close context
dbms_xmlgen.closecontext(qryctx);
end;
XML转化为关系型表数据
CREATE OR REPLACE TYPE address_type AS OBJECT (
address_line1 VARCHAR2(100),
address_line2 VARCHAR2(100),
address_line3 VARCHAR2(100) );
declare
v_xml XMLTYPE;
v_addr address_type;
BEGIN
-- create XML
v_xml := XMLTYPE('
<ADDRESS_TYPE>
<ADDRESS_LINE1>line1</ADDRESS_LINE1>
<ADDRESS_LINE2>line2</ADDRESS_LINE2>
<ADDRESS_LINE3>line3</ADDRESS_LINE3>
</ADDRESS_TYPE>
');
--convert xml to object
v_xml.toObject(v_addr);
-- display xml output
DBMS_OUTPUT.put_line('Address Line 1: ' || v_addr.address_line1 ||
', Address Line 2: ' || v_addr.address_line2 ||
', dress Line 3: ' || v_addr.address_line3);
END;
输出结果
Address Line 1: line1,
Address Line 2: line2,
Address Line 3: line3
CREATE TABLEdept_xml_tab OF XMLType;
INSERT INTOdept_xml_tab
VALUES(
XMLType('<Departmentdeptno="100">
<DeptName>Sports</DeptName>
<EmployeeList>
<Employeeempno="200"><Ename>John</Ename><Salary>33333</Salary>
</Employee>
<Employeeempno="300"><Ename>Jack</Ename><Salary>333444</Salary>
</Employee>
</EmployeeList>
</Department>'));
1 row created.
INSERT INTOdept_xml_tab
VALUES (
XMLType('<Departmentdeptno="200">
<DeptName>Sports</DeptName>
<EmployeeList>
<Employeeempno="400"><Ename>Marlin</Ename><Salary>20000</Salary>
</Employee>
</EmployeeList>
</Department>'));
1 row created.
COMMIT;
You can use SQL function XMLSequenceto un-nest the Employee list items as
top-level SQL rows:
SELECTextractValue(OBJECT_VALUE, '/Department/@deptno') AS deptno,
extractValue(value(em),'/Employee/@empno') AS empno,
extractValue(value(em),'/Employee/Ename') AS ename
FROM dept_xml_tab,
table(XMLSequence(extract(OBJECT_VALUE,
'/Department/EmployeeList/Employee')))em;
This returns the following:
DEPTNO EMPNO ENAME
---------------------------------
100 200 John
100 300 Jack
200 400 Marlin
CREATE TABLE tblControllerProjectsXML OF XMLType
insert into tblControllerProjectsXML
values
(xmltype(
'<Solution>
<Project ProjectID="5"Name="Unassigned TeamWork Project">
<DatabaseMonitoredDBID="2" />
<DatabaseMonitoredDBID="5" />
<DatabaseMonitoredDBID="13" />
<DatabaseMonitoredDBID="12" />
<DatabaseMonitoredDBID="14" />
<DatabaseMonitoredDBID="15" />
</Project>
<Project ProjectID="9"Name="Unassigned TeamWork Project">
<DatabaseMonitoredDBID="9" />
<DatabaseMonitoredDBID="10" />
<DatabaseMonitoredDBID="15" />
</Project>
</Solution>'
))
select * from
(
SELECT extractValue(value(d),'/Database/@MonitoredDBID') AS MonDBID,
extractValue(value(p), '/Project/@ProjectID') AS ProId
FROM tblControllerProjectsXML X,
TABLE(XMLSequence(extract(X.OBJECT_VALUE,'/Solution/Project/Database'))) d,
TABLE(XMLSequence(extract(X.OBJECT_VALUE,'/Solution/Project'))) p
) order by proid,mondbid desc
| MONDBID | PROID |
| 9 | 5 |
| 5 | 5 |
| 2 | 5 |
| 15 | 5 |
| 15 | 5 |
| 14 | 5 |
| 13 | 5 |
| 12 | 5 |
| 10 | 5 |
| 9 | 9 |
| 5 | 9 |
| 2 | 9 |
| 15 | 9 |
| 15 | 9 |
| 14 | 9 |
| 13 | 9 |
| 12 | 9 |
| 10 | 9 |
扫一扫
309
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
