Chapter 47. DBMS

Chapter 47. DBMS_XMLGEN

Table of Contents

47.2.1. ctxHandle

47.3.1. CLOSECONTEXT
47.3.2. CONVERT
47.3.3. GETNUMROWSPROCESSED
47.3.4. GETXML
47.3.5. GETXMLTYPE
47.3.6. NEWCONTEXT
47.3.7. SETMAXROWS
47.3.8. SETNULLHANDLING
47.3.9. SETROWTAG
47.3.10. SETROWSETTAG
47.3.11. SETSKIPROWS

This chapter briefly introduces the DBMS_XMLGEN package, and describes how to use the procedures and functions of the package.

47.1. Overview

DBMS_XMLGEN takes a query and creates an XML document with the query result set.

The following are the constants defined in the DBMS_XMLGEN package.

Conversion Flag
- Prototype
```
ENTITY_ENCODE             CONSTANT NUMBER := 0;
ENTITY_DECODE             CONSTANT NUMBER := 1;
```
- Argument
  
  The following describes the flags used as arguments for DBMS_XMLGEN.CONVERT function.
  
  Argument Description
  ENTITY_ENCODE Output encoded data.
  ENTITY_DECODE Output decoded data.
Null Handling Flag
- Prototype
```
DROP_NULLS             CONSTANT NUMBER := 0;
NULL_ATTR              CONSTANT NUMBER := 1;
EMPTY_TAG              CONSTANT NUMBER := 2;
```
- Arguments
  
  The following describes the flags used as arguments for DBMS_XMLGEN.SETNULLHANDLING procedure.
  
  Argument Description
  DROP_NULLS Do not output NULL values.
  NULL_ATTR Output as NULL ATTRIBUTE.
  EMPTY_TAG Output as EMPTY ELEMENT.

Argument	Description
ENTITY_ENCODE	Output encoded data.
ENTITY_DECODE	Output decoded data.

Argument	Description
DROP_NULLS	Do not output NULL values.
NULL_ATTR	Output as NULL ATTRIBUTE.
EMPTY_TAG	Output as EMPTY ELEMENT.

47.2. Types

This section describes additional types defined in the DBMS_XMLGEN package.

47.2.1. ctxHandle

ctxHandle is a subtype of NUMBER that represents a specific context.

Details about the ctxHandle type are as follows:

Prototype
```
SUBTYPE ctxHandle IS NUMBER;
```

47.3. Procedures and Functions

This section describes the procedures and functions provided by the DBMS_XMLGEN package, in alphabetical order.

47.3.1. CLOSECONTEXT

Closes the context using the context handle.

Details about the CLOSECONTEXT procedure are as follows:

Prototype

DBMS_XMLGEN.CLOSECONTEXT
(
    ctx IN ctxHandle
);

Parameter

Parameter Description
ctx Context handle to close.

Parameter	Description
ctx	Context handle to close.

Example

DECLARE
    ctx dbms_xmlgen.ctxHandle;
BEGIN
    ctx := DBMS_XMLGEN.NEWCONTEXT('select 1 from dual');
    DBMS_XMLGEN.CLOSECONTEXT(ctx);
END;
/

47.3.2. CONVERT

Converts XML data into escaped or unescaped XML data.

It returns a value that is of the same type as the input value of either CLOB or VARCHAR type.

Details about the CONVERT function are as follows:

Prototype

CLOB

DBMS_XMLGEN.CONVERT 
(
    xmlData IN CLOB,
    flag    IN NUMBER := ENTITY_ENCODE
 )  
RETURN CLOB;

VARCHR

DBMS_XMLGEN.CONVERT 
(
    xmlData IN VARCHAR2,
    flag    IN NUMBER := ENTITY_ENCODE
) 
RETURN VARCHAR2;

Parameters

Parameter	Description
xmlData	CLOB or VARCHAR data to encode or decode.
flag	Option to encode or decode data. ENTITY_ENCODE: encode data. (Default value) ENTITY_DECODE: decode data.

Example

DECLARE
    escape_str VARCHAR2(100);
BEGIN
    escape_str := DBMS_XMLGEN.CONVERT('Hi escaped_str! < & >',
                                            	DBMS_XMLGEN.ENTITY_ENCODE);
    DBMS_OUTPUT.PUT_LINE(escape_str);
END;
/

47.3.3. GETNUMROWSPROCESSED

Gets the number of rows processed by GETXML or GETXMLTYPE.

Details about the GETNUMROWSPROCESSED function are as follows:

Prototype

DBMS_XMLGEN.GETNUMROWSPROCESSED 
(
    ctx          IN   ctxHandle
) 
RETURN NUMBER;

Parameter

Parameter Description
ctx Context handle created during NEWCONTEXT function call.

Parameter	Description
ctx	Context handle created during NEWCONTEXT function call.

Example

CREATE TABLE EMP_T (EMP_ID NUMBER, EMP_NAME VARCHAR2(16));
INSERT INTO EMP_T VALUES(2015001, 'TOM');
INSERT INTO EMP_T VALUES(2015002, 'DAVID');
INSERT INTO EMP_T VALUES(2015003, 'GEORGE');
DECLARE
    ctx dbms_xmlgen.ctxHandle;
    result CLOB;
BEGIN
    ctx := DBMS_XMLGEN.NEWCONTEXT('select * from EMP_T');
    result := DBMS_XMLGEN.GETXML(ctx);
    DBMS_OUTPUT.PUT_LINE(DBMS_XMLGEN.GETNUMROWSPROCESSED(ctx));
    DBMS_XMLGEN.CLOSECONTEXT(ctx);
END;
/

47.3.4. GETXML

Creates an CLOB type XML document from ctxHandle.

Details about the GETXML function are as follows:

Prototype

DBMS_XMLGEN.GETXML 
(
    ctx          IN   ctxHandle,
    dtdOrSchema  IN   NUMBER
) 
RETURN CLOB;

Parameters

Parameter Description
ctx Context handle created during NEWCONTEXT function call.
dtdOrSchema Option to output DTD or Schema. Only 'NONE' is allowed.

Parameter	Description
ctx	Context handle created during NEWCONTEXT function call.
dtdOrSchema	Option to output DTD or Schema. Only 'NONE' is allowed.

Example

CREATE TABLE EMP_T (EMP_ID NUMBER, EMP_NAME VARCHAR2(16));
INSERT INTO EMP_T VALUES(2015001, 'TOM');
INSERT INTO EMP_T VALUES(2015002, 'DAVID');
INSERT INTO EMP_T VALUES(2015003, 'GEORGE');
DECLARE
    ctx dbms_xmlgen.ctxHandle;
    result CLOB;
BEGIN
    ctx := DBMS_XMLGEN.NEWCONTEXT('select * from EMP_T');
    result := DBMS_XMLGEN.GETXML(ctx);
    DBMS_OUTPUT.PUT_LINE(result);
    DBMS_XMLGEN.CLOSECONTEXT(ctx);
END;
/

47.3.5. GETXMLTYPE

Creates an XMLType XML document from ctxHandle.

Details about the GETXMLTYPE function are as follows:

Prototype

DBMS_XMLGEN.GETXMLTYPE 
(
    ctx          IN   ctxHandle,
    dtdOrSchema  IN   NUMBER
) 
RETURN XMLType;

Parameters

Parameter Description
ctx Context handle created during NEWCONTEXT function call.
dtdOrSchema Option to output DTD or Schema. Only 'NONE' is allowed.

Parameter	Description
ctx	Context handle created during NEWCONTEXT function call.
dtdOrSchema	Option to output DTD or Schema. Only 'NONE' is allowed.

Example

CREATE TABLE EMP_T (EMP_ID NUMBER, EMP_NAME VARCHAR2(16));
INSERT INTO EMP_T VALUES(2015001, 'TOM');
INSERT INTO EMP_T VALUES(2015002, 'DAVID');
INSERT INTO EMP_T VALUES(2015003, 'GEORGE');
DECLARE
    ctx dbms_xmlgen.ctxHandle;
    result XMLType;
BEGIN
    ctx := DBMS_XMLGEN.NEWCONTEXT('select * from EMP_T');
    result := DBMS_XMLGEN.GETXMLTYPE(ctx);
    DBMS_OUTPUT.PUT_LINE(result);
    DBMS_XMLGEN.CLOSECONTEXT(ctx);
END;
/

47.3.6. NEWCONTEXT

Creates a query statement using the context handle. The context handle is used in the GETXML function to create an XML document.

Details about the NEWCONTEXT function are as follows:

Prototype

DBMS_XMLGEN.NEWCONTEXT 
(
    query IN VARCHAR2
) 
RETURN ctxHandle;

DBMS_XMLGEN.NEWCONTEXT 
(
    query IN SYS_REFCURSOR
) 
RETURN ctxHandle;

Parameter

Parameter Description
query Query or cursor that outputs the data that will be converted into an XML document.

Parameter	Description
query	Query or cursor that outputs the data that will be converted into an XML document.

Example

DECLARE
    ctx dbms_xmlgen.ctxHandle;
BEGIN
    ctx := DBMS_XMLGEN.NEWCONTEXT('select 1 from dual');
    DBMS_XMLGEN.CLOSECONTEXT(ctx);
END;
/

47.3.7. SETMAXROWS

Sets the maximum number of rows that can be processed by GETXML or GETXMLTYPTE.

Details about the SETMAXROWS procedure are as follows:

Prototype

DBMS_XMLGEN.SETMAXROWS 
(
    ctx      IN    ctxHandle,
    maxRows  IN    NUMBER
);

Parameters

Parameter Description
ctx Context handle of the executed query.
maxRows Maximum number of rows.

Parameter	Description
ctx	Context handle of the executed query.
maxRows	Maximum number of rows.

Example

CREATE TABLE EMP_T (EMP_ID NUMBER, EMP_NAME VARCHAR2(16));
INSERT INTO EMP_T VALUES(2015001, 'TOM');
INSERT INTO EMP_T VALUES(2015002, 'DAVID');
INSERT INTO EMP_T VALUES(2015003, 'GEORGE');
DECLARE
    ctx    dbms_xmlgen.ctxHandle;
    result CLOB;
BEGIN
    ctx := DBMS_XMLGEN.NEWCONTEXT('select * from EMP_T');
    DBMS_XMLGEN.SETMAXROWS(ctx, 1);
    result := DBMS_XMLGEN.GETXML(ctx);
    DBMS_OUTPUT.PUT_LINE(result);
    DBMS_XMLGEN.CLOSECONTEXT(ctx);
END;
/

47.3.8. SETNULLHANDLING

Sets NULL data handling method.

Details about the SETNULLHANDLING procedure are as follows:

Prototype

DBMS_XMLGEN.SETNULLHANDLING 
(
    ctx   IN    ctxHandle,
    flag  IN    NUMBER
);

Parameters

Parameter	Description
ctx	Context handle to set NULL data handling method.
flag	Flag options are: DROP_NULLS: Do not output NULL values. NULL_ATTR: Output as NULL ATTRIBUTE. EMPTY_TAG: Output as EMPTY ELEMENT.

Example

CREATE TABLE EMP_T (EMP_ID NUMBER, EMP_NAME VARCHAR2(16));
INSERT INTO EMP_T VALUES(2015001, 'TOM');
INSERT INTO EMP_T VALUES(2015002, NULL);
INSERT INTO EMP_T VALUES(2015003, 'GEORGE');
DECLARE
    ctx    dbms_xmlgen.ctxHandle;
    result CLOB;
BEGIN
    ctx := DBMS_XMLGEN.NEWCONTEXT('select * from EMP_T');
    DBMS_XMLGEN.SETNULLHANDLING(ctx, DBMS_XMLGEN.EMPTY_TAG);
    result := DBMS_XMLGEN.GETXML(ctx);
    DBMS_OUTPUT.PUT_LINE(result);
    DBMS_XMLGEN.CLOSECONTEXT(ctx);
END;
/

47.3.9. SETROWTAG

Sets the row tag name of the XML output from GETXML or GETXMLType function call.

Details about the SETROWTAG procedure are as follows:

Prototype

DBMS_XMLGEN.SETROWTAG 
(
    ctx   IN    ctxHandle,
    rowTagName  IN    VARCHAR2
);

Parameters

Parameter Description
ctx Context handle obtained from NEWCONTEXT function call.
rowTagName Row tag name.

Parameter	Description
ctx	Context handle obtained from NEWCONTEXT function call.
rowTagName	Row tag name.

Example

CREATE TABLE EMP_T (EMP_ID NUMBER, EMP_NAME VARCHAR2(16));
INSERT INTO EMP_T VALUES(2015001, 'TOM');
INSERT INTO EMP_T VALUES(2015002, 'DAVID');
INSERT INTO EMP_T VALUES(2015003, 'GEORGE');
DECLARE
    ctx dbms_xmlgen.ctxHandle;
    result CLOB;
BEGIN
    ctx := DBMS_XMLGEN.NEWCONTEXT('select * from EMP_T');
    DBMS_XMLGEN.SETROWTAG(ctx, 'MYROWTAG');
    result := DBMS_XMLGEN.GETXML(ctx);
    DBMS_OUTPUT.PUT_LINE(result);
    DBMS_XMLGEN.CLOSECONTEXT(ctx);
END;
/

47.3.10. SETROWSETTAG

Sets the row set tag name of the XML output from GETXML or GETXMLType function call.

Details about the SETROWSETTAG procedure are as follows:

Prototype

DBMS_XMLGEN.SETROWSETTAG 
(
    ctx   IN    ctxHandle,
    rowSetTagName  IN    VARCHAR2
);

Parameters

Parameter Description
ctx Context handle obtained from NEWCONTEXT function call.
rowSetTagName Row set tag name.

Parameter	Description
ctx	Context handle obtained from NEWCONTEXT function call.
rowSetTagName	Row set tag name.

Example

CREATE TABLE EMP_T (EMP_ID NUMBER, EMP_NAME VARCHAR2(16));
INSERT INTO EMP_T VALUES(2015001, 'TOM');
INSERT INTO EMP_T VALUES(2015002, 'DAVID');
INSERT INTO EMP_T VALUES(2015003, 'GEORGE');
DECLARE
    ctx dbms_xmlgen.ctxHandle;
    result CLOB;
BEGIN
    ctx := DBMS_XMLGEN.NEWCONTEXT('select * from EMP_T');
    DBMS_XMLGEN.SETROWSETTAG(ctx, 'MYROWSETTAG');
    result := DBMS_XMLGEN.GETXML(ctx);
    DBMS_OUTPUT.PUT_LINE(result);
    DBMS_XMLGEN.CLOSECONTEXT(ctx);
END;
/

47.3.11. SETSKIPROWS

Sets the number of rows to skip in GETXML or GETXMLTYPE.

Details about the SETSKIPROWS procedure are as follows:

Prototype

DBMS_XMLGEN.SETSKIPROWS 
(
    ctx   IN    ctxHandle,
    skipRows  IN    NUMBER
);

Parameters

Parameter Description
ctx Context handle of the executed query.
skipRows Number of rows to skip.

Parameter	Description
ctx	Context handle of the executed query.
skipRows	Number of rows to skip.

Example

CREATE TABLE EMP_T (EMP_ID NUMBER, EMP_NAME VARCHAR2(16));
INSERT INTO EMP_T VALUES(2015001, 'TOM');
INSERT INTO EMP_T VALUES(2015002, 'DAVID');
INSERT INTO EMP_T VALUES(2015003, 'GEORGE');
DECLARE
    ctx dbms_xmlgen.ctxHandle;
    result CLOB;
BEGIN
    ctx := DBMS_XMLGEN.NEWCONTEXT('select * from EMP_T');
    DBMS_XMLGEN.SETSKIPROWS(ctx, 1);
    result := DBMS_XMLGEN.GETXML(ctx);
    DBMS_OUTPUT.PUT_LINE(result);
    DBMS_XMLGEN.CLOSECONTEXT(ctx);
END;
/

Prev		Next
Chapter 46. DBMS_XMLDOM	Home	Chapter 48. DBMS_XPLAN