Mexico Corrupto

sample of the output:

34|Orly Airport

48|Gatwick Airport

56|Heathrow Airport

59|Rome Ciampino Airport

...

Tip You can also use the mysqldump utility to extract the contents of a database or table into

a file. Chapter 12 has more information on how to use this utility to back up and restore

your MySQL databases.

MySQL also supports combining the INSERT and SELECT statements to export

records from one table into another. Here’s an example, which copies passenger names

from the pax table to a separate user table:

mysql> CREATE TABLE user (

-> FirstName VARCHAR(255),

-> LastName VARCHAR(255)

-> );

Query OK, 0 rows affected (0.25 sec)

mysql> INSERT INTO user (FirstName, LastName)

-> SELECT SUBSTRING_INDEX(PaxName, ' ', 1),

-> SUBSTRING_INDEX(PaxName, ' ', -1)

-> FROM pax;

Query OK, 8 rows affected (0.47 sec)

Records: 8 Duplicates: 0 Warnings: 0

196 P a r t I : U s a g e

The field list specified in the INSERT statement must obviously match the columns

returned by the SELECT clause. A mismatch can cause MySQL to produce an error like

the following one:

mysql> INSERT INTO tbl1 (fld1, fld2) SELECT fld1, fld2, fld3 FROM tbl2;

ERROR 1136 (21S01): Column count doesn't match value count at row 1

Naturally, you can also attach a WHERE clause to the SELECT statement to copy only

a subset of the original table’s records into the new table:

mysql> INSERT INTO user (FirstName, LastName)

-> SELECT SUBSTRING_INDEX(PaxName, ' ', 1),

-> SUBSTRING_INDEX(PaxName, ' ', -1)

-> FROM pax WHERE ClassID = 2;

Query OK, 4 rows affected (0.49 sec)

Records: 4 Duplicates: 0 Warnings: 0

Working with XML Data

XML is a powerful tool for the management and effective exploitation of information,

and is widely used today as a way to describe almost any kind of data. MySQL 5.1

includes limited support for XML, providing various functions that can be used to

import and search XML fragments, while MySQL 6.0 (in alpha at the time of this

writing) provides a new statement, the LOAD XML statement, which allows easier

conversion of XML-encoded records into MySQL tables.

Obtaining Results in XML

The easiest way to get started with XML in MySQL is to exit and restart the MySQL

command-line client, this time passing it the --xml option, as shown:

[user@host]# mysql --xml -u root -p

Password: ******

This option puts the command-line client in “XML mode,” forcing its output to be

formatted as well-formed XML. To illustrate, try running a SELECT query:

mysql> SELECT AirportName, AirportID, AirportCode

-> FROM airport

-> LIMIT 0,3;

<?xml version="1.0"?>

<resultset statement="SELECT AirportName, AirportID, AirportCode

FROM airport LIMIT 0,3" xmlns:xsi="http://www.w3.org/2001/XMLSchemainstance">

<row>

<field name="AirportName">Orly Airport</field>

<field name="AirportID">34</field>

<field name="AirportCode">ORY</field>

</row>

PART I

C h a p t e r 8 : Wo r k i n g w i t h D a t a i n D i f f e r e n t F o r m a t s 197

PPAARRTT II

<row>

<field name="AirportName">Gatwick Airport</field>

<field name="AirportID">48</field>

<field name="AirportCode">LGW</field>

</row>

<row>

<field name="AirportName">Heathrow Airport</field>

<field name="AirportID">56</field>

<field name="AirportCode">LHR</field>

</row>

</resultset>

3 rows in set (0.03 sec)

Using XML Functions

MySQL 5.1 introduced two new built-in functions that make it easier to handle data

encoded in XML. These functions, which make use of XPath expressions to access and

update node values, are a significant addition to the MySQL toolkit. The following

sections introduce the basics of XPath and how it can be used in the context of MySQL’s

...