i have mysql xml field like this
<?xml version="1.0" encoding="UTF-8"?>
<record
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://www.loc.gov/MARC21/slim">
<leader>00764nas </leader>
<controlfield tag="221">011258</controlfield>
<datafield tag="578" ind1=" " ind2=" ">
<subfield code="a">jaun</subfield>
</datafield>
<datafield tag="356" ind1=" " ind2=" ">
<subfield code="a"></subfield>
<subfield code="c"></subfield>
</datafield>
<datafield tag="333" ind1=" " ind2=" ">
<subfield code="d">ali</subfield>
<subfield code="p">22</subfield>
</datafield>
<datafield tag="222" ind1=" " ind2=" ">
<subfield code="c"></subfield>
<subfield code="d">1984</subfield>
</datafield>
</record>
i don’t know how to apply SELECT ExtractValue at this field?
i used this query but give me error
select ExtractValue(marcxml,'//datafield[@tag='578']/*') ITEM from staff where staff_no<100 "
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘578’]/*')
Ah yes, you’re using single tags to enclose that string, so using single quotes around 578 messes things up. Maybe you can use double quotes around the string? Or escape the single quotes around 578?
when used this query give me this error
$sql="select ExtractValue(marcxml,'//datafield[@tag="578"]/*') ITEM from staff where staff_no<100 ";
Parse error: syntax error, unexpected T_LNUMBER in conts.php on line 24
and when use query
$sql="select ExtractValue(marcxml,'//datafield[@tag=578]/*') ITEM from staff where staff_no<100 ";
FUNCTION employee.ExtractValue does not exist
altarek:
when used this query give me this error
$sql="select ExtractValue(marcxml,'//datafield[@tag="578"]/*') ITEM from staff where staff_no<100 ";
Parse error: syntax error, unexpected T_LNUMBER in conts.php on line 24
Well, yes, because now your having conflicts between the double quotes enclosing the entire query, and the double quotes around 578.
altarek:
and when use query
$sql="select ExtractValue(marcxml,'//datafield[@tag=578]/*') ITEM from staff where staff_no<100 ";
FUNCTION employee.ExtractValue does not exist
I have never used ExtractValue, so I really have no idea where that employee comes from?
What version of MySQL are you running? Looking at the manuals it seems like ExtractValue() exists since version 5.1
altarek
January 25, 2012, 7:10am
10
Is there another way to deal with marcxml field ??
altarek
January 26, 2012, 11:27am
11
mysql version is (5.0.51a) ,Version does not support ExtractValue
How I can solve this problem?
Upgrading MySQL would be the best solution.
altarek
January 26, 2012, 11:32am
13
is there another solution?
altarek
January 29, 2012, 2:11pm
14
I tried to use this solution but give me conflicts between the double quotes enclosing the entire query, and the double quotes around (u & 856)
i want to save double quotes around (u & 856)
$sqlf="SELECT SUBSTRING(branches.xml, LOCATE('<subfield code="u">',
branches.xml, LOCATE('<datafield tag="856"', branches.xml)+19),
LOCATE('</subfield>', branches.xml, LOCATE('<subfield code="u">',
branches.marcxml, LOCATE('<datafield tag="856"',
branches.xml)+19)) - LOCATE('<subfield code="u">', branches.xml,
LOCATE('<datafield tag="856"', branches.xml)+19)) AS lbb
FROM branches where url IS NOT NuLL" ;
any help?