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

MySQL database how to use XML data, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

Export data to XML format using the-- xml option

In versions prior to MySQL 5.0, there were many restrictions when using the MySQL command line client to export data in XML format. When executing a command or query, use the-- xml or-X option to tell the MySQL client to output the result to XML data, for example, the following command will output all database variables that start with version.

C: > mysql-u-p-e "SHOW VARIABLES LIKE'% version%'"-- xml

The final output XML includes the following standard format:

The entire dataset is included in the node

Each row corresponds to a node

All columns are included in one node

The column name is in the name attribute

The column value is a text node.

Double click code 12 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 20 21 22 24 protocol_version 10 version 5.1.30-community version_comment MySQL Community Server (GPL) version_compile_machine ia32 version_compile_os Win32

For the sake of comparison, the above command tabulates the output below.

Double-click the code to select all 1 2 3 4 5 6 7 8 9 "Variable_name" music + | Variable_name | Value | +-+ | protocol_version | | 10 | | version | 5.1.22-beta-debug | | version_comment | Source distribution | | version_compile_machine | x86room64 | | version_compile_os | suse-linux-gnu | +-+-+ |

The same syntax can be applied to SELECT statements. In my previous article, "making your MySQL query more efficient with temporary tables," I populated client_citizenship temporary tables with logical_delete_indicator=0 records in the initial query.

Double click code 12 3 4 5 6 7 8 9 10 11 12 13 14INSERT INTO client_citizenshipSELECT cl.client_id, cl.date_of_birth, cl.gender, cit.citizenship_id, cit.country_code, cit.primary_citizenshipFROM temp_table_article.client AS cl, temp_table_article.citizenship AS cit Temp_table_article.client_citizenship_rel AS rel WHERE cl.client_id = rel.client_id AND cit.citizenship_id = rel.citizenship_idAND cit.logical_delete_indicator = 0AND cl.logical_delete_indicator = 0

Executing a "SELECT *" query on this temporary table returns the following records:

Double click code 1 2 3client_id, date_of_birth, gender, citizenship_id, country_code, primary_citizenship2, 1944-01-15, F, 4, 20, 02, 1944-01-15, F, 7, 77, 1

When I add the-- xml option, the "SELECT *" query returns the following results in XML format:

Double click code 12 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 20 2 1944-01-15 F 4 20 2 1944-01-15 F 7 77 1

The disadvantage of using the-- xml option is that the mapping from the associated data to XML is fixed, so the output cannot be modified. If you want to do some string manipulation, you can remove the-- xml option and generate XML code in your own way.

Continuing with the previous query, let's take a look at how to modify the output XML to meet the following format requirements:

Double-click the code to select all 1 2 3 4 5 6 7 8 2 1944-01-15 F 7 77 1 is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.