1DBI(3) User Contributed Perl Documentation DBI(3)
2
6 XML::Generator::DBI - Generate SAX events from SQL queries
7
9 use XML::Generator::DBI;
10 use XML::SAX::Writer;
11 use DBI;
12 my $dbh = DBI->connect("dbi:Pg:dbname=foo", "user", "pass");
13 my $sth = $dbh->prepare("select * from mytable where mycol = ?");
14 my $generator = XML::Generator::DBI->new(
15 Handler => XML::SAX::Writer->new(),
16 );
17 $generator->execute($sth, $mycol_value);
18
20 This module generates SAX events from SQL queries against a DBI
21 connection.
22 The default XML structure created is as follows:
24 <database>
26 <select>
27 <row>
28 <column1>1</column1>
29 <column2>fubar</column2>
30 </row>
31 <row>
32 <column1>2</column1>
33 <column2>intravert</column2>
34 </row>
35 </select>
36 </database>
37 Alternatively, pass the option AsAttributes => 1 to either the
39 execute() method, or to the new() method, and your XML will look like:
40 <database>
42 <select>
43 <row column1="1" column2="fubar"/>
44 <row column1="2" column2="intravert"/>
45 </select>
46 </database>
47 Note that with attributes, ordering of columns is likely to be lost,
49 but on the flip side, it may save you some bytes.
50 Nulls are handled by excluding either the attribute or the tag.
52
54 XML::Generator::DBI->new()
55 Create a new XML generator.
56 Parameters are passed as key/value pairs:
58 Handler (required)
60 A SAX handler to recieve the events.
61 dbh (required)
63 A DBI handle on which to execute the queries. Must support the
64 prepare, execute, fetch model of execution, and also support
65 type_info if you wish to use the ShowColumns option (see below).
66 AsAttributes
68 The default is to output everything as elements. If you wish to use
69 attributes instead (perhaps to save some bytes), you can specify
70 the AsAttributes option with a true value.
71 RootElement
73 You can specify the root element name by passing the parameter
74 RootElement => "myelement". The default root element name is
75 "database".
76 QueryElement
78 You can specify the query element name by passing the parameter
79 QueryElement => "thequery". The default is "select".
80 RowElement
82 You can specify the row element name by passing the parameter
83 RowElement => "item". The default is "row".
84 Indent
86 By default this module does no indenting (which is different from
87 the previous version). If you want the XML beautified, pass the
88 Indent option with a true value.
89 ShowColumns
91 If you wish to add information about the columns to your output,
92 specify the ShowColumns option with a true value. This will then
93 show things like the name and data type of the column, whether the
94 column is NULLABLE, the precision and scale, and also the size of
95 the column. All of this information is from $dbh->type_info() (see
96 perldoc DBI), and may change as I'm not 100% happy with the output.
97 ByColumnName
99 It allows usage of column names (aliases) for element generation.
100 Aliases can contain slashes in order to generate child elements.
101 It is limited by the length of aliases - depends on your DBMS
102 Example:
104 $select = qq(
106 SELECT c.client as 'client_id',
107 c.company_name as 'company_name',
108 c.address_line as 'address/address_line',
109 c.city as 'address/city',
110 c.county as 'address/county',
111 c.post_code as 'address/post_code',
112 co.name as 'address/country',
113 c.phone as 'phone',
114 c.fax as 'fax',
115 c.payment_term as 'payment_term',
116 c.accounting_id as 'accounting_id'
117 FROM client c,
119 country co
120 WHERE c.country = co.country
122 AND c.client = $client_id
123 );
124 $gen->execute(
126 $select,
127 undef,
128 ByColumnName => 1,
129 RootElement => 'client_detail',
130 RowElement => 'client',
131 QueryElement => undef
132 );
133 print $output;
135 <?xml version="1.0" encoding="UTF-8"?>
137 <client_detail>
138 <client>
139 <client_id>3</client_id>
140 <company_name>SomeCompanyName</company_name>
141 <address>
142 <address_line>SomeAddress</address_line>
143 <city>SomeCity</city>
144 <county>SomeCounty</county>
145 <post_code>SomePostCode</post_code>
146 <country>SomeCountry</country>
147 </address>
148 <phone>22222</phone>
149 <fax>11111</fax>
150 <payment_term>14</payment_term>
151 <accounting_id>351</accounting_id>
152 </client>
153 </client_detail>
154 GroupBy
156 By this parameter you can group rows based on changes in the value
157 of a particular column. It relys on ordering done by your SQL
158 query. This parameter requires two more parameters:
159 GroupElement - the name of element holding all 'row' elements.
161 GroupAttribute
162 or
163 GroupValueElement
165 GroupAttribute - when the 'value' goes as attribute of
166 GroupElement. GroupAttribute is the name of this attribute.
167 GroupValueElement - when the 'value' goes in a separate
169 element. GroupValueElement is the name of the element holding
170 'value'.
171 Note that in order to avoid unwanted nesting RowElement is undef.
173 Example:
175 contractor_job time_record
177 -------------- -----------
178 9 10
179 9 13
180 9 14
181 10 9
182 10 11
183 10 12
184 $select = qq(
186 SELECT time_record,
187 contractor_job
188 FROM time_record
190 ORDER BY contractor_job
192 );
193 Using GroupAttribute:
195 $gen->execute(
197 $select,
198 undef,
199 ByColumnName => 1,
200 RootElement => 'client_detail',
201 RowElement => undef,
202 GroupBy => 'contractor_job',
203 GroupElement => 'group',
204 GroupAttribute => 'ID',
205 QueryElement => undef
206 );
207 print $output;
209 <?xml version="1.0" encoding="UTF-8"?>
211 <client_detail>
212 <group ID="9">
213 <time_record>10</time_record>
214 <time_record>13</time_record>
215 <time_record>14</time_record>
216 </group>
217 <group ID="10">
218 <time_record>9</time_record>
219 <time_record>11</time_record>
220 <time_record>12</time_record>
221 </group>
222 </client_detail>
223 Using GroupValueElement:
225 $gen->execute(
227 $select,
228 undef,
229 ByColumnName => 1,
230 RootElement => 'client_detail',
231 RowElement => undef,
232 GroupBy => 'contractor_job',
233 GroupElement => 'group',
234 GroupValueElement => 'ID',
235 QueryElement => undef
236 );
237 print $output;
239 <?xml version="1.0" encoding="UTF-8"?>
241 <client_detail>
242 <group>
243 <ID>9</ID>
244 <time_record>10</time_record>
245 <time_record>13</time_record>
246 <time_record>14</time_record>
247 </group>
248 <group>
249 <ID>10</ID>
250 <time_record>9</time_record>
251 <time_record>11</time_record>
252 <time_record>12</time_record>
253 </group>
254 </client_detail>
255 $generator->execute($query, $bind, %params)
257 You execute a query and generate results with the execute method.
258 The first parameter is a string containing the query. The second is a
260 single or set of bind parameters. If you wish to make it more than one
261 bind parameter, it must be passed as an array reference:
262 $generator->execute(
264 "SELECT * FROM Users WHERE name = ?
265 AND password = ?",
266 [ $name, $password ],
267 );
268 Following the bind parameters you may pass any options you wish to use
270 to override the above options to new(). Thus allowing you to turn on
271 and off certain options on a per-query basis.
272 $generator->execute_one($query, $bind, %params)
274 If you wish to execute multiple statements within one XML structure,
275 you can use the "execute_one()" method, as follows:
276 $generator->pre_execute();
278 $generator->execute_one($query);
279 $generator->execute_one($query);
280 $generator->post_execute();
281 The pre and post calls are required.
283
285 Binary data is encoded using Base64. If you are using AsElements, the
286 element containing binary data will have an attribute
287 dbi:encoding="base64", where the DBI namespace is bound to the URL
288 "http://axkit.org/NS/xml-generator-dbi". We detect binary data as
289 anything containing characters outside of the XML UTF-8 allowed
290 character set.
291
293 Matt Sergeant, matt@sergeant.org
294
296 This is free software, you may use it and distribute it under the same
297 terms as Perl itself. Specifically this is the Artistic License, or the
298 GNU GPL Version 2.
299
301 PerlSAX, XML::Handler::YAWriter.
302perl v5.32.0 2020-07-28 DBI(3)