[Koha] Barcode info and maybe SQL/MARCXML help needed

[Doug Dearden]

Hello all,

We are running Koha version 3.4 - all the details are pasted below.  I am working on cleaning up our barcodes, as we mistakenly converted them from our old system (Follett) adding some characters to some of them, and have also input them incorrectly when cataloging since then.  The librarian doesn't have any problem checking things in and out right now as they are consistently wrong, so she knows how to reinterpret a barcode into the format that has been recorded in the database.  Our concern is for that future time when we want to do inventory using a scanner, and the barcode as scanned doesn't match what is in the database.

I have found barcodes in two places.  In the items table, barcode field and in the biblioitems table, marcxml field.  From what I can tell the latter location dates back to our conversion from Follett, as I left the information that Follett stored in the 852 record intact against that day when we might need it.  I have figured out how to update the items table using an SQL "Update" statement.  I have also figured out how to get a report out of the biblioitems table using a Select statement against the marcxml field like this:

SELECT
   ExtractValue (marcxml ,'//datafield[@tag="852"]/subfield[@code="p"]')
FROM biblioitems
WHERE marcxml RLIKE '<datafield tag="852"'
AND (
ExtractValue(marcxml,
'//datafield[@tag="852"]/subfield[@code="p"]/text()') LIKE 'T000____');

In trying to rewrite this to update the barcode portion of the field, to remove the leading "T" I came up with this:

SELECT
   UpdateXML (biblioitems.marcxml, '//datafield[@tag="852"]/subfield[@code="p"]/text("T000____")', RIGHT('//datafield[@tag="852"]/subfield[@code="p"]/text()',7))

However, that returns an error "Unknown table 'biblioitems' in field list" .

So I have a few of questions.


1.       If I fix the barcodes in the items table, will that do it?  Or does Koha store them somewhere else?

2.       Even if Koha only uses the ones in the items table, any ideas how to fix my UpdateXML statement so it works? (Just to keep things matched up).

3.       If an UpdateXML statement won't work on the marcxml field, any suggestions for another approach?

Thanks,

Doug Dearden
School for Advanced Research
sarweb.org


Koha version: 3.04.01.000
OS version ('uname -a'): Linux SAR75Linux 2.6.32-5-686 #1 SMP Tue Mar 8 21:36:00 UTC 2011 i686 GNU/Linux
Perl interpreter: /usr/bin/perl Perl version: 5.010001

Perl @INC:
/home/koha/koha-3.4.1
/etc/perl
/usr/local/lib/perl/5.10.1
/usr/local/share/perl/5.10.1
/usr/lib/perl5
/usr/share/perl5
/usr/lib/perl/5.10
/usr/share/perl/5.10
/usr/local/lib/site_perl
.
MySQL version: mysql Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (i486) using readline 6.1
Apache version: Server version: Apache/2.2.16 (Debian)
Zebra version: Zebra 2.0.47 (C) 1994-2010, Index Data ApS Zebra is free software, covered by the GNU General Public License, and you are welcome to change it and/or distribute copies of it under certain conditions. SHA1 ID: e4e44d1916c6e80e01aad163b487ee41c22272ba Using ICU