SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql xml record

    i have mysql xml field like this
    Code XML:
    <?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. #2
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i used this query but give me error

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

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    What does the error say?

  4. #4
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    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. #6
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    when used this query give me this error

    Code MySQL:
    $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

    Code MySQL:
    $sql="select  ExtractValue(marcxml,'//datafield[@tag=578]/*')  ITEM from staff where staff_no<100 ";
    FUNCTION employee.ExtractValue does not exist

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by altarek View Post
    when used this query give me this error

    Code MySQL:
    $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.
    Quote Originally Posted by altarek View Post
    and when use query

    Code MySQL:
    $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?

  8. #8
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    employee database name

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    What version of MySQL are you running? Looking at the manuals it seems like ExtractValue() exists since version 5.1

  10. #10
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is there another way to deal with marcxml field ??

  11. #11
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    mysql version is (5.0.51a) ,Version does not support ExtractValue

    How I can solve this problem?

  12. #12
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Upgrading MySQL would be the best solution.

  13. #13
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    is there another solution?

  14. #14
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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)

    Code MySQL:
    $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?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •