/* 
 * SQL2XML.SQL - stored procedures to present relational data in XML format
 * 
 * Description
 * ===========
 * This file contains the stored procedure 'sp_tab2xml', which presents the
 * rows in a database table (or view) in XML format: each row will be 
 * presented as an XML documented, with tags corresponding to the column 
 * names.
 * This functionality may be useful for users who want a quick-and-easy
 * way to present data in relational database tables into XML format.
 *
 * Please note:
 * ------------
 * This probably isn't the most exciting piece of software you've
 * ever seen (I've certainly written more interesting stuff). I wrote these
 * procedures because I kept getting requests from ASE users who simply 
 * want to XML-ize some of their data. Well, that's exactly what 
 * this stored procedure is meant for.
 *
 * This stored procedure is rather straightforward, and 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.
 *
 * This code can be found at http://www.sypron.nl/xml.html.
 *
 *
 * Installation
 * ============
 * Execute this script using "isql", using a login having "sa_role".
 * The stored procedure will be created in the sybsystemprocs database.
 *
 * 
 * Usage
 * =====
 * Use as follows:
 *     sp_tab2xml your_table_name [, "dtd_specification" ] [, "column-list" ] [, "where/order-by clause" ]
 *
 * This will list all rows in the specified table as XML documents.
 * The DTD specification is optional and will be copied blindly to
 * the XML result (no validation is performed).
 * Example:
 *
 * 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>
 *
 *
 * The optional column list parameter can be used to filter certain columns.
 * Example:
 *
 * 1> sp_tab2xml publishers, NULL, "pub_id, city"
 * 2> go
 *
 * Colums can also be modified, and the name of XML tags an be changed:
 * Example:
 *
 * 1> sp_tab2xml publishers, NULL, "Town=upper(city), Publisher=pub_id"
 * 2> go
 *
 *
 * The optional where/order-by clause is internally attached to a select 
 * query so you can filter rows and sorts the results. Example:
 *
 * 1> sp_tab2xml publishers, NULL, NULL, "where city like 'B%' order by state"
 * 2> go
 *
 * 
 *
 * Notes
 * =====
 * - This stored procedure relies heavily on the execute-immediate feature
 *   of ASE, so it can only be used in ASE 12.0 or later.
 *
 * - The maximum length of any character string expression is 255 bytes;
 *   when used in ASE 12.5, the 255-byte limit still applies (in other
 *   words, the longer (var)char types in 12.5 aren't supported)
 *
 * - The contents of the specified column list and where/order-by clause are 
 *   not validated prior to execution. 
 *
 * - When specifying values for all parameters, the resulting string can 
 *   easily exceed 255 characters. This is not checked.
 *
 * - Some very arbitrary formatting decisions have been made for 
 *   binary and datetime values; change these if you don't like 'm...
 *
 * - (var)binary datatypes are converted to character strings and 
 *   prefixed with "0x" (remove this if you don't like it...)
 *
 * - (var)binary datatypes are converted to character strings, so
 *   (var)binary columns longer than 127 bytes will be truncated 
 *   in the result.
 *
 * - For columns of text datatype, the result will start with a 
 *   warning message, followed by the first 200-something characters.
 *
 * - Columns of image and Java datatypes are not supported; instead
 *   of a data value, a message will be substituted.
 *
 * - The owner of a table cannot be specified. This can be worked around 
 *   by selecting rows into another table first and running sp_tab2xml 
 *   on that table. 
 *
 * - When operated on a #temp table, the '#' character won't appear in 
 *   the XML tags: for table #xyz, "tmp_xyz" is used in the XML tags.
 *
 *
 * Revision History
 * ================
 * Version 1.0  05-May-2002  First version
 * Version 1.1  07-May-2002  Revisions for better XML compliance
 * Version 1.2  07-May-2002  Clean up work tables
 * Version 1.3  29-Jan-2003  Added where/order-by clause and column list 
 *                           parameters for more flexibility
 * Version 1.4  08-Jun-2003  Handles identity columns now; supports non-sa 
 *                           users and #temp tables; various small fixes.
 * 
 *
 * Copyright Note & Disclaimer :
 * =============================
 * This software is provided "as is"; there is no warranty of any kind.
 * While this software is believed to work accurately, it may not work 
 * correctly and/or reliably in a production environment. In no event shall  
 * Rob Verschoor and/or Sypron B.V. be liable for any damages resulting 
 * from the use of this software. 
 * You are allowed to use this software free of charge for your own 
 * professional, non-commercial purposes. 
 * You are not allowed to sell or bundle this software or use it for any 
 * other commercial purpose without prior written permission from 
 * Rob Verschoor/Sypron B.V.
 * You may (re)distribute only unaltered copies of this software, which 
 * must include this copyright note, as well as the copyright note in 
 * the header of each stored procedure.
 *
 * Note: All trademarks are acknowledged.
 *
 * Please send any comments, bugs, suggestions etc. to the below email
 * address.
 *
 * (c) 2002-2003 Copyright Rob Verschoor/Sypron B.V.
 *                    The Netherlands
 *
 *                    Email: rob@sypron.nl
 *                    WWW  : http://www.sypron.nl/
 *---------------------------------------------------------------------------- 
 */

set nocount on
go
set flushmessage on
go

print " "
print "Copyright (c) 2002-2003 Rob Verschoor/Sypron B.V."
print "For more information about these stored procedures, go to"
print "http://www.sypron.nl/xml.html "
print " " 

use master
go

-- version check: requires 12.0 or later ('cos we're using 'execute immediate')
if isnull(object_id("master.dbo.sysqueryplans"),99) >= 99
begin
   print ""
   print ""
   print "***************************************"
   print "***************************************"
   print " You must run ASE version 12.0 or later"
   print " to install these stored procedures."
   print "***************************************"
   print "***************************************"
   print " "
   print " "
   print " "
   print "Quitting..."
   print " "
   set background on    -- terminate this script now
end
go

-- check sa_role/sso_role
if charindex("sa_role", show_role()) = 0 or charindex("sso_role", show_role()) = 0
begin
   print ""
   print ""
   print "***************************************"
   print "***************************************"
   print " You need 'sa_role' and 'sso_role' to "
   print " run this script.  Please retry..."
   print "***************************************"
   print "***************************************"
   print " "
   print " "
   print " "
   print "Quitting..."
   print " "
   select syb_quit()
end
go

print ""
print "Installing 'sp_tab2xml'..."
print "Usage: "
print "   sp_tab2xml your_table_name [, ""dtd_specification"" ] [, ""column-list"" ] [, ""where/order-by clause"" ]"
print ""
go

sp_configure "allow updates", 1
go

use sybsystemprocs
go

--------------------------------------------------------------------------
--
-- Create the stored procedure 'sp_row2xml'
-- This processes a single row; it is to be called by sp_tab2xml; it 
-- cannot be used by itself
-- 
--------------------------------------------------------------------------

create table #sql2xmlcol (char_value varchar(255) null)
go

if object_id("sp_row2xml") <> NULL
  drop proc sp_row2xml
go

create proc sp_row2xml
/* Copyright (c) 2002-2003 Rob Verschoor/Sypron B.V. */
-- this procedure is called by sp_tab2xml
  @tmprow varchar(80)
as
begin
  declare @cmd varchar(255) 
  declare @tag varchar(255) 
  declare @err int, @rc int
  declare @total_length int, @ret int
  declare @type int
  declare @colname varchar(30)
  declare @objname varchar(30)
  declare @id int

  --select @id = object_id(@tmprow)

  select @id = id
  from tempdb.dbo.sysobjects 
  where name = substring(@tmprow,14,70)

  if @id = NULL
  begin
     print "Error in 'sp_row2xml': can't get id of '%1!'", @tmprow
     return -1 
  end

  -- loop over all columns of this table
  declare col_cursor cursor for
     select name, type
     from tempdb.dbo.syscolumns 
     where id = @id
     order by colid
  for read only
  select @err = @@error, @rc = @@rowcount
  if @err != 0 return -1

  open col_cursor  -- open the cursor
  select @err = @@error
  if @err != 0 return -1

  while 1 = 1     
  begin
     fetch col_cursor into @colname, @type
     if @@error <> 0 or @@sqlstatus = 1 
     begin
        print "Error fetching row"
	break
     end

     if @@sqlstatus = 2 break  -- the end has been reached, exit the loop

     truncate table #sql2xmlcol

     --
     -- Convert the column to a string into a #temp table.
     -- Any datatype-specific formatting should go here
     --
     if @type in (58,61,111) -- datetime 
        select @cmd = "insert #sql2xmlcol select convert(varchar(255), " + @colname + ", 109) from " + @tmprow
     else if @type in (37,45) --(var)binary
        select @cmd = "insert #sql2xmlcol select '0x' + bintostr(" + @colname + ") from " + @tmprow
     else if @type = 34 -- image
        select @cmd = "insert #sql2xmlcol select '[column contains ""image"" data]'" 
     else if @type = 35 -- text
        select @cmd = "insert #sql2xmlcol select '[column contains ""text"" data] ' +  convert(varchar(255), " + @colname + ") from " + @tmprow
     else if @type = 36 -- Java
        select @cmd = "insert #sql2xmlcol select '[column is a Java datatype]'" 
     else -- the rest (numeric, character) 
        select @cmd = "insert #sql2xmlcol select convert(varchar(255), " + @colname + ") from " + @tmprow

     exec (@cmd)

     -- ... and pick up the string value for the #temp table
     select @tag = char_value from #sql2xmlcol

     print "   <%1!>%2!</%3!>", @colname, @tag, @colname
   end

   close col_cursor  -- close cursor
   if @@error <> 0 return -1

   deallocate cursor col_cursor  -- ... and clean up

   return 0
end
go
grant all on sp_row2xml to public
go

drop table #sql2xmlcol
go

--------------------------------------------------------------------------
--
-- Create the stored procedure 'sp_tab2xml'
-- This takes a table or view and outputs the rows as XML documents
-- 
--------------------------------------------------------------------------

if object_id("sp_tab2xml") <> NULL
  drop proc sp_tab2xml
go

create proc sp_tab2xml
/* Copyright (c) 2002-2003 Rob Verschoor/Sypron B.V. */
  @objname varchar(30) = NULL,
  @dtd_line varchar(255) = NULL,
  @collist varchar(255) = NULL,
  @clause varchar(255) = NULL
as
begin
  declare @cmd varchar(255)    -- in 12.5, this can be changed to varchar(16384)
  declare @err int, @rc int
  declare @total_length int, @ret int
  declare @id int, @tmpid int
  declare @type char(2)
  declare @tmptab varchar(80)
  declare @tmprow varchar(80)
  declare @idcolname varchar(30)
  declare @xml_hdr varchar(50)
  declare @objname_tag varchar(30)


  select @xml_hdr = "<?xml version=""1.0""?>"

  if @objname = NULL or @objname = '?'
  begin
     print " "
     print "Usage:"
     print "   sp_tab2xml your_table_name [, ""dtd_specification"" ] [, ""column-list"" ] [, ""where/order-by clause"" ]"
     print " "

     print "This will list all rows in the specified table as XML documents."
     print "The DTD specification is optional and will be copied blindly to"
     print "the XML result (no validation is performed)."
     print "The column-list and where/order-by clauses can be used to filter"
     print "and order the results. These are not validated prior to execution!"

     return -1
  end

  --
  -- some checks
  --
  if @objname like "%.%.%"
  begin
      print "Object must be in the current database."
      return -1
  end

  if @objname like "%.%"
  begin
      print "You cannot specify the owner of the object."
      print "Instead, use 'setuser' or 'set proxy' first."
      return -1
  end

   --
   -- find the object
   --
   if @objname like "#%"
   begin 
      if object_id(@objname) = NULL
      begin
	 print "Object '%1!' not found", @objname
	 return -1
      end
   end
   else
   begin
      select @id = id, @type = type 
      from dbo.sysobjects
      where name = @objname
	and  uid = user_id()

      select @err = @@error, @rc = @@rowcount

      if @rc <= 0
      begin
	 print "Object '%1!' not found", @objname
	 return -1
      end

      if @rc > 1
      begin
	 print "Object '%1!' exists %2! times -- must change this code !", @objname, @rc
	 return -1
      end

      -- determine the object type
      --
      if @type not in ("U", "S", "V")
      begin
	 print "'sp_tab2xml' can only be used on tables or views."
	 print "Object '%1!' has sysobjects.type = '%2!'", @objname, @type
	 return -1
      end
   end

  --
  -- Create table to hold column values in char format
  -- This table is needed as an interface between the 
  -- context of the stored procedure and the context of the 
  -- exec() statement used in sp_row2xml
  --
  create table #sql2xmlcol (char_value varchar(255) null)

  -- 
  -- The following tables will contain the result set.
  -- These tables are created through execute-immediate 
  -- because the table schema will be different each time;
  -- using exec() will avoid problems here (this procedure
  -- can not be implemented in pre-12.0 because exec()
  -- is missing there).
  --
  select @tmptab = "tempdb.guest.sql2xmltab_" + convert(varchar,@@spid)
  select @tmprow = "tempdb.guest.sql2xmlrow_" + convert(varchar,@@spid)

  --
  -- Create table to store result set (we don't want to loop
  -- over the original table, as this may be accessed by other
  -- processes)
  --

  if object_id(@tmptab) != NULL
  begin
     exec("drop table " + @tmptab)
  end
  select @cmd = "select " + isnull(@collist, "*") + " into " + @tmptab + " from " + @objname + " " + @clause

  -- uncomment the following line for debugging the final SQL command
  --print "@cmd=[%1!]",@cmd

  exec(@cmd)
  select @err = @@error
  if @err != 0
  begin
     print "Error (%1!) executing [%2!]", @err, @cmd
     goto cleanup
  end

  -- pick up object ID of work table
  select @tmpid = id
  from tempdb.dbo.sysobjects 
  where name = substring(@tmptab,14,70)
  
   if @tmpid = NULL
   begin
      print "Error in 'sp_tab2xml': can't get id of '%1!'", @tmptab
      goto cleanup
   end

   -- 
   -- check for identity columns
   --
   select @idcolname = name 
   from tempdb.dbo.syscolumns
   where id = @tmpid
     and status & 128 = 128

   select @err = @@error, @rc = @@rowcount
   if @rc > 0
   begin
      -- 
      -- Remove the identity property for the identity column
      --
      -- If you're curious about what goes on here, see my book 
      -- 'Tips, Tricks & Recipes for Sybase ASE' at http://www.sypron.nl/ttr  !
      --
      begin transaction
	 update tempdb.dbo.syscolumns
	 set status = status & ~128  -- clear this bit
	 where id = @tmpid
	   and name = @idcolname
	   and status & 128 = 128

	 if @@rowcount != 1 or @@error != 0
	 begin
	    print "Error updating tempdb.dbo.syscolumns to remove identity property for '%1!'", @tmptab
	    goto cleanup
	 end

	 update tempdb.dbo.sysobjects
	 set sysstat2 = sysstat2 & ~64  -- clear this bit
	 where id = @tmpid
	   and sysstat2 & 64 = 64

	 if @@rowcount != 1 or @@error != 0
	 begin
	    print "Error updating tempdb.dbo.objects to remove identity property for '%1!'", @tmptab
	    goto cleanup
	 end

      commit
  end

  --
  -- Create table to store a single row from result set
  --  
  if object_id(@tmprow) != NULL
  begin
    exec("drop table " + @tmprow)
  end
  exec("select * into " + @tmprow + " from " + @tmptab + " where 1=2")

  --								
  -- Place xml header tag into output stream                    
  --								
  print "%1!", @xml_hdr					        
  if @dtd_line != NULL
  print "%1!", @dtd_line
  -- Following row to avoid XML parsing errors due to multi toplevels
  print "<resultset>"						
  --
  -- Now process the result set row by row 
  -- 
  while 1 = 1
  begin
     exec("truncate table " + @tmprow)

     set rowcount 1
 
     exec("insert " + @tmprow + " select * from " + @tmptab)
     if @@rowcount = 0
     begin
        set rowcount 0
        break
     end

     exec("delete " + @tmptab)

     set rowcount 0

     -- for #temp tables, replace the '#' with "tmp_"
     select @objname_tag = @objname
     if @objname like "#%"
        select @objname_tag = "tmp_" + substring(@objname,2,99)

     -- print all columns as XML tags
     print "<%1!>", @objname_tag    
     exec @ret = sp_row2xml @tmprow 
     print "</%1!>", @objname_tag
     print ""

     if @ret != 0
     begin
	print "Error in sp_row2xml."
	goto cleanup
     end
  end

  -- Following row to avoid XML parsing errors due to multi toplevels. 
  print "</resultset>"					      

  -- 
  -- Clean up work tables
  --
cleanup:
  exec("drop table " + @tmptab)
  exec("drop table " + @tmprow)
  return 0
end
go
grant all on sp_tab2xml to public
go

sp_configure "allow updates", 0
go

print "Ready."
print ""
go

-- 
-- end of file
--