An Entrepreneur, Coach, IT Consultant, Strategic Adviser, and a Traveler craving to explore and contribute to forming a better society.

Friday, March 7, 2008

XML in MySQL

4 comments :

Keywords: PHP, MySQL, XML, Database, LAMP, BLOB, XML in MySQL

XML in MySQL

Interesting topic huh? Yes, it is very interesting to explore in this topic as XML & mySQL provides lots of options to work out.

I got a chance to attend a mySQL conference held in Chennai. It was technically a nice one though it was not organised well.

I just wanted to summarize some of mySQL features when it comes on XML. I dont know if other databases offers facilities like this, but mySQL offers.

Now, We're going to explore effective XML usage in mySQL.

I hope you all know about BLOB data type in mySQL. Binary Large OBject(BLOB) is used to store any large files, images, audio, video and of course xml too. Creating a table with blob datatype object,

Creation:

mysql> create table sample(
id int not null auto_increment primary key,
xmldoc blob not null,
remarks varchar(200) not null default 'No Remarks'
);

Now, let us assume a file named "new.xml" available in "C:\" and the file consists of the following contents,

<?xml version="1.0" encoding="ISO-8859-1"?>
<catalog>
<cd>
<title>Movie 1</title>
<artist>Artist 1</artist>
<country>India</country>
<company>20th Century Fox</company>
<price>10.90</price>
<year>2007</year>
</cd>
<cd>
<title>Movie 2</title>
<artist>Artist 2</artist>
<country>USA</country>
<company>Sony Pictures</company>
<price>8.95</price>
<year>2008</year>
</cd>
</catalog>

Insertion:

You can use the following SQL statement to insert a XML file into the table. load_file() function will help you load the entire file(xml file here) as a string into the table column.

mysql> insert into sample values( 1, load_file('c:/new.xml'), "" );

Selection:

You can use a normal "Select" statement to view the contents,

mysql> select xmldoc from sample limit 1 into @XML1;
+----+--------------------------------------------------------------+---------+
| id | xmldoc | remarks |
+----+--------------------------------------------------------------+---------+
| 1 | <?xml version="1.0" encoding="ISO-8859-1"?>
<catalog>
<cd>
<title>Movie 1</title>
<artist>Artist 1</artist>
<country>India</country>
<company>20th Century Fox</company>
<price>10.90</price>
<year>2007</year>
</cd>
<cd>
<title>Movie 2</title>
<artist>Artist 2</artist>
<country>USA</country>
<company>Sony Pictures</company>
<price>8.95</price>
<year>2008</year>
</cd>
</catalog> | |
+----+--------------------------------------------------------------+---------+
1 row in set (0.00 sec)

ExtractValue() -> is the function you will have to use to get the value of a specific node in a XML structure.

The syntax is as follows,

ExtractValue(XML_String, XPath_String);

Please have a look at the following example, it will get you the "Title" of the 2nd CD.

mysql> select extractvalue( @XML1, "//cd[2]/title" );
+--------------------------------------------+
| extractvalue( @XML1, "//cd[2]/title" ) |
+--------------------------------------------+
| Movie 2 |
+--------------------------------------------+
1 row in set (0.00 sec)

To get first CD's Title, you may have to issue the following SQL Statement:

mysql> select extractvalue( @XML1, "//cd[1]/title" );
+--------------------------------------------+
| extractvalue( @XML1, "//cd[1]/title" ) |
+--------------------------------------------+
| Movie 1 |
+--------------------------------------------+
1 row in set (0.00 sec)

Updation:

UpdateXML() -> is the function you will have to use to update the XML structure. It allows you to update a specific node of the XML.

The syntax is as follows,

UpdateXML(XML_Document, XPath_String, New_Value);

The following SQL statement will update the 2nd CD node's title with the name of "Apocalypto"

mysql> select updatexml( xmldoc, '//cd[2]/title', '<title>Apocalypto</title>' ) from sample;
+--------------------------------------------------------------------------
| updatexml( xmldoc, '//cd[2]/title', '<title>Apocalypto</title>' )
+--------------------------------------------------------------------------
| <?xml version="1.0" encoding="ISO-8859-1"?>
<catalog>
<cd>
<title>Movie 1</title>
<artist>Artist 1</artist>
<country>India</country>
<company>20th Century Fox</company>
<price>10.90</price>
<year>2007</year>
</cd>
<cd>
<title>Apocalypto</title>
<artist>Artist 2</artist>
<country>USA</country>
<company>Sony Pictures</company>
<price>8.95</price>
<year>2008</year>
</cd>
</catalog> |
+--------------------------------------------------------------------------
1 row in set (0.00 sec)

I hope this article may help you all to understand all about XML in mySQL.

See you all in my next post.

 

Keywords: PHP, MySQL, XML, Database, LAMP, BLOB, XML in MySQL

 

4 comments :

Gani said...

Hi Kathir,

The time I spent to read this topic was really worth. Thanks for sharing your knowledge with everyone.

Kathiravan Manoharan said...

Thanks for your appreciation Gani!...

Shankari said...

useful information Kathir

suhas said...

Hi Kathir,
Really nice one!!
But this is only one sided!!
How to export mysql table data into xml format..?
If you provide that information too... it will be rocking!!!