Question: I have close to 100 XML files containing various stored procedure names within the tag . Each file may contain more than one SP or the same repeating again. 

```
    <DataSourceName>SQL300</DataSourceName>
    <CommandType>StoredProcedure</CommandType>
    <CommandText>dbDummy.dbo.abc456Audit </CommandText>

```

I took over a project to migrate the SQL server data to SAS grid and all SSRS reports to SAS. I will have to map all the data. 

Is there any easy Linux/perl/VBA/Python way to do this in one step? 
I wanted something like:

```
File Name    Stored Procedures
Audit93       dbDummy.dbo.abc456Audit
Audit93       dbDummy.dbo.abc123Audit

```

I tried: grep, sed, awk, perl, VBA .

methods described here and in other places. I could able to extract all the tag info into one flat file, but nothing close to what I was looking for above. I need some technique to extract the file name along with the tags. 

I would appreciate if someone can shed some lights on how to approach this. 

Thanks!

Answer: 
You can try the below code:

```
use strict;
use warnings;
use Excel::Writer::XLSX;

my $dir = $ARGV[0];

opendir(DIR, "$dir") || die "Couldn't open the directory - $!";

my @files = grep(/\.xml$/,readdir(DIR));
my $OutFile = "Output". "\." . "xlsx";
my $workbook  = Excel::Writer::XLSX->new($OutFile);
my $worksheet = $workbook->add_worksheet('SotredProcedures');
# Create a format for the column headings
my $header = $workbook->add_format();
$header->set_bold();
$header->set_color('red');
# Create a Text Wrap format
my $format1 = $workbook->add_format();
$format1->set_text_wrap();

$worksheet->write(0, 0, "Filenames", $header);
$worksheet->write(0, 1, "Stored Procedures", $header);
$worksheet->set_column(0, 1, 12);
$worksheet->set_column(1, 2, 100);

my $row=1;
foreach our $filename (@files){
    my $document = do {
         local $/ = undef;
         open my $fh,'<',$filename or die "Couldn't open the file $filename:$!";
       <$fh>;
      };

      my $checkval = "false";
      while($document =~ /<CommandText>(.*?)<\/CommandText>/isg)
         {
             if($1 ne "")
              {
               $checkval = "true";
               $worksheet->write($row,0,$filename,$format1); 
               $worksheet->write($row,1,$1,$format1);
               $row++;
                  }
              }
         if($checkval eq "false")
           {
              print "No <CommandText> Value in $filename\n";
              }   

        }     

```
@Praveen' Perl solution is better (more robust, and produces richer excel);

Following a simple command to build a tab-separated-value that
probably will do the job:

```
( 
  echo -e "file\tSP" ;
  grep -PoH '(?<=<CommandText>).*?(?=<)' *.xml | tr ':' '\t' 
) > stored_proc.tsv

```

If you prefer a `xls` file:

```
ssconvert stored_proc.tsv stored_proc.xls

```

Finally see the result with excel, gnumeric, whatever...

```
gnumeric stored_proc.tsv

```