Sybase ASE
11.0 11.5 11.9 12.0 12.5 15.x
Last updated: 26 January 2008
XML in Sybase ASE
 
There is a growing interest among ASE users for combining SQL and XML.
Perhaps the most common requirement is to represent an SQL result set in XML format, but combining XML and SQL (so-called 'SQLX') is becoming ever more common. Depending on your ASE version, ASE offers different levles of support for SQLX (you may want to read this bottom-up to get the full picture):
  • ASE 15.0: ASE 15.0 has a greatly improved XML engine compared with 12.5.1. Not the least improvement is that XML is no longer a separately licensable feature, but is included in the core ASE product in ASE 15 at no additional cost.
    Other improvements are greatly expanded XPath/XQuery support, XML schema support, and new functions to manipulate XML documents such as xmlextract(), xmlvalidate(), and xmltable() (in ASE 15.0.2).
    An ASE 15 feature that combines really well with XML is that of computed columns, which offers a level of automatic 'shredding' of XML documents into 'relational' columns. By creating materialized computed column based on xmlextract(), a particular element from the XML document is automatically made available as a 'normal' column, which -for example- can then be indexed. This way, it becomes possible to access XML data based on the values of its elements, but with performance comparable to accessing classic, non-XML data.
    To enable XML in ASE 15, you still need to run sp_configure 'enable xml', 1 .
  • ASE 12.5.1: ASE 12.5.1 comes with a completely new implementation of XML inside ASE. Unlike earlier attempts at XML-in-ASE, 12.5.1 does not use Java-in-ASE this new 'native' XML functionality (actually, it does use Java for some of the minor features, but the main functionality doesn't).
    XML functionality is disabled by default, and should be enabled by running sp_configure 'enable xml', 1 ; this requires having licensed the XML feature (see below).
    Among many other things (like XPath/XQuery support), this new feature lets you turn an SQL result set into an XML document by simply adding the for xml clause to a select statement:
    1> select pub_id, pub_name from publishers for xml
    2> go
    
    <resultset xmlns:xsi="http://www.w3.org/2001/
    XMLSchema-instance">
    <row>
       <pub_id>0736</pub_id>
       <pub_name>New Age Books</pub_name>
    </row>
    <row>
       <pub_id>0877</pub_id>
       <pub_name>Binnet & Hardley</pub_name>
    </row>
    <row>
       <pub_id>1389</pub_id>
       <pub_name>Algodata Infosystems</pub_name>
    </row>
    </resultset>
    
    To extract elements from an XML document, use the xmlextract() function:
    -- first create some test data
    create table MyXMLTab (id int, xmldoc text)
    go
    
    insert MyXMLTab values (1, 
    '<pubs><pub_id>0736</pub_id>
    <pub_name>New Age Books</pub_name></pubs>')
    
    insert MyXMLTab values (2, 
    '<pubs><pub_id>1389</pub_id>
    <pub_name>Algodata Infosystems</pub_name></pubs>')
    go
    
    -- now retrieve the data
    1> select  
    2>   pub_id = xmlextract("//pubs/pub_id/text()", xmldoc), 
    3>   pub_id_tag = xmlextract("//pubs/pub_id", xmldoc) 
    4> from MyXMLTab where id = 2
    5> go
    
     pub_id pub_id_tag
     ------ ---------------------
     0877   <pub_id>0877</pub_id>
    
    Note how the text() function strips the XML tags from the XML element. Also note that the returned value is still a text string, so if conversion to a numeric datatype is desired, this should still be done explicitly with convert().

    This is only a brief example of the XML functionality in 12.5.1+. Many more functions exist; full details can be found in the ASE product documentation.
    The XML stuff in 12.5.1 is a licensable feature, but fortunately, it is included in the free Developer's Edition of 12.5.x. ASE 12.5.1 also supports web services, which is based on this XML functionality.
  • ASE pre-12.5.1: In pre-12.5.1, ASE supports XML/XQL based on the Java functionality in ASE. This works well provided you have XML documents already stored in the database and you want to parse/query these using XQL. Unfortunately, there isn't an easy way of converting between SQL format and XML (this can be implemented with some of the included Java classes, but this requires implementation work on your part). Also, you'll need to license the ASE_JAVA option to use this functionality.
    Full details are described here.
  • ASE 12.0+: In 12.0+, you can also turn the contents of a table into XML format using the stored procedure sp_tab2xml, described below. This is quite a simple tool (though it uses rather advanced T-SQL programming tricks), and does not require any licensable options; yet, it is surprisingly useful.
  • Lastly, Thomas Gagne's 'is' tool is basically an isql replacement that can return a result set as XML (or HTML); it works well, but you'll have to build the tool from the source code your self - YMMV...


sp_tab2xml: a simple SQL-to-XML tool for Sybase ASE
I have repeatedly received requests from ASE users who simply want to present some of their relational data in XML format, but don't want to bother with Java licenses or custom software development. Basically, many ASE users seem to need a simple way to present a result set as XML.

For those requirements, I wrote a (rather straightforward) stored procedure named sp_tab2xml which presents the rows in a database table (or view) as XML documents, with tags corresponding to the column names.
Before proceeding: note that this stored proc makes sense only if you're not on ASE 15 yet, since the XML functionality available there is endlessy better...

You can download this stored procedure here.

Let's just display the contents of the table pubs3..publishers as XML:
1> use pubs3                                 
2> go                                        
1> sp_tab2xml publishers                     
2> go                                        
<?xml version="1.0"?>
<resultset>
<publishers>
   <pub_id>0736</pub_id>
   <pub_name>New Age Books</pub_name>
   <city>Boston</city>
   <state>MA</state>
</publishers>

<publishers>
   <pub_id>0877</pub_id>
   <pub_name>Binnet & Hardley</pub_name>
   <city>Washington</city>
   <state>DC</state>
</publishers>

<publishers>
   <pub_id>1389</pub_id>
   <pub_name>Algodata Infosystems</pub_name>
   <city>Berkeley</city>
   <state>CA</state>
</publishers>

</resultset>
(return status = 0)
The column-list parameter can be used to include only certain columns:
1> sp_tab2xml publishers, NULL, "pub_id, city"
2> go
<?xml version="1.0"?>
<resultset>
<publishers>
   <pub_id>0736</pub_id>
   <city>Boston</city>
</publishers>

<publishers>
   <pub_id>0877</pub_id>
   <city>Washington</city>
</publishers>

<publishers>
   <pub_id>1389</pub_id>
   <city>Berkeley</city>
</publishers>

</resultset>
(return status = 0)
Expressions are also allowed, and XML tags can be renamed:
1> sp_tab2xml publishers, NULL, 
2> "Town=upper(city), Publisher=pub_id"
3> go
<?xml version="1.0"?>
<resultset>
<publishers>
   <Town>BOSTON</Town>
   <Publisher>0736</Publisher>
</publishers>

<publishers>
   <Town>WASHINGTON</Town>
   <Publisher>0877</Publisher>
</publishers>

<publishers>
   <Town>BERKELEY</Town>
   <Publisher>1389</Publisher>
</publishers>

</resultset>
(return status = 0)
Finally, the where and order-by clause can be used to filter and order the rows:
1> sp_tab2xml publishers, NULL, NULL, 
2> "where city like 'B%' order by state"
3> go
<?xml version="1.0"?>
<resultset>
<publishers>
   <pub_id>1389</pub_id>
   <pub_name>Algodata Infosystems</pub_name>
   <city>Berkeley</city>
   <state>CA</state>
</publishers>

<publishers>
   <pub_id>0736</pub_id>
   <pub_name>New Age Books</pub_name>
   <city>Boston</city>
   <state>MA</state>
</publishers>

</resultset>
(return status = 0)

Installation
To install the stored procedure, follow these steps (again, don't go here if you have ASE 15 available):
  • Download the code from here; this will get you a file named SQL2XML.SQL (current version: 1.4, 03-Jun-2003)
  • Execute this script with isql, using a login having sa_role.

Revision history
  • Version 1.4 - 08-Jun-2003 - Now works for #temp tables as well as identity columns. Also, non-sa_role users won't get an error message anymore. Some other small problems have been fixed as well (thanks to Carsten Laszlo for helpful feedback).


  • Version 1.3 - 29-Jan-2003 - Added an optional column list and where/order-by clause as parameters for more flexibility.


  • Version 1.1/2 - 07-May-2002 - Some changes to the XML formatting to allow XML parsers (like MS Internet Explorer) to better recognise the result. (thanks to Grant Queenin for contributing this)


  • Version 1.0 - 05-May-2002 - First version

Notes
Apart from the regular disclaimers, please note the following:
  • This stored procedure relies heavily on the execute-immediate feature of ASE, so it can only be used in ASE 12.0 or later.
  • sp_tab2xml is a rather straightforward procedure. It is not intended as production-grade code, but rather as a demonstration of how this kind of functionality may be implemented using standard ASE features. Readers are encouraged to use this code as a starting point for writing code that suits their needs.
  • As you may know, ASE 12.5 (and to a lesser extent, also ASE 12.0) comes with support for XML and XQL. While this offers some very interesting functionality for storage and processing of XML, it doesn't give you an easy way of presenting your relational data as XML -- at least you'll need to do some custom development in Java (note that you need to license the ASE_JAVA option to use any of these ASE features).
    The stored procedure sp_tab2xml (described above), should NOT be viewed as a replacement for these ASE features; it is just a simple tool which 'disguises' relational data as XML; it cannot do the rather mature type of processing that these ASE features are capable of.
  • Closing remark for DBAs:
    Please read the header of the downloaded file for additional notes about usage and limitations of this stored procedure. While it looks great, there are some things it can't handle -- so make sure you understand those limitations before you enthousiastically tell your management that your database is now fully XML-enabled...
  • Closing remark for managers:
    Congratulations! Your database is now a full-fledged XML server thanks to sp_tab2xml. Just remember: from now on, your company will be leveraging the latest database technology to gain a competitive advantage that will improve ROI and benefit shareholders, customers, and your stock options. Of course, some finishing touches may be needed to suit your complex environment even better. Guess what: I'm available for consulting!


 
 This document is located at www.sypron.nl/xml.html