07
XML in MySQL
Posted under XML, mySQLInteresting 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.
