Shulou(Shulou.com)05/31 Report--

This article introduces how the process of using XML data in MySQL is like, the content is very detailed, interested friends can refer to it, I hope it can be helpful to you.

Using XML data in MySQL can better manage the data and format all the data. What is the process like? The following editor will explain what is the process of using XML data in MySQL?

What is the process of using XML data in MySQL

The biggest drawback of the method used above is that the lines to be retrieved are written dead in the code, which is very inflexible. Let's use a stored procedure to solve this problem.

MySqlDump

The MySqlDump client written by IgorRomanenko is originally a backup program that can back up a database or transfer one database to another (MySQL or other database). This backup process is actually a process of creating and populating tables. In addition, MySqlDump can also generate CSV,XML or other articles controlled by delimiters.

Although there is more than one way to perform MySqlDump, I want to use the following syntax:

Mysqldump--xmldatabasename [tables]

You can also use the standard Unix/Dos method to output to a file, such as the following command to convert a table into a XML file:

C:\ > mysqldump--xmltemp_table_articleclient > c:\\ sqldump.xml

When we open sqldump.xml, we find that the MySqlDump client includes more information than the-- xml option, but it outputs each row, field name, and value, and MySqlDump outputs the table structure and table data separately.

Name= "client" > client_idType=int (10) unsignedNull=NO "Key=PRI" Extra=auto_increment/ > Field= "date_of_birth" Type= "date" Null= "YES" Key= "Extra=" / > 11976-02-12M121944-01-15F031956-06-04M141938-11-19F0

What is the process of using XML data in MySQL

DELIMITER | DROPPROCEDUREIFEXISTSxmldump_load | CREATEPROCEDURExmldump_load (file_nameVARCHAR, database_nameVARCHAR, table_nameVARCHAR) BEGINDECLARExmlTEXT;DECLAREnrowsINT;DECLARErownumINTDEFAULT1;DECLAREncolsINT;DECLAREcolnumINTDEFAULT1;DECLAREins_listTEXTDEFAULT'';DECLAREval_listTEXTDEFAULT'';DECLAREtmpVARCHAR; # load the contents of the XML file into a string SETxml=LOAD_FILE (file_name); # get the number of SETnrows=ExtractValue in this table (xml,'count (/ mysqldump/database [@ name=$database_name] / table_data [@ name=$table_name] / row)') # get the number of SETncols=ExtractValue in this table (xml,'count (/ mysqldump/database [@ name=$database_name] / table_data [@ name=$table_name] / row [1] / field)'); # for each WHILErownumsourcec:xmldump_load.sql;ORmysql >\ .c:xmldump_load.sql

This stored procedure accepts the following three input parameters:

XML enter the file name, target database name, table name to create

Let's take the sqldump.xml file, database and table names created earlier as an example.

Mysql > callxmldump_load ('c:\ sqldump.xml','client_info','client')

This command inserts the contents of the sqldump.xml file into the client table of the client_info database.

Upcoming new features

MySQL6.0 can accept the new SQL statement, version 6.0.3 will provide LOADXML function, directly import XML file, no more stored procedures, at the time of writing this article is still the 5.4Beta version, so you have to wait patiently for a while.

About how the process of using XML data in MySQL is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.