Localized and relational XML

Oracle xml db provides localized formats and relational database access.

XML is quickly becoming the preferred language for data exchange between enterprises. However, most enterprises store their data in relational databases such as Oracle9i. So how do you connect hierarchical, document-centric XML and table-Oriented Relational databases? Are you storing XML documents as files in the file system? Or are XML documents separated and data stored in relational databases? When selecting between the two methods, you must weigh the data you use. But what if you don't have to choose? If you can use two methods at the same time, what would you do? You can use the new features of the xml db knowledge base (xml db repository) in Oracle9i Database 2nd.

　　Xml db Knowledge Base description

Oracle xml db is neither a single product nor a separate option that you must install. Oracle xml db refers to a set of XML features and technologies directly built into the Oracle9i database. One of the key features is the xml db knowledge base. This knowledge base allows you to directly store XML documents in Oracle9i Database 2nd. Once your XML document is stored in this knowledge base, you can access XML data in an XML-centric or relational-centric manner.

To store XML data in your database, you simply need to write an XML document file using FTP, HTTP, WebDAV, and other industry-standard protocols. Retrieving XML data from a database is as simple as executing an SQL query or reading a file using one of the preceding protocols.

　　Scenario settings

Suppose you are selling a CD made by an independent artist. You need to exchange information with major music stores, online sites, and artists themselves. You have developed the XML document format used to describe Cd content, as shown in Listing 1. Now you want to use the xml db knowledge base to store information in the database. You want to easily access data and local XML documents through SQL. In short, you want data to be relational and hierarchical. In this article, I am your DBA and implementing it is my job.

　　Register XML Mode

My first step is to register your XML schema using the xml db knowledge base. When I register an XML schema, this knowledge base creates object types and object tables that can save the schema instance. The following describes how to use dbms_xmlschema.registeruri executed through SQL * Plus to obtain the XML format shown in step 2 from http://gennick.com/cd.xsd. then, you can register the region:

Begin Dbms_xmlschema.registeruri ( 'CD. XSD ', 'Http: // gennick.com/cd.xsd '); End; /

Note: In addition to the create permission for various mode object types, I also need the alter session and query rewrite permissions to register the mode and create the example in this article.

Listing 3 shows some structures and objects created by registering the CD mode. An XML table named cd331_tab is created to save the mode. Each CD document in the knowledge base is represented by a row in the table. I can query the user_xml_tables data dictionary view to get a list of XML tables. In this example, I simply register the pre-and post-query views in the mode and then find the new table name. Each row in the cd331_tab contains a cd327_t instance. This type corresponds to the one created in our XML mode. The first element of the XML document is represented as a property of the cd327_t type. The attribute name matches the XML field name. For example, the title field in this object type corresponds directly to the title element in XML mode. The songs field corresponds to the songs element. Songs is a complex element in XML mode. It is also mapped to another object type "songs328_t ". If I use the SQL * Plus command describe "songs328_t" and continue to study the songs field definition, I will see that the song set is eventually implemented as a varray. In varray, each element represents a song.

When I register a schema, I can control the object and type names generated by the Oracle9i database; I can also control the specific data types used to store my XML data. You can use the attributes defined in the xml db knowledge base and the partial annotation XML mode of the oraxdb namespace to perform these control. When I do not provide those attributes, the Oracle9i database generates them for me. I can view the schema version stored in this knowledge base to simply browse the content generated by the Oracle9i database. Figure 1 shows how you can easily access the knowledge base data. This time, you access the data through HTTP using a standard Web browser. Figure 1 shows a part of the CD mode in my knowledge base. You can see the mode annotations, all of which start with "oraxdb. Note: The URL uses port 8080, which is the default http port used by the knowledge base.

By default, all objects created in registration mode belong to users in registration mode. In this example, I have tables and types in listing 3 and all other objects related to the CD mode. Because I registered the mode, any XML file (an instance in this CD mode) that I saved to the knowledge base will be split and stored in the cd331_tab table. This mode and registration are specific to me. The CD files saved by other users will not be stored in my tables. You can use an optional parameter for dbms_xmlschema.register mode to create a global mode that affects all users so that all users can save the CD document to the table.

　　Create an XML folder

To store cd xml documents in the xml db knowledge base, I need a folder to store them. To create a folder, log on to Oracle as a system user and execute the PL/SQL block in Listing 4. Call dbms_xdb.createfolder to create the first-level folder named/CD. The PL/SQL block is then created using the dbms_xdb.setacl process to grant all folder permissions to the owner (that is, the system user) and read permissions to the access control list (ACL) of all other users ). To change the folder owner from system to gennick, you need to call the update statement on the resource_view of the database. After a folder is created, submission is important. The folder is not visible to other sessions until you submit the folder. Now I can use ftp or WebDAV as gennick to connect and save the XML file to the/CD folder.

　　Save XML document

Once I have registered the mode, I create a folder to save my XML document, and save the document to the knowledge base as easy as copying a file. Listing 5 shows a copy of The legendsofthegreatlakes. xml file to the FTP Session of the knowledge base. Port 2100 used by the FTP open command is the default port used by the knowledge base for FTP sessions. Note that I can simply use Windows copy and paste operations in the WebDAV and Windows Web Folders as before, instead of using FTP.

　　Use resource_view

An important view you should know is the view named resource_view. The resource_view view returns a line for each document and folder in the knowledge base you access. For example, you can run the following query to obtain a list of all XML documents in the/CD folder.

Select any_path From resource_view Where under_path (Res, '/Cd') = 1 And extractvalue (Res, '/Resource/contenttype') = 'text/xml '; Any_path ------------------------------- /CD/gospel/nothingless. xml /CD/legendsofthegreatlakes. xml

The new under_path function shown above allows you to test whether a given knowledge base resource is in the folder (or path) you specified. In this example, I use this function to limit the query results to the resources in the/CD folder and Its subfolders. You can use a hierarchical domain index created for the basic table to make path-based queries on the resource_view view more efficient. This index is part of the knowledge base; you do not need to create it.

Figure 1: part of the sample CD mode in the xml db Knowledge Base

The RES field in the resource view does not represent the resource itself, but only the metadata of the resource. Use the new extractvalue function on the Res field to check the content type of each resource. Therefore, the query results are further restricted to the path to the XML document. '/Resource/contenttype' syntax indicates the XPath notation. XPath is the standard representation of each part of an XML document. you will use it in many queries to XML data.

Given a database path, you can use the new xdburitype object type to retrieve all or some basic XML documents. Listing 6 shows two queries. The first query is an extension of the above Code. xdburitype is used to retrieve all XML documents in the/CD folder. To extract only the CD title, the second query in Listing 6 is further improved by appending the standard XPath syntax to the end of the URL.

　　Relational Access to Knowledge Base Data

You can also access the XML data in the knowledge base by directly accessing the basic table. The basic table created when I registered the CD mode is the cd331_tab. You can directly write query statements for this table, but these queries must support xml. To simplify access to XML data by using a report tool designed for relational data, you can create a view as shown in listing 7. In addition to views, listing 7 also creates an index for the artist's name. Views and indexes enable me to effectively execute standard link queries such as the following:

Select title
From cd_master
Where artist = 'Carl behrend ';

　　Update XML data

Unfortunately, because all fields in the cd_master view are based on SQL functions, this view cannot be updated. However, you can update the XML data in the knowledge base. I only need to update the basic table created when the registration mode is as follows:

Update cd331_tab CD Set Value (CD) = updatexml ( Value (CD ), '/CD/website/text ()', 'Http: // greatlakeslegends.com/ Legends.htm ');

Note the use of the XPath syntax in this new updatexml function. The path '/CD/website/text ()' indicates the text of the website element of the CD document to be updated. The third parameter of updatexml specifies a new value for that text. This is an appropriate update and highly efficient. The xml db knowledge base does not need to reconstruct the entire XML document that has been changed. Because the schema is registered, the xml db knowledge base can rewrite this query in the way that only the website attribute is touched in the underlying object structure.

　　Conclusion

By using the xml db knowledge base, you can store XML documents in the database and access those documents using standard Internet protocols. At the same time, you can use standard link queries to access the same XML documents or parts of those documents. You do not have XML data or relational data, but only data. "XML" and "relationship" are only different examples of viewing data. By separating data from examples, Oracle9i protects one of your most important assets-data-from the impact of Sample changes.

An important view you should know is the view named resource_view. The resource_view view returns a line for each document and folder in the knowledge base you access. For example, you can run the following query to obtain a list of all XML documents in the/CD folder.

Select any_path From resource_view Where under_path (Res, '/Cd') = 1 And extractvalue (Res, '/Resource/contenttype') = 'text/xml '; Any_path ------------------------------- /CD/gospel/nothingless. xml /CD/legendsofthegreatlakes. xml

The new under_path function shown above allows you to test whether a given knowledge base resource is in the folder (or path) you specified. In this example, I use this function to limit the query results to the resources in the/CD folder and Its subfolders. You can use a hierarchical domain index created for the basic table to make path-based queries on the resource_view view more efficient. This index is part of the knowledge base; you do not need to create it.

Figure 1: part of the sample CD mode in the xml db Knowledge Base

The RES field in the resource view does not represent the resource itself, but only the metadata of the resource. Use the new extractvalue function on the Res field to check the content type of each resource. Therefore, the query results are further restricted to the path to the XML document. '/Resource/contenttype' syntax indicates the XPath notation. XPath is the standard representation of each part of an XML document. you will use it in many queries to XML data.

Given a database path, you can use the new xdburitype object type to retrieve all or some basic XML documents. Listing 6 shows two queries. The first query is an extension of the above Code. xdburitype is used to retrieve all XML documents in the/CD folder. To extract only the CD title, the second query in Listing 6 is further improved by appending the standard XPath syntax to the end of the URL.

　　Relational Access to Knowledge Base Data

You can also access the XML data in the knowledge base by directly accessing the basic table. The basic table created when I registered the CD mode is the cd331_tab. You can directly write query statements for this table, but these queries must support xml. To simplify access to XML data by using a report tool designed for relational data, you can create a view as shown in listing 7. In addition to views, listing 7 also creates an index for the artist's name. Views and indexes enable me to effectively execute standard link queries such as the following:

Select title
From cd_master
Where artist = 'Carl behrend ';

　　Update XML data

Unfortunately, because all fields in the cd_master view are based on SQL functions, this view cannot be updated. However, you can update the XML data in the knowledge base. I only need to update the basic table created when the registration mode is as follows:

Update cd331_tab CD Set Value (CD) = updatexml ( Value (CD ), '/CD/website/text ()', 'Http: // greatlakeslegends.com/ Legends.htm ');

Note the use of the XPath syntax in this new updatexml function. The path '/CD/website/text ()' indicates the text of the website element of the CD document to be updated. The third parameter of updatexml specifies a new value for that text. This is an appropriate update and highly efficient. The xml db knowledge base does not need to reconstruct the entire XML document that has been changed. Because the schema is registered, the xml db knowledge base can rewrite this query in the way that only the website attribute is touched in the underlying object structure.

　　Conclusion

By using the xml db knowledge base, you can store XML documents in the database and access those documents using standard Internet protocols. At the same time, you can use standard link queries to access the same XML documents or parts of those documents. You do not have XML data or relational data, but only data. "XML" and "relationship" are only different examples of viewing data. By separating data from examples, Oracle9i protects one of your most important assets-data-from the impact of Sample changes.