Building Queries and Data Views
|
 |
 |
|
|
 |
 |
This chapter describes source and target XML schemas, also called target schemas, as used in the Data View Builder to define queries. It also describes how XML namespaces can be used in your queries.
The following topics are covered:
XML schemas are used in Liquid Data to represent the hierarchical structure of various data sets and the query structure. The Data View Builder uses XML schema representations as follows:
For relational databases accessed through JDBC drivers, a schema is automatically generated based on available metadata.
For XML files, views, complex parameter types (CPTs), stored procedures, delimited files, or web services, you first develop and then specify the schema using the WebLogic Administration Console.
Note: For the versions of the XQuery and XML specifications implemented in see Supported XQuery and XML Schema Versions In Liquid Data in the XQuery Reference Guide.
The Data View Builder provides graphical representation of source schemas in a tree structure format. The visual representations can be expanded and collapsed for convenience and readability.
Figure 4-1 Sample source schemas
If you are building a query that depends upon more than one data source, you will use multiple source schemas (one for each data source).
You can apply a keyword search to any source or target schema, as well as to functions. Simply click the Open search icon at the top of the pane and a search field will appear. Enter any valid search string (case does not matter) and if it exists in the pane the string will be highlighted.
Wildcard symbols (? or *) are not allowed. However, any word or partial word will be found if it appears in the pane. For example, if you search on the string TAT the element STATE will be found it if exists in the pane.
Text search is circular beginning at the currently highlighted element. In other words, if the search will be satisfied by an element above the currently highlighted line it will eventually be found if you keep clicking the Search button.
In the Data View Builder you can use source schemas as many times as needed, simply by dragging an additional copy of the data source scheme into the work area.
A source is said to be replicated if the source schema appears multiple times in a query. In XQuery, a source is replicated if document_name appears multiple times in the XQuery, usually appearing in two different for clauses. Similarly, in SQL a source is said to be replicated if the source (table) appears twice in a FROM clause (or in two different FROM clauses).
Source replication is necessary whenever you want to use a data source in a way that will require iterating over the source twice. Another way to state this is when two different tuples from a source will be required at the same time.
Sometimes it is helpful to use more than one copy of a data source schema to improve query performance. In other cases, however, having more than one copy of a data source schema is necessary.
Take, for example, a very simple problem: you want to build a target schema that lists product list prices over a certain amount and under a certain amount. XQuery functions exist for the greater-than [gt] and less-than-or-equal-to [le] test conditions. Using Advanced view you could disable where clause conditions to make the query valid (see Sorting Query Results).
But a clearer and cleaner approach would be to use two source instances that each reference the same data source. From the first instance of the source schema, PB-BB, PRODUCTS would be projected under Expensive products. From the second instance, PB-BB2, PRODUCTS would be projected under Cheap products (Figure 5-34). In both cases, Copy and Paste and Map are used. (See Mapping to Target Schemas for more information on mapping of complex elements to target schemas.)
Figure 4-2 Project Illustrating Use of Two Copies of a Data Source Schema
Then it is a simple matter of creating the [ge]/[lt] conditions as described in To resolve this problem click Advanced view in the Conditions section. You will notice that instead of the two conditions you created, four are listed. This is because Advanced view shows you the actual where clause conditions used in the query, based on application of the Data View Builder best-guess autoscope rules..
The XQuery generated by this project (Listing 4-1) illustrates this approach.
Listing 4-1 XQuery returning product list prices in two groups
<results>
<expensive_products>
{
for $PB_BB.PRODUCTS_15 in document("PB-BB")/db/PRODUCTS
where ($PB_BB.PRODUCTS_15/LIST_PRICE gt 100)
return
$PB_BB.PRODUCTS_15
}
</expensive_products>
<cheap_products>
{
for $PB_BB2.PRODUCTS_21 in document("PB-BB")/db/PRODUCTS
where ($PB_BB2.PRODUCTS_21/LIST_PRICE le 100)
return
$PB_BB2.PRODUCTS_21
}
</cheap_products>
</results>
In the above query PRODUCTS with list prices greater than or equal to [gt] 100 are returned for the PB_BB data source. Similarly, PRODUCTS with list prices less than 100 are returned for the PB_BB2 data source. Of course the underlying data source is the same.
Another example of necessary source replication would be a self-join in SQL. In the classic example of a self-join, the query retrieves all employee names that match a particular manager ID.
SELECT emp.name, mgr.name
FROM employee emp, employee mgr
WHERE emp.manager_id = mgr.id
In XQuery, the query would appear similar to that in Listing 4-2.
Listing 4-2 Query retrieves records where employee manager ID field matches a particular manager ID
<employee_managers>
{
for $emp in document("employee")//employee
for $mgr in document("employee")//employee
where $emp.manager_id eq $mgr.id
return
<employee_manager>
<employee> {$emp.name} </employee>
<manager> {$mgr.name} </manager>
</employee_manager>
}
</employee_managers>
In both of these examples, given the sources, there is no way to write these queries without replicating the source schemas.
In ambiguous cases, both replicating and not replicating a source would lead to reasonable queries. For example, a self-join to get employee-manager pairs was shown in a previous example. Without replicating the source, you could:
Of course, the Data View Builder would interpret this query as: "give me all employees who are their own manager". Under such circumstances the option of creating multiple copies of a source schema reduces possible confusion or confusing results.
A target schema describes the structure of a query result that will be produced when the query runs. As with source schemas, the Data View Builder provides a graphical representation of target schemas in a tree structure format.
Figure 4-3 Sample Target Schema
Target schemas have these main purposes:
You can specify a target schema in the Data View Builder in the following ways:
To open and set a target schema for a project:
This brings up a file browser.
Figure 4-4 Liquid Data Repository Highlighted in File Browser
If you choose Repository in the Open dialog, the Data View Builder displays target schemas in the Liquid Data repository.
Figure 4-5 Schema File Selected
Use these guidelines when working with target schemas:
customer and orders. All examples in Building Queries demonstrate this guideline.Of the several examples included in this section the following particularly demonstrate these guidelines:
Customer table contains first_name, last_name, email, and phone elements and each of those elements is required in the target schema, then you need to map each element of your query before saving it.For a detailed description of target schemas, see Schemas and Namespaces in Liquid Data.
Target schemas are composed of complex elements, simple elements (child elements), and attributes. You can set element properties using the Properties dialog box, which you access by right-clicking on the element.
The following properties can be set:
When you save a project, the schema definitions of all source and target schemas that you mapped in the project are saved. When you reopen the project, Data View Builder first looks for the schema definitions in the Liquid Data repository.
If a schema definition is unavailable, the schema definition saved in the project file is used. Data View Builder adds the schema to the list of available resources, but flags it as offline by putting a red mark over the schema name. A warning is also generated in the WebLogic Administration Console log that queries using this schema will not run.
Offline resources are available only to the previously associated project.
If a schema file has an import statement with a relative path to another schema file, Liquid Data resolves the location of the imported files according to the following rules:
<ldrepository>/schemas directory, attempt to resolve it relative to the directory in which the schema file (the first one with the import statement) is saved.For example, if you have a schema file in the following location in the repository:
<ldrepository>/schemas/dir1/dir2/s.xsd
and it contains the following import statement:
import dir3/file.xsd
then Liquid Data first looks for a schema file named:
<ldrepository>/schemas/dir3/file.xsd
and, if it does not find it relative to the root level of the repository, Liquid Data looks for it in:
<ldrepository>/schemas/dir1/dir2/dir3/file.xsd
As a further example, assume the file.xsd import was resolved in:
<ldrepository>/schemas/dir3/file.xsd
If file.xsd in turn has the following import statement:
import dir4/another.xsd
then Liquid Data first attempts to resolve this import statement relative to the root of the repository:
<ldrepository>/schemas/dir4/another.xsd
If the file is not there, it then resolves it relative to the original <ldrepository>/schemas/dir1/dir2/s.xsd file, as follows:
<ldrepository>/schemas/dir1/dir2/dir4/another.xsd
XML namespaces are a mechanism by which you can ensure that there are no name conflicts (or ambiguity) when combining XML documents or referencing an XML element.
Liquid Data supports XML namespaces and includes namespaces in the queries generated in Data View Builder.
This section includes the following topics:
XML namespaces appear in queries as a string followed by a colon. For example, the xs:integer data type uses the XML namespace xs. Actually, xs is an alias (called a prefix) for the URI name of the namespace. (See Table 4-7 for the full set of predefined XQuery namespaces.)
XML namespaces ensure that names do not collide when combining data from heterogeneous XML documents.
For example, there could be an element <tires> in a document related to automobile manufacturers. In a document related to bicycle tire manufacturers, there is also a <tires> element. Obviously, combining these elements would be problematic under most circumstances. XML namespaces easily avoid such name collisions by referring to the elements as <automobile:tires> and <bicycle:tires>.
In a XML schema namespaces — including the target namespace — are declared in the schema tag. Here is an example:
<schema xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:bea="http://www.bea.com/public/schemas"
targetnamespace="http://www.bea.com/public/schemas"
...
The first line of the above schema contains the default namespace, which is the namespace of all the unqualified elements in the schema.
For example, if you see the following element in a schema document:
<element name="appliance"type="string"/>
the element element, and the attribute name and type all belong to the default namespace, as do unprefixed types such as string.
The second line of the schema contains a namespace declaration — bea — which is simply an association of a URI with a prefix. There can be any number of such declarations in a schema.
Lastly, comes the target namespace, declared with the targetNamespace attribute. It this case, the target namespace is bound to the namespace declared on the second line, meaning that all element and attribute names declared in this document belong to:
http://www.bea.com/public/schemas
References to types declared in this schema document must be prefixed. For example:
<complexType name="AddressType">
<sequence>
<element name="street_address" type="string"/>
...
</sequence>
</complexType>
<element name="address" type="bea:AddressType"/>
The following table shows predefined namespaces used in XQuery:
The following are some Internet links where you can find more information on XML namespaces:
http://www.w3.org/TR/REC-xml-names/
See also Supported XQuery and XML Schema Versions In Liquid Data in the XQuery Reference Guide.
The Data View Builder automatically generates the correct namespace declarations when generating a query.
However, when a target schema is created in the Data View Builder, its elements and attributes are unqualified, meaning that the target namespace is not automatically part of the element or attribute name.
Figure 4-8 Example of a schema with unqualified attributes and elements
If you want elements and attributes appear as qualified, you need to use an editor outside Data View Builder to modify the generated schema for either or both attributeFormDefault and elementFormDefault to be set to qualified. See Listing 4-3.
Listing 4-3 Schema Tag Setting Elements and Attributes to Qualified (emphasis added)
<xsd:schema targetNamespace="urn:schemas-bea-com:ld-cocpt" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:cocpt="urn:schemas-bea-com:ld-cocpt" attributeFormDefault="qualified" elementFormDefault="qualified">
Once attributes and elements have been set to qualified, they will appear as such in the Data View Builder when the target schema is set to your newly edited file.
Figure 4-9 Example of a schema with qualified attributes and elements
Note: If you are hand-coding your queries (not using the Data View Builder as a query generator), you must include the necessary namespace declaration(s) to satisfy Liquid Data server requirements. For a list of data sources that require namespace declarations, see Data Sources that Require Namespace Declarations on page 4-18.
The beginning portion of an XQuery is known as the prolog. For Liquid Data queries, the namespace declarations appear in the XQuery prolog. There can be zero or more namespace declarations in a query prolog. Each namespace has the following form:
namespace <logical_name> = "<URI>"
where <logical_name> is a string used as a prefix in the query and <URI> is a uniform resource indicator.
Consider the following simple query:
namespace view = "urn:views"
<CustomerOrderID>
{
for $view:MY_VIEW.order_2 in
view:MY_VIEW()/results/result/BroadBand/order
return
<ORDER_ID>{ xf:data($view:MY_VIEW.order_2/ORDER_ID) }
</ORDER_ID>
}
</CustomerOrderID>
namespace view = "urn:views"
is the namespace declaration in this query. Each time the object (in this case, MY_VIEW) is referenced in the query, the object name is prefixed with the logical name view.
You must define namespaces in the XQuery prolog in order to use them in a query (except for the predefined namespaces described in Predefined Namespaces in XQuery on page 4-15). If you do not define namespaces in the XQuery prolog, the query will fail with a compilation error.
When you use the Data View Builder to create or modify target schemas, you can specify a namespace for an element or an attribute. Such a specified namespace is added to the XML markup in the query (and therefore to the query results).
You can set or change a target namespace using the Target Namespace menu option, available from the Data View Builder Query menu when in Design mode.
Figure 4-10 Target Namespace Dialog Box
Figure 4-11 shows adding a local name called db to an element of the target schema named crm2 from the Properties dialog box. If multiple namespaces are available, you can select one from the drop-down list box.
You can access the Properties dialog box by right-clicking on an element in your target schema.
Figure 4-11 Properties Dialog Box
The query results for this target schema definition are of a form similar to:
<crm2:db xmlns:crm2="urs:schemas-bea-com:ld-crmp"> 100.0 </crm2:db>
All data sources except relational databases and XML files require the namespace declaration in the XQuery prolog. Thus the following data sources require namespace declarations in the XQuery prolog:
Liquid Data 1.0 did not support XML namespaces, and any queries used in Liquid Data 1.0 must be migrated to work in Liquid Data 8.1. If you have queries that are generated in a Data View Builder project file, you can open the project file in Data View Builder 8.1. When you click the Test tab, the Data View Builder automatically generates the new query with the proper namespace declarations in the query prolog.
If you have stored queries and data views, you must use the queryMigrate tool to migrate the queries so they work properly in Liquid Data 8.1. For information on the queryMigrate tool, see Migrating from Liquid Data 1.0 to 8.1 in the Liquid Data Migration Guide.
|
|
|
