Example MySQL SELECT statement to parse MARC data for import via Adobe Bridge

gistfile1.sql

SELECT CONCAT(REPLACE(items.itemcallnumber,".","_"),".tif") AS filename,

items.itemcallnumber AS title,
ExtractValue(biblioitems.marcxml,'//datafield[@tag="500"]/subfield[@code="a"]') AS description,

CONCAT_WS(" ",biblio.title,ExtractValue(biblioitems.marcxml,'//datafield[@tag="245"]/subfield[@code="h"]')) as headline,

items.dateaccessioned as "date created",

CONCAT_WS(" ",ExtractValue(biblioitems.marcxml,'//datafield[@tag="100"]/subfield[@code="a"]'),
ExtractValue(biblioitems.marcxml,'//datafield[@tag="100"]/subfield[@code="d"]')) AS creator,

REPLACE(ExtractValue(biblioitems.marcxml,'//datafield[@tag="540"][1]/subfield[@code="u"][1]'),"http://photoarchive.aarome.org/index/rights.html","http://dhc.aarome.org/usage") AS "usage terms",
ExtractValue(biblioitems.marcxml,'//datafield[@tag="856"][@ind1="4"][@ind2="1"]/subfield[@code="z"]') AS credit,
ExtractValue(biblioitems.marcxml,'//datafield[@tag="856"]/subfield[@code="z"]') AS source,
REPLACE(ExtractValue(biblioitems.marcxml,'//datafield[@tag="856"]/subfield[@code="u"]'),"http://photoarchive.aarome.org/index/index/tsk/mass","http://dhc.aarome.org/copyright") AS "copyright notice",
ExtractValue(biblioitems.marcxml,'//datafield[@tag="540"]/subfield[@code="a"]') AS "copyright url",

/* Cascading NULLIF statements to strip out unwanted semicolons */

/* 610 */
CONCAT_WS(";",NULLIF(CONCAT_WS("--",NULLIF(ExtractValue(biblioitems.marcxml,'//datafield[@tag="610"][@ind1="2"][@ind2="0"][1]/subfield[@code="a"][1]')," "),
NULLIF(ExtractValue(biblioitems.marcxml,'//datafield[@tag="610"][@ind1="2"][@ind2="0"][1]/subfield[@code="x"][1]')," "))," "),

/* 650 */
NULLIF(CONCAT_WS("--",NULLIF(ExtractValue(biblioitems.marcxml,'//datafield[@tag="650"][@ind1=" "][@ind2="0"]/subfield[@code="a"][1]')," "),
NULLIF(ExtractValue(biblioitems.marcxml,'//datafield[@tag="650"][@ind1=" "][@ind2="0"]/subfield[@code="y"]')," "),
NULLIF(ExtractValue(biblioitems.marcxml,'//datafield[@tag="650"][@ind1=" "][@ind2="0"]/subfield[@code="z"]')," "))," "),

NULLIF(ExtractValue(biblioitems.marcxml,'//datafield[@tag="650"][@ind1=" "][@ind2="0"]/subfield[@code="a"][2]')," "),

/* 655 */
NULLIF(CONCAT_WS("--",NULLIF(ExtractValue(biblioitems.marcxml,'//datafield[@tag="655"][1]/subfield[@code="a"]')," "),
NULLIF(ExtractValue(biblioitems.marcxml,'//datafield[@tag="655"][1]/subfield[@code="y"]')," "))," "),

/* 655 adding another child node exclusively for the FW series */
NULLIF(CONCAT_WS("--",NULLIF(ExtractValue(biblioitems.marcxml,'//datafield[@tag="655"][2]/subfield[@code="a"]')," "),
NULLIF(ExtractValue(biblioitems.marcxml,'//datafield[@tag="655"][2]/subfield[@code="y"]')," "))," "),

/* 662 */
NULLIF(CONCAT_WS("--",NULLIF(ExtractValue(biblioitems.marcxml,'//datafield[@tag="662"]/subfield[@code="a"]')," "),
NULLIF(ExtractValue(biblioitems.marcxml,'//datafield[@tag="662"]/subfield[@code="b"]')," "),
NULLIF(ExtractValue(biblioitems.marcxml,'//datafield[@tag="662"]/subfield[@code="d"]')," "))," "),

/* 700 */
NULLIF(CONCAT_WS(" ",ExtractValue(biblioitems.marcxml,'//datafield[@tag="700"][@ind1="1"][@ind2=" "]/subfield[@code="a"]'),
ExtractValue(biblioitems.marcxml,'//datafield[@tag="700"][@ind1="1"][@ind2=" "]/subfield[@code="d"]'))," ")) AS "keywords 1"

FROM items LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) 
LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)
WHERE items.itemcallnumber LIKE 'AAR.FW%' AND items.itype="VMPHOTO" AND items.location="photo" ORDER by items.itemcallnumber asc