Mysql xml record

Databases
1

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?

2

i used this query but give me error

select  ExtractValue(marcxml,'//datafield[@tag='578']/*')  ITEM from staff where staff_no<100 "
3

What does the error say?

4

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’]/*')

5

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?

6

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

7

Well, yes, because now your having conflicts between the double quotes enclosing the entire query, and the double quotes around 578.

I have never used ExtractValue, so I really have no idea where that employee comes from?

8

employee database name

9

What version of MySQL are you running? Looking at the manuals it seems like ExtractValue() exists since version 5.1

10

Is there another way to deal with marcxml field ??

11

mysql version is (5.0.51a) ,Version does not support ExtractValue

How I can solve this problem?

12

Upgrading MySQL would be the best solution.

13

is there another solution?

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?