Vyoms OneStopTesting.com - Testing EBooks, Tutorials, Articles, Jobs, Training Institutes etc.
OneStopGate.com - Gate EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
OneStopMBA.com - MBA EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
OneStopIAS.com - IAS EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
OneStopSAP.com - SAP EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
OneStopGRE.com - of GRE EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
Bookmark and Share Rss Feeds

Data Formatting, Working with XML Data in MySQL | Articles | Recent Articles | News Article | Interesting Articles | Technology Articles | Articles On Education | Articles On Corporate | Company Articles | College Articles | Articles on Recession
Sponsored Ads
Hot Jobs
Fresher Jobs
Experienced Jobs
Government Jobs
Walkin Jobs
Placement Section
Company Profiles
Interview Questions
Placement Papers
Resources @ VYOMS
Companies In India
Consultants In India
Colleges In India
Exams In India
Latest Results
Notifications In India
Call Centers In India
Training Institutes In India
Job Communities In India
Courses In India
Jobs by Keyskills
Jobs by Functional Areas
Learn @ VYOMS
GATE Preparation
GRE Preparation
GMAT Preparation
IAS Preparation
SAP Preparation
Testing Preparation
MBA Preparation
News @ VYOMS
Freshers News
Job Articles
Latest News
India News Network
Interview Ebook
Get 30,000+ Interview Questions & Answers in an eBook.
Interview Success Kit - Get Success in Job Interviews
  • 30,000+ Interview Questions
  • Most Questions Answered
  • 5 FREE Bonuses
  • Free Upgrades

VYOMS TOP EMPLOYERS

Wipro Technologies
Tata Consultancy Services
Accenture
IBM
Satyam
Genpact
Cognizant Technologies

Home » Articles » Data Formatting, Working with XML Data in MySQL

Data Formatting, Working with XML Data in MySQL








Article Posted On Date : Wednesday, February 3, 2010


Data Formatting, Working with XML Data in MySQL
Advertisements

In the "Working with XML Data in MySQL" article, we explored some of MySQL's XML capabilities, including the --xml command line option, which provides a means of formatting query output as XML, as well as a couple of new XML-specific functions that were introduced in MySQL 5.1.5. In today's article, we'll continue to explore XML support in MySQL. Specifically, we'll look at a nicer way to format our resultset so that the proper headers appear in the column headings, rather than the supplied expressions. We'll also expand the last example of the previous installment to retrieve all the records from the XML document, using a stored procedure.



Adding Our Own Headers to ExtractValue's Output



If you recall, our final example of the last article imported the contents of an XML file into a variable using the LOAD_FILE() function and then selected some fields from it using the ExtractValue() function:




CREATE TEMPORARY TABLE client_citizenship (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
xml_data TEXT NOT NULL
);

SET @xml = LOAD_FILE("c:\client_citizenships.xml");

INSERT INTO client_citizenship VALUES (NULL, @xml);

SELECT xml_data FROM client_citizenship LIMIT 1 INTO @xml;

SELECT ExtractValue(@xml, '//row[2]/field[1]/@name'),
ExtractValue(@xml, '//row[2]/field[1]');


The last SELECT statement extracted the first <field> of the second <row> node:




...
</row>

<row> (row[2])
<field name="client_id">2</field> (field[1])
<field name="date_of_birth">1944-01-15</field>
...


This produced the following resultset:



+----------------------------------------------+----------------------------------------+
|ExtractValue(@xml, "//row[2]/field[1]/@name") |ExtractValue(@xml, '//row[2]/field[1]') |
+----------------------------------------------+----------------------------------------+
|client_id |2 |
+----------------------------------------------+----------------------------------------+

Notice that MySQL uses the expressions as the column headers. To format the resultset with the field name in the header, we need to use a Prepared Statement. A Prepared Statement is a special type of SQL statement that has been precompiled. This means that when the Prepared Statement is executed, the DBMS can just run the Prepared Statement SQL without having to compile it first. Therefore it normally reduces execution time to use a Prepared Statement over a regular query when you want to execute the same statement many times. In this instance, the advantage to using a Prepared Statement is that we can set the header in one statement, and then concatenate it to our select string:

SET @header = SELECT ExtractValue(@xml, "//row[2]/field[1]/@name");
SET @qry = CONCAT("SELECT ExtractValue(@xml, "//row[2]/field[1]") AS ", @header, ";");
PREPARE stmt FROM @qry;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Now our data is formatted how we're used to seeing it:

+-----------+
| client_id |
+-----------+
| 2 |
+-----------+

Using the ExtractValue() Function Within a Stored Procedure

One obvious limitation to the above approach is that the rows are hard-coded so, unless you're after specific values, it won't be of much use for more generic tasks, such as importing an entire XML document into a table. For that, we need to use a stored proc.

MySqlDump

The MySqlDump client is a backup program originally written by Igor Romanenko. It can be used to dump a database or a collection of databases for backup or transfer to another database (MySQL or other). The dump typically contains SQL statements to create the table, populate it, or both. However, MySqlDump can also be used to generate files in CSV, other delimited text, or XML format, which is what we'll be doing here.

Although there is more than one way to run MySqlDump, the syntax for what we want to do is as follows:

mysqldump --xml databasename [tables]

You can also extract to a file by using the standard UNIX/DOS method of outputting to a file. Here is the command that I used to convert my client table into an XML file:

C:>mysqldump --xml temp_table_article client > c:\sqldump.xml

Looking a the sqldump.xml, we can see that the MySqlDump client includes a lot more information than was produced by the --xml flag alone. Whereas it simply outputed each row, field names and values, MySqlDump separates the output into "table_structure" and "table_data" sections:

<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database name="temp_table_article">
<table_structure name="client">
<field Field="client_id" Type="int(10) unsigned" Null="NO" Key="PRI" Extra="auto_increment" />
<field Field="date_of_birth" Type="date" Null="YES" Key="" Extra="" />
<field Field="gender" Type="char(1)" Null="YES" Key="" Extra="" />
<field Field="logical_delete_indicator" Type="tinyint(1)" Null="NO" Key="" Default="0" Extra="" />
<key Table="client" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="client_id"
Collation="A" Cardinality="4" Null="" Index_type="BTREE" Comment="" />
<key Table="client" Non_unique="0" Key_name="client_id" Seq_in_index="1" Column_name="client_id"
Collation="A" Cardinality="4" Null="" Index_type="BTREE" Comment="" />
<options Name="client" Engine="InnoDB" Version="10" Row_format="Compact" Rows="4" Avg_row_length="4096"
Data_length="16384" Max_data_length="0" Index_length="16384" Data_free="10485760"
Auto_increment="5"
Create_time="2009-10-05 17:51:34" Collation="latin1_swedish_ci" Create_options=""
Comment="" />
</table_structure>
<table_data name="client">
<row>
<field name="client_id">1</field>
<field name="date_of_birth">1976-02-12</field>
<field name="gender">M</field>
<field name="logical_delete_indicator">1</field>
</row>
<row>
<field name="client_id">2</field>
<field name="date_of_birth">1944-01-15</field>
<field name="gender">F</field>
<field name="logical_delete_indicator">0</field>
</row>
<row>
<field name="client_id">3</field>
<field name="date_of_birth">1956-06-04</field>
<field name="gender">M</field>
<field name="logical_delete_indicator">1</field>
</row>
<row>
<field name="client_id">4</field>
<field name="date_of_birth">1938-11-19</field>
<field name="gender">F</field>
<field name="logical_delete_indicator">0</field>
</row>
</table_data>
</database>
</mysqldump>

The reason that we used this tool to create the XML data structure is because there are already stored procedures available to insert MySqlDump XML data into a table. One such proc was written by MySQL developer Alexander Barkov. His xmldump_load procedure extracts data from the XML elements and attributes found in a file created using mysqldump --xml, and inserts this data into the columns of a MySQL table. Here is the full source of this stored procedure:

DELIMITER |

DROP PROCEDURE IF EXISTS xmldump_load |

CREATE PROCEDURE xmldump_load( file_name VARCHAR(128),
database_name VARCHAR(128),
table_name VARCHAR(128))
BEGIN
DECLARE xml TEXT;

DECLARE nrows INT;
DECLARE rownum INT DEFAULT 1;

DECLARE ncols INT;
DECLARE colnum INT DEFAULT 1;

DECLARE ins_list TEXT DEFAULT '';
DECLARE val_list TEXT DEFAULT '';

DECLARE tmp VARCHAR(255);

# load the XML file's contents into a string
SET xml = LOAD_FILE(file_name);

# get the number of <row>s in this table
SET nrows = ExtractValue(xml,
'count(/mysqldump/database[@name=$database_name]/table_data[@name=$table_name]/row)');

# get the number of <field>s (columns) in this table
SET ncols = ExtractValue(xml,
'count(/mysqldump/database[@name=$database_name]/table_data[@name=$table_name]/row[1]/field)');

# for each <row>
WHILE rownum <= nrows DO

# for each <field> (column)
WHILE colnum <= ncols DO
SET tmp = ExtractValue(xml,
'/mysqldump/database[@name=$database_name]/table_data[@name=$table_name]/row[$rownum]/field[$colnum]/@name');
SET ins_list = CONCAT(ins_list, tmp, IF(colnum<ncols, ',', ''));
SET tmp = ExtractValue(xml,
'/mysqldump/database[@name=$database_name]/table_data[@name=$table_name]/row[$rownum]/field[$colnum]');
SET val_list = CONCAT(val_list, '''', tmp ,'''', IF(colnum<ncols, ',', ''));
SET colnum = colnum + 1;
END WHILE;

SET @ins_text = CONCAT('INSERT INTO t1 (', ins_list, ') VALUES (', val_list, ')');

SET ins_list = '';
SET val_list = '';

PREPARE stmt FROM @ins_text;
EXECUTE stmt;

SET rownum = rownum + 1;
SET colnum = 1;
END WHILE;
END |

DELIMITER ;

Examine the code carefully and you'll see that the xmldump_load proc uses the same XML tools as we've used so far, such as the LOAD_FILE() and ExtractValue() functions. What it adds are two nested While loops to iterate through every row and column.

Here are the steps that one would take to import the sqldump.xml file that we created earlier:

The first thing we need to do is import the xmldump_load proc into MySQL if this is the first time that we use it:

C:> mysql db_name < text_file

C:> mysql temp_table_article < c:xmldump_load.sql

You can also load the proc from within the MySql client using the source or . commands:

mysql> source c:xmldump_load.sql;

OR

mysql> . c:xmldump_load.sql;

The proc accepts the three following input parameters:

the XML input file name

the database name to write to

the table name to create

Now we can supply the sqldump.xml file that we created earlier, along with a database and table name:

mysql> call xmldump_load('c:sqldump.xml ', 'client_info', 'client');

That will insert the contents of the sqldump.xml file into the client table of the client_info database.

Coming Soon...

A new SQL statement has been accepted for MySQL 6.0, and will be available in version 6.0.3. LOAD XML greatly simplifies the task of importing data from an XML file into a MySQL table, without having to use the stored procedure discussed previously. At the time of this writing, version 5.4 is in Beta, so you'll have to wait a bit for that.






Sponsored Ads



Interview Questions
HR Interview Questions
Testing Interview Questions
SAP Interview Questions
Business Intelligence Interview Questions
Call Center Interview Questions

Databases

Clipper Interview Questions
DBA Interview Questions
Firebird Interview Questions
Hierarchical Interview Questions
Informix Interview Questions
Microsoft Access Interview Questions
MS SqlServer Interview Questions
MYSQL Interview Questions
Network Interview Questions
Object Relational Interview Questions
PL/SQL Interview Questions
PostgreSQL Interview Questions
Progress Interview Questions
Relational Interview Questions
SQL Interview Questions
SQL Server Interview Questions
Stored Procedures Interview Questions
Sybase Interview Questions
Teradata Interview Questions

Microsof Technologies

.Net Database Interview Questions
.Net Deployement Interview Questions
ADO.NET Interview Questions
ADO.NET 2.0 Interview Questions
Architecture Interview Questions
ASP Interview Questions
ASP.NET Interview Questions
ASP.NET 2.0 Interview Questions
C# Interview Questions
Csharp Interview Questions
DataGrid Interview Questions
DotNet Interview Questions
Microsoft Basics Interview Questions
Microsoft.NET Interview Questions
Microsoft.NET 2.0 Interview Questions
Share Point Interview Questions
Silverlight Interview Questions
VB.NET Interview Questions
VC++ Interview Questions
Visual Basic Interview Questions

Java / J2EE

Applet Interview Questions
Core Java Interview Questions
Eclipse Interview Questions
EJB Interview Questions
Hibernate Interview Questions
J2ME Interview Questions
J2SE Interview Questions
Java Interview Questions
Java Beans Interview Questions
Java Patterns Interview Questions
Java Security Interview Questions
Java Swing Interview Questions
JBOSS Interview Questions
JDBC Interview Questions
JMS Interview Questions
JSF Interview Questions
JSP Interview Questions
RMI Interview Questions
Servlet Interview Questions
Socket Programming Interview Questions
Springs Interview Questions
Struts Interview Questions
Web Sphere Interview Questions

Programming Languages

C Interview Questions
C++ Interview Questions
CGI Interview Questions
Delphi Interview Questions
Fortran Interview Questions
ILU Interview Questions
LISP Interview Questions
Pascal Interview Questions
Perl Interview Questions
PHP Interview Questions
Ruby Interview Questions
Signature Interview Questions
UML Interview Questions
VBA Interview Questions
Windows Interview Questions
Mainframe Interview Questions


Copyright © 2001-2017 Vyoms.com. All Rights Reserved. Home | About Us | Advertise With Vyoms.com | Jobs | Contact Us | Feedback | Link to Us | Privacy Policy | Terms & Conditions
Placement Papers | Get Your Free Website | IAS Preparation | C++ Interview Questions | C Interview Questions | Report a Bug | Romantic Shayari | CAT 2017

Fresher Jobs | Experienced Jobs | Government Jobs | Walkin Jobs | Company Profiles | Interview Questions | Placement Papers | Companies In India | Consultants In India | Colleges In India | Exams In India | Latest Results | Notifications In India | Call Centers In India | Training Institutes In India | Job Communities In India | Courses In India | Jobs by Keyskills | Jobs by Functional Areas

Testing Articles | Testing Books | Testing Certifications | Testing FAQs | Testing Downloads | Testing Interview Questions | Testing Jobs | Testing Training Institutes

Gate Articles | Gate Books | Gate Colleges | Gate Downloads | Gate Faqs | Gate Jobs | Gate News | Gate Sample Papers | Gate Training Institutes

MBA Articles | MBA Books | MBA Case Studies | MBA Business Schools | MBA Current Affairs | MBA Downloads | MBA Events | MBA Notifications | MBA FAQs | MBA Jobs
MBA Job Consultants | MBA News | MBA Results | MBA Courses | MBA Sample Papers | MBA Interview Questions | MBA Training Institutes

GRE Articles | GRE Books | GRE Colleges | GRE Downloads | GRE Events | GRE FAQs | GRE News | GRE Training Institutes | GRE Sample Papers

IAS Articles | IAS Books | IAS Current Affairs | IAS Downloads | IAS Events | IAS FAQs | IAS News | IAS Notifications | IAS UPSC Jobs | IAS Previous Question Papers
IAS Results | IAS Sample Papers | IAS Interview Questions | IAS Training Institutes | IAS Toppers Interview

SAP Articles | SAP Books | SAP Certifications | SAP Companies | SAP Study Materials | SAP Events | SAP FAQs | SAP Jobs | SAP Job Consultants
SAP Links | SAP News | SAP Sample Papers | SAP Interview Questions | SAP Training Institutes |


Copyright ©2001-2017 Vyoms.com, All Rights Reserved.
Disclaimer: VYOMS.com has taken all reasonable steps to ensure that information on this site is authentic. Applicants are advised to research bonafides of advertisers independently. VYOMS.com shall not have any responsibility in this regard.