Example inserts a Date element as the last child of an Action element. Example deletes the LineItem element whose ItemNumber attribute has value Example creates a view of table purchaseorder. A SQL query that involves XQuery expressions can often be automatically rewritten optimized in one or more ways. This optimization is referred to as XML query rewrite or optimization.
XPath expressions are a proper subset of XQuery expressions. The rewritten SQL statement can also make use of any B-tree indexes on the underlying data structures. This can take place for both queries and update operations. As with database queries generally, you determine whether tuning is required by examining the execution plan for a query.
If the plan is not optimal, then consult the following documentation for specific tuning information:. In addition, be aware that the following expressions can be expensive to process, so they might add performance overhead when processing large volumes of data:. Several competing optimization possibilities can exist for queries with XQuery expressions, depending on various factors such as the XMLType storage model and indexing that are used. By default, Oracle XML DB follows a prioritized set of rules to determine which of the possible optimizations should be used for any given query and context.
This behavior is referred to as rule-based XML query rewrite. In this mode, Oracle XML DB estimates the performance of the various XML optimization possibilities for a given query and chooses the combination that is expected to be most performant. Example shows the optimization of XMLTable in the same context. Here again is the query of Example , together with its execution plan, which shows that the query has been optimized.
The XQuery result is never materialized. Instead, the underlying storage columns for the XML collection element LineItem are used to generate the overall result set. This example traverses table oe. The XMLTable expression is evaluated for each purchase-order document. The UnitPrice attribute of collection element LineItem is an appropriate index target. Instead of using table purchaseorder from sample database schema HR , you could manually create a new purchaseorder table in a different database schema with the same properties and same data, but having OCTs with user-friendly names.
With this index defined, the query of Example results in the following execution plan, which shows that the XMLTable expression has driven the overall evaluation. You can examine an execution plan for your SQL code to determine whether XQuery optimization occurs or the plan is instead suboptimal. When this mode is on, the plan of execution is automatically checked for XQuery optimization, and if the plan is suboptimal then an error is raised and diagnostic information is written to the trace file indicating which operators are not rewritten.
The main advantage of XMLOptimizationCheck is that it brings a potential problem to your attention immediately. For this reason, you might find it preferable to leave it turned on at all times. Then, if an application change or a database change for some reason prevents a SQL operation from rewriting, execution is stopped instead of performance being negatively impacted without your being aware of the cause.
Users of older releases directly manipulated event to obtain XQuery optimization information. Only the event is available to Java users. You can improve the performance of fn:doc and fn:collection queries over the Oracle XML DB Repository, by linking them to the actual database tables that hold the repository data being queried.
When repository XML data is stored object-relationally or as binary XML, queries that use fn:doc and fn:collection are evaluated functionally; that is, they are not optimized to access the underlying storage tables directly. To improve the performance of such queries, you must link them to the actual database tables that hold the repository data being queried.
You can do that in either of the following ways:. These SQL functions reference repository resources in a performant way. Both methods have the same effect. Oracle recommends that you use the ora:defaultTable pragma because it lets you continue to use the XQuery standard functions fn:doc and fn:collection and it simplifies your code.
You can use Oracle XQuery extension-expression pragma ora:defaultTable to improve the performance of querying repository data. Oracle XQuery extension-expression pragma ora:defaultTable lets you specify the default table used to store repository data that you query. Example illustrates this; the query is rewritten automatically to what is shown in Example For clarity of scope Oracle recommends that you apply pragma ora:defaultTable directly to the relevant document or collection expression, fn:doc or fn:collection , rather than to a larger expression.
XQuery function fn:data is used here to atomize its argument, in this case returning the XMLRef node's typed atomic value. In Example , the various FLWOR clauses perform these operations: The outer for iterates over the sequence of XML elements returned by fn:collection : each element corresponds to a row of relational table oe. See Also: Example for the execution plan of Example See the serialization specification for more information on this topic. XQuery defines the Static Context as follows:.
The static context includes information like the following refer to the XQuery specification for the complete list :. Most of the components in the static context can be initialized or augmented in the query prolog. In the following example, the boundary-space policy is explicitly specified:. If a static context component is not initialized in the query prolog, an implementation default is used.
In theory this means that the same query can behave in substantially different ways between two "conformant" XQuery implementations. Applications often need to change the defaults for some of the static context components.
If preserving boundary spaces in all queries is a requirement, applications have the option to add the boundary-space declaration to the queries, as shown above.
But in many cases it is preferable to override the implementation's default through the API, so that the same settings are applied to all queries. First, you must retrieve the implementation's default values for the static context components through an XQStaticContext object. XQStaticContext defines setter and getter methods for the various static context components. As shown in the previous example, an XQStaticContext is a value object. Changing any of the static context components doesn't have any direct effect.
Once the static context is updated, all and only subsequently created XQExpression and XQPreparedExpression objects will assume the new values for the static context components. In the previous examples, the static context is updated at the connection level, and as such all subsequently created XQExpression and XQPreparedExpression objects are affected.
This is perfect if you want all your XQuery expressions to be based on the same defaults in the static context. Again, such an approach is useful if some static context components need to be changed for a specific expression, but you want to keep the default values for most of the other expression being executed. Almost all static context components are accessible through XQStaticContext. Here is the list:. The most frequently used properties are "Binding mode" and "Scrollability," and they are discussed in other chapters of this XQJ tutorial.
Supporting query timeout, which sets the number of seconds an implementation will wait for a query to execute, is optional; implementations are free to ignore it. However, it is out of scope for this XQJ tutorial to go into all the details.
XQuery defines a sequence type as a type that can be expressed using the SequenceType syntax. It consists of an item type that constrains the type of each item in the sequence, and a cardinality that constrains the number of items in the sequence. There are some more attributes defined on XQItemType related to user defined schema types, but that would bring us too far afield in the context of this introductory tutorial.
It's a long list; here is a small excerpt:. Iterating over query results, XQJ allows you to request precise type information about each item. Suppose you want to use a different get XXX method, depending on the item type:. This can make your code rather complex and long. Sometimes it can't be avoided, but most of the time a number of shortcuts are available to you.
As explained in Processing Query Results , you can use some of the more the general purpose methods. Suppose you need a DOM node in case the query returns a node, and the string value for all atomic values. The next simple example shows how to do this:. That's it for the dynamic type of items. With DataDirect XQuery , this example outputs xs:integer to stdout. Similarly, you can use the prepared expression to retrieve information about the external variables. As shown in the next examples, first we determine the external variables declared in the query; next we retrieve the static type of each of the external variables:.
A fully functional example of such an "XQuery Web Service" is available on www. All the information required to generate such an XML schema definition is available in the sequence type of each declared variable. And XQJ makes this information immediately accessible. We could write a piece of code translating the relevant item kinds and base types to an XML Schema definition as shown above — it's only a matter of writing a number of Java switch statements.
But is there an easier way? More precisely, it is a requirement to return a human-readable string. Applications have also the ability to create XQItemType objects. Creating XDM Instances show how this can be done and describes other use cases. This section contains more details on that subject. XQJ defines this mapping and glues it all together. Let's take a look at a simple example:. It provides a number of methods to bind values to external variables.
The first argument to the bindObject method is a QName , which identifies the external variable in your XQuery. The second argument is the Java object to be bound. The third argument, for which null is specified in the example above, allows you to override the default mapping. This is shown next. A Java Integer is by default mapped to xs:int. The second external variable is bound with an xs:integer instance — the application explicitly specifies to create such an XDM instance.
As such, the last 'instance of' evaluates to false, as xs:integer is not extending xs:int. We have introduced the bindObject method through some examples, but XQDynamicContext has many more bind methods.
String and will convert it to the specified type according to the casting rules from xs:string; essentially, the specified string must be in the lexical space of the specified atomic type.
In contrast, the following two bindAtomicValue invocations will fail. The first because "abc" is not in the value spaces of xs:integer. The second one because no type has been specified as third parameter; unlike with bindObject , bindAtomicValue has no default mapping and a XQItemType must be specified as third argument:. The default mapping for int is xs:int; as such we specify the type as third parameter:. Binding a DOM node is also possible; basically it is equivalent to bindObject, with the restriction that the argument must be a DOM node and as such the XDM instance is always a node, never an atomic value.
Let's read an XML document, foo. But of course, this is something you only want to use in specific scenarios. The simple use case of binding an XML file can be easily accomplished in a single line. In the previous chapters, we learned how to handle XDM instances that result from query execution; iterating through sequences; and getting access to the items in the sequence.
What if we want to create an XDM instance, without executing a query? This functionality is offered through the XQDataFactory interface. An XQDataFactory can create the following types of objects:. In XQJ 1. We also showed how these objects are used to describe the static type of a query result and external variables. How do we create such type objects in our application? Using dom4j , similar to McDowell's solution :. XML handling is a bit simpler using dom4j. And several other comparable XML libraries exist.
Alternatives to dom4j are discussed here. I like XOM more than dom4j for its simplicity and correctness. The comments of this answer :. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams?
Collectives on Stack Overflow. Learn more. Asked 12 years, 8 months ago. Active 3 years, 10 months ago. Viewed 37k times. What would be the simplest way to do this? Improve this question. I'll show you using an example Consider this compressed XML example, ie. This is done automatically through the process that we call auto-flattening of tables. If you prefer, you can also flatten the tables manually if you wish to compose the table model yourself but I'll have to dedicate a seperate blog entry to that topic sometime.
The XML-elements to tables mapping enables you to do all the cool querying stuff with your XML files, that I have shown you so many times before.
0コメント