|
|
IOTN :: Field Book :: ORACLE 
|
|
SQLX Function, XML 예제
|
|
최길호
[LIST]
|
2005-09-21 22:39:01, 조회 : 20,104 |
SQLX Function
XMLElement()
XMLAttributes()
XMLForest()
XMLColAttVal()
XMLSequence()
XMLConcat()
XMLAgg()
XMLElement()
----------------------------------------------------------------------------------------------------
select empno,xmlelement("Emp",ename|| ' '||job) as result from emp;
7934
<Emp>MILLER CLERK</Emp>
select xmlelement("Emp",
xmlelement("ename",ename),
xmlelement("job",job)) from emp;
<Emp>
<ename>MILLER</ename>
<job>CLERK</job>
</Emp>
----------------------------------------------------------------------------------------------------
select xmlelement("Emp",xmlattributes(empno as "id",ename as "name")) from emp;
<Emp id="7900" name="JAMES"/>
<Emp id="7902" name="FORD"/>
----------------------------------------------------------------------------------------------------
select xmlelement("Eml",xmlforest(hiredate,deptno as "department")) from emp;
<Eml>
<HIREDATE>82/01/23</HIREDATE>
<department>10</department>
</Eml>
----------------------------------------------------------------------------------------------------
select xmlelement("Department",
xmlattributes(deptno as "id"),
xmlagg(xmlelement("emp",ename))) from emp
group by deptno;
<Department id="30">
<emp>ALLEN</emp>
<emp>JAMES</emp>
<emp>BLAKE</emp>
</Department>
----------------------------------------------------------------------------------------------------
select XMLElement("Emp",
XMLColAttVal(
hiredate,
deptno)
) as result from emp;
<Emp>
<column name="HIREDATE">82/01/23</column>
<column name="DEPTNO">10</column>
</Emp>
----------------------------------------------------------------------------------------------------
select xmlconcat(
xmlelement("name",ename),
xmlelement("job",job)
) from emp;
<name>MILLER</name
<job>CLERK</job>
----------------------------------------------------------------------------------------------------
select value(e).getclobval() from
table(xmlsequence
(cursor(select * from emp))
) e;
<ROW>
<EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
<JOB>CLERK</JOB>
<MGR>79
----------------------------------------------------------------------------------------------------
select extractvalue(value(T),'/Description')
from purchaseorder X,
table(xmlsequence(
extract(x.doc,'//LineItem/Description')
)
)T; |
18.97.9.172
|
|
|
|
Copyright 1999-2024 Zeroboard / skin by 최길호(gilho.kr@gmail.com)
|
|
|
11/21