How to extract data from marcxml.biblioitmes ?
Dear Friends, I would like to prepare a report from "marcxml" field in "biblioitems" table. The report should include author, title, subject and abstract. I got some hint from koha wiki page, http://wiki.koha-community.org/wiki/SQL_Reports_Library#Query_MARC But I failed to wrote an SQL query for the purpose. I am not familiar with writing query to extract data from XML part in MySQL. Can you help me to prepare a report from "marcxml" ? If anybody from developers or users give guideline with examples, that will be a good contribution to Koha community. -- Vimal Kumar V. Asst. Librarian Asian School of Business Technopark, Trivandrum-695 581 Web: www.vimalkumar.co.nr Blog: http://vimalkumar.oksociety.in OK Society Volunteer. http://oksociety.in --------------------------------------------------------------------------- "I forget what I was taught. I only remember what I have learnt" -Patrick White
Hi, I did some work recently extracting data from the marcxml field. These resources gave me a general idea about the relevant MySQL functions and about XPath, the notation used to extract data from the marcxml field: http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html http://dev.mysql.com/tech-resources/articles/mysql-5.1-xml.html http://www.w3schools.com/xpath/xpath_syntax.asp If you specify exactly which MARC tags & subfields you want, I'll see if I can help with a query. Cheers, Daniel Grobani System Administrator John A. Graziano Memorial Library Samuel Merritt University http://www.samuelmerritt.edu/library Vimal Kumar-3 wrote:
Dear Friends, I would like to prepare a report from "marcxml" field in "biblioitems" table. The report should include author, title, subject and abstract. I got some hint from koha wiki page, http://wiki.koha-community.org/wiki/SQL_Reports_Library#Query_MARC
But I failed to wrote an SQL query for the purpose. I am not familiar with writing query to extract data from XML part in MySQL. Can you help me to prepare a report from "marcxml" ?
If anybody from developers or users give guideline with examples, that will be a good contribution to Koha community.
-- Vimal Kumar V. Asst. Librarian Asian School of Business Technopark, Trivandrum-695 581 Web: www.vimalkumar.co.nr Blog: http://vimalkumar.oksociety.in
OK Society Volunteer. http://oksociety.in --------------------------------------------------------------------------- "I forget what I was taught. I only remember what I have learnt" -Patrick White
_______________________________________________ Koha mailing list Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
-- View this message in context: http://old.nabble.com/How-to-extract-data-from-marcxml.biblioitmes---tp28514... Sent from the Koha - Discuss mailing list archive at Nabble.com.
Daniel Grobani wrote:
These resources gave me a general idea about the relevant MySQL functions and about XPath, the notation used to extract data from the marcxml field:
http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html http://dev.mysql.com/tech-resources/articles/mysql-5.1-xml.html http://www.w3schools.com/xpath/xpath_syntax.asp
For older installs of Koha, be aware that the XML functions are not available prior to Mysql 5.0. cheers rickw -- _________________________________ Rick Welykochy || Praxis Services In the modern world the stupid are cocksure while the intelligent are full of doubt. -- Bertrand Russell
On 10/05/10 22:52, Daniel Grobani wrote:
Hi,
I did some work recently extracting data from the marcxml field.
These resources gave me a general idea about the relevant MySQL functions and about XPath, the notation used to extract data from the marcxml field:
http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html http://dev.mysql.com/tech-resources/articles/mysql-5.1-xml.html http://www.w3schools.com/xpath/xpath_syntax.asp
If you specify exactly which MARC tags& subfields you want, I'll see if I can help with a query.
My XPATH (such as it ever was) is rather rusty but playing with the ExtractValue function it seems happy if I select on datafield's attributes or subfield's attributes but not both. So I can extract tag x or subfield y but not subfield y of tag x. Anyone have a better xpath expression. Colin -- Colin Campbell Chief Software Engineer, PTFS Europe Limited Content Management and Library Solutions +44 (0) 208 366 1295 (phone) +44 (0) 7759 633626 (mobile) colin.campbell@ptfs-europe.com skype: colin_campbell2 http://www.ptfs-europe.com
On Tue, May 11, 2010 at 12:27 PM, Colin Campbell <colin.campbell@ptfs-europe.com> wrote:
My XPATH (such as it ever was) is rather rusty but playing with the ExtractValue function it seems happy if I select on datafield's attributes or subfield's attributes but not both. So I can extract tag x or subfield y but not subfield y of tag x. Anyone have a better xpath expression.
FWIW, here is an example I worked up some time ago: 'SELECT ExtractValue((SELECT marcxml FROM biblioitems WHERE biblionumber=14), '//datafield[@tag="260"]/subfield[@code>="b"]') AS ITEM;' Which returns all subfields b from tag 260 for a given bib. ExtractValue is not extremely flexible it seems, but potentially useful. Kind Regards, Chris
Vimal emailed me off-list with the data he needs to extract. This should do it: SELECT extractValue(marcxml,"collection/record/datafield[@tag='952'][1]/subfield[@code='p']"), extractValue(marcxml,"collection/record/datafield[@tag='024'][1]/subfield[@code='a']"), extractValue(marcxml,"collection/record/datafield[@tag='020'][1]/subfield[@code='a']"), extractValue(marcxml,"collection/record/datafield[@tag='100'][1]/subfield[@code='a']"), extractValue(marcxml,"collection/record/datafield[@tag='245'][1]/subfield[@code='a']"), extractValue(marcxml,"collection/record/datafield[@tag='650'][1]/subfield[@code='a']"), extractValue(marcxml,"collection/record/datafield[@tag='500'][1]/subfield[@code='a]"), extractValue(marcxml,"collection/record/datafield[@tag='520'][1]/subfield[@code='a']"), extractValue(marcxml,"collection/record/datafield[@tag='650'][1]/subfield[@code='a']") FROM biblioitems Cheers, Daniel Vimal Kumar-3 wrote:
Dear Friends, I would like to prepare a report from "marcxml" field in "biblioitems" table. The report should include author, title, subject and abstract. I got some hint from koha wiki page, http://wiki.koha-community.org/wiki/SQL_Reports_Library#Query_MARC
But I failed to wrote an SQL query for the purpose. I am not familiar with writing query to extract data from XML part in MySQL. Can you help me to prepare a report from "marcxml" ?
If anybody from developers or users give guideline with examples, that will be a good contribution to Koha community.
-- Vimal Kumar V. Asst. Librarian Asian School of Business Technopark, Trivandrum-695 581 Web: www.vimalkumar.co.nr Blog: http://vimalkumar.oksociety.in
OK Society Volunteer. http://oksociety.in --------------------------------------------------------------------------- "I forget what I was taught. I only remember what I have learnt" -Patrick White
_______________________________________________ Koha mailing list Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
-- View this message in context: http://old.nabble.com/How-to-extract-data-from-marcxml.biblioitmes---tp28514... Sent from the Koha - Discuss mailing list archive at Nabble.com.
participants (5)
-
Chris Nighswonger -
Colin Campbell -
Daniel Grobani -
Rick Welykochy -
Vimal Kumar