Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2012
    Posts
    1

    Unanswered: select from xml field

    Hi

    I have mysql field i want to select from it,

    <?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>
    mysql version is 5.0 not support
    extract value function is there another way to solve it

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    You can find a solution using PHP. There are very functions that handle XML parsing returning an structure. Have a look at some sample code:

    Code:
    $ cat test.php
    <?php
    	$file = "test.xml";
    	$xml = simplexml_load_file($file);
    	foreach($xml->datafield as $key => $value) {
    		echo "Tag: " . $value->attributes()->tag . "\n";
    		foreach($value->subfield as $k => $v) {
    			echo "subfield: " . $v . " code: " . $v->attributes()->code . "\n";
    		}
    	}
    $ cat test.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>
    $ php test.php 
    Tag: 578
    subfield: jaun code: a
    Tag: 356
    subfield:  code: a
    subfield:  code: c
    Tag: 333
    subfield: ali code: d
    subfield: 22 code: p
    Tag: 222
    subfield:  code: c
    subfield: 1984 code: d
    With this you can then extract the information you want either reformatting it or create your own insert statements within the PHP and execute.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

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