SELECT XMLELEMENT ("Date", hire_date)
FROM hr.employees
WHERE employee_id = 203;
------------------------------------------------------------------------------------------------
SELECT XMLELEMENT ("Date", TO_CHAR (hire_date))
FROM hr.employees
WHERE employee_id = 203;
------------------------------------------------------------------------------------------------
SELECT e.employee_id,
XMLELEMENT ("Emp", e.first_name || ' ' || e.last_name) AS "RESULTADO"
FROM hr.employees e
WHERE employee_id > 200;
------------------------------------------------------------------------------------------------
SELECT XMLELEMENT ("Emp",
XMLELEMENT ("name", e.first_name || ' ' || e.last_name),
XMLELEMENT ("hiredate", e.hire_date))
AS "RESULT"
FROM hr.employees e
WHERE employee_id > 200;
------------------------------------------------------------------------------------------------
SELECT XMLELEMENT (
"Emp",
XmlAttributes (e.employee_id AS "ID",
e.first_name || ' ' || e.last_name AS "Name"))
AS "RESULTADO"
FROM hr.employees e
WHERE employee_id > 200;
------------------------------------------------------------------------------------------------
SELECT XMLELEMENT (
"Emp->Special",
XMLAttributes (e.last_name || ', ' || e.first_name AS "Last,First"))
AS "RESULT"
FROM hr.employees e
WHERE employee_id = 201;
------------------------------------------------------------------------------------------------
SELECT XMLELEMENT (
"Employee",
XMLAttributes (
'http://www.w3.org/2001/XMLSchema' AS "xmlns:xsi",
'http://www.oracle.com/Employee.xsd' AS "xsi:nonamespaceSchemaLocation"),
XMLFOREST (employee_id, last_name, salary))
AS "RESULT"
FROM hr.employees
WHERE department_id = 10;
------------------------------------------------------------------------------------------------
CREATE OR REPLACE TYPE emp_t AS OBJECT
("@EMPNO" NUMBER (4), ENAME VARCHAR2 (10));
------------------------------------------------------------------------------------------------
CREATE OR REPLACE TYPE emplist_t AS TABLE OF emp_t;
------------------------------------------------------------------------------------------------
CREATE OR REPLACE TYPE dept_t AS OBJECT
("@DEPTNO" NUMBER (2),
DNAME VARCHAR2 (14),
EMP_LIST emplist_t);
------------------------------------------------------------------------------------------------
SELECT XMLELEMENT (
"Department",
dept_t (
department_id,
department_name,
CAST (
MULTISET (SELECT employee_id, last_name
FROM hr.employees e
WHERE e.department_id = d.department_id) AS emplist_t)))
AS deptxml
FROM hr.departments d
WHERE d.department_id = 10;
------------------------------------------------------------------------------------------------
SELECT XMLELEMENT (
"Emp",
XMLAttributes (e.first_name || ' ' || e.last_name AS "name"),
XMLFOREST (e.hire_date, e.department_id AS "department"))
AS "RESULT"
FROM HR.employees e
WHERE e.department_id = 20;
------------------------------------------------------------------------------------------------
SELECT XMLFOREST (
dept_t (
department_id,
department_name,
CAST (
MULTISET (SELECT employee_id, last_name
FROM hr.employees e
WHERE e.department_id = d.department_id) AS emplist_t)) AS "Department")
AS deptxml
FROM hr.departments d
WHERE department_id = 10;
------------------------------------------------------------------------------------------------
SELECT VALUE (T).getstringval () Attribute_Value
FROM TABLE (
XMLSEQUENCE (
EXTRACT (XMLType ('
V1V2V3'), '/A/B'))) T;
------------------------------------------------------------------------------------------------
CREATE TABLE emp_xml_tab OF XMLTYPE;
------------------------------------------------------------------------------------------------
INSERT INTO emp_xml_tab
VALUES (
XMLType (
'
112
Joe
50000
217
Jane
60000
412
Jack
40000
'));------------------------------------------------------------------------------------------------
SELECT VALUE (em).getClobVal () AS "XMLTYPE"
FROM TABLE (XMLSEQUENCE (CURSOR (SELECT *
FROM hr.employees
WHERE employee_id = 104))) em;
------------------------------------------------------------------------------------------------
CREATE TABLE dept_xml_tab OF XMLTYPE;
------------------------------------------------------------------------------------------------
INSERT INTO dept_xml_tab
VALUES (
XMLType (
'
Sports
John33333
Jack333444
'));------------------------------------------------------------------------------------------------
INSERT INTO dept_xml_tab
VALUES (
XMLType (
'
Sports
Marlin20000
'));------------------------------------------------------------------------------------------------
SELECT EXTRACTVALUE (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;
------------------------------------------------------------------------------------------------
SELECT XMLCONCAT (
XMLSequenceType (XMLType ('
1236'),
XMLType ('
Widget'),
XMLType ('
29.99'))).
getClobVal ()
AS "RESULT"
FROM DUAL;
------------------------------------------------------------------------------------------------
SELECT XMLCONCAT (XMLELEMENT ("first", e.first_name),
XMLELEMENT ("last", e.last_name))
AS "RESULT"
FROM employees e;
------------------------------------------------------------------------------------------------
SELECT XMLELEMENT (
"Department",
XMLAGG (XMLELEMENT ("Employee", e.job_id || ' ' || e.last_name)
ORDER BY e.last_name))
AS "Dept_list"
FROM hr.employees e
WHERE e.department_id = 30 OR e.department_id = 40;
------------------------------------------------------------------------------------------------
SELECT XMLELEMENT (
"Department",
XMLAttributes (department_id AS "deptno"),
XMLAGG (XMLELEMENT ("Employee", e.job_id || ' ' || e.last_name)))
AS "Dept_list"
FROM hr.employees e
GROUP BY e.department_id;
------------------------------------------------------------------------------------------------
SELECT last_name, employee_id
FROM hr.employees
WHERE department_id = 30;
------------------------------------------------------------------------------------------------
CREATE TABLE dependents
(
id NUMBER (4) PRIMARY KEY,
employee_id NUMBER (4),
name VARCHAR2 (10)
);
------------------------------------------------------------------------------------------------
INSERT INTO dependents VALUES (1, 114, 'MARK');
INSERT INTO dependents VALUES (2, 114, 'JACK');
INSERT INTO dependents VALUES (3, 115, 'JANE');
INSERT INTO dependents VALUES (4, 116, 'HELEN');
INSERT INTO dependents VALUES (5, 116, 'FRANK');
COMMIT;
------------------------------------------------------------------------------------------------
SELECT XMLELEMENT (
"Department",
XMLAttributes (d.department_name AS "name"),
(SELECT XMLAGG (
XMLELEMENT (
"emp",
XMLAttributes (e.last_name AS name),
(SELECT XMLAGG (
XMLELEMENT (
"dependent",
XMLAttributes (de.name AS "name")))
FROM dependents de
WHERE de.employee_id = e.employee_id)))
FROM hr.employees e
WHERE e.department_id = d.department_id))
AS "dept_list"
FROM hr.departments d
WHERE department_id = 30;
------------------------------------------------------------------------------------------------
SELECT XMLPI (NAME "OrderAnalysisComp",
'imported, reconfigured, disassembled')
AS pi
FROM DUAL;
------------------------------------------------------------------------------------------------
SELECT XMLCOMMENT (' Esto es un comentario') AS comentario FROM DUAL;
------------------------------------------------------------------------------------------------
SELECT XMLROOT (XMLType ('
143598'),
VERSION '1.0',
STANDALONE YES)
AS XMLROOT
FROM DUAL;
------------------------------------------------------------------------------------------------
SELECT XMLSERIALIZE (DOCUMENT XMLType ('
143598') AS CLOB)
AS xmlserialize_doc
FROM DUAL;
------------------------------------------------------------------------------------------------
SELECT XMLPARSE (
CONTENT '124
Acme Enterprises
32987457
' WELLFORMED) AS correo
FROM DUAL d;
------------------------------------------------------------------------------------------------
SELECT XMLELEMENT (
"Emp",
XmlAttributes (e.first_name || ' ' || e.last_name AS "fullname"),
XMLCOLATTVAL (e.hire_date, e.department_id AS "department"))
AS "RESULTADO"
FROM hr.employees e
WHERE e.department_id = 30;
------------------------------------------------------------------------------------------------
SELECT XMLELEMENT (
"PurchaseOrder",
XMLELEMENT ("Address",
XMLCDATA ('100 Pennsylvania Ave.'),
XMLELEMENT ("City", 'Washington, D.C.')))
AS RESULT
FROM DUAL;
------------------------------------------------------------------------------------------------
CREATE TABLE temp_clob_tab (result CLOB);
------------------------------------------------------------------------------------------------
DECLARE
qryCtx DBMS_XMLGEN.ctxHandle;
result CLOB;
BEGIN
qryCtx := DBMS_XMLGEN.newContext ('SELECT * FROM hr.employees');
-- Set the row header to be EMPLOYEE
DBMS_XMLGEN.setRowTag (qryCtx, 'EMPLOYEE');
-- Get the result
result := DBMS_XMLGEN.getXML (qryCtx);
INSERT INTO temp_clob_tab
VALUES (result);
--Close context
DBMS_XMLGEN.closeContext (qryCtx);
END;
------------------------------------------------------------------------------------------------
SELECT *
FROM temp_clob_tab
WHERE ROWNUM = 1;
------------------------------------------------------------------------------------------------
CREATE TABLE temp_clob_tab (result CLOB);
DECLARE
qryCtx DBMS_XMLGEN.ctxHandle;
result CLOB;
BEGIN
-- Get the query context;
qryCtx := DBMS_XMLGEN.newContext ('SELECT * FROM hr.employees');
-- Set the maximum number of rows to be 2
DBMS_XMLGEN.setMaxRows (qryCtx, 2);
LOOP
-- Get the result
result := DBMS_XMLGEN.getXML (qryCtx);
-- If no rows were processed, then quit
EXIT WHEN DBMS_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 a stream,
-- put it in a queue, or do any other processing.
INSERT INTO temp_clob_tab
VALUES (result);
END LOOP;
--close context
DBMS_XMLGEN.closeContext (qryCtx);
END;
/
------------------------------------------------------------------------------------------------
SELECT *
FROM temp_clob_tab
WHERE ROWNUM < 3;
------------------------------------------------------------------------------------------------
CREATE TABLE new_departments
(
department_id NUMBER PRIMARY KEY,
department_name VARCHAR2 (20)
);
------------------------------------------------------------------------------------------------
CREATE TABLE new_employees
(
employee_id NUMBER PRIMARY KEY,
last_name VARCHAR2 (20),
department_id NUMBER REFERENCES new_departments
);
------------------------------------------------------------------------------------------------
CREATE TYPE emp_t AS OBJECT
("@employee_id" NUMBER, last_name VARCHAR2 (20));
------------------------------------------------------------------------------------------------
INSERT INTO new_departments VALUES (10, 'SALES');
INSERT INTO new_departments VALUES (20, 'ACCOUNTING');
INSERT INTO new_employees VALUES (30, 'Scott', 10);
INSERT INTO new_employees VALUES (31, 'Mary', 10);
INSERT INTO new_employees VALUES (40, 'John', 20);
INSERT INTO new_employees VALUES (41, 'Jerry', 20);
COMMIT;
------------------------------------------------------------------------------------------------
CREATE TYPE emplist_t AS TABLE OF emp_t;
------------------------------------------------------------------------------------------------
CREATE TYPE dept_t AS OBJECT
("@department_id" NUMBER,
department_name VARCHAR2 (20),
emplist emplist_t);
------------------------------------------------------------------------------------------------
CREATE TABLE temp_clob_tab (result CLOB);
------------------------------------------------------------------------------------------------
DECLARE
qryCtx DBMS_XMLGEN.ctxHandle;
result CLOB;
BEGIN
DBMS_XMLGEN.setRowTag (qryCtx, NULL);
qryCtx :=
DBMS_XMLGEN.
newContext (
'SELECT dept_t(department_id,
department_name,
CAST(MULTISET
(SELECT e.employee_id, e.last_name
FROM new_employees e
WHERE e.department_id = d.department_id)
AS emplist_t))
AS deptxml
FROM new_departments d');
-- now get the result
result := DBMS_XMLGEN.getXML (qryCtx);
INSERT INTO temp_clob_tab
VALUES (result);
-- close context
DBMS_XMLGEN.closeContext (qryCtx);
END;
------------------------------------------------------------------------------------------------
SELECT * FROM temp_clob_tab;
------------------------------------------------------------------------------------------------
CREATE TABLE dept
(
deptno NUMBER PRIMARY KEY,
dname VARCHAR2 (20),
LOC VARCHAR2 (20)
);
------------------------------------------------------------------------------------------------
CREATE TABLE emp
(
empno NUMBER PRIMARY KEY,
ename VARCHAR2 (20),
deptno NUMBER REFERENCES dept
);
------------------------------------------------------------------------------------------------
CREATE TYPE emp_t AS OBJECT
("@empno" NUMBER, -- empno defined as attribute
ename VARCHAR2 (20));
------------------------------------------------------------------------------------------------
INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (10, 'Sports', 'NEW YORK');
INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (20, 'Accounting', 'SEATTLE');
INSERT INTO EMP (EMPNO, ENAME, DEPTNO) VALUES (200, 'John', 10);
INSERT INTO EMP (EMPNO, ENAME, DEPTNO) VALUES (300, 'Jack', 10);
INSERT INTO EMP (EMPNO, ENAME, DEPTNO) VALUES (400, 'Mary', 20);
INSERT INTO EMP (EMPNO, ENAME, DEPTNO) VALUES (500, 'Jerry', 20);
COMMIT;
------------------------------------------------------------------------------------------------
CREATE TYPE phonelist_vartyp AS VARRAY (10) OF VARCHAR2 (20);
------------------------------------------------------------------------------------------------
CREATE TYPE address_typ AS OBJECT
(Street VARCHAR2 (200),
City VARCHAR2 (200),
State CHAR (2),
Zip VARCHAR2 (20));
------------------------------------------------------------------------------------------------
CREATE TYPE customer_typ AS OBJECT
(CustNo NUMBER,
CustName VARCHAR2 (200),
Address address_typ,
PhoneList phonelist_vartyp);
------------------------------------------------------------------------------------------------
CREATE TYPE stockitem_typ AS OBJECT
("@StockNo" NUMBER, Price NUMBER, TaxRate NUMBER);
------------------------------------------------------------------------------------------------
CREATE TYPE lineitem_typ AS OBJECT
("@LineItemNo" NUMBER,
Item stockitem_typ,
Quantity NUMBER,
Discount NUMBER);
------------------------------------------------------------------------------------------------
CREATE TYPE lineitems_ntabtyp AS TABLE OF lineitem_typ;
------------------------------------------------------------------------------------------------
CREATE TYPE po_typ
AUTHID CURRENT_USER AS OBJECT
(PONO NUMBER,
Cust_ref REF customer_typ,
OrderDate DATE,
ShipDate TIMESTAMP,
LineItems_ntab lineitems_ntabtyp,
ShipToAddr address_typ);
------------------------------------------------------------------------------------------------
CREATE TABLE customer_tab
(
CustNo NUMBER NOT NULL,
CustName VARCHAR2 (200),
Street VARCHAR2 (200),
City VARCHAR2 (200),
State CHAR (2),
Zip VARCHAR2 (20),
Phone1 VARCHAR2 (20),
Phone2 VARCHAR2 (20),
Phone3 VARCHAR2 (20),
CONSTRAINT cust_pk PRIMARY KEY (CustNo)
);
------------------------------------------------------------------------------------------------
CREATE TABLE po_tab
(
PONo NUMBER, /* purchase order number */
Custno NUMBER /* foreign KEY referencing customer */
CONSTRAINT po_cust_fk REFERENCES customer_tab,
OrderDate DATE, /* date of order */
ShipDate TIMESTAMP, /* date to be shipped */
ToStreet VARCHAR2 (200), /* shipto address */
ToCity VARCHAR2 (200),
ToState CHAR (2),
ToZip VARCHAR2 (20),
CONSTRAINT po_pk PRIMARY KEY (PONo)
);
------------------------------------------------------------------------------------------------
CREATE TABLE stock_tab
(
StockNo NUMBER CONSTRAINT stock_uk UNIQUE,
Price NUMBER,
TaxRate NUMBER
);
------------------------------------------------------------------------------------------------
CREATE TABLE lineitems_tab
(
LineItemNo NUMBER,
PONo NUMBER CONSTRAINT li_po_fk REFERENCES po_tab,
StockNo NUMBER,
Quantity NUMBER,
Discount NUMBER,
CONSTRAINT li_pk PRIMARY KEY (PONo, LineItemNo)
);
------------------------------------------------------------------------------------------------
CREATE OR REPLACE VIEW customer
OF customer_typ
WITH OBJECT IDENTIFIER (CustNo)
AS
SELECT c.custno,
c.custname,
address_typ (c.street,
c.city,
c.state,
c.zip),
phonelist_vartyp (phone1, phone2, phone3)
FROM customer_tab c;
------------------------------------------------------------------------------------------------
CREATE OR REPLACE VIEW po
OF po_typ
WITH OBJECT IDENTIFIER (PONo)
AS
SELECT p.pono,
MAKE_REF (Customer, P.Custno),
p.orderdate,
p.shipdate,
CAST (
MULTISET (
SELECT lineitem_typ (
l.lineitemno,
stockitem_typ (l.stockno, s.price, s.taxrate),
l.quantity,
l.discount)
FROM lineitems_tab l, stock_tab s
WHERE l.pono = p.pono AND s.stockno = l.stockno) AS lineitems_ntabtyp),
address_typ (p.tostreet,
p.tocity,
p.tostate,
p.tozip)
FROM po_tab p;
------------------------------------------------------------------------------------------------
CREATE TABLE po_xml_tab
(
poid NUMBER,
podoc XMLTYPE
);
INSERT INTO stock_tab VALUES (1004, 6750.00, 2);
INSERT INTO stock_tab VALUES (1011, 4500.23, 2);
INSERT INTO stock_tab VALUES (1534, 2234.00, 2);
INSERT INTO stock_tab VALUES (1535, 3456.23, 2);
------------------------------------------------------------------------------------------------
INSERT INTO customer_tab VALUES (1,
'Jean Nance',
'2 Avocet Drive',
'Redwood Shores',
'CA',
'95054',
'415-555-1212',
NULL,
NULL);
INSERT INTO customer_tab
VALUES (2,
'John Nike',
'323 College Drive',
'Edison',
'NJ',
'08820',
'609-555-1212',
'201-555-1212',
NULL);
------------------------------------------------------------------------------------------------
-- Place orders
INSERT INTO po_tab
VALUES (1001,
1,
'10-04-1997',
'10-05-1997',
NULL,
NULL,
NULL,
NULL);
INSERT INTO po_tab
VALUES (2001,
2,
'20-APR-1997',
'20-MAY-1997',
'55 Madison Ave',
'Madison',
'WI',
'53715');
------------------------------------------------------------------------------------------------
-- Detail line items
INSERT INTO lineitems_tab
VALUES (01,
1001,
1534,
12,
0);
INSERT INTO lineitems_tab
VALUES (02,
1001,
1535,
10,
10);
INSERT INTO lineitems_tab
VALUES (01,
2001,
1004,
1,
0);
INSERT INTO lineitems_tab
VALUES (02,
2001,
1011,
2,
1);
------------------------------------------------------------------------------------------------
DECLARE
qryCtx DBMS_XMLGEN.ctxHandle;
pxml XMLTYPE;
cxml CLOB;
BEGIN
-- get query context;
qryCtx :=
DBMS_XMLGEN.
newContext (
'SELECT pono,deref(cust_ref) customer,
p.orderdate,
p.shipdate,
lineitems_ntab lineitems,
shiptoaddr
FROM po p');
-- set maximum number of rows to be 1,
DBMS_XMLGEN.setMaxRows (qryCtx, 1);
-- set ROWSET tag to NULL and ROW tag to PurchaseOrder
DBMS_XMLGEN.setRowSetTag (qryCtx, NULL);
DBMS_XMLGEN.setRowTag (qryCtx, 'PurchaseOrder');
LOOP
-- get purchase order in XML format
pxml := DBMS_XMLGEN.getXMLType (qryCtx);
-- if there were no rows processed, then quit
EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed (qryCtx) = 0;
-- Store XMLType po in po_xml table (get the pono out)
INSERT INTO po_xml_tab (poid, poDoc)
VALUES (pxml.EXTRACT ('//PONO/text()').getNumberVal (), pxml);
END LOOP;
END;
------------------------------------------------------------------------------------------------
SELECT x.podoc.getClobVal () xpo
FROM po_xml_tab x;
CREATE TABLE emp_tab
(
emp_id NUMBER PRIMARY KEY,
name VARCHAR2 (20),
dept_id NUMBER
);
------------------------------------------------------------------------------------------------
INSERT INTO emp_tab VALUES (122, 'Scott', 301);
INSERT INTO emp_tab VALUES (123, 'Mary', 472);
INSERT INTO emp_tab VALUES (124, 'John', 93);
INSERT INTO emp_tab VALUES (125, 'Howard', 488);
INSERT INTO emp_tab VALUES (126, 'Sue', 16);
COMMIT;
------------------------------------------------------------------------------------------------
DECLARE
ctx NUMBER;
maxrow NUMBER;
xmldoc CLOB;
refcur SYS_REFCURSOR;
BEGIN
DBMS_LOB.createtemporary (xmldoc, TRUE);
maxrow := 3;
OPEN refcur FOR 'SELECT * FROM emp_tab WHERE ROWNUM <= :1' USING maxrow;
ctx := DBMS_XMLGEN.newContext (refcur);
-- xmldoc will have 3 rows
DBMS_XMLGEN.getXML (ctx, xmldoc, DBMS_XMLGEN.NONE);
DBMS_OUTPUT.put_line (xmldoc);
DBMS_LOB.freetemporary (xmldoc);
CLOSE refcur;
DBMS_XMLGEN.closeContext (ctx);
END;
------------------------------------------------------------------------------------------------
CREATE TABLE sqlx_display
(
id NUMBER,
xmldoc XMLTYPE
);
------------------------------------------------------------------------------------------------
DECLARE
qryctx DBMS_XMLGEN.ctxhandle;
result XMLTYPE;
BEGIN
qryctx :=
DBMS_XMLGEN.
newcontextFromHierarchy (
'SELECT level,
XMLElement("employees",
XMLElement("enumber", employee_id),
XMLElement("name", last_name),
XMLElement("Salary", salary),
XMLElement("Hiredate", hire_date))
FROM hr.employees
START WITH last_name=''De Haan'' CONNECT BY PRIOR employee_id=manager_id
ORDER SIBLINGS BY hire_date');
result := DBMS_XMLGEN.getxmltype (qryctx);
DBMS_OUTPUT.put_line ('
');
DBMS_OUTPUT.put_line (TO_CHAR (DBMS_XMLGEN.getNumRowsProcessed (qryctx)));
DBMS_OUTPUT.put_line ('
'); INSERT INTO sqlx_display
VALUES (2, result);
COMMIT;
DBMS_XMLGEN.closecontext (qryctx);
END;
------------------------------------------------------------------------------------------------
CREATE TABLE gg (x XMLTYPE);
------------------------------------------------------------------------------------------------
DECLARE
qryctx DBMS_XMLGEN.ctxhandle;
result CLOB;
BEGIN
qryctx :=
DBMS_XMLGEN.
newcontextFromHierarchy (
'SELECT level,
XMLElement("NAME", last_name) AS myname FROM hr.employees
CONNECT BY PRIOR employee_id=manager_id
START WITH employee_id = 102');
DBMS_XMLGEN.setRowSetTag (qryctx, 'mynum_hierarchy');
result := DBMS_XMLGEN.getxml (qryctx);
DBMS_OUTPUT.put_line ('
');
DBMS_OUTPUT.put_line (TO_CHAR (DBMS_XMLGEN.getNumRowsProcessed (qryctx)));
DBMS_OUTPUT.put_line ('
'); INSERT INTO gg
VALUES (XMLType (result));
COMMIT;
DBMS_XMLGEN.closecontext (qryctx);
END;
------------------------------------------------------------------------------------------------
DECLARE
ctx NUMBER;
xmldoc CLOB;
BEGIN
ctx :=
DBMS_XMLGEN.
newContext ('SELECT * FROM HR.employees WHERE employee_id = :NO');
DBMS_XMLGEN.setBindValue (ctx, 'NO', '145');
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 employees
WHERE hire_date = :MDATE');
DBMS_XMLGEN.setBindValue (ctx, 'MDATE', '01-OCT-96');
xmldoc := DBMS_XMLGEN.getXML (ctx);
DBMS_OUTPUT.put_line (xmldoc);
DBMS_XMLGEN.setBindValue (ctx, 'MDATE', '10-MAR-97');
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 HR.employees
WHERE employee_id = :NO
AND hire_date = :MDATE');
DBMS_XMLGEN.setBindValue (ctx, 'NO', '145');
DBMS_XMLGEN.setBindValue (ctx, 'MDATE', '01-OCT-96');
xmldoc := DBMS_XMLGEN.getXML (ctx);
DBMS_OUTPUT.put_line (xmldoc);
DBMS_XMLGEN.closeContext (ctx);
EXCEPTION
WHEN OTHERS
THEN
DBMS_XMLGEN.closeContext (ctx);
RAISE;
END;
------------------------------------------------------------------------------------------------
SELECT SYS_XMLGEN (employee_id) AS "result"
FROM HR.employees
WHERE first_name LIKE 'John%';
------------------------------------------------------------------------------------------------
SELECT SYS_XMLGEN (x)
FROM table_name
WHERE x IS NOT NULL;
------------------------------------------------------------------------------------------------
SELECT SYS_XMLGEN (first_name).getStringVal ()
FROM HR.employees
WHERE employee_id = 110;
------------------------------------------------------------------------------------------------
SELECT SYS_XMLGEN (employee_id)
FROM hr.employees
WHERE ROWNUM < 2;
------------------------------------------------------------------------------------------------
SELECT SYS_XMLGEN (employee_id * 2)
FROM Hr.employees
WHERE ROWNUM < 2;
------------------------------------------------------------------------------------------------
SELECT SYS_XMLGEN (employee_id * 2, XMLFormat.createformat ('DOUBLE_ID')).
getclobval ()
FROM hr.employees
WHERE ROWNUM < 2;
------------------------------------------------------------------------------------------------
CREATE OR REPLACE TYPE hr.emp_t AS OBJECT
(empno NUMBER (6),
Ename VARCHAR2 (25),
Job VARCHAR2 (10),
Mgr NUMBER (6),
Hiredate DATE,
sal NUMBER (8, 2),
comm NUMBER (2, 2));
------------------------------------------------------------------------------------------------
CREATE OR REPLACE TYPE hr.emplist_t AS TABLE OF emp_t;
------------------------------------------------------------------------------------------------
CREATE OR REPLACE TYPE hr.dept_t AS OBJECT(deptno NUMBER(4),
dname VARCHAR2(30),
loc VARCHAR2(4),
emplist emplist_t);
------------------------------------------------------------------------------------------------
/*
SELECT sys_XMLGen(
dept_t(department_id,
department_name,
d.location_id,
CAST(MULTISET(SELECT emp_t(e.employee_id, e.last_name, e.job_id,
e.manager_id, e.hire_date, e.salary,
e.commission_pct)
FROM hr.employees e
WHERE e.department_id = d.department_id)
AS emplist_t))).getClobVal() deptxml
FROM hr.departments d WHERE department_id = 10 OR department_id = 20;
*/
------------------------------------------------------------------------------------------------
CREATE TABLE po_xml_tab (podoc XMLTYPE);
------------------------------------------------------------------------------------------------
/*
INSERT INTO po_xml_tab VALUES(XMLType('
John
200
Jack
400
Joseph
300
'));COMMIT;
*/
------------------------------------------------------------------------------------------------
SELECT e.podoc.EXTRACT ('/DOCUMENT/EMPLOYEE/ENAME')
FROM po_xml_tab e;
------------------------------------------------------------------------------------------------
SELECT SYS_XMLGEN (e.podoc.EXTRACT ('/DOCUMENT/EMPLOYEE/ENAME')).
getClobVal ()
FROM po_xml_tab e;
------------------------------------------------------------------------------------------------
CREATE OR REPLACE TYPE po_typ
AUTHID CURRENT_USER AS OBJECT
(pono NUMBER,
customer customer_typ,
orderdate DATE,
shipdate TIMESTAMP,
lineitems_ntab lineitems_ntabtyp,
shiptoaddr address_typ);
------------------------------------------------------------------------------------------------
CREATE OR REPLACE VIEW po
OF po_typ
WITH OBJECT IDENTIFIER (PONO)
AS
SELECT p.pono,
customer_typ (p.custno,
c.custname,
c.address,
c.phonelist),
p.orderdate,
p.shipdate,
CAST (
MULTISET (
SELECT lineitem_typ (
l.lineitemno,
stockitem_typ (l.stockno, s.price, s.taxrate),
l.quantity,
l.discount)
FROM lineitems_tab l, stock_tab s
WHERE l.pono = p.pono AND s.stockno = l.stockno) AS lineitems_ntabtyp),
address_typ (p.tostreet,
p.tocity,
p.tostate,
p.tozip)
FROM po_tab p, customer c
WHERE p.custno = c.custno;
------------------------------------------------------------------------------------------------
SELECT SYS_XMLGEN (OBJECT_VALUE, XMLFormat.createFormat ('PurchaseOrder')).
getClobVal ()
PO
FROM po p
WHERE p.pono = 1001;
------------------------------------------------------------------------------------------------
CREATE TABLE dev_tab
(
dev NUMBER,
dev_total NUMBER,
devname VARCHAR2 (20)
);
------------------------------------------------------------------------------------------------
INSERT INTO dev_tab VALUES (16, 5, 'Alexis');
INSERT INTO dev_tab VALUES (2, 14, 'Han');
INSERT INTO dev_tab VALUES (1, 2, 'Jess');
INSERT INTO dev_tab VALUES (9, 88, 'Kurt');
COMMIT;
------------------------------------------------------------------------------------------------
SELECT XMLAGG (
XMLELEMENT ("Dev",
XMLAttributes (dev AS "id", dev_total AS "total"),
devname)
ORDER BY dev)
FROM tab1 dev_total;
------------------------------------------------------------------------------------------------
La mayoría de tablas son del Usuario HR.
