Converting SQL result sets to XML in Sybase ASE

Converting SQL result sets to 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. There are a number of ways of doing this:
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).

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



0736
New Age Books


0877
Binnet & Hardley


1389
Algodata Infosystems



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,
'0736
New Age Books')

insert MyXMLTab values (2,
'1389
Algodata Infosystems')
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 0877

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.1. 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. 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



0736
New Age Books
Boston
MA



0877
Binnet & Hardley
Washington
DC



1389
Algodata Infosystems
Berkeley
CA



(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



0736
Boston



0877
Washington



1389
Berkeley



(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



BOSTON
0736



WASHINGTON
0877



BERKELEY
1389



(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



1389
Algodata Infosystems
Berkeley
CA



0736
New Age Books
Boston
MA



(return status = 0)


CHECK OUT MORE OF THIS ON http://www.sypron.nl/xml.html