[Koha] Report help needed

[Elaine Bradtke]

I have a request for a report that is beyond my paltry abilities and that
of the report builder.


I have this, and it works (thanks Liz!):
SELECT
 biblio.biblionumber,biblio.title,biblio.notes,biblioitems.publishercode,biblioitems.itemtype
FROM biblioitems LEFT JOIN biblio USING (biblionumber)   WHERE
biblioitems.itemtype='CR'

But I need to add in more columns that involve the MARC XML

Specifically
780 t (previous title)
780 w (previous control no.)
866 a (holdings)
866 z (note)
867 e (source)

When I try to add in:
  Extract value ExtractValue(marcxml,
'//datafield[@tag="780"]/subfield[@code="t"]')
I manage to break the above query and it complains of syntax errors.
Does it matter where this Extract phrase comes in?
How do you handle multiple subfields (780 t and w for instance)
For that matter, how do you handle multiple fields?  And how can I label
the columns

The following (and a few variations on the theme) did not work:

SELECT
 biblio.biblionumber,biblio.title,biblio.notes,biblioitems.publishercode,biblioitems.itemtype
ExtractValue(marcxml, '//datafield[@tag="780"]/subfield[@code="t"]')
ExtractValue(marcxml, '//datafield[@tag="780"]/subfield[@code="w"]')
ExtractValue(marcxml, '//datafield[@tag="866"]/subfield[@code="a"]')
ExtractValue(marcxml, '//datafield[@tag="866"]/subfield[@code="z"]')
FROM biblioitems LEFT JOIN biblio USING (biblionumber)   WHERE
biblioitems.itemtype='CR'


As always I am very, very grateful for whatever help you can give.
Thanks
-- 
Elaine Bradtke
Data Wrangler
VWML
English Folk Dance and Song Society | http://www.efdss.org
Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
Tel    +44 (0) 20 7485 2206 (This number is for the English Folk Dance and
Song Society in London, England. If you wish to phone me personally, send
an e-mail first. I work off site)
--------------------------------------------------------------------------
Registered Company No. 297142
Charity Registered in England and Wales No. 305999
---------------------------------------------------------------------------
"Writing about music is like dancing about architecture"
--Elvis Costello (Musician magazine No. 60 (October 1983), p. 52)