Oracle XML DB provides localized formats and relational database access.
XML is rapidly becoming the preferred language of data exchange between enterprises. However, most companies store their data in a relational database as the Oracle9i database. So how do you connect to the hierarchical, document-centric XML and table formats? Are you stored XML documents as files in a file system? Or separate the XML document, store the data in the relational database? When choosing between two methods, it is necessary to trade according to your data. But if you don't have to choose? What do you do if you can use both methods at the same time? You can use the Oracle9i Database 2nd Edition called XML DB Repository (XML DB Repository).
XML DB Knowledge Base Description
Oracle XML DB is neither a separate product is not a separate option you have to install. Oracle XML DB refers to the collection of XML features and technologies directly in the Oracle9i database. One of the key features is the XML DB knowledge base. This knowledge base allows you to store XML documents directly in the Oracle9i database. Once your XML document is stored in this knowledge base, you can access XML data in an XML-centered manner or in relation to relationships.
To store XML data in your database, you only need to write an XML document file using all industry standards such as FTP, HTTP, or WebDAV. The XML data from the database is just as simple as performing a SQL query or by reading one of the above protocols.
Scene setting
Suppose you are selling the CD made by the independent artist. You need to exchange information with the main music stores, online sites and artists themselves. You have developed the XML document format used to describe the CD content, and now you want to store information in the database using the XML DB knowledge base. You want to easily access data via SQL and easily access the local XML document. In short, you want data to have both relationship characteristics and hierarchical features. In this article, I am your DBA, achieving it is my job.
Registered XML mode
My first step is to register your XML mode using the XML DB Knowledge Base. When I register an XML mode, the knowledge base creates an object type and object table that can save that mode instance. The call to the DBMS_Xmlschema.Registeruri executed by SQL * PLUS is obtained from http://gennick.com/cd.xsd to the XML schema shown in Listing 2, then register it:
Begindbms_xmlschema.registeruri ('cd.xsd', 'http://gennick.com/cd.xsd'); END; /
Note: In addition to requiring CREATE privileges for various mode object types, I also need ALTER SESSION and Query Rewrite permissions in order to register and create this article.
Listing 3 shows some of the structures and objects created due to registration CD mode. A name is the XML table of CD331_TAB is created to save the mode: each CD document in the knowledge base will be represented by one line in this table. I can get a list of XML tables by querying the user_xml_tables data dictionary view. In this example, I simply query the view before and after the mode registration and look up the new table name. Each line in the CD331_Tab will contain an instance of a CD327_t type, which corresponds to our XML mode to create. The first layer element of the XML document is represented as an attribute, attribute name, and XML field name of the CD327_T type. For example, the Title field in the object type directly corresponds to the title element in the XML mode. The SONGS field corresponds to the Songs element. Songs is a complex element in XML mode, which is also mapped to another object type "SONGS328_T". If I use SQL * Plus command describe "songs328_t", and continue to study the definition of the Songs field, I will see the song collection is ultimately implemented as a varray, in Varray, each element represents a song. When I register a pattern, I can control Oracle9i database generated objects and type names; I can also control specific data types used to store my XML data. These controls can be made by using the attribute defined by the XML DB Knowledge Base and the part of the ORAXDB namespace. When I don't provide those properties, the Oracle9i database generates them, I can simply browse the content generated by the Oracle9i database by viewing the mode version stored in the knowledge base. Figure 1 illustrates how you can easily access the knowledge base data, this time is accessed by HTTP, using a standard web browser to access. Figure 1 shows a part of the CD mode in my knowledge base, you can see the mode annotation, all of them start with "oraxdb". Note that the URL uses the 8080 port, which is the default HTTP port used by the Knowledge Base.
By default, all objects created when registration mode will belong to users of registration mode. In this example, I have the tables and types in Listing 3 and all objects related to the CD mode. Because I registered the pattern, any of the XML files I saved to the knowledge base (the instance of this CD mode) will be disassembled and stored in the CD331_TAB table. This mode and registration is mortified. The CD files saved by other users will not be stored in my table. You can use an optional parameter to 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
If you want to store the CD XML document in the XML DB Knowledge Base, I need a folder that stores them. To create a folder, I log in to Oracle as the System user and perform the PL / SQL block in Listing 4. Call DBMS_XDB.CREATEFOLDER Create a first layer folder named / CD. The PL / SQL block then creates all folder rights to the owner (which is the System user) using the DBMS_XDB.SetaCl process (i.e. Next In order to change the owner of the folder by SYSTEM to Gennick, you need to call the UPDATE statement on the database's resource_view. After the folder is created, the submission is important; until you are submitted, the folder is visible to other sessions. Now I can use the FTP or WebDAV connection as Gennick and save the XML file to the / CD folder.
Save an XML document
Once I register the mode and create a folder to save my XML document, save the document to the knowledge base as simple as copying files. Listing 5 shows a copy (shown in Listing 1) LegendSoftHegreatlakes.xml file to the FTP session of the knowledge base. The port 2100 used by the FTP Open command is the default port used by the Knowledge Base as the FTP session. Note that I can use FTP, but simply use Windows copy and paste operations like the WebDAV and Windows Web folders. Use resource_view
An important view you should know is a view called Resource_View. The Resource_View view returns a row for each document and folder in the knowledge base you visit. For example, by performing the following query you can get a list of all XML documents under the / CD folder.
Select any_pathfrom resource_viewwhere under_path (res, '/ cd') = 1and extractValue (res, '/ resource / contenttype') = 'text / xml'; Any_Path ----------------- -------------- / CD / gospel / nothingless.xml / cd / legendsofthegreatlakes.xml
The new Under_Path function displayed above allows you to test if a given knowledge base resource is in the folder (or path) you specify. In this example, I use this function to qualify the query results in the / cd folder and their subfolders. By using a hierarchy index created by the Basic Table, the path-based query for the Resource_View view is more efficient. This index is part of the knowledge base; you don't have to create it.
Figure 1: Part of the example 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 representing the resource. Use the new ExtractValue function for the RES field to check the content type of each resource. Therefore, the results of the query are further limited to the path to the XML document. '/ Resource / contenttype' grammar represents the XPath representation. XPath is a standard representation of each part of the XML document; you will use it in many queries for XML data.
Give a repository path, you can retrieve all or part of the basic XML documentation using the new xdburitype object type. Listing 6 shows two queries. The first query is an extension of the above code, using XDBuritype to retrieve all XML documents under the / CD folder. In order to extract only the CD title, the second query in Listing 6 has been further improved, and the standard XPath syntax is attached to the end of the URL.
Access to the relationship between 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 CD mode is CD331_TAB. You can write a query statement directly, but these queries must be supported by XML. To simplify the access to XML data by designing report tools for relational data, you can create a view as shown in Listing 7. In addition to the view, Listing 7 also created an index for the artist name. Views and indexes allow me to effectively implement the following standard relationship queries:
SELECT TIM 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 the basic table created when updating the registration mode as shown below:
Update CD331_TAB CDSET VALUE (CD) = UpdateXML (Value (CD), '/ CD / Website / Text ()', 'http://greatlakeslegends.com/legends.htm'); Note this new UpdateXML function in XPath The use of grammar. Path '/ CD / Website / Text ()' shows the text I want to update the Website element of the CD document. The third parameter of UPDATEXML is the new value for that text. This is an appropriate update and is very efficient. The XML DB Knowledge Base does not need to rebuild the entire XML document that is changed. Because the mode is registered, the XML DB Knowledge Base can rewrite this query in a way that the underlying object structure is only hit by the WebSite property.
in conclusion
By using an XML DB Knowledge Base, you can store the XML document in the database and use standard Internet protocols to access those documents. At the same time, you can also use standard relationship query access to the same XML document or part of those documents. You don't have XML data and relational data, only data. "XML" and "relationship" are just different examples of data. Oracle9i protects your most important assets by separating data from examples from examples - data - data - Data - from exemplification.
An important view you should know is a view called Resource_View. The Resource_View view returns a row for each document and folder in the knowledge base you visit. For example, by performing the following query you can get a list of all XML documents under the / CD folder.
Select any_pathfrom resource_viewwhere under_path (res, '/ cd') = 1and extractValue (res, '/ resource / contenttype') = 'text / xml'; Any_Path ----------------- -------------- / CD / gospel / nothingless.xml / cd / legendsofthegreatlakes.xml
The new Under_Path function displayed above allows you to test if a given knowledge base resource is in the folder (or path) you specify. In this example, I use this function to qualify the query results in the / cd folder and their subfolders. By using a hierarchy index created by the Basic Table, the path-based query for the Resource_View view is more efficient. This index is part of the knowledge base; you don't have to create it.
Figure 1: Part of the example 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 representing the resource. Use the new ExtractValue function for the RES field to check the content type of each resource. Therefore, the results of the query are further limited to the path to the XML document. '/ Resource / contenttype' grammar represents the XPath representation. XPath is a standard representation of each part of the XML document; you will use it in many queries for XML data.
Give a repository path, you can retrieve all or part of the basic XML documentation using the new xdburitype object type. Listing 6 shows two queries. The first query is an extension of the above code, using XDBuritype to retrieve all XML documents under the / CD folder. In order to extract only the CD title, the second query in Listing 6 has been further improved, and the standard XPath syntax is attached to the end of the URL.
Access to the relationship between 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 CD mode is CD331_TAB. You can write a query statement directly, but these queries must be supported by XML. To simplify the access to XML data by designing report tools for relational data, you can create a view as shown in Listing 7. In addition to the view, Listing 7 also created an index for the artist name. Views and indexes allow me to effectively implement the following standard relationship queries: SELECT TIM 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 the basic table created when updating the registration mode as shown below:
Update CD331_TAB CDSET VALUE (CD) = UpdateXML (Value (CD), '/ CD / Website / Text ()', 'http://greatlakeslegends.com/legends.htm');
Note the use of XPath syntax in this new UpdateXML function. Path '/ CD / Website / Text ()' shows the text I want to update the Website element of the CD document. The third parameter of UPDATEXML is the new value for that text. This is an appropriate update and is very efficient. The XML DB Knowledge Base does not need to rebuild the entire XML document that is changed. Because the mode is registered, the XML DB Knowledge Base can rewrite this query in a way that the underlying object structure is only hit by the WebSite property.
in conclusion
By using an XML DB Knowledge Base, you can store the XML document in the database and use standard Internet protocols to access those documents. At the same time, you can also use standard relationship query access to the same XML document or part of those documents. You don't have XML data and relational data, only data. "XML" and "relationship" are just different examples of data. Oracle9i protects your most important assets by separating data from examples from examples - data - data - Data - from exemplification.
