0

I have an XML file with several fields. One of the fields is a reference number (unique). Another field is a profile-id (not unique), which is always the same for all listings.

Separately, I have a list of reference numbers and their profile-ids (in an excel file). What I want to do is automatically match the reference number field, and change the profile-id field in the xml according to the list, so that the resulting xml will have a correct listing of the reference numbers and their respective profile-ids from the list instead of the general, same profile they have now.

Is this possible?

In the example below: the z303-profile-id field is the same for both patrons, each has a unique identifier in z303-ref, and the former is changed according to the latter in the list.

Thank you.

Example:

<patron-record>
    <z303>
      <z303-ref>000018804</z303-ref>
      <z303-profile-id>USE_MSL</z303-profile-id>
    </z303>
</patron-record>
<patron-record>
    <z303>
      <z303-ref>000018867</z303-ref>
      <z303-profile-id>USE_MSL</z303-profile-id>
    </z303>
</patron-record>

List:

000018804               full staff
000018867               Tester

Result:

<patron-record>
    <z303>
      <z303-ref>000018804</z303-ref>
      <z303-profile-id>full staff</z303-profile-id>
    </z303>
</patron-record>
<patron-record>
    <z303>
      <z303-ref>000018867</z303-ref>
      <z303-profile-id>Tester</z303-profile-id>
    </z303>
</patron-record>
Nimrod Yanai
  • 777
  • 2
  • 8
  • 30

1 Answers1

2

Here's how you can do it in xsh, a tool I happen to maintain:

perl {
    open my $LST, '<', shift or die $!;
    while (<$LST>) {
        chomp;
        my ($ref, $id) = split ' ', $_, 2;
        $profile_id->{$ref} = $id;
    }
} ;
open { shift };
for //patron-record/z303 {
    my $new_id = xsh:lookup('profile_id', z303-ref) ;
    if $new_id set z303-profile-id $new_id ;
}
save :b ;

Call as 

xsh -al script.xsh file.lst file.xml

Had you specified the list in an XML file, too, the code would have been much simpler.

choroba
  • 231,213
  • 25
  • 204
  • 289
  • Unfortunately the list is given to me from an outside source, but it might be possible to transform it. I will check :) Is that a code for a batch file that I need to run? Thanks a lot! :D – Nimrod Yanai Feb 27 '17 at 05:38
  • 1
    It's a `xsh` script you can run, and the next block shows how to run it from the command line or a script. – choroba Feb 27 '17 at 05:47
  • 1
    No, it's a text file you can get if you copy the contents of the xls file. Handling of XLS files is possible, but very complex. – choroba Feb 27 '17 at 07:37
  • So I just copy-paste the xsl into a txt file and change it into an lst file? – Nimrod Yanai Feb 27 '17 at 08:48
  • 1
    The `:b` option to `save` keeps a backup, so if something goes wrong, you can always restore the original XML :-) – choroba Feb 27 '17 at 08:52
  • I'm having trouble installing your tool as per the instructions in your site. I've unzipped the folder, opened the command line, navigated to it and tried to install via "cpanm XML::XSH2" but received an error message that cpanm is not recognized. Any advice? – Nimrod Yanai Feb 27 '17 at 10:07
  • Try with `cpan` instead of `cpanm`. – choroba Feb 27 '17 at 10:42
  • Still saying it is not recognized :( Do I need to place the downloaded folder in a specific path for it to work? – Nimrod Yanai Feb 28 '17 at 05:41
  • @NimrodYanai: What OS are you running? – choroba Feb 28 '17 at 06:05
  • Ah, I see. You need to install Perl first. I'd recommend [Strawberry](http://strawberryperl.com/). – choroba Feb 28 '17 at 07:30
  • Isn't there a way to do this with an XSL style sheet? D: This is getting a little complicated for a work computer (they frown on installing things like this, especially since I need a solution that will work for other workers as well) – Nimrod Yanai Feb 28 '17 at 07:40
  • There probably is. I don't use XSL when I can avoid it. – choroba Feb 28 '17 at 08:31