13.6 Oracle Implementation of Semistructured Data

Oracle supports both JSON and XML data, being able to accept documents in either format, allowing documents in both formats to be stored in Oracle databases, and producing output in either format from Oracle databases.

13.6.1 JSON in Oracle

Oracle supports JSON in relational databases, extending the traditional relational capabilities of using SQL. Oracle provides access to JSON data using several Simple Oracle Document Access (SODA) APIs. These APIs allow applications to create, store, and access JSON data in a schemaless fashion, without specifying how the documents are structured or stored in the database, and without using SQL. Current SODA implementations include SODA for Java, Python, C, Node.js, PL/SQL, and REST (Representational State Transfer). The latter can be used with any language that can include HTTP requests. Details about these APIs can be found in the Oracle documentation on SODA. Our discussion will focus on using SQL with JSON. Oracle has added new capabilities and features with current versions, creating some differences between releases that are noted in the following discussion.

Storing and Querying Small JSON Documents

JSON data can be stored in any column that supports character data. If the documents to be stored are small, VARCHAR2 can be used. FIGURE 13.12(A) shows a table, CUSTOMERS1, that consists of a primary key and a single VARCHAR2 type column that stores customer data. To ensure that only well-formed JSON data is stored in the CUSTOMERS1_DATA column, we add an IS JSON constraint for that column.

FIGURE 13.12 Storing and Querying Small JSON Documents

Line 1. CREATE TABLE CUSTOMERS 1 comma open parentheses
Line 2. CUSTOMERS 1 underscore I D NUMBER GENERATED AS IDENTITY comma
Line 3. CUSTOMERS 1 underscore DATA VARCHAR 2 comma open parentheses 4000 comma close parentheses NOT NULL comma close parentheses comma semicolon.
Line 4. ALTER TABLE CUSTOMERS 1
Line 5. ADD CHECK comma open parentheses CUSTOMERS 1 underscore DATA IS J S O N comma close parentheses comma semicolon.

(A) Creating a Table to Store Small JSON Documents

Line 1. INSERT INTO CUSTOMERS 1 comma open parenthesesCUSTOMERS1 underscore DATA comma close parentheses
Line 2. VALUES comma open parentheses open curly brace
Line 3. double quotes NAME double quotes colon double quotes World Wide Travel Agency double quotes comma
Line 4. double quotes ADDRESS double quotes colon open curly brace double quotes STREET double quotes colon double quotes 10 Main Street double quotes comma double quotes CITY double quotes colon double quotes New York double quotes comma
Line 5. double quotes STATE double quotes colon double quotes N Y double quotes comma double quotes ZIP double quotes colon double quotes 10001 double quotes close curly brace comma
Line 6. double quotes TELEPHONE double quotes colon open curly brace double quotes AREA CODE double quotes colon double quotes 212 double quotes comma double quotes NUMBER double quotes colon double quotes 123 4567 double quotes close curly brace comma
Line 7. double quotes TYPE double quotes colon double quotes CORPORATE double quotes comma
Line 8. double quotes STATUS double quotes colon double quotes ACTIVE double quotes close curly brace comma close parentheses comma semicolon.

(B) Inserting JSON Data as Character Data into a Relational Table

Line 1. SELECT asterisk FROM CUSTOMERS 1 comma semicolon.
Line 2. SELECT C.CUSTOMERS 1 underscore DATA dot NAME comma
Line 3. C.CUSTOMERS 1 underscore DATA.ADDRESS dot ZIP
Line 4. FROM CUSTOMERS 1 C comma semicolon.

(C) Retrieving JSON Data

Data can be inserted with the usual INSERT command. The text data in JSON format is assigned in its native form to the VARCHAR2 column, as shown in FIGURE 13.12(B). Because of the IS JSON constraint, Oracle checks that the data is in valid JSON form.

Data can be retrieved using the SQL SELECT command. The first SELECT statement shown in FIGURE 13.12(C) can be used to retrieve the data, including labels, as a single string. However, because we added the IS JSON constraint, Oracle can treat the data as JSON data, and we can use dot notation to access individual elements of the JSON column, similar to the method used in Chapter 9 for OR data. The second query in Figure 13.12 will return only the zip code. When JSON field names such as ZIP are used with dot notation, it is not necessary to enclose them in double quotes. However, unlike SQL, JSON is case-sensitive so the case must match that used in the JSON document. In this example, we cannot write zip in place of ZIP, or name in place of NAME, as we normally could in Oracle.

Storing and Querying Large JSON Documents

Because the maximum size of VARCHAR2 data is relatively small, large JSON documents can be stored using a LOB type, usually as BLOB, which uses less storage than CLOB and stores the document as is without character-set conversion. FIGURE 13.13(A) shows how to create a CUSTOMER_JSON table with a column, CUSTOMER_DATA, that stores JSON data as BLOB.

FIGURE 13.13 Storing and Querying Large JSON Documents

Line 1. CREATE TABLE CUSTOMERS underscore J S O N open parentheses
Line 2. CUSTOMER underscore I D NUMBER PRIMARY KEY comma
Line 3. CUSTOMER underscore DATA BLOB NOT NULL
Line 4. CONSTRAINT C U S T underscore DATA underscore J S O N CHECK open parentheses CUSTOMER underscore DATA IS J S O N close parentheses
Line 5. close parentheses comma semicolon.

(A) Creating a Table to Store Large JSON Documents Using BLOB Datatype

Line 1. Hyphen, hyphen Note colon requires Oracle 21 c or later version
Line 2. CREATE TABLE CUSTOMERS 2 underscore J S O N open parentheses
Line 3. CUSTOMER 2 underscore I D NUMBER PRIMARY KEY comma
Line 4. CUSTOMER 2 underscore DATA J S O N NOT NULL
Line 5. close parentheses comma semicolon.

(B) Creating a Table to Store Large JSON Documents Using JSON Datatype

Line 1. INSERT INTO CUSTOMERS underscore J S O N
Line 2. VALUES open parentheses 1 comma U T L underscore RAW dot CAST underscore TO underscore RAW open parentheses single quote open curly brace
Line 3. double quotes NAME double quotes colon double quotes World Wide Travel Agency double quotes comma
Line 4. double quotes ADDRESS double quotes colon open curly brace double quotes STREET double quotes colon double quotes 10 Main Street double quotes comma double quotes CITY double quotes colon double quotes New York double quotes comma
Line 5. double quotes STATE double quotes colon double quotes NY double quotes comma double quotes ZIP double quotes colon double quotes 10001 double quotes close curly brace comma
Line 6. double quotes TELEPHONE double quotes colon open curly brace double quotes AREA CODE double quotes colon double quotes 212 double quotes comma double quotes NUMBER double quotes colon double quotes 123 4567 double quotes close curly brace comma
Line 7. double quotes TYPE double quotes colon double quotes CORPORATE double quotes comma
Line 8. double quotes STATUS double quotes colon double quotes ACTIVE double quotes close curly brace single quote close parentheses close parentheses comma semicolon.

(C) Inserting JSON Data into a BLOB Column of a Relational Table

Line 1. INSERT INTO CUSTOMERS underscore JSON
Line 2. VALUES open parentheses 2 comma U T L underscore RAW dot CAST underscore TO underscore RAW open parentheses single quote open curly brace
Line 3. double quotes NAME double quotes colon double quotes Mary Jones double quotes comma
Line 4. double quotes ADDRESS double quotes colon open curly brace double quotes STREET double quotes colon double quotes 10 Main Street double quotes comma double quotes CITY double quotes colon double quotes New York double quotes comma double quotes STATE double quotes colon double quotes N Y double quotes comma double quotes ZIP double quotes colon double quotes 10001 double quotes close curly brace comma
Line 5. double quotes TELEPHONE double quotes colon open curly brace double quotes AREA CODE double quotes colon double quotes 212 double quotes comma double quotes NUMBER double quotes colon double quotes 123 4567 double quotes close curly brace comma
Line 6. double quotes TYPE double quotes colon double quotes INDIVIDUAL double quotes comma
Line 7. double quotes STATUS double quotes colon double quotes ACTIVE double quotes comma
Line 8. double quotes ORDERS double quotes colon open square bracket open curly brace double quotes ORDER NUMBER double quotes colon 1001 comma double quotes ORDER DATE double quotes colon double quotes 2022 hyphen 0 2 hyphen 0 6 double quotes comma
Line 9. double quotes ITEMS double quotes colon
Line 10. open square bracket open curly brace double quotes ITEM NAME double quotes colon double quotes PEN double quotes comma double quotes PRICE double quotes colon double quotes 15 point 9 9 double quotes comma double quotes QUANTITY double quotes colon double quotes 1 double quotes close curly brace comma
Line 11. open curly brace double quotes ITEM NAME double quotes colon double quotes DESK LAMP double quotes comma double quotes PRICE double quotes colon double quotes 34 point 9 8 double quotes comma double quotes QUANTITY double quotes colon double quotes 1 double quotes close curly brace close square bracket close curly brace comma
Line 12. open curly brace double quotes ORDER NUMBER double quotes colon 1003 comma double quotes ORDERDATE double quotes colon double quotes 2022 hyphen 02 hyphen 08 double quotes comma
Line 13. double quotes ITEMS double quotes colon
Line 14. open square bracket open curly brace double quotes ITEM NAME double quotes colon double quotes FILING CABINET double quotes comma double quotes PRICE double quotes colon double quotes 45 point 0 0 double quotes comma double quotes QUANTITY double quotes colon double quotes 1 double quotes close curly brace close square bracket close curly brace close square bracket close curly brace
Line 15. single quote close parentheses close parentheses comma semicolon.

(D) Inserting an Array with a Nested Array in a JSON Column Stored as BLOB

Starting with Oracle version 21, the JSON datatype can be used instead of BLOB in the CUSTOMER_DATA column specification. In that case, the IS JSON check is not needed because Oracle will apply the check automatically to validate the JSON input. FIGURE 13.13(B) shows how to specify the JSON datatype.

Inserting JSON data into a column of JSON type is straightforward, requiring no data conversion, and using the same form as that for inserting VARCHAR2 data shown in Figure 13.12(B). To insert data when the BLOB datatype is used, the JSON data must be converted from character to binary before storing, using UTL_RAW.CAST_TO_RAW, as shown in FIGURE 13.13(C), or using Oracle’s TO_BLOB function.

JSON arrays can also be stored in Oracle tables. FIGURE 13.13(D) demonstrates how to insert a nested array of orders within a customer record if the BLOB datatype is used. The same command, without the UTL_RAW.CAST_TO_RAW (), is used if the table was created using the JSON datatype. Note that this customer has two orders, so we have an array of orders, ORDERS, to store them, but each order can have any number of items, so there is a nested array, ITEMS, within each order. Dates in JSON should be written as either "YYYY-MM-DD" or YYYYMMDD, to conform to the ISO 8601 standard, although other formats can be defined.

To insert a large number of records, it is impractical to enter them individually. JSON files can be stored in a directory (usually called a landing area for the data) on the database server machine. The user creates an SQL*Loader control file (with extension .crl), giving the name of the table where the data is to be stored and other details about the file contents, and a second data file (with extension .data) with the names and location of all the JSON files in the landing area that are to be stored in the table, and stores both of these files in the same directory as the JSON files. Finally, the loader is called, passing the names of the control file and the data file as parameters. Details and examples of this process can be found in the online Oracle documentation.

FIGURE 13.14 shows how to retrieve JSON data. Optional conditions can be added to the SELECT command as usual, as shown in Figure 13.14(A). When dot notation is employed, the same SELECT command can be used regardless of whether the data is stored as VARCHAR2, BLOB, or JSON.

FIGURE 13.14 JSON Queries

Line 1 dot SELECT C dot CUSTOMER underscore DATA dot NAME comma C dot CUSTOMER underscore DATA dot TYPE
Line 2 dot FROM CUSTOMERS underscore J S O N C
Line 3 dot WHERE C dot CUSTOMER underscore DATA dot STATUS equals single quote ACTIVE single quote comma semicolon.

(A) Retrieving Data from a JSON Column

Line 1 dot SELECT asterisk FROM CUSTOMERS underscore J S O N comma semicolon.

(B) Query on JSON BLOB Data Returning Binary

Line 1 dot SELECT C dot CUSTOMER underscore DATA dot NAME comma
Line 2 dot C dot CUSTOMER underscore DATA dot ORDERS open square bracket 0 close square bracket dot ORDER NUMBER comma
Line 3 dot C dot CUSTOMER underscore DATA dot ORDERS open square bracket 0 close square bracket dot ORDER DATE comma
Line 4 dot C dot CUSTOMER underscore DATA dot ORDERS open square bracket 0 close square bracket dot ITEMS dot ITEM NAME
Line 5 dot FROM CUSTOMERS underscore J S O N C
Line 6 dot WHERE CUSTOMER underscore I D equals 2 comma semicolon.

(C) Using Dot Notation with Arrays

Line 1 dot SELECT C dot CUSTOMER underscore I D comma C dot CUSTOMER underscore DATA dot NAME comma
Line 2 dot C dot CUSTOMER underscore DATA dot ORDERS open square bracket 0 close square bracket dot ORDER NUMBER comma
Line 3 dot C dot CUSTOMER underscore DATA dot ORDERS open square bracket 0 close square bracket dot ORDER DATE comma
Line 4 dot C dot CUSTOMER underscore DATA dot ORDERS open square bracket 0 close square bracket dot ITEMS open square bracket asterisk close square bracket
Line 5 dot FROM CUSTOMERS underscore J S O N C
Line 6 dot WHERE CUSTOMER underscore I D equals 2 comma semicolon.

(D) Using a Wildcard in an Array Index

Line 1 dot SELECT C dot CUSTOMER underscore DATA dot NAME comma
Line 2 dot C dot CUSTOMER underscore DATA dot ORDERS open square bracket asterisk close square bracket dot ORDER NUMBER comma
Line 3 dot C dot CUSTOMER underscore DATA dot ORDERS open square bracket asterisk close square bracket dot ORDER DATE comma
Line 4 dot C dot CUSTOMER underscore DATA dot ORDERS open square bracket asterisk close square bracket dot ITEMS open square bracket asterisk close square bracket
Line 5 dot FROM CUSTOMERS underscore J S O N C
Line 6 dot WHERE CUSTOMER underscore I D equals 2 comma semicolon.

(E) Using Several Wildcards in a Query

The SELECT(*) statement can be used to retrieve all data, including arrays. However, if the data is stored in binary form as a BLOB, the query shown in Figure 13.14(B) will return just a binary string.

Instead, we use the dot notation, which always returns the data as character type. Note that JSON array indexes begin at 0, so to retrieve the first order from this record, we write the query shown in Figure 13.14(C), which finds the order number, name, and item names for the first order for customer 2. Because we did not use an index value for the ITEMS array, both item names are returned.

Figure 13.14(D) demonstrates the use of the wildcard * in an array index to indicate all positions in an array, in this case, the ITEMS array in Customer 2’s first order. Note that except for its use in an array index, [*], a wildcard cannot be used in a path expression.

Figure 13.14(E) demonstrates that several wildcards can be used in the same query. This example displays details of all orders, including all their items, for Customer 2.

Although all the data is retrieved by the query in Figure 13.14(E), the results are displayed in a confusing fashion. To improve the appearance of the results and to add other conditions, we can use the JSON_QUERY function. This function, and a similar one, JSON_VALUE, are useful for complex queries, allowing users to specify the datatype of a single return value using a RETURNING clause, to wrap values as an array using a WITH WRAPPER clause, and to specify actions to be taken when null or empty values are returned or when an error is raised.

The use of JSON_VALUE is illustrated in FIGURE 13.15. Figure 13.15(A) shows the use of JSON_VALUE to return the date of an order as an actual date rather than as a character string. This function takes two arguments. The first is the name of the JSON column in the Oracle table, and the second is a string specifying the single field to be returned and the desired data type of the return. The name after the closing parenthesis is a user-supplied name for the returned value. If none is specified, the name is simply JSON_VALUE.

FIGURE 13.15 Queries Using JSON Value

Line 1. SELECT J S O N underscore VALUE open parentheses CUSTOMER underscore DATA comma single quote dollar sign dot ORDERS open square bracket 0 close square bracket dot ORDERDATE single quote RETURNING DATE close parentheses ORDER underscore DATE
Line 2. FROM CUSTOMERS underscore J S O N C
Line 3. WHERE C dot CUSTOMER underscore I D equals 2 comma semicolon dot

(A) Using JSON_VALUE to Specify a Return Type

Line 1. SELECT J S O N underscore VALUE open parentheses CUSTOMER underscore DATA comma single quote dollar sign dot ORDERS open square bracket 0 close square bracket dot ORDERTIME single quote RETURNING TIMESTAMP close parentheses ORDER underscore TIME
Line 2. FROM CUSTOMERS underscore J S O N C
Line 3. WHERE C dot CUSTOMER underscore I D equals 2 comma semicolon dot

(B) Showing No Error Message If Field Does Not Exist

Line 1. SELECT J S O N underscore VALUE open parentheses CUSTOMER underscore DATA comma single quote dollar sign dot ORDERS open square bracket 0 close square bracket dot ORDERTIME single quote RETURNING TIMESTAMP ERROR ON ERROR close parentheses
Line 2. MISSINGFIELD
Line 3. FROM CUSTOMERS underscore J S O N C
Line 4. WHERE C dot CUSTOMER underscore I D equals 2 comma semicolon dot

(C) Specifying ERROR ON ERROR

If the field specified in the SELECT clause does not exist, no error message appears. For the query shown in Figure 13.15(B), which refers to ORDERTIME, a field not included in the data, the result is just the user-supplied name and a blank line, so the user cannot determine whether the field does not exist or there is no data.

The user can add an optional error clause to specify how a runtime error should be handled. Oracle defaults to the condition NULL ON ERROR, returning a null value in such cases. To catch this error, the user can add the condition ERROR ON ERROR as shown in Figure 13.15(C), which provides an appropriate error message.

Although JSON_VALUE is useful for retrieving a single value, the JSON_QUERY function can be used to find an array or object and return the results in a more readable format, called pretty printing. If the query finds more than one value, a wrapper is needed to display the multiple values. FIGURE 13.16 shows the use of JSON_QUERY.

Line 1. SELECT J S O N underscore QUERY open parentheses CUSTOMER underscore DATA comma single quote dollar sign dot ORDERS open square bracket asterisk close square bracket single quote
Line 2. RETURNING VARCHAR2 PRETTY WITH WRAPPER close parentheses ORDERS
Line 3. FROM CUSTOMERS underscore J S O N C comma semicolon dot

FIGURE 13.16 Using JSON_QUERY with Wrapper and Pretty Printing

Converting JSON Data to Relational

The JSON_TABLE function can be used to convert JSON results to a relational form, creating a virtual relational table and allowing the user to display the results in a table format or to save them to a relational table. The first argument is the name of the JSON document. This is followed the specification of the columns, with the relational name of each column and the path to the corresponding field in the document. For arrays, a nested path specification, NESTED PATH, is followed by the names and paths for each column of the array. FIGURE 13.17 demonstrates the use of this function.

FIGURE 13.17 Converting JSON Data to Relational

Line 1. SELECT O dot asterisk
Line 2. FROM CUSTOMERS underscore J S O N C comma J S O N underscore TABLE open parentheses
Line 3. C dot CUSTOMER underscore DATA comma single quote dollar sign single quote COLUMNS open parentheses
Line 4. C U S T PATH single quote dollar sign dot NAME single quote comma
Line 5. NESTED PATH single quote dollar sign dot ORDERS open square bracket asterisk close square bracket single quote
Line 6. COLUMNS open parentheses
Line 7. O R D NUM PATH single quote dollar sign dot ORDER NUMBER single quote comma
Line 8. O R D DATE PATH single quote dollar sign dot ORDER DATE single quote
Line 9. close parentheses close parentheses close parentheses O semicolon.

(A) Using JSON_TABLE with Renaming

Line 1. CREATE TABLE C U S T underscore R E L AS
Line 2. SELECT O dot asterisk
Line 3. FROM CUSTOMERS underscore J S O N C comma J S O N underscore TABLE open parentheses
Line 4. C dot CUSTOMER underscore DATA comma single quote dollar sign single quote COLUMNS open parentheses
Line 5. NAME comma
Line 6. NESTED ORDERS open square bracket asterisk close square bracket
Line 7. COLUMNS open parentheses
Line 8. ORDER NUMBER comma
Line 9. ORDER DATE
Line 10. close parentheses close parentheses close parentheses O semicolon.

(B) Using JSON_TABLE without Renaming; Storing in a Relational Table

If the names of the relational columns are the same as those of the attributes, a simpler form can be used, as shown in Figure 13.17(B). In this example, we also show how to insert the virtual table by adding the CREATE TABLE command before the query. The user can then manipulate the relational table using SQL as usual.

Additional Oracle Functions for JSON Data

In addition to JSON_VALUE, JSON_QUERY, and JSON_TABLE, Oracle has the following functions:

  • JSON_EXISTS, which tests whether a particular value exists within the given JSON data.

  • IS_JSON and IS_NOT_JSON, which test whether the given data is well-formed JSON data.

  • JSON_EQUAL, which compares two JSON values and determines whether they are equal, ignoring whitespace and object member order.

  • JSON_TEXTCONTAINS, which does a full-text search of JSON data to determine whether it contains a particular expression. It is used in a WHERE clause or in a CASE statement.

Oracle also permits users to develop a JSON data guide, a facility that allows them to discover information about stored JSON documents, including their type, structure, and contents. A data guide can be updated automatically as new JSON documents are added to the database.

Updating JSON Using SQL

FIGURE 13.18 shows how to update JSON data. To update a value in a JSON column in an Oracle database for a version earlier than 19c, it is necessary to replace the entire record, as shown in Figure 13.18(A), which updates Customer 2’s address.

FIGURE 13.18 Updating JSON Data

Line 1. UPDATE CUSTOMERS underscore J S O N
Line 2. SET CUSTOMER underscore DATA equals U T L underscore RAW dot CAST underscore TO underscore RAW open parentheses
Line 3. single quote open curly brace double quotes NAME double quotes colon double quotes Mary Jones double quotes comma
Line 4. double quotes ADDRESS double quotes colon open curly brace double quotes STREET double quotes colon double quotes 100 Cedar Street double quotes comma double quotes CITY double quotes colon double quotes Brooklyn double quotes comma
Line 5. double quotes STATE double quotes colon double quotes NY double quotes comma double quotes ZIP double quotes colon double quotes 11240 double quotes close curly brace comma
Line 6. double quotes TELEPHONE double quotes colon open curly brace double quotes AREA CODE double quotes colon double quotes 212 double quotes comma double quotes NUMBER double quotes colon double quotes 123 4567 double quotes close curly brace comma
Line 7. double quotes TYPE double quotes colon double quotes CORPORATE double quotes comma
Line 8. double quotes STATUS double quotes colon double quotes ACTIVE double quotes comma
Line 9. double quotes ORDERS double quotes colon open square bracket open curly brace double quotes ORDER NUMBER double quotes colon 1001 comma double quotes ORDER DATE double quotes colon double quotes 2022 hyphen 0 2 hyphen 0 8 double quotes comma
Line 10. double quotes ITEMS double quotes colon
Line 11. open square bracket open curly brace double quotes ITEM NAME double quotes colon double quotes PEN double quotes comma double quotes PRICE double quotes colon double quotes 15 dot 9 9 double quotes comma double quotes QUANTITY double quotes colon double quotes 1 double quotes close curly brace comma
Line 12. open curly brace double quotes ITEM NAME double quotes colon double quotes DESKLAMP double quotes comma double quotes PRICE double quotes colon double quotes 34 point 9 8 double quotes comma double quotes QUANTITY double quotes colon double quotes 1 double quotes close curly brace close square bracket close curly brace comma
Line 13. open curly brace double quotes ORDERNUMBER double quotes colon 1003 comma double quotes ORDER DATE double quotes colon double quotes 2022 hyphen 0 2 hyphen 0 8 double quotes comma double quotes ITEMS double quotes colon
Line 14. open square bracket open curly brace double quotes ITEMNAME double quotes colon double quotes FILING CABINET double quotes comma double quotes PRICE double quotes colon double quotes 45 point 0 0 double quotes comma double quotes QUANTITY double quotes colon double quotes 1 double quotes close curly brace close square bracket close curly brace close square bracket close curly brace single quote close parentheses
Line 15. WHERE CUSTOMER underscore ID equals 2 semicolon

(A) Updating an Entire Record

Line 1. hyphen hyphen Note colon requires Oracle 19 c or later
Line 2. UPDATE CUSTOMERS underscore J S O N
Line 3. SET CUSTOMER underscore DATA equals J S O N underscore MERGE PATH open parentheses
Line 4. CUSTOMER underscore DATA comma
Line 5. single quote open curly brace
Line 6. double quotes NAME double quotes colon double quotes Mary Smith double quotes
Line 7. close curly brace single quote
Line 8. close parentheses
Line 9. WHERE CUSTOMER underscore I D equals 2 semicolon

(B) Updating Selected Fields Using JSON_MERGEPATCH

Oracle 19c and later versions provide a method of patching, called JSON_MERGEPATCH, that allows users to specify only the fields to be updated, as shown in Figure 13.18(B). Although scalar values can be updated by a patch, updating an array still requires replacing the entire array in Oracle 19c.

Oracle 21c, which introduced a native JSON datatype, simplifies updates, including array updates, with the JSON_TRANSFORM function. This function allows the user to insert new values, replace existing values, and remove values in a single statement. Details can be found online in the Oracle JSON Developer’s Guide.

13.6.2 XML DB

Oracle supports XML both as a native data type for use in Oracle databases and as an input and output type for OR databases whose structure mirrors that of XML documents. Oracle XML DB is a standard component of the Oracle DBMS that allows users to validate, parse, store, index, search, query, update, shred, transform, and generate XML data. Users can access data coming from disparate sources, combining it into a single model. It supports the management of multiple large, complex XML documents while providing all the capabilities associated with relational databases, including data security, robust transaction control, high performance, and scalability.

Oracle XML DB incorporates the W3C recommendations for XML, and it provides methods for accessing and querying XML content using XML and SQL syntax. It uses both XML and relational data models interoperably, permitting the user to see the same data as relational tuples and as nodes of an XML document. XML data can be manipulated using SQL and XQuery operators, and relational data can be treated as if it were XML. XML data can be accessed and published using HTTP, FTP, and WebDAV. It can be manipulated using the standard SQL tools in Oracle, such as SQL*Plus, or using APIs provided for Java, C, and PL/SQL. Special indexes for XML data can be created, allowing for optimization of XML operations. Oracle XML DB allows the creation of databases in which the XMLTYPE is used as a native data type. XMLTYPE is an object type, and it can be used as a type for a column or a type for an object table, as well as for parameters and PL/SQL variables. The examples that follow demonstrate the basics of creating and manipulating XML data using SQL*Plus.

Creating Relational Tables with XML Columns

A relational database can have any number of columns of XMLTYPE alongside columns of any other data type that Oracle supports. FIGURE 13.19 shows how to create and populate such a table. FIGURE 13.19(A) shows an SQL command to create a simple table containing a numeric CUSTID column and an XMLTYPE column called CONTACTINFO.

FIGURE 13.19 Using XMLTYPE for a Column

Line 1. CREATE TABLE CUSTOMERS, open parentheses.
Line 2. C U S T I D NUMBER, open parentheses, 4, close parentheses, PRIMARY KEY, comma.
Line 3. CONTACT INFO X M L TYPE, close parentheses, semicolon.

(A) Creating a Table with an XMLTYPE Column

Line 1. INSERT INTO CUSTOMERS VALUES open parentheses 1 comma X M L TYPE open parentheses
Line 2. open single quote Open angled bracket comma CUSTOMER TYPE equals double quotes Corporate double quotes STATUS equals double quotes Active double quotes close angled bracket.
Line 3. Open angled bracket comma NAME close angled bracket. World Wide Travel Agency Open angled bracket comma forward slash NAME close angled bracket.
Line 4. Open angled bracket comma ADDRESS close angled bracket.
Line 5. Open angled bracket comma STREET close angled bracket. 10 Main Street. Open angled bracket comma forward slash STREET close angled bracket.
Line 6. Open angled bracket comma CITY close angled bracket. New York. Open angled bracket comma forward slash CITY close angled bracket.
Line 7. Open angled bracket comma STATE close angled bracket. N Y. Open angled bracket comma forward slash STATE close angled bracket.
Line 8. Open angled bracket comma ZIP close angled bracket. 10001. Open angled bracket comma forward slash ZIP close angled bracket.
Line 9. Open angled bracket comma forward slash ADDRESS close angled bracket.
Line 10. Open angled bracket comma TELEPHONE close angled bracket.
Line 11. Open angled bracket comma AREACODE close angled bracket. 212. Open angled bracket comma forward slash AREACODE close angled bracket.
Line 12. Open angled bracket comma PHONE close angled bracket. 123 4567. Open angled bracket comma forward slash PHONE close angled bracket.
Line 13. Open angled bracket comma forward slash TELEPHONE close angled bracket.
Line 14. Open angled bracket comma forward slash CUSTOMER close angled bracket. open single quote close parentheses close parentheses comma semicolon.
Line 15. Hyphen comma hyphen comma two additional records inserted.

(B) Inserting Records with an XMLTYPE Column

Records can be inserted in several ways, such as reading from a file or using an API. Inserting records individually is easily done through SQL*Plus, using XMLTYPE (or XMLTYPE.CREATEXML) in the same way that we used constructors in OO and OR databases, as shown in FIGURE 13.19(B). The XML input data is formatted exactly as shown earlier in Figure 13.2, with each element tagged. For this example, the other two records shown in Figure 13.2 are inserted in the same manner.

Querying XML Data

There are several ways users can query and manipulate XML data in Oracle XML DB. One of the simplest methods is by using the XQuery language in SQL*Plus, although it can also be used in PL/SQL or Java. The main functions are XMLQuery, XMLCast, XMLTable, and XMLExists. XMLQuery is generally used in the SELECT clause, XMLTable in the FROM clause, and XMLExists in the WHERE clause of queries. The general form of an XMLQuery statement using a path expression is

Line 1. X M L Query open parentheses.
Line 2. Single quote open angled bracket X Query string close angled bracket single quote.
Line 3. PASSING open angled bracket x m l type column close angled bracket.
Line 4. RETURNING CONTENT.
Line 5. Close parentheses.

Here, <XQuery string> is a standard XQuery language string containing a path or FLWOR expression as described in Section 13.4.4, and <xmltype column> is the table column to be returned. You can add NULL ON EMPTY before the closing parenthesis to catch errors if no data is found, and you can add a column name for the return after the closing parenthesis. FIGURE 13.20 shows several queries using XQuery. FIGURE 13.20(A) shows a query that finds the names of all customers.

FIGURE 13.20 Using XQuery

Line 1. SELECT C U S T I D comma
Line 2. X M L QUERY open parentheses single quote forward slash CUSTOMER forward slash forward slash NAME single quote
Line 3. PASSING CONTACT INFO
Line 4. RETURNING CONTENT
Line 5. NULL ON EMPTY
Line 6. close parentheses CUSTOMER underscore NAME
Line 7. FROM CUSTOMERS semicolon.

(A) Using XMLQUERY to Retrieve an XML Field

Conditions can be added as described in Section 13.4.4. The query shown in FIGURE 13.20(B) finds the telephone numbers of all inactive customers. The first example uses a path expression, while the second shows the use of a FLWOR expression.

Line 1. SELECT X M L QUERY open parentheses single quote forward slash CUSTOMER open square bracket at symbol STATUS equals double quotes Inactive double quotes close square bracket forward slash forward slash TELEPHONE single quote
Line 2. PASSING CONTACT INFO
Line 3. RETURNING CONTENT
Line 4. NULL ON EMPTY
Line 5. close parentheses PHONE
Line 6. FROM CUSTOMERS semicolon.
Line 7. hyphen hyphen same query using F L W O R Expression
Line 8. SELECT X M L QUERY open parentheses single quote for dollar symbol x in forward slash forward slash CUSTOMER
Line 9. let dollar symbol stat colon equals double quotes Inactive double quotes
Line 10. where dollar symbol x forward slash at symbol STATUS equals dollar symbol stat
Line 11. order by dollar symbol x forward slash NAME ascending
Line 12. return dollar symbol x forward slash TELEPHONE single quote
Line 13. PASSING CONTACT INFO RETURNING CONTENT NULL ON EMPTY close parentheses
Line 14. FROM CUSTOMERS semicolon.

(B) Using XMLQUERY with a Condition

These examples return XML data with tags surrounding the results. The XMLCAST function can be used to change XML data into a scalar. Its form is

X M L CAST open parentheses value underscore expression AS datatype close parentheses.

The scalar datatypes that can follow the word AS are NUMBER, VARCHAR2, CHAR, CLOB, BLOB, REFXMLTYPE, and any SQL date or time type. FIGURE 13.20(C) demonstrates the use of the XMLCAST function to change the query in Figure 13.20(A) to character data.

Line 1. SELECT X M L Cast open parentheses X M L Query open parentheses single quote forward slash CUSTOMER forward slash forward slash NAME single quote
Line 2. PASSING CONTACT INFO
Line 3. RETURNING CONTENT close parentheses
Line 4. AS VARCHAR 2 open parentheses 25 close parentheses close parentheses C U S T underscore NAME
Line 5. FROM CUSTOMERS semicolon.

(C) Using the XMLCAST Function

The XMLTable function is used to transform the results of an XQuery expression into a virtual relational table, which can then be stored as a regular or temporary table, and operated on as a normal relational table using SQL. It appears in the FROM clause. The format is

Line 1. FROM dot, dot, dot.
Line 2. X M L Table open parentheses.
Line 3. Single quote, open angled bracket X Query string, close angled bracket single quote.
Line 4. PASSING open angled bracket x m l col close angled bracket.
Line 5. COLUMNS open angled bracket col name close angled bracket open angled bracket col type close angled bracket PATH open angled bracket X Query path close angled bracket.
Line 6. Close parentheses.

FIGURE 13.20(D) shows its use to create a virtual relational table consisting of columns CUSTNAME, CUSTADDRESS, and CUSTPHONE for active customers. Note that the source table (here, CUSTOMERS) must be named in the FROM clause, as shown.

Line 1. SELECT CUSTOMERS dot C U S T I D comma t dot asterisk
Line 2. FROM CUSTOMERS comma X M L TABLE open parentheses single quote forward slash CUSTOMER open square bracket at symbol STATUS equals double quotes Active double quotes close square bracket single quote
Line 3. PASSING CUSTOMERS dot CONTACT INFO
Line 4. COLUMNS C U S T NAME VAR CHAR 2 open parentheses 50 close parentheses PATH single quote NAME single quote comma
Line 5. C U S T ADDRESS VAR CHAR 2 open parentheses 50 close parentheses PATH single quote ADDRESS single quote comma
Line 6. C U S T PHONE VAR CHAR 2 open parentheses 11 close parentheses PATH single quote TELEPHONE single quote close parentheses t semicolon.

(D) Using XMLTable to Create a Virtual Relational Table

Using XMLTABLE, the user is essentially shredding the XML document, dividing it into components that are mapped to columns of tables. The table created can be given a name and then treated as a regular relational table by writing a CREATE TABLE... AS command just before the previous query, as in

Line 1. CREATE TABLE CUSTOMERS underscore REL AS.
Line 2. SELECT CUSTOMERS dot C U S T I D, comma, t dot asterisk.
Line 3. FROM dot, dot, dot.

The XMLExists function tests whether an XQuery expression returns a non-empty string, that is, it returns true if the value is found, and false otherwise. It is used in the WHERE clause (or a CASE statement) and its form is

Line 1. WHERE dot, dot, dot.
Line 2. X M L Exists open parentheses.
Line 3. Single quote open angled bracket X Query string close angled bracket single quote
Line 4. PASSING single quote open angled bracket x m l col close angled bracket single quote
Line 5. Close parentheses.

An example is shown in FIGURE 13.20(E), which returns the CUSTID of any customer whose area code is 212.

Line 1. SELECT C U S T I D
Line 2. FROM CUSTOMERS
Line 3. WHERE X M L EXISTS open parentheses single quote forward slash forward slash TELEPHONE open square bracket AREA CODE equals double quotes 212 double quotes close square bracket single quote
Line 4. PASSING CONTACT INFO close parentheses semicolon.

(E) Using XMLEXISTS

Object Tables of XMLTYPE

Oracle also supports object tables that consist entirely of native XMLTYPE entries, as shown in FIGURE 13.21. XML object tables can be created as illustrated in Figure 13.21(A). Like all object tables, no other columns are specified when such a table is created; all of its entries are instances of XML documents. Although the usual method of populating such tables is by reading directly from an XML document in a file, it is possible to insert XML records individually using SQL*Plus, as shown in Figure 13.21(B). The two other customer records can be entered in the same way.

FIGURE 13.21 Using a Table of XMLTYPE

CREATE TABLE CUSTOMERS underscore X M L OF X M L TYPE, semicolon.

(A) Creating an Object Table of XMLTYPE

Line 1. INSERT INTO CUSTOMERS underscore X M L VALUES Open parentheses X M L TYPE Open parentheses
Line 2. Single quote Open angled bracket comma CUSTOMER TYPE equals double quotes Corporate double quotes STATUS equals double quotes Active double quotes Close angled bracket
Line 3. Open angled bracket comma NAME Close angled bracket World Wide Travel Agency Open angled bracket comma Forward slash NAME Close angled bracket
Line 4. Open angled bracket comma ADDRESS Close angled bracket
Line 5. Open angled bracket comma STREET Close angled bracket 10 Main Street Open angled bracket comma Forward slash STREET Close angled bracket
Line 6. Open angled bracket comma CITY Close angled bracket New York Open angled bracket comma Forward slash CITY Close angled bracket
Line 7. Open angled bracket comma STATE Close angled bracket N Y Open angled bracket comma Forward slash STATE Close angled bracket
Line 8. Open angled bracket comma ZIP Close angled bracket 10001 Open angled bracket comma Forward slash ZIP Close angled bracket
Line 9. Open angled bracket comma Forward slash ADDRESS Close angled bracket
Line 10. Open angled bracket comma TELEPHONE Close angled bracket
Line 11. Open angled bracket comma AREACODE Close angled bracket 212 Open angled bracket comma Forward slash AREACODE Close angled bracket
Line 12. Open angled bracket comma PHONE Close angled bracket 123 4567 Open angled bracket comma Forward slash PHONE Close angled bracket
Line 13. Open angled bracket comma Forward slash TELEPHONE Close angled bracket
Line 14. Open angled bracket comma Forward slash CUSTOMER Close angled bracket Single quote close parentheses close parentheses Semicolon.
Line 15. Hyphen hyphen two additional records inserted

(B) Inserting into an XMLTYPE Table

Because a table of XMLType is an object table, we can use the SELECT OBJECT_VALUE or simply SELECT VALUE() as shown in Figure 13.21(C) (as we did in Chapter 9 for Oracle OR tables).

Line 1. SELECT OBJECT underscore VALUE FROM CUSTOMERS underscore X M L, semicolon.
Line 2. SELECT VALUE, open parentheses, C, close parentheses, FROM CUSTOMERS underscore X M L C, semicolon.

(C) Query Using OBJECT_VALUE and VALUE()

We can also use the same XQuery functions XMLQuery, XMLTable, and XMLExists that we used for tables with object columns, so the queries shown in Figure 13.19 run with slight modifications for the CUSTOMERS_XML table. The modified queries are shown in Figure 13.21(D). The reference to CUSTID is dropped, and OBJECT_VALUE is used in the PASSING clause.

Line 1. SELECT X M L QUERY open parentheses single quote forward slash CUSTOMER forward slash forward slash NAME single quote
Line 2. PASSING OBJECT underscore VALUE
Line 3. RETURNING CONTENT
Line 4. NULL ON EMPTY
Line 5. close parentheses CUSTOMER underscore NAME
Line 6. FROM CUSTOMERS underscore X M L semicolon.
Line 7. SELECT X M L QUERY open parentheses single quote forward slash CUSTOMER open square bracket at symbol STATUS equals double quotes Inactive double quotes close square bracket forward slash forward slash TELEPHONE single quote
Line 8. PASSING OBJECT underscore VALUE
Line 9. RETURNING CONTENT
Line 10. NULL ON EMPTY
Line 11. close parentheses PHONE
Line 12. FROM CUSTOMERS underscore X M L semicolon.
Line 13. SELECT t dot asterisk
Line 14. FROM CUSTOMERS underscore X M L comma X M L TABLE open parentheses single quote forward slash CUSTOMER open square bracket at symbol STATUS equals double quotes Active double quotes close square bracket single quote
Line 15. PASSING OBJECT underscore VALUE
Line 16. COLUMNS C U S T NAME VAR CHAR 2 open parentheses 50 close parentheses PATH single quote NAME single quote comma
Line 17. C U S T ADDRESS VAR CHAR 2 open parentheses 50 close parentheses PATH single quote ADDRESS single quote comma
Line 18. C U S T PHONE VAR CHAR 2 open parentheses 11 close parentheses PATH single quote TELEPHONE single quote
Line 19. close parentheses t semicolon.

(D) Queries Modified for Object Table

Updating XML Data

Starting with Oracle 12, Oracle XML DB follows the W3 recommendation by providing an XQuery Update function, which replaces the UPDATEXML function that was used in earlier versions. The UPDATE statement can be used to update an entire XML document or selected nodes of the document, as shown in FIGURE 13.22. The right side of the SET line must be an XMLType instance.

FIGURE 13.22 Updating XML Data

Line 1. UPDATE CUSTOMERS underscore X M L c u
Line 2. SET c u dot OBJECT underscore VALUE equals X M L Type Open parentheses single quote Open angled bracket CUSTOMER TYPE equals double quotes Individual double quotes STATUS equals double quotes Active double quotes Close angled bracket
Line 3. Open angled bracket NAME Close angled bracket Beth Burns Open angled bracket forward slash NAME Close angled bracket
Line 4. Open angled bracket ADDRESS Close angled bracket
Line 5. Open angled bracket STREET Close angled bracket 25 Spruce Street Open angled bracket forward slash STREET Close angled bracket
Line 6. Open angled bracket CITY Close angled bracket San Diego Open angled bracket forward slash CITY Close angled bracket
Line 7. Open angled bracket STATE Close angled bracket CA Open angled bracket forward slash STATE Close angled bracket
Line 8. Open angled bracket ZIP Close angled bracket 92101 Open angled bracket forward slash ZIP Close angled bracket
Line 9. Open angled bracket forward slash ADDRESS Close angled bracket
Line 10. Open angled bracket TELEPHONE Close angled bracket
Line 11. Open angled bracket AREACODE Close angled bracket 619 Open angled bracket forward slash AREACODE Close angled bracket
Line 12. Open angled bracket PHONE Close angled bracket 987 6543 Open angled bracket forward slash PHONE Close angled bracket
Line 13. Open angled bracket forward slash TELEPHONE Close angled bracket
Line 14. Open angled bracket forward slash CUSTOMER Close angled bracket single quote Close parentheses
Line 15. WHERE X M L Exists Open parentheses single quote dollar c forward slash CUSTOMER open square bracket NAME equals double quotes Alice Adams double quotes open square bracket single quote
Line 16. PASSING c u dot OBJECT underscore VALUE AS double quotes c double quotes Close parentheses semicolon.

(A) Updating an Entire Object

The example shown in FIGURE 13.22(A) changes a customer’s name and telephone number in a single statement, while replacing the entire document (the customer’s record). Note that the condition is expressed using the XQuery function XMLExists.

To update the value of a node instead of the entire document, we can modify the contents of the node as shown in FIGURE 13.22(B), which changes the name of customer Mary Jones to Mary King.

Line 1. UPDATE CUSTOMERS underscore X M L c u
Line 2. SET c u dot OBJECT underscore VALUE equals
Line 3. X M L Query Open parentheses single quote copy dollar i colon equals dollar p 1 modify
Line 4. Open parentheses for dollar j in dollar i forward slash CUSTOMER forward slash NAME
Line 5. return replace value of node dollar j with double quotes Mary King double quotes Close parentheses
Line 6. return dollar i single quote
Line 7. PASSING c u dot OBJECT underscore VALUE AS double quotes p 1 double quotes
Line 8. RETURNING CONTENT Close parentheses
Line 9. WHERE X M L Exists Open parentheses single quote dollar c forward slash CUSTOMER open square bracket NAME equals double quotes Mary Jones double quotes open square bracket single quote
Line 10. PASSING c u dot OBJECT underscore VALUE AS double quotes c double quotes Close parentheses semicolon.

(B) Updating the Contents of a Node

In addition to updating existing nodes, new nodes can be inserted or old ones deleted. In FIGURE 13.22(C) we insert a second telephone number for the WorldWide Travel Agency.

Line 1. UPDATE CUSTOMERS underscore X M L c u
Line 2. SET c u dot OBJECT underscore VALUE equals
Line 3. X M L Query Open parentheses single quote copy dollar i colon equals dollar p 1 modify
Line 4. Open parentheses for dollar j in dollar i forward slash CUSTOMER forward slash TELEPHONE
Line 5. return Open parentheses hash o r a colon child-element-name TELEPHONE hash Close parentheses
Line 6. open curly brace insert node dollar p2 into dollar j close curly brace Close parentheses
Line 7. return dollar i single quote
Line 8. PASSING c u dot OBJECT underscore VALUE AS double quotes p 1 double quotes ,
Line 9. X M L Type Open parentheses single quote Open angled bracket TELEPHONE Close angled bracket
Line 10. Open angled bracket ARE ACODE Close angled bracket 619 Open angled bracket forward slash AREA CODE Close angled bracket
Line 11. Open angled bracket PHONE Close angled bracket 888 7777 Open angled bracket forward slash PHONE Close angled bracket
Line 12. Open angled bracket forward slash TELEPHONE Close angled bracket single quote Close parentheses AS double quotes p 2 double quotes
Line 13. RETURNING CONTENT Close parentheses
Line 14. WHERE X M L Exists Open parentheses single quote dollar c forward slash CUSTOMER open square bracket NAME equals double quotes World Wide Travel Agency double quotes open square bracket single quote
Line 15. PASSING c u dot OBJECT underscore VALUE AS double quotes c double quotes Close parentheses semicolon

(C) Inserting a New Child Node

In Figure 13.22 we illustrate removing a node by deleting the STATE in the address of the WorldWide Travel Agency.

Line 1. UPDATE CUSTOMERS underscore X M L c u
Line 2. SET c u dot OBJECT underscore VALUE equals
Line 3. X M L Query Open parentheses single quote copy dollar i colon equals dollar p 1 modify
Line 4. delete nodes dollar i forward slash CUSTOMER forward slash ADDRESS forward slash STATE
Line 5. return dollar i single quote
Line 6. PASSING OBJECT underscore VALUE AS double quotes p 1 double quotes RETURNING CONTENT Close parentheses
Line 7. WHERE X M L Exists Open parentheses single quote dollar c forward slash CUSTOMER open square bracket NAME equals double quotes World Wide Travel Agency double quotes open square bracket single quote
Line 8. PASSING c u dot OBJECT underscore VALUE AS double quotes c double quotes Close parentheses semicolon

(D) Deleting a Child Node

Obtaining XML Data from a File

In the previous examples, we input the XML data from SQL*Plus one record at a time. Data can also be read directly from a file. For example, to read the data shown in the XML instance document in Figure 13.4, you first store the document in an operating system directory on the database server machine. For example, if you have created a directory on the C: drive called DBBook, and saved the XML file in that directory using the name CustomerList.xml, you can enter data from the file into an Oracle table by executing the commands shown in FIGURE 13.23 using SQL*Plus.

FIGURE 13.23 XML Input from a File

Line 1. Forward slash asterisk Assumes Customer List instance document has been saved as
Line 2. C colon back slash D B Book back slash Customer List asterisk Forward slash
Line 3. CREATE TABLE CUSTOMERS2 underscore X M L OF X M L TYPE semicolon.
Line 4. CREATE OR REPLACE DIRECTORY MY X M L DIR AS single quote C colon back slash D B Book single quote semicolon.
Line 5. INSERT INTO CUSTOMERS 2 underscore XML
Line 6. VALUES open parentheses X M L Type open parentheses b file name open parentheses single quote MY X M L DIR single quote , single quote CustomerList.xml single quote close parentheses ,
Line 7. n l s underscore charset underscore id open parentheses single quote A L 3 2 U T F 8 single quote close parentheses close parentheses close parentheses semicolon.

(A) Reading XML Input from a File

Oracle shows that one record has been inserted. If you retrieve the data using the command

Line 1. SET LONG 2000. Line 2. SELECT OBJECT underscore VALUE FROM CUSTOMERS 2 underscore X M L semicolon.

the data is retrieved as a single record, which is the entire CustomerList. However, you can retrieve individual customer records by modifying the queries shown in Figure 13.21(D) to include CUSTOMERLIST as part of the path. For example, the first query is modified as shown in FIGURE 13.23(B).

Line 1. SELECT X M L QUERY open parentheses single quote CUSTOMER LIST Forward slash CUSTOMER Forward slash Forward slash NAME single quote
Line 2. PASSING OBJECT underscore VALUE
Line 3. RETURNING CONTENT
Line 4. NULL ON EMPTY
Line 5. close parentheses CUSTOMER underscore NAME
Line 6. FROM CUSTOMERS 2 underscore X M L semicolon.

(B) Query Modified for CustomerList Object Table

The other queries in Figure 13.21(D) are modified in the same way. Data can be retrieved using XQuery as shown earlier and stored in a relational table using XMLTable, which is essentially shredding the XML document.

When an XML schema exists, data can be entered from an XML instance document directly to an Oracle table and shredded automatically. This process requires registering the XML schema for the document. Registration permits XML DB to create an OR database structure that best fits the XML schema, matching up data types for elements and attributes, creating structures for objects, and using VARRAYs or nested tables for repeating elements. XML instance documents used as input can then be shredded automatically and their components stored in the OR database. The registration process and the XML schema creation can be done in a single step through PL/SQL using the registerSchema method, which has several parameters. The main parameters are the URL where the schema is located, the schema document, the encoding method used, and options for how the schema should be registered. Once a schema is registered, Oracle can do a mapping to an OR table, unless the programmer has chosen other options. The document root corresponds to the name of the table, and elements and attributes are mapped to table columns. VARRAYs and nested tables can store complex data. Shredding the documents provides better performance, but queries can be written as shown previously.

13.6.3 Oracle XML Developer Kits (XDKs)

Oracle provides XML Developer Kits (XDKs) for PL/SQL, Java, C, and C++, which include libraries and utilities for applications using XML with those host languages. Using an XDK allows more control over XML content because the documents can be parsed. Using the XDK allows programmers to use the appropriate parser to obtain and parse an XML document, and to process the elements in the host language, exploiting the best features of both XML and the host language.

The Oracle XML SQL Utility (XSU) is an XDK component that allows two-way mapping between XML and SQL. Using XSU, programmers can do the following:

  • Extract data from XML documents and insert it into database tables or views, using a standard canonical mapping. The elements and attributes of the XML document become attributes of the table.

  • Update or delete data in the database tables or views from data in an XML document using SQL.

  • Extract data from OR tables or views and transform the results set into XML. In the resulting document, the table name becomes the root element, and each row of the table is tagged as a ROW element, within which each attribute is tagged with the name of the database column.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.220.219.33