Virtuoso offers XPATH as a query language for XML views. The statement is there converted into SQL in the context of the mapping defined by the __view XPATH option, which is mandatory. An XPATH query string is a valid top level SQL statement. This is interpreted as a single select or union of selects with the result columns being specified by various XPATH options.
The basic query string
XPATH [__view "cat"]/category
will select any top level category elements from the cat XML view, defined with CREATE XML VIEW. This has a single result column with the serialization string of the selected entity as value. This string starts with the category start tag and ends with the corresponding end tag. As many result rows are generated as there are top level category nodes in the view.
This basic behaviour can be modified by XPATH options enclosed in brackets after the XPATH keyword. These options allow specifying the output columns of the generated select statement.
<xp option> ::= __* | __http | __key | __view NAME | __tag NAME | __quiet | __base_uri STRING | xmlns:NAME '=' STRING | xmlns '=' STRING | __lang STRING | __enc STRING | KEYWORD '=' KEYWORD <option list> ::= | <option list> '[' <option> [...] ']'
The option list may occur between the XPATH keyword and the start of the path, e.g.
XPATH [__key __view "cat"] /category
The effects of the options are as follows:
Send the serialization of the result entities to the HTTP client. This may only be used inside a VSP page context.
Select the key of the selected entities instead of the serialization text.
Select all columns of the selected entity instead of its serialization text. This is only valid when __view is specified and the result set is homogeneous.
Specify that the query be interpreted in the context of the specified CREATE XML VIEW, directly accessing the tables. This is mandatory since the elements referenced can only be mapped to tables in the context of an XML view.
Specify that the query should not signal non-fatal errors. This option is for cases when an incomplete result is anyway better than nothing. Typical example is search in the collection of documents where not all documents are valid.
Specify the base URI that can be used to resolve relative URIs in calls of XPATH functions processXQuery(), processXSLT() and processXSLT(). This is similar to the efect of "declare base-uri" in XQuery.
This declares a pair of namespace prefix and namespace URI for use in the query expression, e.g. xmlns:xs="http://www.w3.org/2001/XMLSchema" or xmlns:ora="http://schemas.oracle.com/xpath/extension"
This declares the default namespace URI for use in the query expression, e.g. xmlns="http://www.example.com/my-schema". This namespace will become both default element namespace and default function namespace.
This declares the language that is used for text search expressions. This is for internationalization purposes only. See subsection "Adding New Languages And Encodings Into Virtuoso" for more details.
This declares encoding of strings that are used for text search expressions. This is for internationalization purposes only. See subsections "Encoding in XPath Expressions" and "Adding New Languages And Encodings Into Virtuoso" for more details.
This is useful only if the expression uses XPATH functions like document() or doc() or if the XPATH expression is an argument of xpath_contains or similar special SQL predicate. These are configuration options that XPATH processor provides to the XML parser when the processor should read a document. Section "Configuration Options of the DTD Validator" lists all supported options.
The XML view mechanism allows generating XML content from relational data and to query relational data as if it were XML without first converting it to XML.
<xml view> ::= CREATE XML VIEW <name> as [ <namespaces_def> ] <element list> <opt_public> <xml view> ::= CREATE XML VIEW <name> as [ <namespaces_def> ] <query spec> [ELEMENT] <opt_public> <namespaces_def> ::= '[' <id_namespace> '='( name | <path string> ) [ ...] ']' <element list> ::= <element> [, ...]* <element> ::= <table> <correlation name> AS [ <id_namespace> ':' ] <element> <columns> [ ON '(' <search condition> ')'] [primary key '(' column_commalist ')' ] [ELEMENT] [ '{' <element list>'}' ] <opt_public> ::= PUBLIC <path string> OWNER <DAV owner name> [PERSISTENT] [INTERVAL <minutes>] <columns> ::= '(' <column> [, ...] ')' <column> ::= <column name> | <column name> AS [ <id_namespace> ':' ] <attribute name> <id_namespace> :: = identifier
The XML view declaration establishes a 'virtual document' a context within which XML hierarchy relationships can be translated into arbitrary joins. The virtual document can be then materialized into an actual set of persistent XML elements or used to generate SQL from XPATH.
Each table in the declaration generates an element into the result document. SQL views can be used as tables to accommodate for hidden joins, sub-queries, ordering and aggregates. If a view is used, which by nature has no primary key, the primary key clause should be used to define a uniquely identifying set of view columns.
The only restriction on the XML view declaration is that each branch has a fixed depth.
The structure of joins used to make the text can be specified in two ways: As a SQL query specification, that is a SELECT from a list of tables with a WHERE clause specifying the joins, or as a tree of join elements. The first form is called automatic and the second is called explicit. The automatic form allows generating a tree with as many levels as there are tables in the join, with elements derived from the rows at each level occupying each level of the hierarchy
With both forms the columns of the tables are mapped into either attributes or child elements of the element representing each row. In the explicit mode, the attribute / element choice can be made for each table, in the automatic mode for the entire view. The explicit mode also allows specifying a different element / attribute name whereas the automatic mode takes the name from the column name. Even if the columns are presented as child elements in the output text, they should be referenced as attributes in XPATH queries evaluated in the context of the view.
An XML namespaces can be used in XML view with two restrictions - all namespace names must be different and the first three letters of namespace name must not be 'xml', except for default namespace with name 'xmlns'.
In the explicit form each level of the hierarchy is declared as a list of child elements. Each such element maps one table or view into an entity according to a join condition. The join conditions can reference columns from the associated table and columns from tables in parent elements. The join condition can also have scalar filtering conditions. A top element's join condition may only specify scalar conditions.
Each set of sibling child nodes is delimited by braces {}. The top level of the view typically consists of one element in the outermost braces. This element has itself a child list delimited by braces. Each such list can have more than one different element.
Each element specifies:
SQL table
Correlation name for use in subsequent joins for this table
XML element name to use for delimiting a row of this table
List of columns, with optional XML element or attribute names
join condition - will relate rows of this table to rows of the table in the enclosing element. If this element is at the top level, this can only consist of scalar conditions
Optional PRIMARY KEY clause, needed if the table in this element is a view, does not have a primary key or if a non-primary key unique identity is desired
Optional ELEMENT flag
Optional list of child elements, delimited by braces
A correlation name is mandatory for all the tables.
The column list can mention a single column or a single column renamed into an XML attribute of a different name. If a column of a table is referenced in a subsequent join condition it must appear in the output columns list. Expressions are not directly allowed but a view with expression columns can be used.
The opt_public clause, when present, offers a shorthand for calling xml_view_publish at the same time as making the definition. This makes a DAV resource reflecting the contents of the view. The contents may either be generated on demand or persisted as a DAV accessible XML document. In the latter case the document may be regenerated at a fixed interval. The interval is expressed in minutes.
The path is expressed as an absolute path from the root collection of the DAV server.
This root collection may be mapped into various places in the web server's URL space.
create xml view xx ... public '/xx.xml' owner 'dav' persistent interval 1;
is equivalent to:
create xml view xx ...; xml_view_publish (xx, /xx.xml', 'dav', 1, 1);
A DAV resource created in this manner can be deleted as any DAV resource. The XML view itself is not affected but a possibly existing refresh job will be automatically deleted.
One XML view can be published several times with different names and owners. There may also exist persistent and non-persistent publications of the same view.
The CREATE XML VIEW statement defines stored procedures for generating an XML text fragment corresponding to each element declared in the view.
The names of the procedures are composed as follows:
create procedure http_view_<view name> (inout output_mode)
create procedure http_<view name>_<element name>_<correlation name> (in pk1 any, ..., in output_mode integer)
An http output procedure is created for each <element> in the create xml view declaration. It takes the primary key columns of the table in question in key order plus a mode flag. It then outputs the serialization of the specified element and any child elements. For an output mode of 0 the result goes directly to an HTTP client. For an output_mode of 1 the procedure returns the serialization as a string. Note that for this to work the tables in question must be real tables and the join conditions must only reference the next higher table in the create xml view tree. Further, the primary key columns of each table should be mentioned in the columns list for that table along with any foreign keys referenced in subsequent join conditions.
create xml view "cat" as { "Demo"."demo"."Categories" "C" as "category" ("CategoryID", "Description" as "description") { "Demo"."demo"."Products" "P" as "product" ("ProductName") on ("P"."CategoryID" = "C"."CategoryID") } }
This declares a two level hierarchy with a category node for each category and a product child node for each product in the category.
create xml view "cats_e" as select "category"."CategoryID", "CategoryName", "ProductName", "ProductID" from "Demo".."Categories" "category", "Demo".."Products" as "product" where "product"."CategoryID" = "category"."CategoryID" element;
Here is a similar example, this time using the element option.
The procedures are
xmlg_cat http_cat_category_C (in categoryid any, in _out integer); http_cat_product_P (in productid any, in _out integer);
In the following example the function returns the selected items as an XML fragment. Consecutive elements are separated by new-lines for readability.
SQL> call "http_cat_category_C" (1, 1); 1 sets? Done. -- 5 msec. RESULT= <category CategoryID="1" description="Soft drinks, coffees, teas, beers, and ales" > <product ProductName="Chai" ></product> <product ProductName="Chang" ></product> <product ProductName="Guarana Fantastica" ></product> <product ProductName="Sasquatch Ale" ></product> <product ProductName="Steeleye Stout" ></product> <product ProductName="Côte de Blaye" ></product> <product ProductName="Chartreuse verte" ></product> <product ProductName="Ipoh Coffee" ></product> <product ProductName="Laughing Lumberjack Lager" ></product> <product ProductName="Outback Lager" ></product> <product ProductName="Rhönbrooou Klosterbier" ></product> <product ProductName="Lakkalikööri" ></product> </category>
The below example shows how to use a SQL view for hiding a join. The below view generates for each table a set of column children and a set of index children, which in turn have column children.
create view KEY_COLS as select KP_KEY_ID, KP_NTH, C.* from SYS_KEY_PARTS, SYS_COLS C where COL_ID = KP_COL; create xml view "schema" as { DB.DBA.SYS_KEYS k as "table" ("KEY_TABLE" as "name", KEY_ID as "key_id", KEY_TABLE as "table") on (k.KEY_IS_MAIN = 1 and k.KEY_MIGRATE_TO is null) { DB.DBA.KEY_COLS c as "column" (COLUMN as name) on (k.KEY_ID = c.KP_KEY_ID) primary key (COL_ID), DB.DBA.SYS_KEYS i as "index" (KEY_NAME as "name", KEY_ID as "key_id", KEY_N_SIGNIFICANT as "n_parts") on (i.KEY_TABLE = k.KEY_TABLE and i.KEY_IS_MAIN = 0 and i.KEY_MIGRATE_TO is null) { DB.DBA.KEY_COLS ic as "column" (COLUMN as "name") on (ic.KP_NTH < i.KEY_N_SIGNIFICANT and ic.KP_KEY_ID = i.KEY_ID) primary key (COL_ID) } } };
The following query will return the subtree describing the Customers table in the demo database:
XPATH [__view 'schema'] /table[@name = 'Demo.demo.Customers']; <table name="0" key_id="1011" table="Demo.demo.Customers" > <column name="CustomerID" ></column> <column name="CompanyName" ></column> <column name="ContactName" ></column> <column name="ContactTitle" ></column> <column name="Address" ></column> <column name="City" ></column> <column name="Region" ></column> <column name="PostalCode" ></column> <column name="Country" ></column> <column name="Phone" ></column> <column name="Fax" ></column> <index name="City" key_id="1012" n_parts="2" > <column name="City" ></column> <column name="CustomerID" ></column> </index> <index name="CompanyName2" key_id="1013" n_parts="2" > <column name="CompanyName" ></column> <column name="CustomerID" ></column> </index> <index name="PostalCode2" key_id="1014" n_parts="2" > <column name="PostalCode" ></column> <column name="CustomerID" ></column> </index> <index name="Region" key_id="1015" n_parts="2" > <column name="Region" ></column> <column name="CustomerID" ></column> </index> </table>
The automatic form of CREATE XML VIEW will take a select statement and infer a hierarchy from it, based on the order of tables in the from clause. The parent table should be to the left of its children. This is practical if the tables form a hierarchy in application terms, like orders and order lines or departments and employees. This notation allows arbitrary depth but all siblings at the same level will be of the same type. Elements of child rows will be child elements of the element of their parent row, where the join condition identifies the child rows for one parent row.
The columns in the selection will appear as attributes or child elements of the rows selected. The names of the attributes will be the names of the columns. The names of the siblings will be the names of the tables in the from clause, without qualifiers or owners. Expressions should not appear in the selection. If the use of expressions is required then you may create a SQL view first to facilitate this.
The ELEMENT keyword may be present at the end of the select, before the publishing keywords. This will cause all columns to be represented as child elements of the element corresponding to the row. Note that even if the element switch is present, the values will appear like attributes in an XPATH query inside the view.
SQL views or derived tables may not appear directly in the select. The reason for this is that a procedure is generated for each level of the generated XML tree and that this must take unique identifying column values for the element in question. If one desires to use a view, the explicit form should be used, with the primary key option specified where appropriate.
Each set of sibling child nodes is delimited by braces {}. The top level of the view typically consists of one element in the outermost braces. This element has itself a child list delimited by braces. Each such list can have more than one different element.
Each element specifies:
SQL table
Correlation name for use in subsequent joins for this table
XML element name to use for delimiting a row of this table
List of columns, with optional XML element or attribute names
join condition - will relate rows of this table to rows of the table in the enclosing element. If this element is at the top level, this can only consist of scalar conditions
Optional PRIMARY KEY clause, needed if the table in this element is a view, does not have a primary key or if a non-primary key unique identity is desired
Optional ELEMENT flag
Optional list of child elements, delimited by braces
create xml view "cats_e" as select "category"."CategoryID", "CategoryName", "ProductName", "ProductID" from "Demo".."Categories" "category", "Demo".."Products" as "product" where "product"."CategoryID" = "category"."CategoryID" element; Add to text: after 'free text and xml'
DB.DBA.xml_view_publish (in view_name varchar, in dav_path varchar, in dav_owner varchar, in is_persistent integer, in refresh_interval integer)
This presents an XML view as a DAV resource. The view name is the name in the create xml view statement, note that this is case sensitive and is never converted since it is a string, not an identifier. The path must be absolute and is interpreted as relative from the DAV root collection. The DAV user is the owner of the resource. If is_persistent is non-zero the resource will be materialized from the view's description. The refresh interval is only applicable if the resource is materialized. If so, this is an interval in minutes. A value of 0 means no automatic refresh.
The reverse operation of xml_view_publish is deleting the DAV resource. xml_view_publish may be called several times to alter the owner or refresh interval.
When an XML document is stored as either text, long xml, xmltype or in the persistent XML format it can contain references to external parsed entities with the <!entity ...> declaration and the &xx; syntax. These are stored as references and not expanded at storage time if the entity is external.
Such references are transparently followed by XPATH and XSLT. A run time error occurs if the referenced resource cannot be accessed when needed. The reference is only followed if the actual subtree is selected by XPATH or XSLT. The resource is retrieved at most once for each XPATH or XSLT operation referencing it, regardless of the number of times the link is traversed. This is transparent, so that the document node of the referenced entity appears as if it were in the place of the reference.
External entity references have an associated URI, which is either absolute with protocol identifier and full path or relative. Relative references must be resolved with respect to the base URI of the referencing document. If the document is stored as a column value in a table it does not have a natural base URI, hence the application must supply one if relative references are to be supported. This is done by specifying an extra column of the same table to contain a path, in the form of collections delimited by slashes, just as the path of a DAV resource or a Unix file system path.
This base URI is associated with an XML column with the IDENTIFIED BY declaration:
create table XML_TEXT ( XT_ID integer, XT_FILE varchar, XT_TEXT long varchar identified by xt_file, primary key (XT_ID) ); create index XT_FILE on XML_TEXT (XT_FILE);
Thus, each time the value of xt_text is retrieved for XML processing by XPATH_CONTAINS or XCONTAINS the base URI is taken from xt_file.
The complete URI for the xt_text of a column of the sample table will be:
virt://<qualified table name>.<uri column>.<text column>:<uri column value>
An example would be:
"virt://DB.DBA.XML_TEXT.XT_FILE.XT_TEXT:sqlreference.xml"
The .. and . in relative paths are treated as with file names when combining relative references to base URI's. A relative reference without a path just replaces the last part of the path in the base URI.
xml_uri_get and xml_uri_merge for more details.
An XPATH expression can appear as a SQL query expression, that is, as a derived table or subquery predicate or scalar subquery. This means that the XPATH expression is expanded compile time to the corresponding SQL. The mapping of the XPATH hierarchy to tables and joins is given by the __view XPATH option, which is mandatory.
The XPATH keyword introduces an embedded XPATH expression. The XPATH text is presented as a string literal. Note that the tokenization rules are different for XPATH and SQL, so having XPATH as a string makes it clear which rules apply to parsing which part of the composite query.
select * from (XPATH '[__* __view "cat"] //product') P order by "P."ProductName"";
will evaluate the //product query in the context of the cat XML view and produce a result set consisting of all the attributes of the product entity as defined in the view.
The __key and __* XPATH options are central here in defining the result columns of the XPATH. The default result column of an XPATH expression is the serialization of the selected entity or scalar, which is most of the time impractical in a SQL context.
The '$' sign introduces a parameter in XPATH. The identifier following the dollar sign should reference a SQL column or variable defined in the surrounding context. The name of the parameter can contain a dot for referencing a column with a correlation name.
For instance, to make a VSP page that outputs the category tree which contains a specific product, one may write:
<HTML> <?vsp declare N varchar; N := {?'name'}; for (XPATH '[__http __view ''cat''] /category[product/@ProductName = $N]' do ; ?> </HTML>
This will iterate over the categories containing a product with ProductName equal to the URL parameter 'name'. Note the __http option that causes the text of the selected entities to go directly to the HTTP client. Note the double '' escape for the XML view name inside the SQL string literal forming the name.
Also note that the N parameter is in upper case to work in all case modes. In some modes SQL identifiers will be converted automatically to upper case but this conversion does not apply inside XPATH.
select * from "Demo".."Categories" C where exists (XPATH '[__view "ord"] //products[@CategoryID = $C.CategoryID]');
This example selects the categories of products that have been mentioned in the ord XML view.
The main difference of SQL and XPATH is that the '-' is not a breaking character in XPATH and that XPATH is case sensitive without any implicit identifier case conversion.
Simalarly to XPATH, XQUERY may also be used as a query languge for XML views. Virtuoso offers a special case of FLWR expression for this purpose. It is possible to use the xmlview() function in FOR clause expressions for querying XML views. This function is similar to document () in the sense that it sets the source of the path to be the logical root of the referenced XML view. The XML view must be a constant known at compile time. A SQL query against the appropriate tables of the XML view is internally constructed and evaluated at run time, producing XML fragments from the selected rows. At no point will non-selected parts of the evaluation of the XML view be physically created. The path expression following from xmlview() may contain filters involving XQuery variables bound in the scope of the path expression, thus allowing joining SQL data to XQuery variable values.
The XQUERY string
for $cat in xmlview("cat")/category return {$cat}
in the query
select xquery_eval(' {for $cat in xmlview("cat")/category return <q>{$cat}</q>}', xtree_doc('<dummy_tag/>'));
is equivalent to the XPATH query string
XPATH [__view "cat"]/category
described above.
The expression xmlview("viewname")/path is not a valid top level SQL statement, but may be used by xquery_eval() function. The path statement is translated into SQL query in the context of the "viewname" (i.e. the necessary table names are taken from "viewname" XML view), so that only the desired relational data will be queried. Functionality of this kind of SQL queries is similar to functionality of the SQL fetch statement, i.e. such a query provides iteration over the result set of a cursor. The query is executed once for each row in the query expression's result set. Thus using the CREATE XML VIEW statement and a XQUERY FOR clause expression with xmlview() function allows you to query the database and to return the results in the form of an XML document and to avoid redundant data access. This kind of queries also allows computing joins between two or more documents and restructuring data.
The <dummy_tag/> tag is not used and it is necessary only as an arbitrary argument for xtree_doc() functions.
create xml view "product" as { "Demo"."demo"."Products" p as "product" ("ProductID", "ProductName" as "product_name","UnitPrice" as "price", "SupplierID","CategoryID") { "Demo"."demo"."Suppliers" s as "supplier" ("SupplierID","CompanyName") on (s."SupplierID" = p."SupplierID") , "Demo"."demo"."Categories" c as "category" ("Description") on (c."CategoryID" = p."CategoryID") } }
This declares a two level hierarchy with a product node for each product and a supplier child node and a category child node of the product.
The following query will return the XML document in which each category node will contain all suppliers supplying products of the given category.
select xquery_eval(' <document> { for $cat in xmlview("cat")/category return ( <category description={$cat/@description}> { distinct ( for $prod in xmlview("product")/product where $cat/@CategoryID=$prod/category/@CategoryID return $prod/supplier ) } </category> ) } </document> ', xtree_doc('<dummy_tag/>') );
returns the XML document in which each category node contains all suppliers supplying products of the given category.
Let a document named suppliers.xml contains supplier elements; each supplier element in turn contains supplier_id and supplier_name subelements. The following query
select xquery_eval(' <supplier_product> { for $supp in document("suppliers.xml")/supplier return ( <supplier>{$supp/supplier_name } <product_name> { for $prod in xmlview("product")/product where string($supp/supplier_id)=$prod/supplier/@SupplierID return string($prod/@product_name) } </product_name> </supplier>) } </supplier_product>', xtree_doc('<dummy_tag/>') );
returns the XML document that contains supplier elements; each supplier element in turn contains supplier_name and product_name elements.
The previous query and the following one show that it is possible to use variables in a XPATH expression following xmlview() functions.
The query
select xquery_eval(' <document> { distinct( let $ex:= "Ex%" for $prod in xmlview("product")//supplier[@CompanyName like $ex] return <supp_id>{$prod/@SupplierID}</supp_id>) } </document>', xtree_doc('<dummy_tag/>'));
is equivalent to
select xquery_eval(' <document> { distinct( for $prod in xmlview("product")//supplier [@CompanyName like "Ex%"] return <supp_id>{$prod/@SupplierID}</supp_id>) } </document>', xtree_doc('<dummy_tag/>'));
and selects all suppliers having attribute "CompanyName" starting with "Ex".
At least two methods may be used to accelerate the execution of queries with xmlview() function. The first method assumes that a path statement following xmlview() function should contain maximum conditions to reduce the result set. For example, the query
select xquery_eval('<w> { for $prod in xmlview("product")/product[@ProductID="1"] return <q>{$prod}</q> }</w>', xtree_doc('<dummy_tag/>'));
will be executed faster than
select xquery_eval('<w> { for $prod in xmlview("product")/product where $prod[@ProductID="1"] return <q>{$prod}</q> }</w>', xtree_doc('<dummy_tag/>'));
due to the SQL query produced from the path expression 'product[@ProductID="1"]' reduces the result set in comparison with the SQL query produced from the path expression 'product' in the second query.
If we execute a join of two (or more) XML views (or XML document and XML view), i.e. the query consists of the nested loops, the second method proposes to carry out a piece of query of the nested loop which is independent of the outer loop outside the outer loop and uses LET clause for it. For example, the query
select xquery_eval('<document> { let $prod_set:=(for $prod in xmlview("product")/product return $prod) for $cat in xmlview("cat")/category return (<category description={$cat/@description}$gt; {distinct(for $prod in $prod_set where $cat/@CategoryID=$prod/category/@CategoryID return $prod/supplier)}</category>)}</document>', xtree_doc('<dummy_tag/>'));
is equivalent to the query in the example 1, but it is about 5 times faster than original one. This method is especially useful for full joins. In this case we do not have a full join and this query may be optimized without the use of temporary result sets if the 'where' clause is replaced with proper filter:
select xquery_eval('<document> { for $cat in xmlview("cat")/category let $catID := $cat/@CategoryID return (<category description={$cat/@description}$gt; {distinct( for $supp in xmlview("product")/product[category/@CategoryID=$catID]/supplier return $supp) }</category>) } </document<', xtree_doc('<dummy_tag/>'));
This variant requires no memory for storing $prod_set and it never fetches redundand fields from "Demo"."demo"."Products" table but it heavily needs index for "Demo"."demo"."Products" on "CategoryID" field. If such index is built the last variant is about 10 times faster than the query in example 1. Similarly, the query in example 2 may be optimized as follows:
select xquery_eval(' <supplier_product> { for $supp in document("suppliers.xml")/supplier let $supp_id:=string($supp/@supplier_id) return ( <supplier>{$supp/supplier_name} <product_name> { for $prod in xmlview("product")/product[supplier/@SupplierID=$supp_id] return string($prod/@product_name) } </product_name> </supplier>) } </supplier_product>', xtree_doc('<dummy_tag/>') );
and it speeds up the operation by more than 15 times.
Virtuoso does not support certain kinds of XPATH expressions applied to the xmlview() function.
1. A path expression must not contain any functions, because it is impossible to translate most of the functions to SQL queries.
2. A path expression must not contain numeric XQUERY variables in the arithmetic expressions. Let a document named products.xml contains product elements; each product element has numeric attribute ProductPrice. A run time error occurs if the following query would be used
select xquery_eval(' <document> { for $prod_doc in document("products.xml")/products/product/@ProductPrice for $prod_view in xmlview("product")/product[@price>$prod_doc+1] return <q>{$prod_doc, $prod_view/@product_name}</q> } </document>', xtree_doc('<dummy_tag/>'));
because the type of $prod_doc is considered as string. As it is mentioned in the previous restriction the using a function in a path expression (e.g. xmlview("product")/product[@price>number($prod_doc)+1]) is not allowed. The correct query is as follows:
select xquery_eval(' <document> { for $prod_doc in document("products.xml")/products/product/@ProductPrice let $prod_doc2:=number($prod_doc) for $prod_view in xmlview("product")/product[@price>$prod_doc2+1] return <q>{$prod_doc, $prod_view/@product_name}</q> } </document>', xtree_doc('<dummy_tag/>'));
3. A path expression must not contain XQUERY variables with the following paths. The following query will not be executed
select xquery_eval(' <document> { for $cat in xmlview("cat")/category, $prod in xmlview("product")/product[@CategoryID=$cat/@CategoryID] return <q>{$prod/supplier}</q> } </document>', xtree_doc('<dummy_tag/>'));
The correct query may be given as
select xquery_eval(' <document> { for $cat in xmlview("cat")/category let $cat_id:=$cat/@CategoryID for $prod in xmlview("product")/product[@CategoryID=$cat_id] return <q>{$prod/supplier}</q> } </document>', xtree_doc('<dummy_tag/>'));
4. Virtuoso does not support a selection of n-th element in a path expression. The following query will not be executed
select xquery_eval(' <document> { for $cat in xmlview("cat")//product[1] return <q>{$cat}</q> } </document>', xtree_doc('<dummy_tag/>'));
5. Virtuoso does not support a dereference (=>) in a path expression.
6. It is not recommended to use the long varchar, long varbinary and long nvarchar data types with the logical and boolean operations in a filter of the path expression. For example, the execution of the following query
select xquery_eval(' <document> { for $prod in xmlview("product")/product[@SupplierID<5]/category[@Description like "Sw%"] return <q>{$prod}</q> } </document>', xtree_doc('<dummy_tag/>'));
may return an error, because the field "Description" has LONG VARCHAR type in the table "demo"."Categories".
Virtuoso supports creating XML views by using annotated XSD schemas referred to as mapping schemas. A file containing a mapping schema may be loaded by calling the xml_load_mapping_schema_decl() function. A name (without extension .xsd) of the file containing a mapping schema is considered to be the name of the xml view, defined by the given mapping schema.
A loaded mapping schema may be queried in the same way as one would query XML views defined using the CREATE XML VIEW statement with XPATH:
XPATH [__view "xml_view_name"]/xpath_query
The XML view "Catmp" from the file "Catmp.xsd" may be loaded using the following statement:
select xml_load_mapping_schema_decl ( 'http://localhost.localdomain/xmlrepository/', 'Catmp.xsd', 'UTF-8', 'x-any' ) ) );
where the contents of "Catmp.xsd" is
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema"> <xsd:annotation> <xsd:appinfo> <sql:relationship name="CategoryProduct" parent="Demo.demo.Categories" parent-key="CategoryID" child="Demo.demo.Products" child-key="CategoryID" /> </xsd:appinfo> </xsd:annotation> <xsd:element name="category" sql:relation="Demo.demo.Categories" type="CategoryType" /> <xsd:complexType name="CategoryType" > <xsd:sequence> <xsd:element name="product" sql:relation="Demo.demo.Products" sql:relationship="CategoryProduct" > <xsd:complexType> <xsd:attribute name="ProductName" type="xsd:string" /> </xsd:complexType> </xsd:element> </xsd:sequence> <xsd:attribute name="CategoryID" type="xsd:integer" /> <xsd:attribute name="description" sql:field="Description" type="xsd:string" /> </xsd:complexType> </xsd:schema>
The XML view "Catmp" loaded from the file "Catmp.xsd" is similar to XML view "cat" defined by CREATE XML VIEW in the section Explicit Xml Views.
The query
XPATH [__view 'Catmp'] /category[@* = 1];
will now return the following result:
<category CategoryID="1" description="Soft drinks, coffees, teas, beers, and ales" > <product ProductName="Chai" ></product> <product ProductName="Chang" ></product> <product ProductName="Guarana Fantastica" ></product> <product ProductName="Sasquatch Ale" ></product> <product ProductName="Steeleye Stout" ></product> <product ProductName="Cote de Blaye" ></product> <product ProductName="Chartreuse verte" ></product> <product ProductName="Ipoh Coffee" ></product> <product ProductName="Laughing Lumberjack Lager" ></product> <product ProductName="Outback Lager" ></product> <product ProductName="Rhonbrau Klosterbier" ></product> <product ProductName="Lakkalikoori" ></product> </category>
Mapping schemas provide more flexibility than XML views defined by the CREATE XML VIEW statement. In the following mapping schema a constant element, "CustomerOrders", an element that does not map to any database table or column but may appear in the resulting XML as a container element of other subelements, is specified by the sql:is-constant annotation.
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema"> <xsd:annotation> <xsd:appinfo> <sql:relationship name="CustOrders" parent="Demo.demo.Customers" parent-key="CustomerID" child="Demo.demo.Orders" child-key="CustomerID" /> </xsd:appinfo> </xsd:annotation> <xsd:element name="Customer" sql:relation="Demo.demo.Customers" > <xsd:complexType> <xsd:sequence> <xsd:element name="CustomerOrders" sql:is-constant="1" > <xsd:complexType> <xsd:sequence> <xsd:element name="Order" sql:relation="Demo.demo.Orders" sql:relationship="CustOrders" maxOccurs="unbounded" > <xsd:complexType> <xsd:attribute name="OrderID" type="xsd:integer" /> <xsd:attribute name="OrderDate" type="xsd:date" /> <xsd:attribute name="CustomerID" type="xsd:string" /> </xsd:complexType> </xsd:element> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:sequence> <xsd:attribute name="CustomerID" type="xsd:string" /> </xsd:complexType> </xsd:element> </xsd:schema>
After loading the "Cust_constant.xsd" file containing the given mapping schema, the xpath query:
XPATH [__view 'Cust_constant'] /category[@* = 1];
will return the following result:
<Customer CustomerID="ALFKI" > <CustomerOrders > <Order CustomerID="ALFKI" OrderDate="1995-09-25 00:00:00.000000" OrderID="10643" ></Order> <Order CustomerID="ALFKI" OrderDate="1995-11-03 00:00:00.000000" OrderID="10692" ></Order> <Order CustomerID="ALFKI" OrderDate="1995-11-13 00:00:00.000000" OrderID="10702" ></Order> <Order CustomerID="ALFKI" OrderDate="1996-02-15 00:00:00.000000" OrderID="10835" ></Order> <Order CustomerID="ALFKI" OrderDate="1996-04-15 00:00:00.000000" OrderID="10952" ></Order> <Order CustomerID="ALFKI" OrderDate="1996-05-09 00:00:00.000000" OrderID="11011" ></Order> </CustomerOrders> </Customer> . . .
Virtuoso does not support all mapping schema annotations at this time. The following are currently unsupported:
Also, there are some restrictions to the structure of mapping schemas:
The XML views, defined by mapping schemas may not be queried using XQUERY. They can however be referenced with the xmlview XPATH functions in path expressions inside an XQuery query.
SQLXML 3.0 documentation: Creating XML Views by Using Annotated XSD Schemas.
A SQLX or FOR XML query has no effect on the database schema. It is a transient event and does not generate procedures or other schema elements.
These define an ad hoc mapping of a result set to XML. There is no possibility of using XPATH to specify a search without first constructing the whole tree. An XML view on the other hand provides a mapping context in which one can make XQUERY or XPATH queries that are mapped into SQL and the XML is only generated after applying the conditions.
XML views will usually be more efficient in complex cases and the notation there may be simpler than the EXPLICIT notation in FOR XML. For simple cases SQLX or FOR XML is the more convenient of the two. SQLX or FOR XML does not restrict the SQL being used and will allow free use of subqueries, expressions, derived tables, qualified joins etc.
Previous
XML Composing Functions in SQL Statements (SQLX) |
Chapter Contents |
Next
Querying Stored XML Data |