SSDN - Xquery to MS/SQL?

XML Editor

Sign Up

Search

Options

Chat

Help

News

Log in

Not Logged in

Topic

Neal Walters

Subject: Xquery to MS/SQL?
Author: Neal Walters
Date: 13 May 2005 07:08 PM

I just created my first rdbxml file to prove that I could access SQL from Stylus Studio. That's a nice feature.

This was my query:
use pubs
select * from authors for xml auto,elements

Now, can I use XQuery against the results without having to save a flat file and then read the flatfile. In other words, can I embed an SQL query similar to above inside an Xquery statement?

I know MS/SQL 2005 has that capability - but as of now the current beta release, they still do not read flat files from their Xquery.

Thanks in advance,
Neal Walters
http://Biztalk-Training.com
http://XML-Online-Training.com

Minollo I.

Subject: Xquery to MS/SQL?
Author: Minollo I.
Date: 13 May 2005 09:53 PM

>Now, can I use XQuery against the results without having to save a
>flat file and then read the flatfile. In other words, can I embed an
>SQL query similar to above inside an Xquery statement?

You can avoid going through the explicit step of saving a result of a SQL query and then using it as an input for your XQuery just by using: doc("dbxml:///mySQLQuery.rdbxml")
If you are interested in accessing a whole table, you can use a JDBC-like URL skipping the .rdbxml altogether: doc("db:///jdbc:datadirect:jxtr:sqlserver://localhost:1433;schema=dbo;table=myTable;user=sa;DatabaseName=Stocks;urltype=.xml") - you can just drag&drop a URL from the project tree, after you have open it through Open File Dialog > Relational DB > URI Builder. Of course under the conver Stylus Studio will still create a local XML file rendering of the SQL/table in question.

On the other hand, if your question really means "can I execute the XQuery against my relational data without ever materializing the whole table(s) as XML file(s)?", then it's different. You will need a special XQuery processor to do that, a processor that allows you to execute XQueries against virtual views of a releational database and that translates the relevant part in SQL. DataDirect is working on such product, and you may be interested in registering for the beta program: http://www.datadirect.com/products/xquery/xquerybetahome/index.ssp

Hope it helps,
Minollo

Neal Walters

Subject: Xquery to MS/SQL?
Author: Neal Walters
Date: 20 May 2005 11:45 AM

My XML query runs fine when I have called SelectAmazonOrders.rdbxml.

use checkout
select AmazonOrderID from tbAmazonOrder
for xml auto, elements

When I tried an Xquery with a doc("SelectAmazonOrders.rdbxml") as shown here:

<Root>
{
for $Message in doc("myAmazonFile.xml")
/AmazonEnvelope/Message/SettlementReport/Order
where empty(doc("dbxml:///SelectAmazonOrders.rdbxml")
/AmazonOrders/tbAmazonOrder[AmazonOrderID=$Message/AmazonOrderID])
return
<AmazonMissingOrders>
<SettlementOrderID>{$Message/AmazonOrderID/text()}</SettlementOrderID>
<DBOrderID>{$Message/AmazonOrderID/text()}</DBOrderID>
</AmazonMissingOrders>
}
</Root>

I get this error:

You must set your CLASSPATH and PATH environment variables before using the Berkeley DB XML adapter. I found another post that says I should have a path to the Berekely DB XML adapter, but I have never heard of it before, don't know where it is, etc... Is it installed with Stylus Studio? Do I have to download it? Do I just path the the Stylus Stuiod BIN directory?

Thanks again,
Neal Walters
http://Biztalk-Training.com

Minollo I.

Subject: Xquery to MS/SQL?
Author: Minollo I.
Date: 20 May 2005 12:21 PM

You should change doc("dbxml:///SelectAmazonOrders.rdbxml") into doc("db:///SelectAmazonOrders.rdbxml"); otherwise you are trying to access the Berkeley DB XML database.

Minollo

Neal Walters

Subject: Xquery to MS/SQL?
Author: Neal Walters
Date: 20 May 2005 02:09 PM

Thanks, but it's two slashes instead of three.
I'm still playing with variations on this theme.

Neal

Minollo I.

Subject: Xquery to MS/SQL?
Author: Minollo I.
Date: 20 May 2005 07:54 PM

You may be interested in evaluating the latest Stylus Studio 6 XML Enterprise Edition Release 3 (http://www.stylusstudio.com/downloads/stylus-studio-enterprise-6.exe).

In the latest version you will be able to run the same XQuery also using the Saxon 8.4 processor and to generate Java code to run the same query externally to Stylus Studio. And the three slashes will be accepted ;)

There are a number of other new features in XML Enterprise Edition; you can find them in the announcement I just posted: http://www.stylusstudio.com/SSDN/default.asp?action=9&read=3658&fid=50

Hope this helps,
Minollo

Kingsley Idehen

Subject: Xquery to MS/SQL?
Author: Kingsley Idehen
Date: 18 May 2005 12:11 PM

I don't want to make this answer appear to be a promo! Its just a simple unveiling of a clear oversight in the response to this question.

OpenLink Virtuoso (like SQL Server 2005) does provide you with the ability to perform XQuery over SQL Data that is exposed as XML (valid or well formed). This has actually been the case for a number of years now :-(

You can use the FOR XML syntax (ala SQL Server), the new SQL/XML (ala SQLX), or XML Schema Mapping to create these XML views of SQL Data.

All that is required is that your SQL Data Source ODBC or JDBC compliant.

To see what I mean in action (a live solution based on what you are describing) see my blog: http://www.openlinksw.com/blog/~kidehen . The RDF, FOAF, RSS, Atom, and other gems are all SQL exposed as XML. If you go to the "Advanced Search" link on the blog it brings you to a search page that provides you with an XQuery option. Naturally, this is an example of XQuery hitting SQL data on the fly (here is an example URI: http://www.openlinksw.com/weblog/public/search.vspx?blogid=127&q=xquery&type=text&output=html ). The important thing about the URI is that it can be used as a context node for XQuery from outside Virtuoso using tools such as Stylus Studio which shows the value of XQuery for transforming and exposing SQL content for XML data consumers.

The data in my demo is stored in Virtuoso but it could have also have been an ODBC or JDBC accessible database or combination of databases (by this I mean my data model could exist across Oracle, Sybase, Ingres, Progress etc. if so required), since Virtuoso in a Virtual DBMS engine also.

BTW - You can obtain a copy of Virtuoso from http://virtuoso.openlinksw.com or explore our online tutorials at: http//demo.openlinksw.com .

I hope this helps clarify this matter by broadening your awarness of the tools that are available today that compliment Stylus Studio and its quest to assist in the unveiling of the potential of XQuery etc..

Kingsley Idehen

Subject: Xquery to MS/SQL?
Author: Kingsley Idehen
Date: 18 May 2005 12:36 PM

Ooops! The query example that I gave in my initial post was a Free Text Query based search. Here is the URI to the XQuery variation of the same query: http://www.openlinksw.com/weblog/public/search.vspx?blogid=127&q=for+%24i+in+node%28%29//p[contains%28.%2C%27XQuery%27%29]+return+%3Cp%3E{+string%28%24i%29+}%3C/p%3E&type=xquery&output=html

which comes down to:
for $i in node()//p[contains(.,'XQuery')]
return <p>{ string($i) }</p>

Powered by Stylus Studio, the world's leading XML IDE for XML, XSLT, XQuery, XML Schema, DTD, XPath, WSDL, XHTML, SQL/XML, and XML Mapping!

Go to Conference:

Log In Options Username: Password:

Site Map | Privacy Policy | Terms of Use | Trademarks

Stylus Studio® and DataDirect XQuery ™are from DataDirect Technologies, is a registered trademark of Progress Software Corporation, in the U.S. and other countries. © 2004-2016 All Rights Reserved.