Re^3: XML File Creation in Perl

"I am thinking of populating data directly into xml_data from each DB1/DB2/DIFF loop."

I suspect that's probably the best option. You'll get some duplicate assignments but I don't imagine that will be a problem. Getting around that would likely involve setting and testing flags that indicate whether a particular assignment has occurred. Benchmark if you feel it's important.

I see you've added some realistic data. Below, you'll see I've added some more to simulate these new scenarios you've described ("... only in DB1 or DB2 ..."). Where you have data like "A B C", I've plugged the spaces with underscores (i.e. "A_B_C"); this was just so I didn't have to rework how I was handling the data with Inline::Files and split: I'm assuming you're already getting your resultsets as array data. One thing I noticed was that I'm getting <KEY>A_B_C</KEY> while your output is showing <KEY>'A B C'</KEY>. The start and end tags act as delimiters so additional quotes aren't generally necessary in XML; they could potentially cause issues like the data being converted to 'A B C': my instinct would be to remove the quotes before populating the XML — you may have a valid reason for leaving them in.

Here's the updated code (includes handling the <null> field):

#!/usr/bin/env perl

use strict;
use warnings;

use Inline::Files;
use XML::Simple qw{:strict};

my %xml_hash = (Data => {});
my $xml_data = $xml_hash{Data};
my (%db1, %db2);

while (<DB1>) {
    my ($root, $table, $key) = split;
    push @{$db1{$root}{$table}}, $key;
    $xml_data->{$root}{$table}{NEW1}{KEY} = [$key];
}

while (<DB2>) {
    my ($root, $table, $key) = split;
    push @{$db2{$root}{$table}}, $key;
    $xml_data->{$root}{$table}{NEW2}{KEY} = [$key];
}

while (<DIFF>) {
    my ($root, $table, $key, $col, $old, $new)
        = map { $_ eq '<null>' ? '' : $_ } split;
    $xml_data->{$root}{$table}{NEW1}{KEY} = $db1{$root}{$table}
        if exists $db1{$root}{$table};
    $xml_data->{$root}{$table}{NEW2}{KEY} = $db2{$root}{$table}
        if exists $db2{$root}{$table};
    $xml_data->{$root}{$table}{MODIFIED}{KEY}{$key}{$col}{oldvalue} = 
+[$old];
    $xml_data->{$root}{$table}{MODIFIED}{KEY}{$key}{$col}{newvalue} = 
+[$new];
}

print XMLout(\%xml_hash, KeepRoot => 1, KeyAttr => {KEY => 'name'});

__DIFF__
EMPLOYEE      EMPLOYEE      XYZ      TITLE    <null>      Mr    
EMPLOYEE      EMPDETAILS    DEF      CITY    California  New York    
CUSTOMER      CUSTOMER      ABC      CAPTION  Regular    Premium   
__DB1__
EMPLOYEE    EMPLOYEE    NEW_EMPLOYEE_1
EMPLOYEE    EMPLOYEE    NEW_EMPLOYEE_9
EMPLOYEE    EMPDETAILS  NEW_EMPLOYEE1-DETAILS
EMPLOYEE    EMPDETAILS  NEW_EMPLOYEE9-DETAILS
EMPLOYEE    EMPDETAILS  NEW_EMPLOYEE16-DETAILS
IN_DB1_ONLY IN_DB1_ONLY IN_DB1_ONLY
IN_DB1+DB2  IN_DB1+DB2  IN_DB1+DB2
__DB2__
EMPLOYEE    EMPLOYEE    NEW_EMPLOYEE_6
EMPLOYEE    EMPDETAILS  NEW_EMPLOYEE6-DETAILS
CUSTOMER    CUSTOMER    NEW_CUSTOMER
IN_DB2_ONLY IN_DB2_ONLY IN_DB2_ONLY
IN_DB1+DB2  IN_DB1+DB2  IN_DB1+DB2
[download]

Output:

$ pm_xml_db_diff2.pl
<Data>
  <CUSTOMER>
    <CUSTOMER>
      <MODIFIED>
        <KEY name="ABC">
          <CAPTION>
            <newvalue>Premium</newvalue>
            <oldvalue>Regular</oldvalue>
          </CAPTION>
        </KEY>
      </MODIFIED>
      <NEW2>
        <KEY>NEW_CUSTOMER</KEY>
      </NEW2>
    </CUSTOMER>
  </CUSTOMER>
  <EMPLOYEE>
    <EMPDETAILS>
      <MODIFIED>
        <KEY name="DEF">
          <CITY>
            <newvalue>New</newvalue>
            <oldvalue>California</oldvalue>
          </CITY>
        </KEY>
      </MODIFIED>
      <NEW1>
        <KEY>NEW_EMPLOYEE1-DETAILS</KEY>
        <KEY>NEW_EMPLOYEE9-DETAILS</KEY>
        <KEY>NEW_EMPLOYEE16-DETAILS</KEY>
      </NEW1>
      <NEW2>
        <KEY>NEW_EMPLOYEE6-DETAILS</KEY>
      </NEW2>
    </EMPDETAILS>
    <EMPLOYEE>
      <MODIFIED>
        <KEY name="XYZ">
          <TITLE>
            <newvalue>Mr</newvalue>
            <oldvalue></oldvalue>
          </TITLE>
        </KEY>
      </MODIFIED>
      <NEW1>
        <KEY>NEW_EMPLOYEE_1</KEY>
        <KEY>NEW_EMPLOYEE_9</KEY>
      </NEW1>
      <NEW2>
        <KEY>NEW_EMPLOYEE_6</KEY>
      </NEW2>
    </EMPLOYEE>
  </EMPLOYEE>
  <IN_DB1+DB2>
    <IN_DB1+DB2>
      <NEW1>
        <KEY>IN_DB1+DB2</KEY>
      </NEW1>
      <NEW2>
        <KEY>IN_DB1+DB2</KEY>
      </NEW2>
    </IN_DB1+DB2>
  </IN_DB1+DB2>
  <IN_DB1_ONLY>
    <IN_DB1_ONLY>
      <NEW1>
        <KEY>IN_DB1_ONLY</KEY>
      </NEW1>
    </IN_DB1_ONLY>
  </IN_DB1_ONLY>
  <IN_DB2_ONLY>
    <IN_DB2_ONLY>
      <NEW2>
        <KEY>IN_DB2_ONLY</KEY>
      </NEW2>
    </IN_DB2_ONLY>
  </IN_DB2_ONLY>
</Data>
[download]

-- Ken

Comment on Re^3: XML File Creation in Perl Select or Download Code

Replies are listed 'Best First'.
Re^4: XML File Creation in Perl by documents9900 (Initiate) on Apr 17, 2013 at 05:26 UTC
I did some correction in the code `while (<DB1>) { chomp; my ($root, $table, $header,$key) = split /\t/; $xml_data->{$root}{$table}{NEW1}{$key} = [$key]; } while (<DB2>) { chomp; my ($root, $table, $header,$key) = split /\t/; $xml_data->{$root}{$table}{NEW2}{$key} = [$key]; } while (<DIFF>) { chomp; my ($root, $table, $key, $col, $old, $new) = split /\t/; $xml_data->{$root}{$table}{MODIFIED}{KEY}{$key}{$col}{oldvalue} = [$ol +d]; $xml_data->{$root}{$table}{MODIFIED}{KEY}{$key}{$col}{newvalue} = [$ne +w]; }` [download] Changes which i did 1) Removed the db1 and db2 code as it is not required. 2. Old code `$xml_data->{$root}{$table}{NEW2} = [$key];` [download] New code `$xml_data->{$root}{$table}{NEW2}{$key} = [$key];` [download] This was required as if there are multiple rows were there for a table which were overwritten with last value as the root, table combination already exists. So from the result set it displayed only the last row in the xml. Now the last problem is `$xml_data->{$root}{$table}{NEW2}{$key} = [$key];` [download] the data is displayed as (Suppose Key value is ABC) `<ABC>ABC</ABC>` [download] but it should be `<KEY>ABC</KEY>` [download] This is coming because i added the key value for key only meaning code is working as expected, but i want KEY to be displayed instead of value	[reply] [d/l] [select]
Re^5: XML File Creation in Perl by kcott (Archbishop) on Apr 17, 2013 at 06:34 UTC
Well, if `$xml_data->{$root}{$table}{NEW2}{$key} = [$key];` [download] is producing `<ABC>ABC</ABC>` [download] then I'd expect `$xml_data->{$root}{$table}{NEW2}{KEY} = [$key];` [download] to produce `<KEY>ABC</KEY>` [download] I recommend you spend some time playing around with the features of XML::Simple. Look at how the various OPTIONS interact with each other; see what differences running in STRICT MODE makes; and so on. -- Ken	[reply] [d/l] [select]
Re^6: XML File Creation in Perl by documents9900 (Initiate) on Apr 17, 2013 at 07:19 UTC
Thanks Ken. I will look in detailed documentation on this. Also, I agree with your point on and the same i also mentioned that it is expected. My point is If i have multiple combinations of root, table, NEW2 isn't that every time the loop runs it overwrites the old value because we are storing $key for `$xml_data->{$root}{$table}{NEW2}{KEY}` So to avoid that, i used `$xml_data->{$root}{$table}{NEW2}{$key} = [$key];` but it resulted into `<ABC>ABC</ABC> <DEF>DEF</DEF>` [download] , without this it displays only the last row with that combination in the file `<KEY>DEF</KEY>`	[reply] [d/l] [select]
Re^7: XML File Creation in Perl by kcott (Archbishop) on Apr 17, 2013 at 07:25 UTC