#!/usr/bin/perl
use strict;
use warnings;
use XML::Twig;
use Text::CSV;
use Data::Dump 'pp';

my @header   = ();
my @csv_rows = ();
my $order_by = 'u_ci_id';

my %seen = ();
my %arx_data = ();

# parse xml in chunks
my $twig = 'XML::Twig'->new( 
  twig_handlers =>  { result => \&record }
);
my $input_xml = get_xml(); # change as required
$twig->parse($input_xml);

# data dump
pp \%arx_data;
#pp \@csv_rows;
#pp \@header;

# sort and dump csv
my @sorted = sort { $b->[0] cmp $a->[0] } @csv_rows;
unshift @sorted,[@header]; # add header

my $outfile = 'xmldump.csv';
my $csv = Text::CSV->new ( { binary => 1 } )  
    or die "Cannot use CSV: ".Text::CSV->error_diag ();
$csv->eol("\n"); 

if ($outfile){        
  open my $fh, ">:encoding(utf8)", $outfile or die "$outfile: $!";
  for (@sorted){
    shift @$_; # remove sort field
    $csv->print ($fh, $_);
  }
  close $fh or die "new.csv: $!"; 
} else {
  for (@sorted){
    shift @$_;
    $csv->print (\*STDOUT, $_);
  }
}  

# parse 1 record
sub record {
  my ($e,$t) = @_;
  
  # csv records
  unless (@header){
    my @info_tags = $t->children;
    @header = map{ $_->name } @info_tags;
    unshift @header,$order_by; # add sort field
  }
  
  # extract data in same order as header row
  my @data = map{ $t->field($_) } @header;
  push @csv_rows,clean(@data); #returns array ref
 
  # build hash structure
  my $app   = $t->field('u_application_id');
  my $u_ci  = $t->field('u_ci_id');
  my $class = $t->field('sys_class_name');
  my $name  = $t->field('name');
  my $ip    = $t->field('ip_address');
  
  if ( $class eq 'cmdb_ci_appl'){
    $arx_data{$u_ci}{'name'} = $name;  
  } 
  
  if ( $class eq 'cmdb_ci_svr' ){
    next if $seen{$app}{$ip}++;
    push @{$arx_data{$app}{'members'}},$ip;
  }
  
}

sub clean {
  my @f = @_;
  for (@f){
    s/\r|\n//g;  # Cleanup Carraige Returns
    s/, / /g;    # Cleanup Comma Space
    s/,/ /g;     # Cleanup Comma
    s/"//g;      # Cleanup Parentheses
    s/^\s+|\s+$//g;    # Trim spaces
    s/([^[:ascii:]]+)/unidecode($1)/ge;
  }
  return \@f;
}

##</code><code>##

# get xml
sub get_xml {
  return '<?xml version="1.0" encoding="UTF-8"?>
<response>
 <result>
  <fqdn>fqdnB</fqdn>
  <ip_address>ip1</ip_address>
  <u_display_name>display1</u_display_name>
  <name>name1, with comma "and" quote</name>
  <mac_address>mac5</mac_address>
  <u_ci_id>CI10334984</u_ci_id>
  <sys_class_name>cmdb_ci_appl</sys_class_name>
  <u_application_id></u_application_id>
 </result>
 <result>
  <fqdn>fqdnA</fqdn>
  <ip_address>ip2</ip_address>
  <u_display_name>display2</u_display_name>
  <name>name2</name>
  <mac_address>mac, 4</mac_address>
  <u_ci_id>CI56789</u_ci_id>
  <sys_class_name>cmdb_ci_svr</sys_class_name>
  <u_application_id>CI10334984</u_application_id>
 </result> 
 <result>
  <fqdn>fqdnC</fqdn>
  <ip_address>ip3</ip_address>
  <u_display_name>display3</u_display_name>
  <name>name2</name>
  <mac_address>mac5</mac_address>
  <u_ci_id>CI56789</u_ci_id>
  <sys_class_name>cmdb_ci_svr</sys_class_name>
  <u_application_id>CI10334984</u_application_id>
</result>
</response>';
}