Skip Headers
Oracle® Database PL/SQL Packages and Types Reference
11g Release 2 (11.2)

Part Number E25788-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub
ARPLS801

177 DBMS_XMLSTORE

DBMS_XMLSTORE provides the ability to store XML data in relational tables.

See Also:

Oracle XML DB Developer's Guide

This chapter contains the following sections:

ARPLS70080

Using DBMS_XMLSTORE

ARPLS73537

Security Model

Owned by XDB, the DBMS_XMLSTORE package must be created by SYS or XDB. The EXECUTE privilege is granted to PUBLIC. Subprograms in this package are executed using the privileges of the current user.

ARPLS70081

Types

ARPLS70082Table 177-1 Types of DBMS_XMLSTORE

Type Description

ctxType

The type of the query context handle. This is the return type of NEWCONTEXT.


ARPLS70083

Summary of DBMS_XMLSTORE Subprograms

ARPLS70084Table 177-2 DBMS_XMLSTORE Package Subprograms

Method Description

CLEARKEYCOLUMNLIST

Clears the key column list.

CLEARUPDATECOLUMNLIST

Clears the update column list.

CLOSECONTEXT

It closes/deallocates a particular save context.

DELETEXML

Deletes records specified by data from the XML document, from the table specified at the context creation time.

INSERTXML

Inserts the XML document into the table specified at the context creation time.

NEWCONTEXT

Creates a save context, and returns the context handle.

SETKEYCOLUMN

This method adds a column to the key column list.

SETROWTAG

Names the tag used in the XML document., to enclose the XML elements corresponding to the database.

SETUPDATECOLUMN

Adds a column to the "update column list".

UPDATEXML

Updates the table given the XML document.


ARPLS70085

CLEARKEYCOLUMNLIST

Clears the key column list.

ARPLS70086Syntax

PROCEDURE clearKeyColumnList(
   ctxHdl IN ctxType);
Parameter IN / OUT Description
ctxHdl (IN) Context handle.

ARPLS70087

CLEARUPDATECOLUMNLIST

Clears the update column list.

ARPLS70088Syntax

PROCEDURE clearUpdateColumnList(
   ctxHdl IN ctxType);
Parameter IN / OUT Description
ctxHdl (IN) Context handle.

ARPLS70089

CLOSECONTEXT

Closes/deallocates a particular save context.

ARPLS70090Syntax

PROCEDURE closeContext(
   ctxHdl IN ctxType);
Parameter IN / OUT Description
ctxHdl (IN) Context handle.

ARPLS70091

DELETEXML

Deletes records specified by data from the XML document from the table specified at the context creation time, and returns the number of rows deleted.

Syntax Description
FUNCTION deleteXML(

   ctxHdl IN ctxPType,

   xDoc IN VARCHAR2)

RETURN NUMBER;

Uses a VARCHAR2 type for the xDoc parameter.
FUNCTION deleteXML(

   ctxHdl IN ctxType,

   xDoc IN CLOB)

RETURN NUMBER;

Uses a CLOB type for the xDoc parameter.
FUNCTION deleteXML(

   ctxHdl IN ctxType,

   xDoc IN XMLType)

RETURN NUMBER;

Uses an XMLType type for the xDoc parameter.


Parameter IN / OUT Description
ctxHdl (IN) Context handle.
xDoc (IN) String containing the XML document.

ARPLS70092

INSERTXML

Inserts the XML document into the table specified at the context creation time, and returns the number of rows inserted.

Note that if a user passes an XML file for insertXML to DBMS_XMLSTORE which contains extra elements (ones that do not match up to any columns in the table), Oracle will try to insert into those columns unless SETUPDATECOLUMN is used. The use of setUpdateColumn is optional only if the elements in the XML file match up to the columns in the table.

Syntax Description
FUNCTION insertXML(

   ctxHdl IN ctxType,

   xDoc IN VARCHAR2)

RETURN NUMBER;

Passes in the xDoc parameter as a VARCHAR2.
FUNCTION insertXML(

   ctxHdl IN ctxType,

   xDoc IN CLOB)

RETURN NUMBER;

Passes in the xDoc parameter as a CLOB.
FUNCTION insertXML(

   ctxHdl IN ctxType,

   xDoc IN XMLType)

RETURN NUMBER;

Passes in the xDoc parameter as an XMLType.


Parameter IN / OUT Description
ctxHdl (IN) Context handle.
xDoc (IN) String containing the XML document.

ARPLS70093

NEWCONTEXT

Creates a save context, and returns the context handle.

ARPLS70094Syntax

FUNCTION newContext(
   targetTable IN VARCHAR2)
RETURN ctxType;
Parameter IN / OUT Description
targetTable (IN) The target table into which to load the XML document.

ARPLS70095

SETKEYCOLUMN

This method adds a column to the "key column list". The value for the column cannot be NULL. In case of update or delete, the columns in the key column list make up the WHERE clause of the statement. The key columns list must be specified before updates can complete; this is optional for delete operations

ARPLS70096Syntax

PROCEDURE setKeyColumn( 
   ctxHdl IN ctxType,
   colName IN VARCHAR2);
Parameter IN / OUT Description
ctxHdl (IN) Context handle.
colName (IN) Column to be added to the key column list; cannot be NULL.

ARPLS70097

SETROWTAG

Names the tag used in the XML document, to enclose the XML elements corresponding to databse records.

ARPLS70098Syntax

PROCEDURE setRowTag(
   ctxHdl IN ctxType,
   tag IN VARCHAR2);
Parameter IN / OUT Description
ctxHdl (IN) Context handle.
tag (IN) Tag name.

ARPLS70099

SETUPDATECOLUMN

Adds a column to the update column list. In case of insert, the default is to insert values to all the columns in the table; on the other hand, in case of updates, the default is to only update the columns corresponding to the tags present in the ROW element of the XML document. When the update column list is specified, the columns making up this list alone will get updated or inserted into.

Note that if a user passes an XML file for INSERTXML to DBMS_XMLSTORE which contains extra elements (ones that do not match up to any columns in the table), Oracle will try to insert into those columns unless setUpdateColumn is used. The use of setUpdateColumn is optional only if the elements in the XML file match up to the columns in the table.

ARPLS70100Syntax

PROCEDURE setUpdateColumn( 
   ctxHdl IN ctxType,
   colName IN VARCHAR2);
Parameter IN / OUT Description
ctxHdl (IN) Context handle.
colName (IN) Column to be added to the update column list.

ARPLS70101

UPDATEXML

Updates the table specified at the context creation time with data from the XML document, and returns the number of rows updated. The options are described in the following table.

Syntax Description
FUNCTION updateXML(

   ctxHdl IN ctxType,

   xDoc IN VARCHAR2)

RETURN NUMBER;

Passes in the xDoc parameter as a VARCHAR2.
FUNCTION updateXML(

   ctxHdl IN ctxType,

   xDoc IN CLOB)

RETURN NUMBER;

Passes in the xDoc parameter as a CLOB.
FUNCTION updateXML(

   ctxHdl IN ctxType,

   xDoc IN XMLType)

RETURN NUMBER;

Passes in the xDoc parameter as a XMLType.


Parameter IN / OUT Description
ctxHdl (IN) Context handle.
xDoc (IN) String containing the XML document.

Reader Comment

   

Comments, corrections, and suggestions are forwarded to authors every week. By submitting, you confirm you agree to the terms and conditions. Use the OTN forums for product questions. For support or consulting, file a service request through My Oracle Support.

Hide Navigation

Quick Lookup

Database Library · Master Index · Master Glossary · Book List · Data Dictionary · SQL Keywords · Initialization Parameters · Advanced Search · Error Messages

Main Categories

This Page

This Document

New and changed documents:
RSS Feed HTML RSS Feed PDF