This is an
excerpt from the book
Advanced PL/SQL: The Definitive Reference by Boobal Ganesan.
XMLTYPE
is the datatype used to store XML data. The type constructor
accepts different input like character, binary or ref
cursor. The text passed to the constructor must be a
well-formed document.
SELECT
XMLTYPE
(
'<X/>'
)
FROM
DUAL;
XMLTYPE
-------
< X/>
The XMLTYPE is constructed from a
character literal.
SELECT
XMLTYPE
(
TO_BLOB
(
HEXTORAW ('3C696D673E3A2D293C2F696D673E')
),
NLS_CHARSET_ID('WE8MSWIN1252')
)
FROM
DUAL;
XMLTYPE(TO_BLOB
---------------
< img>:-)</img>
A BLOB
is passed to the XMLTYPE constructor.
NLS_CHARSET_ID
returns the character set used for the encoding of the BLOB.
Oracle 9i does not support BLOB as input.
SELECT
XMLTYPE
(
BFILENAME('TEMP', 'test.xml'),
NLS_CHARSET_ID('WE8MSWIN1252')
)
FROM
DUAL;
XMLTYPE(BFILENAME('TEM
----------------------
< msg>Hello
World</msg>
A file is passed to the XMLTYPE
constructor. BFILENAME returns
a BFILElocator to
the file test.xml in the Oracle directory TEMP.
SELECT
XMLTYPE
(
CURSOR
(
SELECT
*
FROM
DEPT
)
)
FROM
DUAL;
XMLTYPE(CURSOR(SELECT*FROMDEPT))
--------------------------------
< ?xml version="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>
<ROW>
<DEPTNO>30</DEPTNO>
<DNAME>SALES</DNAME>
<LOC>CHICAGO</LOC>
</ROW>
<ROW>
<DEPTNO>40</DEPTNO>
<DNAME>OPERATIONS</DNAME>
<LOC>BOSTON</LOC>
</ROW>
< /ROWSET>
XMLTYPE accepts a Ref Cursor as a
parameter.
This is a data type in Oracle that
can be used for storing XML data in the database.
Create table xml_table(xml_data
xmltype);
Insert into xml_table values
('<Employees><Name>Alex</Name><DOB>30-Sep-1989</DOB><Job_ID>101</Job_ID><Sal>2000</Sal></Employees>');
Commit;
Result:
table XML_TABLE created.
1 rows inserted.
committed.
This can also be used as a construct
for explicit conversion of string into XML as shown below,
SELECT xmltype('<Employees><Name>Alex</Name><DOB>30-Sep-1989</DOB><Job_ID>101</Job_ID><Sal>2000</Sal></Employees>')
FROM dual;
Result:
<Employees>
<Name>Alex</Name>
<DOB>30-Sep-1989</DOB>
<Job_ID>101</Job_ID>
<Sal>2000</Sal>
</Employees>
When the XML tags are not properly
handled during the explicit conversion, the statement fails
with an ORA-31011:
XML parsing failed error.
This construct can also be used for
generating XML documents from the result of an SQL statement
when joint forces with the ref cursor as shown in the below
example. The GETCLOBVAL operator converts an XML type
document into a CLOB data. For more details on ref cursors,
please refer Chapter 7,
The Cursors and
Ref-cursors in PL/SQL.
DECLARE
l_rc_var1 SYS_REFCURSOR;
l_xt_var2 XMLTYPE;
BEGIN
OPEN l_rc_var1 FOR SELECT first_name, last_name,
email, phone_number, hire_date, job_id, salary FROM
employees WHERE employee_id IN (100,101);
l_xt_var2 := XMLTYPE(l_rc_var1);
dbms_output.put_line(l_xt_var2.getClobVal);
END;
/
Result:
<?xml version="1.0"?>
<ROWSET>
<ROW>
<FIRST_NAME>Steven</FIRST_NAME>
<LAST_NAME>King</LAST_NAME>
<EMAIL>SKING</EMAIL>
<PHONE_NUMBER>515.123.4567</PHONE_NUMBER>
<HIRE_DATE>17-JUN-03</HIRE_DATE>
<JOB_ID>AD_PRES</JOB_ID>
<SALARY>24000</SALARY>
</ROW>
<ROW>
<FIRST_NAME>Neena</FIRST_NAME>
<LAST_NAME>Kochhar</LAST_NAME>
<EMAIL>NKOCHHAR</EMAIL>
<PHONE_NUMBER>515.123.4568</PHONE_NUMBER>
<HIRE_DATE>21-SEP-05</HIRE_DATE>
<JOB_ID>AD_VP</JOB_ID>
<SALARY>17000</SALARY>
</ROW>
</ROWSET>
 |
Need to learn to program with PL/SQL?
For complete notes on programming in PL/SQL, we
recommend the book
Advanced PL/SQL: The Definitive Reference by Boobal Ganesan.
This is a complete book on PL/SQL with
everything you need to know to write efficient and
complex PL/SQL code. |
|
|
|
|
|
Oracle Training from Don Burleson
The best on site
"Oracle
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
|
|
|
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|
