Example DBMSXMLGEN Generating Complex XML Inputting User Defined Types For
When you input a user-defined type UDT value to DBMS_XMLGEN functions, the user-defined type is mapped to an XML document using canonical mapping. In the canonical mapping, user-defined type's attributes are mapped to XML elements. Attributes with names starting with are mapped to attributes of the preceding element. User-defined types can be used for nesting in the resulting XML document. For example, consider tables, EMP and DEPT To generate a hierarchical view of the data, that is,...
Returning Partial Results
When selecting the results of a large column, you might want to retrieve only a portion of the result and create a URL to the column instead. For example, consider the case of a travel story Web site. If all the travel stories are stored in a table, and users search for a set of relevant stories, you do not want to list each entire story in the result page. Instead, you show the first 100 characters or gist of the story and then return a URL to the full story.This can be done as follows Example...
Searching XML Data with Oracle Text
This chapter explains the use of Oracle Text functionality in indexing and querying XML data. It contains the following sections Searching XML Data with Oracle Text Assumptions Made in This Chapter's Examples Oracle Text Users and Roles Querying with the CONTAINS Operator Using the WITHIN Operator to Narrow Query Down to Document Sections INPATH or HASPATH Operators Search Using XPath-Like Expressions Building a Query Application with Oracle Text Step 1. Create a Section Group Preference Step...
How Does Oracle Enqueue and Dequeue and Process XML Messages
In an OTN document, it says that an Oracle database can enqueue and dequeue XML messages and process them. How does it do this Do I have to use XML SQL Utility XSU in order to insert an XML file into a table before processing it, or can I enqueue an XML file directly, parse it, and dispatch its messages through the AQ process Must I use XML SQL Utility every time I want to INSERT or UPDATE XML data into Oracle9i Database Answer AQ supports enqueing and dequeing objects. These objects can have...
Dequeue Using AQ XML Servlet
You can perform dequeue requests over the Internet using SOAP. See Also Oracle9i Application Developer's Guide - Advanced Queuing for information about receiving AQ messages using SOAP. In the Books0nline scenario, assume that the East shipping application receives AQ messages with a correlation identifier 'RUSH' over the Internet. Example 23-4 Receiving and Dequeuing AQ XML Messages The dequeue request will have the following format lt xml version 1.0 gt lt AQXmlReceive xmlns lt...
Introducing Java DOM and Java Bean APIs for XMLType
Oracle XML DB supports the following Java Application Program Interfaces APIs Java Document Object Model DOM API for XMLType. This is a generic API for client and server, for both XML schema-based and non- schema-based documents. It is implemented using the Java package oracle.xdb.dom. To access XMLType data using JDBC use the class oracle.xdb.XMLType. Java Bean API for XMLType. This is a high performance API for the server for XML schema-based documents. It is implemented using Java package...
Configuring the UriFactory Package to Handle DBUris
The UriFactory, as explained in Creating Instances of UriType Objects with the UriFactory Package on page 12-25, takes a URL and generates the appropriate subtypes of the UriType to handle the corresponding protocol. For HTTP URLs, UriFactory creates instances of the HttpUriType. But when you have an HTTP URL that represents a URI path, it is more efficient to store and process it as a DBUriType instance in the database. The DBUriType processing involves fewer layers of communication and...
Specifying Doctype Limiters to Distinguish Between Tags
Oracle8i release 8.1.5 and higher allow you to specify doctype limiters to distinguish between these tags across doctypes. Simply specify the doctype in parentheses before the tag as follows contact address' address' Now when the XML section group sees an address tag, it will index it as the address section when the document type is contact, or as the email section when the document type is mail.
XMLAgg Function
XMLAgg function is an aggregate function that produces a forest of XML elements from a collection of XML elements. Figure 10-7 describes the XMLAgg syntax. As with XMLConcat , any arguments that are null are dropped from the result. XMLAgg function is similar to the SYS_XMLAGG function except that it returns a forest of nodes, and does not take the XMLFormat parameter. This function can be used to concatenate XMLType instances across multiple rows. Example 10-13 XMLAgg Generating Department...
getContentType Function
getContentType function returns the MIME information for the URL. The HttpUriType de-references the URL and gets the MIME header information. You can use this information to decide whether to retrieve the URL as BLOB or CLOB based on the MIME type. You would treat a Web page with a MIME type of x jpeg as a BLOB, and one with a MIME type of text plain or text html as a CLOB. Example 12-1 Using getContentType and HttpUriType to Return HTTP Headers Getting the content type does not fetch all the...
Oracle XML DB Servlet Example
The following is a simple servlet example that reads a parameter specified in a URL as a path name, and writes out the content of that XML document to the output stream. Example 20-1 Writing an Oracle XML DB Servlet test.java import javax.servlet.http. import javax.servlet. import java.util. import java.io. import javax.naming. import oracle.xdb.dom. public class test extends HttpServlet protected void doGet HttpServletRequest req, HttpServletResponse resp throws ServletException, IOException...
Oracle XML DB Configuration Example
The following is a sample Oracle XML DB configuration file Example A-1 Oracle XML DB Configuration File lt xdbconfig lt protocolconfig gt lt common gt lt extension-mappings gt lt mime-mappings gt lt mime-mapping gt lt mime-mapping gt lt mime-mapping gt lt mime-mapping gt lt mime-mapping gt lt mime-mapping gt lt lang-mappings gt lt lang-mapping gt lt lang gt english lt lang gt lt lang-mapping gt lt lang-mappings gt lt charset-mappings gt lt charset-mappings gt lt encoding-mappings gt lt...
Using XMLType In Triggers
You can use the new and old binds inside triggers to read and modify the XMLType column values. For INSERT and UPDATE statements, you can modify the new value to change the value being inserted. Example 4-39 Creating XMLType Triggers For example, you can write a trigger to change the purchase order if it does not contain a shipping address CREATE OR REPLACE TRIGGER po_trigger BEFORE INSERT OR UPDATE ON po_xml_tab FOR EACH ROW declare if NEW.poDoc.existsnode ' SHIPADDR' 0 then when updating, if...
PLSQL DOM API for XMLType Examples
Example 8-1 Creating and Manipulating a DOM Document This example illustrates how to create a DOMDocument handle for an example element PERSON -- This example illustrates how to create a DOMDocument handle for an example docelem dbms_xmldom.DOMElement node dbms_xmldom.DOMNode nodelist dbms_xmldom.DOMNodelist buf varchar2 2000 var xmltype ' lt PERSON gt lt NAME gt ramesh lt NAME gt lt PERSON gt ' doc dbms_xmldom.newDOMDocument var dbms_xmldom.writetobuffer ndoc, buf docelem...
updateXML and NULL Values
UpdateXML treats NULL values by mapping them to non-existent attribute, element, or text values. For instance if you update node, ' empno text ' with a NULL value, it is treated as if element empno is being removed. Setting an attribute to NULL removes the attribute. There are exceptions to this. The section, NULL Updates When Object Types Are Generated by XML Schema Registration on page 4-36 discusses this further. In this case, when you update an element and pass a NULL value to it, the...
XMLSEQUENCE Function
The XMLSequence function has two forms The first form inputs an XMLType instance and returns a VARRAY of top-level nodes. This form can be used to shred XML fragments into multiple rows. The second form takes as input a REFCURSOR argument, with an optional instance of the XMLFormat object and returns the varray of XMLTypes corresponding to each row of the cursor. This form can be used to construct XMLType instances from arbitrary SQL queries. Note that in this release, this use of XMLFormat...
Location Path Examples Using Unabbreviated Syntax
Table C-1 lists examples of location paths using the unabbreviated syntax. Table C-1 XPath Location Path Examples Using Unabbreviated Syntax Table C-1 XPath Location Path Examples Using Unabbreviated Syntax Selects the para element children of the context node Selects all element children of the context node Selects all text node children of the context node Selects all the children of the context node, whatever their Selects the name attribute of the context node Selects all the attributes of...
Generating XML from Oraclei Database Using DBMSXMLGEN
DBMS_XMLGEN creates XML documents from any SQL query by mapping the database query results into XML. It gets the XML document as a CLOB or XMLType. It provides a fetch interface whereby you can specify the maximum rows and rows to skip. This is useful for pagination requirements in Web applications. DBMS_ XMLGEN also provides options for changing tag names for ROW, ROWSET, and so on. The parameters of the package can restrict the number of rows retrieved, the enclosing tag names. To summarize,...
Example SYSXMLGEN Converting a UserDefined Type to XML
When you input a user-defined type value to SYS_XMLGEN , the user-defined type gets mapped to an XML document using a canonical mapping. In the canonical mapping the user-defined type's attributes are mapped to XML elements. Any type attributes with names starting with are mapped to an attribute of the preceding element. User-defined types can be used to get nesting within the result XML document. Using the same example as given in the DBMS_XMLGEN section Example 10-18, DBMS_XMLGEN Generating...
Using INPATH Operator for Path Searching in XML Documents
You can use INPATH operator to perform path searching in XML documents. Table 7-2 summarizes the ways you can use the INPATH operator for path searching. Table 7-2 Path Searching XML Documents Using the INPATH Operator Table 7-2 Path Searching XML Documents Using the INPATH Operator Virginia INPATH STATE Virginia INPATH STATE Finds all documents where the word virginia appears between lt STATE gt and lt STATE gt . The STATE element can appear at any level of the document structure. Virginia...
Internet Data Access Presentation IDAP
You can access AQ over the Internet by using Simple Object Access Protocol SOAP . Internet Data Access Presentation IDAP is the SOAP specification for AQ operations. IDAP defines XML message structure for the body of the SOAP request. An IDAP-structured message is transmitted over the Internet using transport protocols such as HTTP or SMTP. IDAP uses the text xml content type to specify the body of the SOAP request. XML provides the presentation for IDAP request and response messages as follows...
XPath Expression Rewrites for existsNode
existsNode returns a numerical value 0 or 1 indicating if the XPath returns any nodes text or element nodes . Based on the mapping discussed in the earlier section, an existsNode simply checks if a scalar element is non-NULL in the case where the XPath targets a text node or a non-scalar node and checks for the existance of the element using the SYS_XDBPD otherwise. If the SYS_XDBPD attribute is absent, then the existance of a scalar node is determined by the NULL information for the scalar...
Rewrite for extract
extract retrieves the results of XPath as XML. The rewrite for extract is similar to that of extractValue for those Xpath expressions involving text nodes. Extract Mapping with Document Order Maintained Table 5-15 shows the mapping of various XPath in the case of extract when document order is preserved that is, when SYS_XDBPD exists and maintainDOM true in the schema document . Note The examples show XMLElement and XMLForest with an empty alias string to indicate that you create a XML instance...
getClob and getBlob Functions
In the case of DBUri, scalar binary data is handled specially. In the case of a getClob call on a DBUri ' SCOTT DBTAB ROW B text ' where B is a BLOB column, the data is converted to HEX and sent out. In the case of a getBlob call, the data is returned in binary form. However, if an XML document is requested, as in ' SCOTT DBTAB ROW B', then the XML document will contain the binary in HEX form. XDBUriType is a new subtype of UriType. It provides a way to expose documents in the ORACLE XML DB...
existsNode Function
The syntax for the existsNode function is described in Figure 4-2 and also as follows existsNode XMLType_instance IN XMLType, XPath_string IN VARCHAR2, namespace_string IN varchar2 null RETURN NUMBER existsNode function on XMLType checks if the given XPath evaluation results in at least a single XML element or text node. If so, it returns the numeric value 1, otherwise, it returns a 0. Namespace can be used to identify the mapping of prefix es specified in the XPath_string to the corresponding...
XMLType Queue Payloads
You can create queues with payloads that contain XMLType attributes. These can be used for transmitting and storing messages that contain XML documents. By defining Oracle objects with XMLType attributes, you can do the following Store more than one type of XML document in the same queue. The documents are stored internally as CLOBs. Selectively dequeue messages with XMLType attributes using the operators existsNode , extract , and so on. Define transformations to convert Oracle objects to...
Example DBMSXMLGEN Generating Simple XML with Pagination
Instead of generating all the XML for all rows, you can use the fetch interface that DBMS_XMLGEN provides to retrieve a fixed number of rows each time. This speeds up response time and also can help in scaling applications that need a DOM API on the resulting XML, particularly if the number of rows is large. The following example illustrates how to use DBMS_XMLGEN to retrieve results from table scott.emp -- create a table to hold the results CREATE TABLE temp_clob_tab result clob qryCtx...
Sample DBMSXMLGEN Query Result
The following shows a sample result from executing a select from scott.emp query on a database lt xml version 1.0 gt lt EMPNO gt 30 lt EMPNO gt lt ENAME gt Scott lt ENAME gt lt SALARY gt 20000 lt SALARY gt lt ROW gt lt ROW gt lt EMPNO gt 30 lt EMPNO gt lt ENAME gt Mary lt ENAME gt lt AGE gt 40 lt AGE gt lt ROW gt lt ROWSET gt The result of the getXML using DBMS_XMLGen package is a CLOB. The default mapping is as follows Every row of the query result maps to an XML element with the default tag...
Defining Your Own Default Table Storage for XML SchemaBased Documents
There is an exception to this storage paradigm when storing XML schema-based XML documents. When an XML schema is registered with Oracle XML DB you can define a default storage table for each root element defined in the XML schema. You can define your own default storage tables by adding an xdb defaultTable attribute to the definition of the top level element When the schema is registered, Oracle XML DB establishes a link between the Repository and the default tables defined by your XML schema....
Searching Conference Proceedings Example jsp
Here is the full jsp example illustrating how you can use Oracle Text to create an online XML-based Conference Proceedings look-up application. lt page import java.sql. , oracle.jsp.dbutil. gt lt jsp useBean id name scope request gt lt jsp setProperty name name property value param query gt lt jsp useBean gt String java.util.Properties info new java.util.Properties Connection conn null ResultSet rset null Statement stmt null if name.isEmpty gt lt html gt lt title gt OOW Paper Search lt title gt...
Creating Tables with XMLType Columns
See Figure 21-22 shows the Create Table General page. To create a table with XMLType columns follow these steps 1. From the Create Table property sheet, enter the desired name of the table you are creating on the General page. 2. Select the table owner from the drop-down list Schema. Leave Tablespace at the default setting. 3. Select Define Columns. 4. Enter the Name. Enter the Datatype select XMLType from the drop-down list. The XMLType Options dialog window appears. See Figure 21-23. Figure...
Dbmsxdbversion
DBMS_XDB_VERSION along with DBMS_XDB implement the Oracle XML DB versioning API. Table F-10 summarizes the DBMS_XDB_VERSION functions and procedures. See Also Chapter 14, Oracle XML DB Versioning Table F-10 DBMS_XDB_VERSION Functions and Procedures Table F-10 DBMS_XDB_VERSION Functions and Procedures Turns a regular resource whose pathname is given Checks out a VCR before updating or deleting it. Checks in a checked-out VCR and returns the resource id of the newly-created version. Checks in a...
Using JNDI Contexts XDBContextFactory
Oracle XML DB implementation of JNDI provides a context factory class XDBContextFactory . This implements the initialContextFactory interface used by JNDI to create initial contexts. The initial context describes the starting point in the Repository hierarchy from which all name resolutions happens, with the exception of absolute names which begin with . Oracle XML DB does not differentiate between initialContexts and Contexts since it stores the initial path of the application in an...
PLSQL XSLT Processor API DBMSXSLPROCESSOR Methods
The methods in PL SQL XSLT Processor API DBMS_XSLPROCESSOR use two PL SQL types specific to the XSL Processor implementation. These are the Processor type and the Stylesheet type. Table 8-4 lists PL SQL XSLT Processor DBMS_XSLPROCESSOR methods. Note There is no space between the method declaration and the arguments, for example processXSL p Processor, ss Stylesheet, xmldoc DOMDocument Table 8-4 DBMS_XSLPROCESSOR Methods Page 1 of 2 Table 8-4 DBMS_XSLPROCESSOR Methods Page 1 of 2 Argument p...
PLSQL XSLT Processor for XMLType Example
Example 8-5 Transforming an XML Document Using an XSL Stylesheet This example transforms an XML document by using the processXSL call.Expect the following output XML with tags ordered based on tag name lt empno gt 1 lt empno gt lt fname gt robert lt fname gt lt job gt engineer lt job gt lt lname gt smith lt lname gt lt sal gt 1000 lt sal gt lt emp gt indomdoc dbms_xmldomi .domdocument xsltdomdoc dbms_xmldom .domdocument outdomdocf dbms_xmldom .domdocumentfragment dbms_xslprocessor.processor...
WC XPath Support for Extraction Condition Checks and Updates
Oracle9i Release 1 9.0.1 provided the extract and existsNode functions on XMLType objects. These allowed XPath-based queries against XML documents. This release provides additional support as follows extract , existsNode , and extractValue now allow for a namespace-based operation. extract , existsNode , and extractValue support the full XPath function set, including axis operators. updateXML function new replaces part of the XMLType DOM by using XPath as a locator.
Using HttpUriType and DBUriType
HttpUriType and DBUriType are subtypes of UriType and implement the functions for HTTP and DBUri references respectively. Note HttpUriType cannot store relative HTTP references in this release. Example 12-14 DBUriType Creating DBUri References The following example creates a table with a column of type DBUriType and assigns a value to it. CREATE TABLE DBURiTab DBUri DBUriType, dbDocName VARCHAR2 2000 -- insert values into it INSERT INTO DBUriTab VALUES SELECT e.DBUri.getCLOB from DBUriTab e
HttpUriType Functions
Use HttpUriType to store references to data that can be accessed through the HTTP protocol. HttpUriType uses the UTL_HTTP package to fetch the data and hence the session settings for the package can also be used to influence the HTTP fetch using this mechanism. Table 12-2 lists the HttpUriType member functions. Table 12-2 HttpUriType Member Functions Table 12-2 HttpUriType Member Functions Returns the value pointed to by the URL as a character LOB value. The character encoding is the same as...
PLSQL DOM API for XMLType DBMSXMLDOM Calling Sequence
Figure 8-1 illustrates the PL SQL DOM API for XMLType DBMS_XMLDOM calling sequence. You can create a DOM document DOMDocument from an existing XMLType or as an empty document. 1. The newDOMDocument procedure processes the XMLTYpe or empty document. This creates a DOMDocument. 2. You can use the DOM API methods such as, createElement, createText, createAttribute, and createComment, and so on, to traverse and extend the DOM tree. See Table 8-1 for a full list of available methods. 3. The results...
XMLType API
XMLType is a system-defined opaque type for handling XML data. XMLType has predefined member functions to extract XML nodes and fragments. You can create columns of XMLType and insert XML documents into them. You can also generate XML documents as XMLType instances dynamically using SQL functions, SYS_ XMLGEN and SYS_XMLAGG, the PL SQL package DBMS_XMLGEN, and the SQLX functions. Table F-1 lists the XMLType API functions. See Also Oracle9i XML API Reference - XDK and Oracle XML DB Chapter 4,...
Querying XML Data Use CONTAINS or existsNode
Oracle9i Release 1 9.0.1 introduced the Oracle Text PATH_SECTION_GROUP, INPATH , and HASPATH query operators. These allow you to do XPath-like text query searches on XML documents using the CONTAINS operator. CONTAINS, however, supports only a subset of XPath functionality. Also, there are important semantic differences between the CONTAINS operator and the existsNode function. The existsNode, extract and extractValue SQL functions and the corresponding member functions of XMLType provide full...
Example NonValid Uses of extractValue
-- 0RA-19025 EXTRACTVALUE returns value of only one node -- ORA-19025 EXTRACTVALUE returns value of only one node Example 3-9 Using extractValue in the WHERE Clause extractValue can also be used in the WHERE clause of a SELECT, UPDATE, or DELETE statement. This makes it possible to perform joins between XMLType tables or tables containing XMLType columns and other relational tables or XMLType tables. The following query shows you how to use extractValue in both the SELECT list and the WHERE...
Operations You Can Perform Using UNDERPATH and EQUALSPATH
You can perform the following operations using UNDER_PATH and EQUALS_PATH - List the directory given by the path name Given a condition, containing UNDER_PATH operator or other SQL operators See the Using the Resource View and Path View API and EQUALS_PATH.
Example Using XSQL Servlets and Nested XMLAgg Functions to Aggregate the
The following example uses nested xmlagg functions to aggregate the results of a dynamically-constructed XML document containing departments and nested employees into a single XML result document, wrapped in a lt DepartmentList gt element lt xsql include-xml connection orcl92 select XmlElement DepartmentList, XmlAgg XmlAttributes deptno as Id , XmlForest dname as Name , select Xm Element Employees, XmlAgg XmlAttributes empno as Id , XmlForest ename as Name, sal as Salary, job as Job .getClobVal...
DOM Fidelity 1
Document Object Model DOM fidelity is the concept of retaining the structure of a retrieved XML document, compared to the original XML document, for DOM traversals. DOM fidelity is needed to ensure the accuracy and integrity of XML documents stored in Oracle XML DB. See Also Setting the SQLInLine Attribute to FALSE for Out-of-Line Storage on page 5-38 How Oracle XML DB Ensures DOM Fidelity with XML Schema All elements and attributes declared in the XML schema are mapped to separate attributes...
Example XMLColAttVal Generating an Emp Element Per Employee with Name Attribute
This example generates an Emp element for each employee, with a name attribute and elements with the employee's start date and department as the content. SELECT ' ' e.lname AS name , XMLCOLATTVAL e.hire, e.dept AS department AS result FROM employees e This query might produce the following XML result lt Emp name John Smith gt lt column lt column lt Emp gt lt Emp name Mary Martin gt lt column lt column lt Emp gt lt Emp name Samantha Stevens gt XMLColAttVal is an Oracle SQLX extension function.
Example XMLType View Creating XMLType View Using XMLElement Function
The following statement creates an XMLType view using XMLElement generation function DROP TABLE employees CREATE TABLE employees empno number 4 , fname varchar2 20 , lname varchar2 20 , hire date, salary number 6 2100, 'John', 'Smith', Date'2000-05-24', 30000 INSERT INTO employees VALUES 2200, 'Mary', 'Martin', Date'1996-02-01', 30000 CREATE OR REPLACE VIEW Emp_view OF XMLTYPE WITH OBJECT ID AS SELECT XMLELEMENT Emp, XMLAttributes empno , XMLForest e.fname ' ' e.lname AS name, e.hire AS...
XMLForest Function
XMLForest function produces a forest of XML elements from the given list of arguments. The arguments may be value expressions with optional aliases. Figure 10-4 describes the XMLForest syntax. The list of value expressions are converted to XML format. For a given expression, if the AS clause is omitted, the fully escaped form of the column name is used as the name of the enclosing tag of the element. For an object type or collection, the AS clause is mandatory, and for other types, it can still...
PLSQL Parser API for XMLType DBMSXMLPARSER
XML documents are made up of storage units, called entities, that contain either parsed or unparsed data. Parsed data is made up of characters, some of which form character data and some of which form markup. Markup encodes a description of the document's storage layout and logical structure. XML provides a mechanism for imposing constraints on the storage layout and logical structure. A software module called an XML parser or processor reads XML documents and provides access to their content...
Example Using updateXML to Replace Contents of a Node Tree Associated with
In this example updateXML replaces the contents of the node tree associated with the element identified by the XPath expression ' PurchaseOrders LineItems LineItem 2 '. Note In this example, since the replacement value is a Node tree, the third argument to the updateXML function is supplied as an instance of the XMLType datatype. UPDATE XMLTABLE t SET value t updateXML value t , xmltype ' lt LineItem ItemNumber 4 gt lt Description gt Andrei Rublev lt Description gt lt Part Id 715515009928...