Virtuoso Universal Server
4.5
Data Management and
Integration
Reviewer’s Guide
OpenLink Software
Published: March
7, 2006
© 2006 OpenLink Software. All rights reserved.
OpenLink, UDA, Virtuoso registered trademarks or trademarks
of OpenLink Software in the United States and/or other countries.
The names of actual companies and products mentioned herein
may be the trademarks of their respective owners.
Table of Contents
1 Preface.............................................................................................. 3
1.1 Objective........................................................................................ 3
1.2 System Requirements.................................................................... 3
1.2.1 Software....................................................................................... 3
1.2.2 Operating Systems and Browsers......................................................... 3
1.3 Installation.................................................................................... 3
2 Virtuoso Universal
Server................................................................... 4
2.1 Introduction.................................................................................. 4
2.1.1 Figure 2 - Virtuoso Conceptual Architecture including:............................... 4
2.1.2 Figure 2 - Virtuoso Conceptual Architecture............................................ 4
2.1.3 Enterprise Data Integration................................................................ 4
2.1.4 Web
Services Composition................................................................. 4
2.1.5 Process
Integration.......................................................................... 4
3 Enterprise Data
Integration (EDI)...................................................... 5
3.1 What Is This?................................................................................ 5
3.1.1
Virtuoso’s Cost-Effective route to the Real-Time
Enterprise........................ 5
3.1.2 Benefits of Virtuoso for EDI............................................................... 5
3.2 The Value Proposition.................................................................... 5
3.3 Exploiting Value Proposition......................................................... 6
3.3.1 Virtual Database (VDB) for Heterogeneous SQL Data............................... 6
3.3.2 Attaching/Linking ODBC Datasources.................................................... 6
3.3.3 ODBC Datasources and Heterogeneous Replication................................... 8
3.3.4 Command Line Invocation................................................................ 12
3.3.5 Screens to be replaced with Northwind Tutorial of doc type
literal.............. 15
3.3.6 Figure 38 – Select the Web Service.................................................... 15
3.3.7 Figure 39 – Optionally choose to receive or submit
data.......................... 15
3.3.8 Figure 40 – Web Service Data Source................................................. 16
3.3.9 Figure 41 – Select the operation for providing the
XML data..................... 16
3.3.10 Figure 42 – Service Options for XML Data........................................... 16
3.3.11 Figure 43 – DataSource Notification.................................................. 16
3.3.12 Native XML Content Management..................................................... 18
3.3.13 Figure 58 – Adding a Virtual directory................................................ 21
3.3.14 RAW Mode.................................................................................. 29
3.3.15 Using AUTO Mode......................................................................... 30
3.3.16 Figure 77 –
SQL Query using the FOR XML AUTO................................ 30
3.3.17 FOR AUTO
ELEMENT Option............................................................. 30
3.3.18 Figure 78 –
SQL Query using the FOR XML AUTO ELEMENTS.................. 30
3.3.19 Explicit Mode............................................................................... 30
3.3.20 Other Content Sources................................................................... 38
4 Summary......................................................................................... 40
4.1.1 Enterprise Empowerment................................................................. 40
4.1.2 Enterprise Data Virtualization............................................................ 40
5 Appendix......................................................................................... 41
5.1 Demo and Northwind Database................................................... 41
5.2 Industry Standards Support........................................................ 41
5.3 Related Links............................................................................... 41
Thank
you for taking the time to evaluate OpenLink’s Virtuoso Universal Server
4.5. This guide introduces the
Virtuoso 4.5 Server as well as Virtuoso’s new administration and configuration
tool, the Virtuoso Conductor.
This
guide takes you through Virtuoso, covering virtual and federated database,
publishing and utilizing web services, XML storage, processing and querying,
business process management and more.
We
illustrate these aspects of the product with sample code and Queries against
the Virtuoso Demo database as well as links to documentation, tutorials and
other key resources.
OpenLink Software hopes you find this guide informative while you
learn what Virtuoso has to offer for your enterprise.
For
More information on Virtuoso or OpenLink Software, contact us at:
Sales: sales@openlinksw.com
General general.information@openlinksw.com
Technical Support technical.support@openlinksw.com
Or
visit our web sites:
OpenLink
Software http://www.openlinksw.com
Virtuoso http://virtuoso.openlinksw.com
The
Virtuoso Universal Server installation contains the Virtuoso VDBMS, server
administrator web files, and Virtuoso client connectivity.
·
Virtuoso Universal Server 4.5
·
Virtuoso Conductor VAD 1.00.3051
Virtuoso is available on a number of hardware
platforms. Listed
below are some of the operating system ports currently available.
Hardware
·
Microsoft Windows 2000, XP (32 and 64 bit platforms),
Server 2003
·
Linux (Intel and AMD64) (glibc2.1, glibc2.2, glibc2.3
compatible)
·
Linux (IBM pSeries 64 bit)
·
Mac OS X 10.2+ (“Jaguar”) & 10.3+ (“Panther”)
·
Solaris 2.8 / 2.9 (Sparc)
·
AIX 4.3.x/5.0.x/5.1.x 32 and 64 bit Power PC
·
IE 6.0, Mozilla (Firefox), Opera, etc.
Download
the Virtuoso 4.5 installer from OpenLink Software Virtuoso Download area at: http://oplweb.openlinksw.com/download.
Install both default and demo databases by following the instructions
provided by the installer. At the
end of the installation process, the Virtuoso
server will be installed
with both default and demo databases.
On Windows, the Virtuoso start menu will appear which
provides menu options for the Conductor, Web Applications, the 3.5 Classic
Administration tool and links to other resources as shown in Figure 1 - Virtuoso
Start Menu.
Figure 1 - Virtuoso Start Menu
This reviewer’s guide presents Virtuoso 4.5, the
latest version of OpenLink Software’s next-generation Universal
Server Platform. Virtuoso
facilitates the development and deployment of a new generation of
Enterprise-wide, Internet-, Intranet-, and Extranet-based solutions. Virtuoso capabilities include handling
of Disparate Databases & Data Sources, Web Service Composition and Business
Process Integration.
The conceptual
architecture diagram below highlights Virtuoso’s key features.
·
Enterprise Data Integration –Virtuoso
Virtual Database Engine
·
Web Services Composition –Web Services
Platform
·
Introduction to Process Integration – Blog,
BPEL, etc.
2.1.2
Figure 2 -
Virtuoso Conceptual Architecture
The sections
throughout this guide are aimed at demonstrating Virtuoso’s value proposition
in these key areas. The
step-by-step examples will quickly introduce you to Virtuoso and show you many
useful techniques and features.
The examples grouped by area include:
·
Identifying
disparate data for integration and linking the data into Virtuoso
·
Creating
XML based documents or ODBC data sources for heterogeneous data
·
Identifying
monolithic applications from which services are to be decoupled, composing, and
linking their functionality into Virtuoso
·
Creating
virtual directories as endpoints for exposing functionality for web services
clients.
·
See
the Virtuoso 4.5 Reviewers Guide on Application Integration and Web for
Services for more information.
·
See
the OpenLink BPEL
Process Manager Reviewers Guide for more information.
Enterprise Data Integration, EDI is the ability to
integrate data across all types of application boundaries including
transactional, operational, platform, language and content. Successful EDI reduces the
total cost of ownership and increases the return on IT investments.
3.1.1 Virtuoso’s Cost-Effective
route to the Real-Time Enterprise
Using Virtuoso customers can implement the real time
enterprise in the areas of access to heterogeneous data, web services exposure
of data, accessing partners via web services, flexible XML storage and
processing and more. All this can
be achieved without committing to any single programming language, operating
system or back-end database.
· Integrate
Data across the enterprise and among suppliers, partners, and customers
· Industry
trends moving B2B, Enterprise Data Virtualization, Application Integration,
Legacy (DBMS, APPS) into one comprehensive real-time environment.
· Reduce
costs associated with integration
· Maintenance,
recoding – costs escalation while keeping up with Market conditions...
· Standards-based
Service-Oriented Architectures (SOAs) built on Web Services interfaces
Virtuoso is
ideal for creating what we call a Data Junction Box, linking in disparate
legacy databases that can service the needs of various business intelligence
tools, desktop productivity tools, or web services that exist throughout the
enterprise.
·
The
Virtual Database engine provides a single XML, ODBC, JDBC, .NET, or OLE DB
connection to heterogeneous data sources that includes all major database
engines, HTML/XHTML, XML, Free Text, and Multimedia data.
·
Semantic Content Production & Syndication through leveraging
of built-in support for XSL-T, XML Schema, SQLX, XQuery, and XPath, which
enables the transformation of heterogeneous data into a myriad of semantic data
formats such as RSS, FOAF, RDF, and beyond.
·
Placing
application server level logic inside Virtuoso through Java or .NET hosting is
still more efficient than using a separate application server process. This
application logic may then be accessed through Virtuoso’s web services
protocols suite, among other paths.
Virtuoso’s
federated database engine provides transparent XML, ODBC, JDBC, ADO.NET, and
OLE DB access to relational data residing on any ODBC/JDBC accessible DBMS.
Tables from any number of such databases may be linked into a Virtuoso schema,
making them indistinguishable from tables stored in the Virtuoso engine itself.
Virtuoso’s
SQL 92/2K implementation incorporates data location as part of its
heterogeneous distributed query optimizations. It hides SQL dialect differences
across external DBMS from applications and developers. Queries and stored
procedures may span an arbitrary number of databases, and Virtuoso will use
two-phase commit where appropriate.
In the next section, Attaching/Linking
ODBC Datasources you will see how easy it is to link in
tables and manage the Virtual Database feature of Virtuoso.
Before you can link an external table into Virtuoso, you
need to configure an ODBC datasource. In many cases, you already may have an
ODBC datasource defined.
If you do not have any data sources defined, you should
configure your datasource using the appropriate ODBC driver for the datasource
in question. Depending on the
case, you may choose to use the ODBC driver that is provided by the database
vendor for their database or you can use a third-party middleware vendor, such
as OpenLink Software. In
both cases, you should configure your ODBC datasource in accordance with the
directions from the driver vendor.
Before
linking an external table into Virtuoso, you should first verify that the
datasource is configured and working.
To verify the datasource, use an ODBC based tool such as Microsoft
Access, or run a check using the ODBC driver manager to test your
datasource. This step will allow
you to verify that the datasource has been configured, the database is
accessible, and you have the correct access credentials.
Tables contained in a remote datasource can be linked into
Virtuoso so that they appear as local database tables. The following steps show how to link
tables from an ODBC datasource.
For demonstration purposes, the example uses a DataSource defined for a
MS SQL Server database containing the Northwind Schema.
Step 1.
If you have not already done so, start your browser and point
the URL to the Virtuoso Demo instance.
The format is:
http://www.yourmachinenamedomain:portname/conductor as seen in Figure
3 - Virtuoso Conductor below. Example: http:/localhost:8890/conductor
Alternatively, on the Windows Platform, you can
invoke the Conductor from the start menu OpenLink Virtuoso Demo UI under OpenLink
Universal Server 4.5.
Figure 3 - Virtuoso Conductor
Step 2.
On the left hand side of the Conductor User Interface, enter
the Account and Password for the Virtuoso Database Administrator in the input fields
provided and click the Login Button. By
default, the Account and Password are both dba.
Note 1 - For security purposes, we suggest changing during
the initial installation, or post installation through the Conductor.
Step 3.
Once you have logged in, navigate and select the Database
TAB, then select the External
Data Sources TAB.
Step 4.
Add a NEW datasource by navigating to the Connected
Datasource Tab, choosing a datasource from
the drop down list and then
click the ADD NEW button as seen
in Figure
4 – Adding New Data Sources.
Figure 4 – Adding New Data Sources
Note 2
- The list displayed will initially indicate
there are no datasources until you add a new one.
Step 5.
The next step is to enter the access credentials for the
Datasource. As shown in Figure 5 –
Entering Data Source Credentials, enter the
username and password (Note the * next to the prompts for the required fields)
for the chosen database and click the Connect button. Note you should have the proper grants in place for the user
and password to allow you to select tables or edit the contents of the
database.
Figure 5 – Entering Data Source Credentials
Step 6.
Once you have successfully connected, the Connected Datasource
screen seen below in Figure
6 – List of Connected Data Sources will be updated
and you will be able to add tables by selecting the 
Link
objects link under
the Action heading. After you have
chosen the Link objects, you can view and select all External tables, views and
stored procedures associated with that particular DSN.
Figure 6 – List of Connected Data Sources
Step 7.
Next, select the objects (Tables, Views, Stored Procedures)
you want to view by marking the checkboxes, and then click the Apply button to list of objects as
shown below in Figure
7 – Viewing External Objects. In the list presented includes a
checkbox, the type and name of the object. Mark the checkbox next to each of the objects you want
linked or check ALL to link
all the objects, and then click the LINK button.
Figure 7 – Viewing External Objects
Figure 8 – Selecting External
Objects
Step 8.
As shown in Figure
9 – Linking External Objects below, you can optionally change the name of the
Database Catalog, owner as well as the object name if desired. When you have modified your
selection hit the LINK button.
Figure 9 – Linking External Objects
Step 9.
Once the objects have been linked, you will be returned to the
External Linked Object Screen as seen in Figure
10 – External Objects Linked. In this
screen, you can select any of the object Links under the Local Name column,
which will bring up an interactive SQL pop up box where you can execute a SQL
statement to verify you are linked in object. Additionally, you can drop any of the objects by marking the
checkboxes provided and click the Unlink Link or mark the ALL and click the
Unlink Selected button.
Figure 10 – External Objects Linked
Through these methods, any ODBC datasource can be
linked in to Virtuoso including Oracle, DB2, Sybase and many other ODBC
accessible databases.
Once the objects, which may include all the tables, views,
or stored procedures from the remote DSN, have been linked into Virtuoso, they
can be queried using the Interactive SQL Query (ISQL) in the Conductor. You can invoke this tool directly from
the highlighted links presented in the External Linked Object tab, select the
Interactive SQL Link in the left hand frame or navigate to Interactive SQL.
In this interface, you can type a variety of SQL
statements and easily verify and test linked tables by typing in and executing
SQL that accesses a remote table.
Step 1.
To
use the Interactive SQL tool select the Relational Data Using SQL from the
Query Tools option in the Conductor menu tree.
Step 2.
In
the Interactive SQL Screen type in any SQL statement for any of the tables you
have linked in or type in the following SQL select statement:
SELECT
* FROM Northwind.Demo_SQLSVR.Employees
Then
Press the EXECUTE
button and review the results as shown in Figure
11 – Testing a SQL select statement
using Interactive SQL below.
Figure
11 – Testing a SQL select statement
using Interactive SQL
Virtuoso offers a range of tools and technologies for
replicating and synchronizing data. This provides organizations with the flexibility
to move data around the enterprise between Virtuoso and other ODBC accessible
data sources.
Replication may be a one-time copy, a periodically
synchronized one way, or two way mirroring of data between a Virtuoso and
another database.
Virtuoso provides replication of data to linked in
heterogeneous data sources through direct SQL Statements or through the
Conductor. The Conductor UI
includes wizards for basic, incremental, bi-directional snapshot and transactional
replication that define the publications, subscriptions and synchronization
intervals required.
Virtuoso monitors for data changes in the tables
involved and synchronizes all data changes made by the subscriber and the
publisher on periodic basis or on-demand by the subscriber.
The following example uses the Virtuoso linked-in table,
Demo_SQLSVR that was linked in the previous section, Linking Remote Tables in Virtuoso.
The linked in Demo_SQLSVR is our “SOURCE” datasource, which
includes objects including tables, stored procedures, views and content for the
Northwind database.
Our target datasource for the basic replication will be an empty Oracle
database whose DSN in this example is called Demo_Oracle. Virtuoso will replicate data between
the SQL Server Northwind database and Oracle where it will initiate data
transfer, create the objects and data as well as resolve possible update
conflicts.
Step 1.
Navigate to the Replication Tab, select the Basic sub-tab,
choose the Query (Table select) to Remote option, and click the Create New
Snapshot button as shown Figure
12 – Create New Snapshot.
Figure 12 – Create New Snapshot
Step 2.
Select the target data source by browsing through the
connected datasources and clicking on the target. Once you have chosen the data source, enter the access
credentials and click Add Data source button. As shown in the Figure
13 – Adding a Data Source we have chosen
the Demo_Oracle DSN.
Figure 13 – Adding a Data Source
Step 3.
Select the Connected Datasource button, which will bring
you to the next step to add the queries from the tables, by clicking on the Add
Queries from Tables button as shown in
Figure 14 – Adding Queries from Tables.
Figure 14 – Adding Queries from Tables
Step 4.
As shown below in Figure
15 – Select Source Catalog, select the
Source Catalog and schema, as shown below we will select the Northwind VDB for
our Demo_SQLSVR DSN.
Figure 15 – Select Source Catalog
Step 5.
As shown in Figure
16 – Selecting Object from the Source Catalog, Put a check
next to all the tables in the Northwind database, click on Add to Snapshot, and
click the NEXT button as seen in Figure
17 – Adding Object Queries from Source Catalog.
Figure 16 – Selecting Object from the Source
Catalog
Figure 17 – Adding Object Queries from Source
Catalog
Step 6.
As shown below in Figure
18 – Choosing Destination Parameters, choose the
Destination parameters for the target datasource. Choose the catalog, schema names as well as the table
names and click the Create button.
Figure 18 – Choosing Destination Parameters
Step 7.
The next step will create the snapshot as well as
synchronizing changes of the data source.
The result of each replication and the number of rows replicated is
displayed as seen below in Figure
19 – Replicating Source to Target. Once replication completes, hit the OK
button.
Figure 19 – Replicating Source to Target
Once you have finished you can query the datasource,
perform joins, updates and other operations. Virtuoso Replication is not limited to periodic copying of
tables. If we operate between
Virtuoso instances, a near-real time transactional replication mode is
available for replicating changes transaction by transaction, including
replicating logical operations such as procedure calls.
Features include:
· Keeping
Virtuoso DAV repositories in sync between Virtuoso installations.
· Keeping
Virtuoso servers in real-time sync for load balancing or hot standby
situations. This feature is based on real-time log shipping; therefore, it will
work for intermittently connected systems.
· Sharing
mobile data through SyncML. The Virtuoso SyncML server has pluggable logic for
format conversions between common mobile formats such as Vcard and Vcalendar.
3.3.3.1.1 Performing
Heterogeneous Queries with Linked Tables
Once the objects from the Oracle and MS SQL Server
database have been successfully linked into Virtuoso, they can be used in a
programs, viewed from the Schema Objects UI or can be queried using the ISQL
tool.
The following example illustrates executing a query, which
uses a view that includes a heterogeneous join created from the linked in
Northwind database tables from Oracle and MS SQL Server.
Step 1.
Navigate to the Database Tab and select the ISQL tool. Insert the following SQL, which will
create the view Northwind..VDBOrderDetails
as show below in Figure
20 – View using a Heterogeneous Join from linked in tables.
CREATE VIEW Northwind..VDBOrderDetails AS
3.3.3.2
SELECT
Northwind.Demo_SQLSVR.Order_Details.OrderID,
Northwind.Demo_SQLSVR.Order_Details.ProductID,
Northwind.Demo_ORACLE.Products.ProductName,
Northwind.Demo_SQLSVR.Order_Details.UnitPrice,
Northwind.Demo_SQLSVR.Order_Details.Quantity,
Northwind.Demo_SQLSVR.Order_Details.Discount
3.3.3.3
FROM
Northwind.Demo_ORACLE.Products
3.3.3.4
INNER JOIN
Northwind.Demo_SQLSVR.Order_Details ON
Northwind.Demo_ORACLE.Products.ProductID =
Northwind.Demo_SQLSVR.Order_Details.ProductID
Figure 20 – View using a Heterogeneous Join
from linked in tables
Step 2.
In
the Interactive SQL Screen type in the following SQL statement for to query the
view and check the box and input 10 Rows to limit the result set returned 10 as
shown in Figure 21 – Selecting Rows using the Northwind..VDBOrderDetails
View.
SELECT * FROM Northwind..VDBOrderDetails
http://www.openlinksw.com/press/press.htm
Figure 21 – Selecting Rows using the
Northwind..VDBOrderDetails View
Step 3.
Then
Press the EXECUTE
button and review the results as shown in Figure
22 – Viewing Results, then press RETURN to enter additional queries.
Figure 22 – Viewing Results
3.3.3.5
More Information
For more information regarding the administration of ODBC
Datasources, Remote Datasources or ODBC see:
·
Virtuoso
Remote Table Documentation and Virtuoso Replication
·
OpenLink ODBC Data Access Drivers documentation - Universal Data Access Drivers
(Multi-Tier) or Universal Data
Access Drivers (Single-Tier)
While this guide does not
provide detailed instructions on every ODBC client application, there are some
general rules and information that is common for making client connections to
the Virtuoso server.
To connect to the
Virtuoso Server using a client application other than ISQL or the system
administrator’s tool you will need to use an ODBC connection. An
ODBC data source is simply a name set up on the client machine that is
associated with specific connection information to a remote database. To use an ODBC connection with a client
application such as Access or Excel, you will need to know the ODBC data source
name set up on the client machine. By default when you install Virtuoso, there
are two Data Source Names (DSN) defined for the Virtuoso server. The DSN is set up to use the Virtuoso
driver for Virtuoso Universal Server installed and are named Local Virtuoso
(the default database) and Local Virtuoso Demo (includes the demo database,
tutorials and demos).
You will need to refer to the client application’s own
Help pages for information on making ODBC connections using their interfaces.
ISQL is Virtuosos Interactive SQL (ISQL) is a text-mode user interface, which allows the user to
execute SQL. These tools can be
invoked from the command line of the operating system or invoked from within
the Conductor.
The ISQL utility
can be invoked at the Operating System via ISQL or ISQLO command and on the
windows platform can be executed from the start menu.
The following screen in Figure 23 - ISQLO using DSN Virtuoso
Demo depicts a
session on the Windows Operating System using the ISQLO utility. The connection in this example uses a
current DSN called VirtuosoDemo.
Once connected after using the Connect string (DSN=Virtuoso; UID=DBA,
PWD=DBA”) any SQL command can be issued against the datasource. The example uses the tables function to view all tables in the current database.
Figure 23 - ISQLO using DSN Virtuoso Demo
The next connection as seen in the example shown in Figure 24 –
ODBC Connection with a Northwind Database from SQL Server uses a DSN
called MSSQL_Northwind. This
connection uses the same basic connect string syntax; the difference is
connecting to a remote datasource linked via Virtuoso Virtual Database.
Figure 24 – ODBC Connection with a Northwind
Database from SQL Server
ISQL can also be invoked from the Conductor by selecting
ISQL from the left navigation menu or you can navigate to the Database Tab and
run it from the Interactive SQL sub-tab as shown in Figure 25 – ISQL Invocation
from the Conductor UI.
Figure 25 – ISQL Invocation from the Conductor
UI
For more information regarding the Interactive SQL command
line tool and connect string options as well as detailed information on this
tool, see:
·
Virtuoso ISQL Utility
Documentation
On the Windows Platform Microsoft Access is the choice
of many for prototyping and developing simple applications. Any table in Virtuoso whether it is
native table or a virtual table (linked-in Remote Datasource) can be accessed
from Microsoft Access via odbc.
The following example shows some simple steps to
create and link the table from both a native Virtuoso table.
Step 1.
The first step, before using Access against Virtuoso Data, you
need to create a new database using the Access Database Administration
tool. This is
accomplished by first select the database.
Figure 26 – Microsoft Office
Access
Figure 27 – Create Database
Step 2.
From the File menu,
select Get External Data, and
choose either Import or Link
Tables.
Figure 28 – Get External Data
Step 3.
From the Files of Type
drop-down list, select ODBC Databases.
Step 4.
In the Select Data Source window, choose the Machine Data
Source tab. , In this screen there
are two DSNs, the first named Local Virtuoso (the default) and Local
Virtuoso Demo, The demo database, (On your
own machine, the data source will have whatever name you gave it.) Select the Local
Virtuoso Demo datasource from the list and
click OK.
Figure 29 – ODBC Machine
Datasources
Step 5.
In the Connect dialog box, enter the Virtuoso username and
password and click OK. (The default is dba/dba)
Figure 30 – OpenLink Virtuoso
Login
Step 6.
Optionally as seen in Figures 20- 23 you can connect via the OpenLink
Virtuoso DSN configuration wizard. Here you can select the server, change the
authentication method, choose the database and change the login.
Figure 31 – OpenLink Virtuoso DSN
Configuration Wizard
Figure 32 – OpenLink Virtuoso DSN
Configuration Authentication Method selection
Figure 33 – OpenLink Virtuoso DSN
Configuration Default Database selection
Step 7.
The list of tables you see includes all of the tables on the
Virtuoso server for the DSN named.
Once you have selected the tables that belong to your schema, choose the
tables you want for the reporting or application development, then Click OK as shown below in Figure 34 – Selecting tables and Figure 35 -
List of Tables.
Figure 34 – Selecting tables
Figure 35 - List of Tables
Step 8.
In this example, we have chosen demo.XQItems and
demo.XQusers. We can then use
Access to modify fields, field content and develop reports and application
using. Figure 36 –
Access Table View below shows the
table view of the table demo.XQItems, which is stored in Virtuoso.
Figure 36 – Access Table View
Note 3 - Access requires that a table have a primary key
field or it will prompt you for it before establishing the link. Then you should see the link in your
database window and you can proceed with your work.
The next time you open that database, the links will still
be there (unless you explicitly deleted them).
Note 4 – If a datasource has been changed, you will
need to delete and recreate links in order for them to work.
Note 5 - Virtuoso Hosts the Interop Test results and this
particular example uses the Interop tests results from Round 4 group G, which
are of type doc literal with parameters to be compatible with the InfoPath
product.
Invoke
InfoPath and select NEW Datasource option
Figure 37 – Microsoft Office
InfoPath
3.3.6 Figure 38 – Select the Web
Service
3.3.7
Figure 39 – Optionally choose
to receive or submit data
3.3.8
Figure 40 – Web Service Data
Source
3.3.9
Figure 41 – Select the
operation for providing the XML data
3.3.10
Figure 42 – Service Options
for XML Data
3.3.11
Figure 43 – DataSource
Notification
Figure 44 - Query Form
XML has thoroughly established itself as the standard
format for data interchange between heterogeneous systems. As a self-describing
data format, it is ideally suited to the task of transport and processing in a
standardized way across a vast range of platforms and applications.
It is for this reason that XML has been embraced by nearly
all of the relational database vendors in some form or another, most often as a
way of transforming relational query results to a form that can be readily
consumed by XML technologies such as parsers and XSL-T processors.
The most popular mechanism of generating XML data from
relational queries is known as SQL/XML (or SQLX). SQLX is an ISO and ANSI
standard that has been embraced by most major relational database vendors. In addition to SQLX, The W3C’s XQuery
is emerging as the means of choice for querying XML repositories and
transforming XML data for complex reporting and synthesis. Additionally, Virtuoso’s XQuery implementation
allows accessing relational data from inside XQuery queries via a mapping
schema mechanism, transforming XQuery conditions into corresponding SQL
conditions and transparently constructing XML data from native relational data.
XML content management is not without challenges. Storage and retrieval models for XML
data sources are far less rigid than relational data stores, and as such, XML
may be stored anywhere that a standard file is stored, both in local file
systems and remote locations. XML
may also be stored using arbitrary file extensions (or none at all), making it
impossible to identify XML files by extension alone.
Because of XML’s distributed nature, and the inherent
value in being able to integrate external XML content into an enterprise’s
working systems, Virtuoso attempts to provide as many tools as possible to
facilitate the retrieval and management of XML content both within its own
content storage facilities, and with external content storage systems.
Virtuoso can be set up to retrieve content from external
web sites and host it in its own WebDAV repository through Virtuoso’s WebDAV
Robot Control also known as the Virtuoso Spider utility.
This section outlines the steps for setting up a target
for a Web Robot accessing content from the News is Free.
3.3.12.1.1.1
Web
Robot Target
The first few steps illustrate creating the Web Robot
Target.
Step 1.
Navigate to the HTTP Services Tab and select the Content
Management sub-tab, then select the Robot Control Tab and click the New Robot
Target button as shown below in Figure
45 – HTTP Content Robot Control.
Figure 45 – HTTP Content Robot Control
Step 2.
As shown in Figure
46 – Web Import Target Details below input the Web Robot Target Details including:
· Target
description:
·
Target URL
Example: http://httpuser:password@host:port/start_path
·
Copy to Local
DAV collection (Directory)
·
Checkbox for
Single Download Page
·
Local Resource
Owner
·
Download Date
·
Follow or
exclude Matching Links
·
Import Options
·
Download
Images
·
\WebDAV
Methods
· Delete
if remove on remote detected
§ Follow
HTTP redirects
§ Store
content locally
Figure 46 – Web Import
Target Details
Step
3.
Next select the Import Queue button as shown below in Figure 47
– Web Robot Target Entries
Figure 47 – Web Robot
Target Entries
The next steps
in the process are setting up the Import Queues. The Web Robot queues page shows you a list of web copy
targets that have been enlisted with the Virtuoso Server and a list of web
robot update schedules.
This page
provides a number of options for each Robot Target. These options include:
Start and Update
Schedule and Reset
Stop
Step
4.
As shown in Figure
48 – Web Import Queue enter Start to begin.
Figure 48 – Web Import
Queue
Figure 49 – Results for Robot Target
Step
5.
As shown in Figure
48 – Web Import Queue above, you can configure the scheduled update
interval by pressing the 
Schedule link and
entering a value in minutes as shown below in Figure 50 – Creating Web Robot
Schedule Events.
Figure 50 – Creating Web
Robot Schedule Events
Step
6.
Once the event details have saved by selecting the SAVE
button, you can re-start the schedule by pressing the Start link or
update the Events by selecting Edit as shown below in Figure 51 –
Current Scheduled Updates. Manual updates for a given robot target is
performed by pressing the Update link. You can stop the
scheduled updates taking place by pressing the 
Stop
link.
Figure 51 – Current
Scheduled Updates
3.3.12.1.1.3 Retrieved Sites
You can view a list of the links
and data retrieved from the web copy from the Retrieved Site page by selecting
the Retrieve Sites button as shown above in Figure 51 – Current Scheduled
Updates. Retrieved content can be viewed or removed by selecting the Edit link
as shown in Figure
52 – Retrieved Sites and Figure 53 - Web Robot Retrieved Links.
Figure 52 – Retrieved
Sites
Figure 53 - Web Robot Retrieved
Links
This Export page allows you to export content from the
WebDAV repository.
Note 6 -
You can only export content that has been retrieved using Virtuoso’s
Web Robot.
As shown below in Figure
54 - Web Robot Exporting Content from Retrieve Site List, this page displays the list of targets that have been
retrieved. Next to each
target item there is an Export link, which when selected will
export that content.
Figure 54 - Web Robot
Exporting Content from Retrieved Site List
Step 7.
Click
the EXPORT link next to the target. Once selected, the Export Content
screen will be displayed for selecting the export target location as shown
below in Figure
55 - Web Robot Exporting Content
Figure 55 - Web Robot
Exporting Content
Step 8.
In
the Export Content screen. The
export method options include the File System or DAV. Mark one of these methods to store the exported
content from the target. Once
chosen, type the File or WebDAV path and click the EXPORT
button. Once the export has
completed, a confirmation page will be displayed.
Note 7 -
When selecting the file system method you are restricted to Virtuoso targets,
however WebDAV methods can be applied to any WebDAV server. WebDAV methods
assume that the target is publicly available for writing.
3.3.12.2
More Information
For more detailed information on the Web Robot and input entries required for this utility, see:
·
Virtuoso
Web Robot (Spider) Documentation
URL’s
have become the default way of accessing documents and applications.
Application servers and databases therefore need to make resources accessible
in a URL namespace. Virtuoso offers a built-in Web-based Distributed
Authoring and Versioning. WebDAV repository capable of hosting static and
dynamic content. Types of dynamic
content include Virtuoso native VSP and VSPX web pages, stored queries and
depending on hosting options as well as PHP or ASP.net pages.
WebDAV
resources are controlled by a Unix-like security scheme where SQL accounts and
roles are associated to resources and collections. The server-wide
URL namespace can be mapped across multiple HTTP listeners, providing
isolation and HTTPS access for selected points. These WebDAV resources
are stored in a relational table, making it easy for applications to
produce and alter WebDAV resources.
Besides
documents, WebDAV can host dynamic content in ASP .Net, PHP 4, JSP, Virtuoso’s
native VSPX web page language and others. In addition, XML reports generated
from relational or XQuery queries can be directly published in DAV without any
programming. Because DAV resources are in a database, these are covered by
database backups and transaction logging and can be replicated using
Virtuoso’s replication capabilities. Additionally, text and XML
resources are automatically indexed in the repository’s full text index
for fast retrieval.
Virtuoso
WebDAV can be used as a file server for a number of web-authoring tools that
support publishing and exporting of content to WebDAV; as well as for operating
systems that have a DAV file system driver. For example, Windows XP and
Mac OSX applications and desktops can transparently manipulate data in Virtuoso
DAV.
DAV, or WebDAV, is a protocol for
Web-based Distributed Authoring and Versioning. In the Conductor, the location where content items are
placed is called the repository. Content elements are called documents,
which correspond to files, and folders/collections, that
correspond to directories.
The
WebDAV interface can also be invoked from the Conductor by selecting the DAV
Browser link from the left navigation menu or you can navigate to the Database
Tab and run it from the Interactive SQL sub-tab as shown in Figure 56 – WebDAV.
Figure 56 – WebDAV
3.3.12.2.1.2
Virtuoso
Virtual Directories
A virtual directory specifies an HTTP listener and a URL
path that leads to a given collection in the internal DAV namespace. This allows for multihosting, virtual
hosting and exposing different sections of the DAV repository with different
protocols, for example via HTTPS only.
In addition, Virtual directories are needed for specifying which DAV
collections may hold executable content and under which SQL user’s privileges
such content will execute.
The following example below defines a virtual directory
called NorthwindDEMO
Step 1.
In
the Conductor, navigate to the HTTP Services TAB and select the Server
Management sub-tab. Expand the Directories by selecting
the 
Folder ICON and click the Add New
Directory link.
Figure 57 – HTTP Virtual
Directory Option from the Conductor
Step 2.
In
the HTTP path screen, choose the virtual directory type or an existing virtual
directory to use as template as seen in Figure 58 – Adding a Virtual directory.
3.3.13 Figure 58 – Adding a Virtual
directory
Step 3.
Enter
details in the form to define the mapping as shown in Figure 59 - Mapping details. Most of the fields are optional.
In this example, only the logical and physical paths and the default page name
are required.
Figure 59 - Mapping details
Step 4.
The
following URLs will then be equivalent:
· http://localhost:8890/NorthwindDEMO
Figure 60 – Listing of Virtual
Directory Mappings directories
The Microsoft Windows Operating System has a concept of
Web Folders. This mechanism is how Windows connects to a WebDAV data
store. This file like
representation of a Web Directory can be traversed like any other file system
and any file can be copied and moved to and from WebDAV using normal
drag-and-drop methods. The
following includes steps to create a WebDAV folder in Windows against a Virtual
Directory in Virtuoso. We
will also show how you can drag and drop a file in Windows and see it persisted
in Virtuoso.
Step
1.
To
create a Web Folder open the Windows Explorer and navigate to My
Network Places as shown in Figure 61 – Adding Network Places.
Figure 61 – Adding Network Places
Step 2.
You
will find an icon or option to Add Network Place which when
clicked will lead you to the wizard as shown below in Figure 62 - Web Folder Wizard
Figure 62 - Web Folder Wizard
Step
3.
Select the NEXT bottom
in the Wizard, which will bring you to the next screen for selecting the
Network place. On this
screen select choose another network location and click the NEXT button again
as shown in Figure 63 - Network Place Selection.
Figure 63 - Network Place Selection
Step 4.
The
first screen in Figure 64 - WebDAV location encourages you to type the location of the network place,
for connecting to a Virtuoso DAV you will need to know the server location and port
number. If you installed Virtuoso on your local machine taking default options
then you would probably be typing:
http://localhost:8890/DAV/
Figure 64 - WebDAV location
Step 5.
As
shown in Figure 65 - WebDAV authentication below you must enter the authentication information,
which will require a username and a password. As in the previous examples, we will use the defaults for
the installation and enter dav for both the username and password.
Figure 65 - WebDAV authentication
Step 6.
To
complete the creation of a Web Folder you will be prompted to supply a name for
the network location, which should be meaningful for the location in question.
In the example in Figure 66 - Name of your WebDAV Web Folder, the label is Local DAV Directory on Port 8890.
Figure 66 - Name of your WebDAV Web Folder
Step 7.
The next step in the process is to
close out of the wizard as seen in Figure 67 – Network Wizard Completion Screen. Once you
have exited, the explorer will automatically open a new Window overlooking the
new location if the option is checked to automatically open the window.
Figure 67 – Network Wizard Completion Screen
Step 8.
In Figure 68 - Files contained in your DAV as seen below, the Explorer window lists the files and
directories of this newly created folder.
Figure 68 - Files contained in your DAV
Step 9.
From
this window, you can select any of the files for access or editing. Should you choose to close out of
this window, your new Web Folder can easily be found again from My Network
Places where the link is saved.
More Information
·
Virtuoso
WebDAV Server Documentation
Virtuoso’s SQLX
features apply not only to natively stored relational tables, but also extend
out to virtually linked tables. The benefits of this capability are obvious
when you imagine the need to extract information as XML from a database that
does not inherently support SQLX, or from several heterogeneous databases that
may or may not support SQLX
The following section includes
Virtuoso SQL extensions for the SQLX and FOR XML option. The benefits for using Virtuoso in
these areas include:
·
Performance: These options are enhanced by more
advanced support for XML indexes.
·
Structure and Unstructured Data: Virtuoso has
the best of both worlds and handles both structured XML and
unstructured XML.
o
Unstructured data that require XML document repositories
for applications that work with unstructured data such as pages on a Web site.
o
Structured data is really for those applications that
require traditional tabular relational data and need to express that data as
XML.
·
Data Manipulation using XPath and SQL: Virtuoso supports combinations of XSLT,
XPath and SQL for manipulating XML documents.
o
You can also retrieve regular relational data in XML
formats and perform an XSLT transformation of the data to text, HTML or custom
formats of XML.
o Like
Oracle 9i and 10g, Virtuoso contains a set of special SQL functions that allows
XML data to be manipulated as relational data. A new data type called XMLType
enables storage of XML data.
SQLX (SQL/XML)
functions are based on the SQLX standard, which is a combination of XML and SQL
functionality. An effort by the H2.3 Task
Group
(Formerly the SQLX Group), including IBM and Oracle are working together to
standardize XML extensions to SQL. This effort is known as SQL/XML or more
commonly known as SQLX. This feature extends SQL with a collection of functions
added for creating XML elements from standard relational queries.
SQLX functions allow creating XML
elements, node sets and attributes as part of SQL queries. An aggregate function is offered for
bundling a sub-query’s result set inside a single XML value.
3.3.13.1.2.1.1
SQLX
functions
Below are just few examples using the SQLX functions
implemented in Virtuoso using the demo database that is based on the Northwind schema. The following SQL statements show in
the Figures below have be executed via Virtuoso Conductor, however they can be used in APIs or using the
command line utility ISQL.
3.3.13.1.2.1.1.1
SQLX Function XMLElement
This example shown in Figure 69 —
Simple select using XMLElement and Figure
70 - Using XML Element with the MS SQL Server Northwind VDB Linked Tables, is a simple use of XMLELEMENT() for creating the XML element
‘Employee’ (for each employee) without content:
Figure 69 — Simple select
using XMLElement
SELECT TOP 10 XMLELEMENT (“Employee”) from “Demo”.”demo”.”Employees”;
Figure 70 - Using XML Element with the MS SQL Server
Northwind VDB Linked Tables
SELECT TOP 10 XMLELEMENT (“Employee”) from
“Northwind”.”Demo_SQLSVR”.”Employees”;
This example in Figure 71 —
Example select using XMLElement shows a use of XMLELEMENT () for creating the xml element
‘EmployeeName’ (for each employee) with the text content that is value of the
‘LastName’ column from the “Demo”.”demo”.”Employees” table.
Figure 71 — Example select
using XMLElement
Select XMLELEMENT (“EmployeeName”, “LastName”) from “Demo”.”demo”.”Employees”;
3.3.13.1.2.1.1.2
SQLX Function XMLAttributes
This example shows the use of XMLELEMENT() for creating the xml element ‘Employee’
with the ‘EmployeeID’ attribute, text content that is concatenations of the
value of the ‘FirstName’ column, of the blank character and of the value of the
‘LastName’, and the ‘Title’ sub element having ‘title’ attribute using the
XMLAttributes function.
Figure 72 — Example SQLX XMLElement
Select XMLELEMENT (‘Employee’,
XMLATTRIBUTES (“EmployeeID”),
“FirstName”||’ ‘||”LastName”,
XMLELEMENT (“Title”, XMLATTRIBUTES (“Title”
as “title”)))from “Demo”.”demo”.”Employees”;
3.3.13.1.2.1.1.3
SQLX Function XMLForest
The example in Figure 73 —
Example SQLX XMLForest and XMLElement shows a select statement using XMLELEMENT () with the nested call of
XMLFOREST () function. The result is identical to the previous example shown in
Figure 58.
Figure
73 — Example SQLX
XMLForest and XMLElement
Select XMLELEMENT (‘FullName’,
XMLFOREST (“FirstName”as “firstname”, “LastName” as “lastame”))from “Demo”.”demo”.”Employees”;
3.3.13.1.2.1.1.4
SQLX Function XMLConcat
The XMLCONCAT() functions
accepts a list of XML value expressions as its arguments, and produces a forest
of elements by concatenating the XML values that are returned from the same row
to make one value. Essentially XMLCONCAT works just like XMLFOREST, except that
XMLCONCAT parameters are node sets or XML elements. In this usage, NULL expressions are dropped from the
result and if all the value expressions are NULLs then the function, returns
NULL.
The
example in
Figure 74 — Example SQLX XMLConcat produces a forest from the ‘FName’ and ‘LName’ elements
for each employee:
Figure 74 — Example SQLX XMLConcat
Select XMLCONCAT (
XMLELEMENT (‘FName’, “FirstName”), XMLELEMENT
(‘LName’, “LastName”))
from “Demo”.”demo”.”Employees”;
For more
information and details regarding Virtuoso SQLX
implementation, see our on-line:
·
Virtuoso
SQLX Documentation
·
Virtuoso
SQLX Tutorials
Virtuoso extends SQL-92 with the FOR XML clause.
Similar in syntax and function to MS SQL Server Virtuoso support the FOR
XML modes including RAW, AUTO and
EXPLICIT.
The following
examples show a couple of FOR XML clauses.
3.3.14
RAW Mode
This SELECT
statement retrieves information from Customers and Orders table in the
Northwind database. This query in Figure 75 – SQL
Query using the FOR XML Raw specifies the RAW mode in the FOR XML clause:
Figure 75 – SQL Query using the FOR XML Raw
3.3.14.1 SELECT TOP 1 * FROM
demo..Customers FOR XML RAW
This query
returns the first row in the Customer table. Here is what the results look
like:
<document>
<ROW CustomerID=”ALFKI” CompanyName=”Alfreds Futterkiste” ContactName=”Maria
Anders” ContactTitle=”Sales Representative” Address=”Obere Str. 57” City=”Berlin” Region=”” PostalCode=”12209” Country=”Germany” Phone=”030-0074321” Fax=”030-0076545”
/>
</document>
Any
of these SQL Queries can be saved as XML templates. To Save the queries as XML templates navigate to the XML
Services tab and select the SQL-XML
from the Interactive SQL screen by the SAVE option. XML Template appears as shown below in 76
Figure 76 – Saving the Query as an XML Template
3.3.15
Using AUTO Mode
Let us look at
the same query in Figure 77
– SQL Query using the FOR
XML AUTO, but this time with the AUTO mode qualifier:
3.3.16 Figure 77 – SQL Query using the FOR XML AUTO
3.3.16.1 SELECT TOP 1 * FROM
demo..Customers FOR XML AUTO
Virtuoso is
similar to MS SQL Server for these FOR extensions. You can view the contents of the saved XML document
and as you can see the only difference between AUTO and RAW in this example is
that the row element name has been replaced by the name of the table,
Customers.
<document>
<Customers CustomerID=”ALFKI” CompanyName=”Alfreds
Futterkiste” ContactName=”Maria Anders” ContactTitle=”Sales Representative” Address=”Obere Str. 57” City=”Berlin” Region=”” PostalCode=”12209” Country=”Germany” Phone=”030-0074321” Fax=”030-0076545” />
</document>
3.3.17
FOR AUTO ELEMENT Option
A feature of
AUTO mode is the ELEMENT option, which returns results from table columns as
elements instead of attributes.
The query below in Figure
78 – SQL Query using the FOR
XML AUTO ELEMENTS is the same as in Figure
77 – SQL Query using the FOR
XML AUTO above, but adds the element option:
3.3.18 Figure 78 – SQL Query using the FOR XML AUTO
ELEMENTS
3.3.18.1 SELECT TOP 1 * FROM
demo..Customers FOR XML AUTO ELEMENTS
Returns this
format:
<document>
<Customers>
<CustomerID>ALFKI</CustomerID>
<CompanyName>Alfreds Futterkiste</CompanyName>
<ContactName>Maria Anders</ContactName>
<ContactTitle>Sales
Representative</ContactTitle>
<Address>Obere Str. 57</Address>
<City>Berlin</City>
<Region></Region>
<PostalCode>12209<PostalCode>
<Country>Germany</Country>
<Phone>030-0074321</Phone>
<Fax>030-0076545</Fax>
</Customers>
</document>
The ELEMENT
option produces the data in a column format so it is easier to read, etc.
The EXPLICIT
mode offers the most control of XML document formatting returned from an SQL
Server URL query. For more
details regarding the use of the Explicit mode, consult the Virtuoso
Documentation.
3.3.19.1
More Information
For more
details on the FOR XML, see our online:
· Virtuoso
FOR XML Documentation
· Virtuoso
FOR XML Tutorials
The Virtuoso XQuery implementation can transparently
access SQL data with a mapping schema. The mapping schema defines a table
or set of joined tables as a virtual XML document, which can be accessed from
inside the XQuery expression similarly to an outside XML text document. XML data is constructed on the fly
based on the path applied to the mapping schema, thus avoiding unnecessary data
access
Mapping schemas provide
information on how XML data is to be mapped to a relational database. Using the mapping schema, relational
data can be retrieved as an XML document.
Virtuoso supports creating XML views by using annotated
XSD schemas, which are referred to as mapping schemas. A file containing a
mapping schema may be loaded by calling the xml_load_mapping_schema_decl() function as shown in Figure 79 -
Mapping Schema View, which contains
a mapping schema for some of the Demo database tables. A name (without
extension .xsd) of the file containing a mapping schema is considered the name
of the xml view, defined by a 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.
Figure 79 - Mapping Schema View –
DB..Map01
xml_reload_mapping_schema_decl (“,
‘file:/tutorial/xml/usecases/map01.xsd’ , ‘UTF-8’ , ‘x-any’ , ‘<xsd:schema
xmlns:xsd=”http://www.w3.org/2001/XMLSchema”
xmlns:sql=”urn:schemas-microsoft-com:mapping-schema”>
<!--targetNamespace=”http://www.openlinksw.com/demo”-->
<xsd:annotation>
3.3.19.2
<xsd:appinfo>
<sql:relationship name=”CustOrders”
parent=”Demo.demo.Customers”
parent-key=”CustomerID”
child=”Demo.demo.Orders”
child-key=”CustomerID” />
<sql:relationship name=”OrderOrderDetail”
parent=”Demo.demo.Orders”
parent-key=”OrderID”
child=”Demo.demo.Order_Details”
child-key=”OrderID” />
<sql:relationship name=”OrderDetailProducts”
parent=”Demo.demo.Order_Details”
parent-key=”ProductID”
child=”Demo.demo.Products”
child-key=”ProductID” />
<sql:relationship name=”ProductsCategories”
parent=”Demo.demo.Products”
parent-key=”CategoryID”
child=”Demo.demo.Categories”
child-key=”CategoryID” />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name=”Customer”
sql:relation=”Demo.demo.Customers” maxOccurs=”unbounded” minOccurs=”0”>
3.3.19.3
<xsd:complexType>
<xsd:sequence>
<xsd:element name=”Order”
sql:relation=”Demo.demo.Orders” sql:relationship=”CustOrders”
maxOccurs=”unbounded”>
3.3.19.4
<xsd:complexType>
<xsd:sequence>
<xsd:element name=”Item”
sql:relation=”Demo.demo.Order_Details” sql:relationship=”OrderOrderDetail”
maxOccurs=”unbounded”>
<xsd:complexType>
<xsd:attribute name=”ProductID”
type=”xsd:integer” />
<xsd:attribute name=”CategoryName”
type=”xsd:string”
sql:relation=”Demo.demo.Categories”
sql:field=”CategoryName” sql:relationship=”ProductsCategories”/>
<xsd:attribute name=”UnitPrice” type=”xsd:double”
/>
<xsd:attribute name=”Quantity” type=”xsd:integer”
/>
<xsd:attribute name=”Discount” type=”xsd:float”
/>
3.3.19.5
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name=”OrderID” type=”xsd:integer” />
<xsd:attribute name=”OrderDate” type=”xsd:dateTime” />
<xsd:attribute name=”ShippedDate” type=”xsd:dateTime”
/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name=”CustomerID”
type=”xsd:string” />
<xsd:attribute name=”CompanyName”
type=”xsd:string” />
<xsd:attribute name=”ContactName”
type=”xsd:string” />
</xsd:complexType>
</xsd:element>
</xsd:schema>
‘ )
Step 1.
Navigate to the XML Services Tab, select SQLX-XML sub-tab and
then enter the following query as shown below in Figure 80 —Query using a mapping
schema XML view
Figure 80 —Query using a mapping schema XML
view
/Customer[@CustomerID like “A%”]
Step 2.
Select the Mapping Schema map01 from the pull down list, enter
the WebDAV Resource as shown in Figure
81 — Choosing the Mapping Schema.
Figure 81 — Choosing the Mapping Schema
Step 3.
As shown in Figure
82 — Executing the , mark the Radio
set next to the Create as XML template, enter the Query Description and click
the Execute button. The results
are shown below in Figure
83 - Query using Mapping Schema Execution
Results.
Figure 82 — Executing
the Mapping Schema Query
Figure 83 - Query using Mapping Schema Execution
Results
More Information
For More details on Mapping Schemas, see our on-line:
·
Virtuoso
Mapping Schema Documentation
·
Virtuoso
Mapping Schema Tutorial
Virtuoso’s XQuery features apply not only to documents
stored in its WebDAV repository, but also to native and virtual relational data
that is exposed as XML content. The benefits of this capability are obvious
when you imagine the need to query information as XML from a database that does
not inherently support XQuery, or from several heterogeneous databases that may
or may not support XML composition.
The XQuery support in the Conductor allows you to create,
execute, save and reload queries using XQuery. In the following steps, we
will query the table XQuery test files table, with “name” as the key column and
“text” as the data column.
When executed the query will find all rows in the table XQ.XQ.TEST_FILES
that have the value “xqdemo/bib.xml” in their Name column.
Step 1.
Navigate to the XML Services Tab, select the XQuery sub-tab
and then check the radio set option Database Table for the Query Context Mode
and click the NEXT button as shown below in Figure
84 – XQuery Execution.
Figure 84 – XQuery Execution
Step 2.
As shown in Figure 85 – Selecting Database Catalog and Tables,
select the Database Catalog XQ, which contain the XQuery Examples and the
XQuery Table XQ.XQ.TEST_FILES and
click the NEXT button.
Figure 85 – Selecting Database Catalog and
Tables
Step 3.
As shown in Figure
86 – Selecting the Key and Value Column Attributes, select the Key
and Value attributes. Next, select
the xqdemo/bib.xml” Key Column Value Drop Down and then click the NEXT button as
shown below in Figure 87 – Selecting the Value of the Key Column.
Figure 86 – Selecting the
Key and Value Column Attributes
Figure 87 – Selecting the Value of the Key
Column
Step 4.
AS
shown in Figure 88 - XQuery document below, contains a document() call specifying a document
named “bib.xml” which has been automatically filled in the Conductor. Choose to perform XSL transformation or change the
root element, then click then the NEXT button.
Note 8: In order to have the query run properly, we first
set the Path form value to “xqdemo/”. This causes the query to find all rows in
the table XQ.XQ.TEST_FILES that have the value “xqdemo/bib.xml” in their Name
column.
Figure 88 - XQuery document
<bib>
{for $b in document(“bib.xml”)/bib/book
where $b/publisher = “Addison-Wesley” and $b/@year > 1991
return
<book year = {$b/@year}>{$b/title}</book>
}
</bib>
Step 5. As shown below in Figure 89 – Storing Query in WebDAV, you have the option to store the resulting query in WebDAV or create an XQuery Template, then click the NEXT button. A screen will be display as seen in Figure 90 –Result of XQuery Execution upon a successful execution of the XQuery query.
Figure 89 – Storing Query in WebDAV
Figure 90 –Result of XQuery Execution
With Virtuoso, we can save several types of queries
into XML templates. These include
saving SQL Queries, XPATH Queries and XQuery into XML Templates. The example below illustrates saving
and the XQuery example illustrated in simple demonstration above into an XML
Template.
Using the Conductor, you can make and save a query to an
XML template in WebDAV by changing the option under the Store into in the Save
Parameters to Persist in XML as shown below in Figure
91 – Saving a Query as an XML Template. To save
this as an XML template select the SAVE button and the query will be copied to an XML
template page as shown in Figure
92 - Stored XQuery Templates.
Figure 91 – Saving a Query as an XML Template
Figure 92 - Stored XQuery Templates
3.3.19.6.4 Querying multiple documents with XQuery
Virtuoso provides the ability to query multiple
documents also known as collections with a single XQuery statement. This feature introduced in 4.5 is part
of the XQuery fn:collection function
implementation that provides a vehicle for querying a collection of documents contained in
either a local or a remote DAV directory.
Note 9 - Local DAV collections can be accessed by
providing “http://local.virt/DAV/” or “http://localhost:PORT/” URI.
Note 10 - Remote DAV collections that are any other URI
that begins with http://, the Virtuoso engine uses the
“PROPFIND” method to get the list of documents contained in remote collection.
3.3.19.7
More Information
For more information, regarding XQuery and XML Templates
see our on-line:
·
Virtuoso
XML Template Documentation
·
Virtuoso
XML Template Tutorials
3.3.19.8.1
XSL-T support
Virtuoso’s built-in XSLT engine enables any XML document
including those containing orders or inventories to product information to be
converted on the fly to a format that is suitable for any application.
Virtuoso supports XSLT
1.0 transformations as SQL callable functions, which can be called from ISQL or
Virtuoso PL. In addition, the
built in XSLT engine may retrieve both data and stylesheets from any location
including local file systems, local tables, remote datasources, or even the
Web.
Virtuoso unique since it
combines XSLT, XPath and XQuery in one engine so that any application can use
XSLT-specific functions in XPath expressions or XQuery functions. Any extension functions defined
for the XLST engine can also be used in XPath and XQuery expressions with out
any additional code making it more straightforward and easy to program.
A good example of a Virtuoso XSLT transformation is
demonstrated through the SQL->XML Translation page. In this case, we can use an XML Stylesheets to define
how to transform an XML document into another document. From this interface,
you could transform for a document into different formats like HTML or PDF for
example.
Step 1.
In the Conductor, navigate to the XML Services and then
select the SQL-XML
Translation sub-tab.
Step 2.
In the SQL-XML translation screen, as seen in Figure 93 – SQL-XML Translation below, enter the SQL select statement for the query you wish to
execute, and then type the root XML element name into the Root
Element field.
Note 11 - The results of the query will be contained within
this root element.
Figure 93 – SQL-XML Translation
Step 3.
Type
the full path and resource name where you want your XML resource to reside
under WebDAV in the WebDAV Location of Resource field.
Step 4.
XSLT
transformation is performed on document retrieval time. To select the style
sheet you may either type the location of the style sheet in the XSLT
Style sheet field. Click Browse to locate valid XSLT
style sheet from the WebDAV repository.
You can check the query before execution by selecting the Check Query
button that will parse the query and generate the XML output in a pop up screen
as shown below in Figure 94 – Checking SQL-XML Queries.
Figure 94 – Checking SQL-XML Queries
XSLT transformation can
also be performed on the XQuery Execution Page, which is found in the XQuery
sub-tab.
The following example
will perform a search across a DAV collection and use XSLT Style sheet for
transformation.
Step 1.
Navigate
to the XQuery sub-tab under XML Services and select the radio-set option next
to DAV Resources and click Next as shown in Figure
95 - XQuery Execution DAV .
Figure 95 - XQuery Execution DAV Collection
Figure 96 – XQuery Browsing DAV
Collection
Step 2.
As
shown in Figure 97 – XQuery XPath Expression below, enter any XPATH query expression in the text area
that you wish to search in a WebDAV XML document. For example enter an XPATH expression //*
or //title.
Figure 97 – XQuery XPath Expression
Step 3.
In
this screen check the XSL translation checkbox, style sheet and Root element
option. Then can check the query
before execution by selecting the Check Query button that will parse the query
and generate the XML output in a pop up screen as shown below in Figure 98– Checking your XQuery Query. After you
have checked the query, click the Next button.
Figure 98– Checking your XQuery Query
Step 4.
Enter the WebDAV
location to store your query and click the SAV button as shown below in Figure 99 – Storing XQuery Results.
Figure 99 – Storing XQuery Results
3.3.19.9
XPath
Expressions
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.
XPath expressions can also be executed from ISQL or used
in programs. The following is an example of an XPath expression
execution and results in the Conductor.
Step 1.
Navigate to the Database Table and select the ISQL sub-tab
Step 2.
Enter in the following XPATH Query as shown below in Figure 100 – XPATH Execution from ISQL, limit the query to 10 rows and Click the Execute button.
Figure 100 – XPATH Execution from ISQL
SELECT * from (XPATH ‘[__* __view
“DB”.”DBA”.”cat”]//product/@ProductName’) P order by P.”ProductName”
Figure 101 – Results from XPATH
expression executed via ISQL
For more information regarding XMLT or XPath Support, see
our on-line:
·
Virtuoso
XSLT Documentation
·
Virtuoso
XSLT Tutorials or Virtuoso
XSLT for Web Page Processing Tutorials
3.3.19.10.1.1
Document Type
Definitions (DTDs)
A Document Type Definition or DTD for short is a
document that describes the structure of a Web page written in XML. This document is a formal definition of
the all the elements, structures, and rules for marking up a given type of SGML
document.
You can store a DTD at the beginning of a document or
externally in a separate file.
Used by validators and checkers to make sure your documents
adhere to the standard.
The following is an example of a declaration for an
employees address DTD.
<!ELEMENT address (name?, (pobox|street+), city, state, zip)>
<!ATTLIST address
idID #IMPLIED
country CDATA #FIXED “US”
>
3.3.19.10.1.2
XML
Schema
XML
Schemas are XML documents themselves that can be managed by XML authoring
tools, or through XSLT. The implementation of XML Schema in Virtuoso is based
on the W3C XML Schema Specification. Unlike DTDs, XML Schemas are written
using XML elements and attributes instead of a special notation. XML Schemas
offer a number of built-in datatypes and a type hierarchy that separates type
definition from element declaration.
For example, you can derive an international address type by extending
the definition of some base address type, or you can derive an age datatype
from integer by restricting it to values strictly greater than 0 and less than
100.
The Virtuoso interface to XML Schema is represented
primarily by two functions:
·
xml_validate_schema()
·
xml_load_schema_decl()
The signature of the function xml_validate_schema
() is the same as the function xml_validate_dtd
(). It parses and validates an XML document. The root element of the
document must contain the “schemaLocation”
attribute with the value of the document’s URI.
As described above, the XML Schema Processor implemented
within Virtuoso relies on the XML Schema DTD, which is composed of two files:
“XMLSchema.dtd” and “datatypes.dtd.” These files must be placed in the system
directory (see xml_add_system_path
()).
Note 12 - Virtuoso does not cache XML Schema documents; they
are completely re-processed every time the document is loaded.
3.3.19.10.2
Generating XML Schema and DTD Data
This example in Figure 102 – XML_AUTO_SCHEMA and Figure 103 – XML_AUTO_DTD shows an example uses two of Virtuoso built functions xml_auto_schema () and xml_auto_dtd
(). Theses functions can be
used to generate XML Schema or DTD information about any given SQL query:
Figure 102 – XML_AUTO_SCHEMA
SELECT xml_auto_schema (‘select u_name from sys_users’, ‘root’);
SQL> callret
<xsd:schema xmlns:xsd=”http://www.w3.org/2001/XMLSchema”>
<xsd:annotation>
<xsd:documentation>
Schema for output of the following SQL statement:
<![CDATA[select u_name from sys_users]]>
</xsd:documentation>
</xsd:annotation>
<xsd:element name=”root” type=”root__Type”/>
<xsd:complexType name=”root__Type”>
<xsd:sequence>
<xsd:element name=”SYS_USERS” type=”SYS_USERS_Type” minOccurs=”0” maxOccurs=”unbounded”/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name=”SYS_USERS_Type”>
<xsd:attribute name=”U_NAME” type=”xsd:string”/>
</xsd:complexType>
</xsd:schema>
1 Rows. -- 1843 msec.
Figure 103 – XML_AUTO_DTD
Select xml_auto_dtd(‘select u_name from sys_users’, ‘root’);
SQL> callret
_
<!-- dtd for output of the following SQL statement:
select u_name from sys_users
-->
<!ELEMENT root (#PCDATA | SYS_USERS)* >
<!ELEMENT SYS_USERS (#PCDATA)* >
<!ATTLIST SYS_USERS
U_NAME CDATA #IMPLIED >
1 Rows. -- 411 msec.
More Information
For more information regarding XML Schema Support, see our
on-line:
·
Virtuoso
XML Schema Documentation
News
and discussions forums from around the world are available in different forms,
from RSS, RDF; ATOM feeds to NNTP (Network News
Transport Protocol) Usenet Groups. As we rapidly towards in a more semantic World
Wide Web, there is will be a natural increase in the need to integrate data
that resides in Usenet discussion threads, Blogs posts, and Wikis. This will
also increasing include integration across other Virtuoso resident or
accessible data sources such as emails, reports, SQL tables, Web Services etc.
Virtuoso’s
News Services is a NNTP based server that provides
fast access to Internet (Usenet) news groups with a simple interface for creating,
administering and accessing NNTP newsgroups. Virtuoso News Services enable
integration of multiple news forums whether they reside in an internal
corporate intranet or externally on the Internet.
More
than just a NNTP newsreader, Virtuoso News Services has the ability to perform
a powerful Usenet text search where by news content that resides in Virtuoso is
dynamically indexed to provide keyword searches across multiple forums.
The
following examples demonstrate the News Group Administration aspect of the
Virtuoso News Services, which allows you to view and configure Newsgroups associated with the
Virtuoso News Server. Like Virtuoso’s virtual database, Virtuoso can “link” in
newsgroups from remote news servers. This interface allows you to view the
configuration and control the newsgroups both local and remote.
Step 1.
In
the Conductor, navigate to the NNTP Services Tab and select the NNTP Servers
sub-tab as shown Figure 104 - Adding New News Server.
Note 13 - The
Virtuoso News server component needs to be enabled in the Virtuoso.ini
file by specifying the NewsServerPort number.
Figure 104 - Adding New News Server
Step 2.
In
this form as shown in Figure 105 – NNTP Server Configuration below, input the details for the remote server to be
associated with Virtuoso. This
includes:
·
NNTP
address: The IP address or hostname of the
external news server. Example
news.readfreenews.net
·
Port number that the news server is
listening on. By default, news servers listen on port 119.
·
Username and Password
(optional) access credentials if required by the news server.
Once you have entered the form, click the Add
button.
Figure 105 – NNTP Server Configuration
Step 3.
NNTP News servers addresses are listed as seen in Figure 106 – News Server Administration. Use the 
plus
icon to expand a server exposing a table of newsgroups that have been linked.
Once expanded, you can use the minus icon to contract the news server table.
Figure 106 – News Server Administration
Step 4.
The
options placed on the title bar of the news server name are available for
configuring the association to the external news server. Option include:
·
Edit{server Address}, which will bring you to the NNTP Server Group Subscriptions
Form as seen below in Figure
107 – NTTP Server Group Subscription.
·
Actions
o
Edit, which allows you to edit of the servers location, port or
authentication credentials.
o
Delete, which removes the external news servers from Virtuoso.
Figure 107 – NTTP Server Group Subscription
Step 5.
Select
the Link Subscribe to newsgroups
shown above in Figure 107 – NTTP Server Group Subscription to subscribe to a
newsgroup on the server.
Figure 108 – News Group
Subscription
Note 14 - you can
provide a filter to the Newsgroup Match field and a
number of groups to list per page. Press the Fetch Groups button to
fetch the groups from the server.
Step 6.
As
seen in Figures 88 below, a new table should appear
on the page with the list of groups. Navigation buttons allow you to go the Next
or Previous page. If a table of groups is not
displayed, this could be because either there are no groups available, none has
been fetched into the cache yet or the filter is not broad enough.
Figure 109 – Fetched Groups
For more information regarding NNTP Support,
see our on-line:
·
Virtuoso
NNTP Documentation
·
Virtuoso
NNTP Tutorials
Standards-compliance ensures that you retain the Freedom
to Mix & Match best-of-class combinations of existing and future Operating
Systems, Programming Environments, Database Engines and Data-Access Middleware
that best support your organization’s needs.
Virtuoso will never lead you into a technology cul-de-sac
where ultimate exit and protocol server, as opposed to multiple singe-purpose
server offerings.
Virtuoso’s core is a pioneering Virtual Database Engine
that uses industry-standard data-access technologies to provide transparent
access to disparate SQL, XML, Free Text and other data sources.
A single ODBC-, JDBC, ADO .Net or OLE DB provider connection to Virtuoso can
connect you transparently to tables hosted in multiple NET-, ODBC- or
JDBC-accessible database - facilitating heterogeneous joins across these
databases, and the generation of XML document and data fragments.
A number of the
examples in this document reference the Virtuoso demo database, which is
derived from Microsoft’s Northwind database ships with Virtuoso 4.5. In addition, where indicated other
examples specifically use the Northwind database. This database can be obtained from Microsoft at
http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&displaylang=en
·
Runtimes & Frameworks
Microsoft .NET, Mono, J2EE
·
Web Services
SOAP, UDDI, WSDL, WS-Security, WS-Routing, WS-Reliable Messaging, WS-Policy,
WS-Trust, BPEL4WS
·
XML
XPath, XQuery, XSL-T, XML Schema
·
Web & Internet
WebDAV, HTTP, SMTP, LDAP, POP3
·
SQL Data Access
SQL-200n, SQLX, ODBC, JDBC, ADO.NET, and OLE-DB.
·
Virtuoso On-line Tutorials and Demonstrations http://demo.openlinksw.com:8890/tutorial/
·
Virutoso
Blog - http://www.openlinksw.com/weblogs/virtuoso/.
·
Documentation
- http://docs.openlinksw.com/virtuoso/index.html
·
For additional information about Virtuoso, see the OpenLink Virtuoso Product Web
site . The resources available
on this site include links to technical articles and white papers, downloads
pricing, documentation, animated demonstrations and FAQs
Details regarding the SQLX standard
can be found at http://sqlx.org.
