Is there any Ruby framework to return database data to XML?

Question

Instead of writing the SQL query, is there any lazy way to get the data from database in XML format.

Answer 1

Well... Rails is a framework for Ruby that can accomplish what you are looking for, give this a try in a controller:

respond_to do |format|
  if @obj.save!
    format.xml {}
  end
end

Activerecord can do lots of other magic as well.

After installing rails you must install the Gem for SQL Server:

sudo gem install activerecord-sqlserver-adapter --source=http://gems.rubyonrails.org

Then add a few things to your database.yml to get it to work with SQL Server:

development:
  ...
  adapter: sqlserver
  mode: odbc
  dsn: sqlserverapp 
  ...

Rails has some cool methods for manipulating and reading XML built in, but I suggest the gem Nokogiri.

Answer 2

I'm afraid that you're mixing apples and oranges here. You want to replace SQL with XML? While SQL is used to tell the database which data you want returned, XML is usually used as a format to wrap that resulting data into. So, you can't simply replace one with another because they do not share common purpose.

OTOH, if you want the easiest way in Ruby to get data from the database into XML, you might try using Rails, but if you don't need whole artillery coming with Rails and want to avoid whole MVC goodness, you might try combining some lightweight ORM such as Sequel for retrieving data and Builder for packing the results to XML. Can't get much simpler than that:

require 'sequel'
require 'builder'

DB = Sequel.connect('postgres://test:test@localhost/jablan')
# SELECT * FROM test;
res = DB[:test]

# creating XML outputter
builder = Builder::XmlMarkup.new(:indent => 2)

# creating root XML tag 'dataset'
xml = builder.dataset do |bd|
  res.each do |row|
    # create 'row' tag for each row from the returned dataset
    bd.row do |br|
      res.columns.each do |col|
        # create tag for each column in a row, named after DB column name,
        # get the actual value and fill the tag with it
        br.send(col, row[col])
      end
    end
  end
end

puts xml

gives:

jablan@jablan-hp:~/dev$ ruby sequel_xml.rb 
<dataset>
  <row>
    <id>1</id>
    <name>foo</name>
    <time>2009-10-01 00:00:00 +0200</time>
  </row>
  <row>
    <id>2</id>
    <name>bar</name>
    <time>2009-11-01 00:00:00 +0100</time>
  </row>
  <row>
    <id>3</id>
    <name>baz</name>
    <time>2009-12-01 00:00:00 +0100</time>
  </row>
</dataset>

Answer 3

Hi there, there a few frameworks that allow to access the database without using any sql. ActiveRecord, Datamapper .. and i guess there are some more.

I use ActiveRecord, because it is standard in Rails, and fits my needs.

You can use a script as the following:

require 'active_record'

## configuration: all constants should be configured here
#
DEFAULT_ENVIRONMENT = 'development'

database_type = ARGV[0]

if database_type.nil?
  database_type = DEFAULT_ENVIRONMENT
end

database_yaml = IO.read('../../config/database.yml')

## define model

class Group < ActiveRecord::Base
end



## the script!

class Script 

  def self.run(env)
    groups = Group.find(:all, :limit => 10)
    #groups = Group.find(:first)
    xml_string = Array.new
    xml_string << '<?xml version="1.0" encoding="UTF-8"?>'
    xml_string << "<groups>"
    groups.each do |group|
      xml_string << group.to_xml(:skip_instruct => true)
    end
    xml_string << "</groups>"

    # write to file
    f = File.open("groups.xml", "w") { |f| f.write(xml_string.join("\n")) }    

  end
end

databases = YAML::load(database_yaml)
ActiveRecord::Base.establish_connection(databases[database_type])
Script.run(database_type)

So, what i do is first declare a class Group, which will automatically to a table called "groups". Nothing else needed. Iterate over all groups, and convert to_xml (without the doc-type), and save to file.

Pretty straightforward.

You do have to specify a database.yml file somewhere to be able to connect to the database.

Is this clear?

Answer 4

Regarding the lazy approach, this works in sql server 2005, 2008

SELECT * FROM SomeTable
FOR XML AUTO, ELEMENTS