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

XML-handling functions.

XPath

If you’ve worked with XML data, you already know that the XML specification defines

certain rules that a document must adhere to in order to be well formed. One of the

most important rules is that every XML document must have a single outermost

element, called the “root element,” which, in turn, may contain other elements, nested

in a hierarchical manner.

Now, it seems logical to assume that if an XML document is laid out in this

structured, hierarchical tree, it’s possible to move at will from any node on the tree to

any other node on the tree. And that’s where XPath comes in—it provides a standard

addressing mechanism for an XML document that makes it possible to access and

manipulate any element, attribute, or text node on the tree.

XPath is an important component of both XML stylesheet transformations (XSLT)

and the XPointer linking language. By providing XML developers with a standard

method of addressing any part of an XML document, XPath is a small, yet important

piece of the whole XML jigsaw. XSLT uses it extensively to match nodes in an XML

source tree, while XPointer uses it in combination with XLink to identify specific

locations in an XML document.

Location Paths XPath represents an XML document as a tree containing a number of

different node types. In order to illustrate this, consider the following XML document:

<?xml version="1.0"?>

<recipe>

<name source="India">Chicken Tikka</name>

<author>Anonymous</author>

<date>1 June 1999</date>

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

<ingredients>

<item>Boneless chicken breasts</item>

<item>Chopped onions</item>

<item>Ginger</item>

<item>Garlic</item>

<item>Red chili powder</item>

<item>Butter</item>

</ingredients>

<process>

<step num="1">Cut chicken into cubes, wash and apply lime juice and

salt</step>

<step num="2">Add ginger, garlic, chili, coriander and lime juice in

a separate bowl</step>

<step num="3">Mix well, and add chicken to marinate for 3-4 hours</step>

<step num="4">Place chicken pieces on skewers and barbeque</step>

<step num="5">Remove, apply butter, and barbeque again until meat is

tender</step>

<step num="6">Garnish with lemon and chopped onions</step>

</process>

</recipe>

XPath makes it possible to locate a node, or set of nodes, at any level of this tree,

using a location path. A location path may be either an absolute path, which expresses a

location with reference to the root node, or a relative path, which expresses a location

with reference to the current node (also known as the context node). Location paths are

made up of a series of location steps, each identifying one level in the XPath tree and

separated from each other by a forward slash (/).

A location step is expressed as the sum of three components in the format axis::nodetest[

predicates]. The axis defines the relationship to use when selecting nodes, a node-test

specifies the types of nodes to select, and optional predicates filter out unwanted nodes

from the resulting collection.

Axes, Node Tests, and Predicates An axis defines the relationship between the current

node and the nodes to be selected—whether, for example, they are children of the

current node, siblings of the current node, or the parent of the current node.

The XPath specification defines a number of axes; the most important ones are

listed in Table 8-1.

Caution The “following” and “preceding” axes are not supported in MySQL at the time of

this writing.

...