Link to home
Start Free TrialLog in
Avatar of Jagdish Devaku
Jagdish Devaku

asked on

XML Parsing in MySQL

Hi,

We are planning for a database migration from MS SQL Server to MySQL. Most of our procedures in SQL Server are working with XML data, but we are issues where trying to convert these procedures to MySQL.

Below is the sample XML file. We are sending XML data (not the XML file) directly as a input to the procedure.

With the XML we want the data in the following tabular format in MySQL

Date | Attendance | Personal_Id

and

Attendance | Personal_Id

Can any experts help us to do this in MySQL.

I tried with ExtractValue but with no help. May be I am not using it properly.

Versions of MySQL we tried for the above: 5.1 & 5.5

Thanks in Advance.
xml---Sample.txt
Avatar of Kalpan
Kalpan
Flag of India image

Another option is to use the perl script as follow

http://www.kitebird.com/articles/mysql-xml.html
Also refer the PHP scripting to do the same
<?php require_once ('connect.php'); 
db_connect();
 
$xmlReader = new XMLReader();
 
$filename = "getmein.xml";
$url = "http://feeds.perfb.com/index.php/download?OEMAIL=danielrafique@vyceversa.co.uk&PX=d0310005b5e00cc254c6bc3c1fe075f0&DISPLAYFORMAT=XML&REVERSEMAPXML=yes&PRODUCTDB_ID=357";
file_put_contents($filename, file_get_contents($url));
 
$xmlReader->open($filename); 
 
while ($xmlReader->read()) 
{
}
switch ($xmlReader->name)
{
case 'product':
 
$dom = new DOMDocument();
$domNode = $xmlReader->expand();
$element = $dom->appendChild(domNode);
$domstring = utf8_encode($dom->saveXML($element));
$product = new SimpleXMLElement($domString);
 
$product_code = $product->product_code;
$product_name = $product->product_name;
$level1 = $product->level1;
$level2 = $product->level2;
$description = $product->description;
$buyat_short_deeplink_url = $product->buyat_short_deeplink_url;
 
$image_url = $product->image_url;
$currency = $product->currency;
$price = $product->online_price;
$city = $product->city;
$country = $product->country;
$event_date = $product->event_date;
$event_venue = $product->event_venue;
$number_available = $product->number_available;
 
if (strlen($product_code)> 0)
{
$query = mysql_query("REPLACE INTO icanget_tickets
(product_code, product_name, level1, level2, description, buyat_short_deeplink_url, image_url, currency, price, country, event_date, event_venue, number_available)
 
VALUES ('$product_code', '$product_name', '$level1', '$level2', '$description', '$buyat_short_deeplink_url', '$image_url', '$currency', '$price', '$country', '$event_date', '$event_venue', '$number_available')");
 
echo $product_name . "has been inserted </br>";
}
break;
 
}
 
?>

Open in new window

Avatar of Jagdish Devaku
Jagdish Devaku

ASKER

Thanks for the responses.

I am not looking for PHP or Perl scripting.

I need pure MySQL script to do this.

@kalmax: I have already gone through that link before, but i could not get any solution...

Any more helps...
*MySQL XML import test drive*
Here is a quick test drive for the load xml command, just to show you how it
works.

*Creating the test tables*

Start by creating the test table with the following command:

create table test.person(
person_id int not null,
fname varchar(40) null,
lname varchar(40) null,
created timestamp,
primary key(person_id));

Now we are ready for some testing!

*First test: data as attributes*

For this test we use file person.xml, with the following content:

<list>

<person person_id='1' fname="Erik" lname="Wetterberg"/>

<person person_id='2' fname="Sven" lname="Svensson"/>

</list>

To read this into MySQL, we use the following command:

mysql> load xml local infile 'person.xml' into table test.person

-> rows identified by '<person>';

Query OK, 2 rows affected (0.08 sec)

Records: 2 Deleted: 0 Skipped: 0 Warnings: 0

Check the result with the following command:

mysql> select * from test.person;

+-----------+-------+------------+---------------------+

| person_id | fname | lname | created |

+-----------+-------+------------+---------------------+

| 1 | Erik | Wetterberg | 2006-12-05 18:17:22 |

| 2 | Sven | Svensson | 2006-12-05 18:17:22 |

+-----------+-------+------------+---------------------+

2 rows in set (0.00 sec)

Hi,

Thanks Kalmax.

Actually I am not looking for Load XML... as we will be directly sending the xml string from the application and i specified in the question itself.

Anymore helps....
not sure ...about any mysql function that would load the xml link straight to mysql database...

I suppose you would need Load function with mysql or add perl or php script to do this...

Thanks

If not XML...

Is there any other way where I will be sending multiple rows of data to MySQL and its get processed in MySQL.

My application is developed in .NET.

Any helps...
What about using the Extracvalue() function?

reference:
http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html#function_extractvalue
Hi Aikimark,

We are unable to get multiple rows using extractvalue(). So extractvalue() is not an option for my requirement. I already mentioned this in my question....

Bye.
1. part of the problem might be that you have specified UTF-16 encoding, but it lacks a unicode byte order mark (BOM).

>>We are unable to get multiple rows...
2. If you look at the linked page, you will see examples of iterating through the XML.
I know this can be done through LoadXML - but as per our scenario we can't use loadxml as we will lot of simultaneous transactions...

The solution I am looking for is not there in the linked page.


the linked page is about using the Extractvalue() function, not LoadXML.
Oh.  I see, your 'data' are actually attributes.
ASKER CERTIFIED SOLUTION
Avatar of aikimark
aikimark
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Aside from a minor type-o, 'count('count(//Attendance_Info/Attendance)', I believe the approach above by aikimark should work perfectly fine. I now have my MySQL instance reinstalled so I can test this through for you ... and mock up how it would look in stored procedure.
Although, I think ExtractValue needs to have a static XPath expression, so potentially passing the $rownum will not work but will work that all out when I test.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks all for your support... I have started on this... i will let you the results...

Thanks mwvisa1 for your detailed info...
You are welcome. Let us know how this works out for you. I have started an article on this to help others and show a neat trick to doing this from a single select statement that can be used a standalone query or as the main statement in stored procedure. I hope to be done shortly and so will post a link here ...
Finished article (at least an intro, playing with some advanced topics on the subject):
https://www.experts-exchange.com/Database/MySQL/A_3574-A-MySQL-Tidbit-In-line-XML-Parsing.html

Hopefully that is useful to you in addition to future readers.
Great article, mwvisa1.  Quickly written, too.
Superb. I really appreciate the efforts by mwvisa1, aikimark & kalmax. Thanks to all.

Thanks for the excellent article by mwvisa1.