Hi experts,

I need to read a few columns from a database table and write out the result in a specified xml format.

For e.g say the table (called SKU) has the following data

#ITEM, LOC--->column headers

011-5000, DLSDC

011-5100, FRMNT

I need this to be written out in the following xml-format in a file say sku.xml. Since i did not know how the xml-formatting would come out in this message, i enclosed the desired/actual xml-format within code tags (though this is not actually code)

Desired_xml_format

=================

  <metadata>
    <field name="ITEM" alias="a1"/>
    <field name="LOC" alias="a2"/>
  </metadata>
  <data>
    <rec>
      <a1>011-5000</a1>
      <a2>DLSDC</a2>
    </rec>
    <rec>
      <a1>011-5100</a1>
      <a2>FRMNT</a2>
    </rec>
  </data>

[download]

I tried the following code to write this out in xml format. (since i am a newbie, i tried to search the perl monks/google repository and did a cut/paste of an example using modules like DBI; XML::Generator::DBI; XML::Handler::YAWriter; as follows. But this obviously gives me an output using the standard xml format (which is different from the desired output)

use strict;
use DBI;
use XML::Generator::DBI;
use XML::Handler::YAWriter;
my $dbh = DBI->connect ("dbi:Oracle:host=myhostname;sid=mysid;port=152
+1",
            "userid", "pwd",
      { RaiseError => 0, AutoCommit => 0,ora_envhp=> 0}); 

my $out = XML::Handler::YAWriter->new (AsFile => "sku.xml");
   my $gen = XML::Generator::DBI->new (
                                   Handler => $out,
                                   dbh => $dbh,
                                   Indent  => 1
                               );
   $gen->execute ("SELECT ITEM,LOC FROM SKU");
   $dbh->disconnect ();

[download]

The actual output i get using the above script is as follows

Actual_xml_format

=================

<?xml version="1.0" encoding="UTF-8"?><database>
 <select>
  <row>
   <ITEM>011-5000</ITEM>
   <LOC>DLSDC</LOC>
  </row>
  <row>
   <ITEM>011-5100</ITEM>
   <LOC>FRMNT</LOC>
  </row>
  <row>
 </select>
</database>

[download]

Can you point out what i need to do to get the output in the desired xml format. Are there some easy ways to get this done? (e.g. some other modules i need to use which give output using an aliased xml output similar to the desired output)?

Thank you in advance

In reply to perl xml format question by lukka1

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.