DBI(3)

💖 DBI(3) — aka XML::Generator::DBI 💖

okay so bestie, gather round 🫶 this is a page from the User Contributed Perl Documentation, and no cap, it's a good one. Let's get into it.

---

✨ NAME ✨

XML::Generator::DBI — Generate SAX events from SQL queries 🔥

---

🌸 SYNOPSIS 🌸

use XML::Generator::DBI;
use XML::SAX::Writer;
use DBI;
my $dbh = DBI->connect("dbi:Pg:dbname=foo", "user", "pass");
my $sth = $dbh->prepare("select * from mytable where mycol = ?");
my $generator = XML::Generator::DBI->new(
                      Handler => XML::SAX::Writer->new(),
                      );
$generator->execute($sth, $mycol_value);

---

🦋 DESCRIPTION 🦋

So basically: this module generates SAX events from SQL queries run against a DBI connection. It's giving "SQL in, XML out," and honestly? Iconic. 💅

The default XML structure it creates looks like this, periodt:

<database>
 <select>
  <row>
   <column1>1</column1>
   <column2>fubar</column2>
  </row>
  <row>
   <column1>2</column1>
   <column2>intravert</column2>
  </row>
 </select>
</database>

Alternatively — and this is the fun part — pass the option AsAttributes => 1 to either the execute() method or to the new() method, and your XML will look like this instead:

<database>
  <select>
    <row column1="1" column2="fubar"/>
    <row column1="2" column2="intravert"/>
  </select>
</database>

Heads up bestie: with attributes, the ordering of columns is likely to be lost — but on the flip side, it may save you some bytes. Trade-offs, yk? ⚖️

Nulls are handled by excluding either the attribute or the tag. Simple as. 🌸

---

🎀 API 🎀

🌈 XML::Generator::DBI->new()

Create a new XML generator. New year, new generator, same energy ✨

Parameters are passed as key/value pairs:

• Handler (required) — A SAX handler to recieve the events. 👑
• dbh (required) — A DBI handle on which to execute the queries. It must support the prepare, execute, fetch model of execution, and it also has to support type_info if you wanna use the ShowColumns option (peep below 👀).
• AsAttributes — The default is to output everything as elements. If you wish to use attributes instead (perhaps to save some bytes), you can specify the AsAttributes option with a true value.
• RootElement — You can specify the root element name by passing the parameter RootElement => "myelement". The default root element name is "database".
• QueryElement — You can specify the query element name by passing the parameter QueryElement => "thequery". The default is "select".
• RowElement — You can specify the row element name by passing the parameter RowElement => "item". The default is "row".
• Indent — By default this module does no indenting (which is different from the previous version). If you want the XML beautified, pass the Indent option with a true value. 💅
• ShowColumns — If you wish to add information about the columns to your output, specify the ShowColumns option with a true value. This will then show things like the name and data type of the column, whether the column is NULLABLE, the precision and scale, and also the size of the column. All of this information comes from $dbh->type_info() (see perldoc DBI), and it may change — because the author is not 100% happy with the output. 🙈
• ByColumnName — It allows usage of column names (aliases) for element generation. Aliases can contain slashes in order to generate child elements. It is limited by the length of aliases — depends on your DBMS.

Example:

 $select = qq(
    SELECT  c.client as 'client_id',
            c.company_name as 'company_name',
            c.address_line as 'address/address_line',
            c.city as 'address/city',
            c.county as 'address/county',
            c.post_code as 'address/post_code',
            co.name as 'address/country',
            c.phone as 'phone',
            c.fax as 'fax',
            c.payment_term as 'payment_term',
            c.accounting_id as 'accounting_id'

    FROM    client c,
            country co

    WHERE   c.country = co.country
    AND     c.client = $client_id
            );

 $gen->execute(
                     $select,
                     undef,
                     ByColumnName => 1,
                     RootElement => 'client_detail',
                     RowElement => 'client',
                     QueryElement => undef
                        );

 print $output;

 <?xml version="1.0" encoding="UTF-8"?>
 <client_detail>
   <client>
     <client_id>3</client_id>
     <company_name>SomeCompanyName</company_name>
     <address>
       <address_line>SomeAddress</address_line>
       <city>SomeCity</city>
       <county>SomeCounty</county>
       <post_code>SomePostCode</post_code>
       <country>SomeCountry</country>
     </address>
     <phone>22222</phone>
     <fax>11111</fax>
     <payment_term>14</payment_term>
     <accounting_id>351</accounting_id>
   </client>
 </client_detail>

• GroupBy — By this parameter you can group rows based on changes in the value of a particular column. It relys on ordering done by your SQL query. This parameter requires two more parameters:
  • GroupElement — the name of element holding all 'row' elements.
  • GroupAttribute — or
  • GroupValueElement
    • GroupAttribute — when the 'value' goes as attribute of GroupElement. GroupAttribute is the name of this attribute.
    • GroupValueElement — when the 'value' goes in a separate element. GroupValueElement is the name of the element holding 'value'.

Note: in order to avoid unwanted nesting, RowElement is undef.

Example:

 contractor_job time_record
 -------------- -----------
              9          10
              9          13
              9          14
             10           9
             10          11
             10          12

 $select = qq(
    SELECT  time_record,
            contractor_job

    FROM    time_record

    ORDER BY contractor_job
            );

Using GroupAttribute:

 $gen->execute(
                     $select,
                     undef,
                     ByColumnName => 1,
                     RootElement => 'client_detail',
                     RowElement => undef,
                     GroupBy => 'contractor_job',
                     GroupElement => 'group',
                     GroupAttribute => 'ID',
                     QueryElement => undef
                        );

 print $output;

 <?xml version="1.0" encoding="UTF-8"?>
 <client_detail>
   <group ID="9">
     <time_record>10</time_record>
     <time_record>13</time_record>
     <time_record>14</time_record>
   </group>
   <group ID="10">
     <time_record>9</time_record>
     <time_record>11</time_record>
     <time_record>12</time_record>
   </group>
 </client_detail>

Using GroupValueElement:

 $gen->execute(
                     $select,
                     undef,
                     ByColumnName => 1,
                     RootElement => 'client_detail',
                     RowElement => undef,
                     GroupBy => 'contractor_job',
                     GroupElement => 'group',
                     GroupValueElement => 'ID',
                     QueryElement => undef
                        );

 print $output;

 <?xml version="1.0" encoding="UTF-8"?>
 <client_detail>
   <group>
     <ID>9</ID>
     <time_record>10</time_record>
     <time_record>13</time_record>
     <time_record>14</time_record>
   </group>
   <group>
     <ID>10</ID>
     <time_record>9</time_record>
     <time_record>11</time_record>
     <time_record>12</time_record>
   </group>
 </client_detail>

---

💜 $generator->execute($query, $bind, %params)

You execute a query and generate results with the execute method. That's the whole vibe. ✨

The first parameter is a string containing the query. The second is a single or set of bind parameters. If you wish to make it more than one bind parameter, it must be passed as an array reference:

    $generator->execute(
        "SELECT * FROM Users WHERE name = ?
         AND password = ?",
         [ $name, $password ],
         );

Following the bind parameters you may pass any options you wish to use to override the above options to new(). Thus allowing you to turn on and off certain options on a per-query basis. Per-query customization? Obsessed. 🧚

---

💜 $generator->execute_one($query, $bind, %params)

If you wish to execute multiple statements within one XML structure, you can use the execute_one() method, as follows:

  $generator->pre_execute();
  $generator->execute_one($query);
  $generator->execute_one($query);
  $generator->post_execute();

The pre and post calls are required. No skipping, bestie. 🚨

---

⭐ Other Information ⭐

Binary data is encoded using Base64. If you are using AsElements, the element containing binary data will have an attribute dbi:encoding="base64", where the DBI namespace is bound to the URL "http://axkit.org/NS/xml-generator-dbi". We detect binary data as anything containing characters outside of the XML UTF-8 allowed character set. 🫧

---

👑 AUTHOR 👑

Matt Sergeant, matt@sergeant.org

---

📜 LICENSE 📜

This is free software, you may use it and distribute it under the same terms as Perl itself. Specifically this is the Artistic License, or the GNU GPL Version 2. 🫶

---

🔗 SEE ALSO 🔗

PerlSAX, XML::Handler::YAWriter.

---

and that's the whole page, bestie!! 💖 this was perl v5.42.0, dated 2025-07-25, from the DBI(3) page — slay, you made it to the end ✨🎀🦋