XML to CSV with nested and definite elements in Perl

Question

I need to convert my xml file to csv Format. But I in csv file I need not all Information fron the XML, just 2 elements (IP Address and id from device).

 #!/usr/bin/perl
 use strict;
 use warnings;
 use Data::Dumper;
 use XML::Simple;

  #Elements, that I want see in my csv
  my @Fields = qw{id, ipAddress};

  open(my $out, '>', 'output.csv') or die "Output: $!\n";
  print $out join(',', @Fields) . "\n";

  my $xml = XMLin('input.xml', ForceArray => ['entity']);
  foreach my $entity (@{$xml->{entity}}) {
  no warnings;
  print $out join(',', map{$_->{content}} @{$entity}{@Fields}) . "\n";

Input.xml

      <?xml version="1.0" ?>
          <queryResponse last="41" first="0" count="42" type="Devices" responseType="listEntityInstances" requestUrl="https://hostname/webacs/api/v1/data/Devices?.full=true" rootUrl="https://hostname/webacs/api/v1/data">
             <entity dtoType="devicesDTO" type="Devices" url="https://hostname/webacs/api/v1/data/Devices/20">
               <devicesDTO displayName="20" id="20">
               <clearedAlarms>1</clearedAlarms>
               <collectionDetail>&lt;status&gt;&lt;general code="SUCCESS"/&gt;&lt;/status&gt;</collectionDetail>
               <collectionTime>2017-03-30T09:47:07.606+02:00</collectionTime>
               <creationTime>2016-02-29T17:32:13.116+01:00</creationTime>
               <ipAddress>1.1.1.1</ipAddress>
               <location> </location>
               <majorAlarms>0</majorAlarms>
                <softwareType>IOS</softwareType>
                <softwareVersion>12.2(55)SE9</softwareVersion>
                <warningAlarms>0</warningAlarms>
             </devicesDTO>
          </entity>
          <entity dtoType="devicesDTO" type="Devices" url="https://hostname/webacs/api/v1/data/Devices/21">
               <devicesDTO displayName="21" id="21">
               <clearedAlarms>1</clearedAlarms>
               <collectionDetail>&lt;status&gt;&lt;general code="SUCCESS"/&gt;&lt;/status&gt;</collectionDetail>
               <collectionTime>2017-03-30T09:47:07.606+02:00</collectionTime>
               <creationTime>2016-02-29T17:32:13.116+01:00</creationTime>
               <ipAddress>2.2.2.2</ipAddress>
               <location> </location>
               <majorAlarms>0</majorAlarms>
                <softwareType>IOS</softwareType>
                <softwareVersion>12.2(55)SE9</softwareVersion>
                <warningAlarms>0</warningAlarms>
             </devicesDTO>
          </entity>
        </queryResponse>

As result I have

 id, ipAddress
 ,
 ,

I am not sure that:

     my $xml = XMLin('input.xml', ForceArray => ['entity']);
     foreach my $entity (@{$xml->{entity}})

is right in my case. Should I do it with tag entity?

You should not use XML::Simple in new code. It's not simple, often wrong and the documentation suggests to not use it. — simbabque, Jan 12 '18 at 15:53

score 1 · Accepted Answer · answered Jan 12 '18 at 16:04

When dealing with XML::Simple, it's always a good idea to use Data::Dumper first to look at the data structure.

foreach my $entity ( @{ $xml->{entity} } ) {
    print Dumper $entity;

This will show you:

$VAR1 = {
          'url' => 'https://hostname/webacs/api/v1/data/Devices/20',
          'type' => 'Devices',
          'dtoType' => 'devicesDTO',
          'devicesDTO' => {
                          'displayName' => '20',
                          'creationTime' => '2016-02-29T17:32:13.116+01:00',
                          'warningAlarms' => '0',
                          'ipAddress' => '1.1.1.1',
                          'clearedAlarms' => '1',
                          'majorAlarms' => '0',
                          'collectionDetail' => '<status><general code="SUCCESS"/></status>',
                          'location' => {},
                          'collectionTime' => '2017-03-30T09:47:07.606+02:00',
                          'softwareType' => 'IOS',
                          'id' => '20',
                          'softwareVersion' => '12.2(55)SE9'
                        }
        };

So it's quite clear that your @Fields are in the wrong place. Those keys are not in $entity directly, but rather in $entity->{devicesDTO}.

There is also no need to use $_->{content} with the map. In fact, there are no content keys in that data structure.

foreach my $entity ( @{ $xml->{entity} } ) {
    print join( ',', @{ $entity->{devicesDTO} }{@Fields} ) . "\n";
}

This will produce the output

id,ipAddress
20,1.1.1.1
21,2.2.2.2

Note that you have a stray comma in your qw{} that you don't need. The idea of qw is that you do not need to use commas. You should also decide if you want your variables to have lower case letters or not. Mixing that is bad style, and the convention in Perl is to use snake case.

my @fields = qw{id ipAddress};

Thank you a lot for advise! It works! But my file output.csv is empty. Should I write `open(my $out, '>', 'output.csv') or die "Output: $!\n"; print $out join(',', @Fields) . "\n";` at the end of script? — StayCalm, Jan 12 '18 at 16:21
@StayCalm you didn't show your full code, so I don't know. If you have a follow up question, please ask it as new question. — simbabque, Jan 12 '18 at 16:22

XML to CSV with nested and definite elements in Perl

1 Answers1

Linked