Get the match number and the town of the first address
mysql>
mysql> CREATE TABLE MyTable
-> (MATCHNO INTEGER NOT NULL PRIMARY KEY,
-> MATCH_INFO TEXT);
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> INSERT INTO MyTable VALUES (1,
-> '<match number=1>Value1
'> <team>Team1
'> <number>1</number>
'> <division>first</division>
'> </team>
'> <Employee>Emp1
'> <number>6</number>
'> <name>Name1
'> <lastname>Link</lastname>
'> <initials>R</initials>
'> </name>
'> <address>Address1
'> <street>Street1</street>
'> <houseno>80</houseno>
'> <postcode>1234KK</postcode>
'> <town>Stratford</town>
'> </address>
'> </Employee>
'> <sets>Set1
'> <won>3</won>
'> <lost>1</lost>
'> </sets>
'> </match>')
-> ;
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO MyTable VALUES (9,
-> '<match number=9>Match2
'> <team>Team2
'> <number>2</number>
'> <division>second</division>
'> </team>
'> <Employee>Emp2
'> <number>27</number>
'> <name>Name2
'> <lastname>Smith</lastname>
'> <initials>DD</initials>
'> </name>
'> <address>Address2
'> <street>Street2</street>
'> <houseno>804</houseno>
'> <postcode>8457DK</postcode>
'> <town>Eltham</town>
'> </address>
'> <phones>Phone1
'> <number>1234567</number>
'> <number>1111111</number>
'> <number>2222222</number>
'> <number>3333333</number>
'> </phones>
'> </Employee>
'> <sets>Set2
'> <won>3</won>
'> <lost>2</lost>
'> </sets>
'> </match>')
-> ;
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO MyTable VALUES (12,
-> '<match number=12>Value12
'> <team>Team2
'> <number>2</number>
'> <division>second</division>
'> </team>
'> <Employee>Emp9
'> <number>8</number>
'> <name>Name8
'> <lastname>Mary</lastname>
'> <initials>B</initials>
'> </name>
'> <address>Street4
'> <street>Station Road</street>
'> <houseno>4</houseno>
'> <postcode>6584RO</postcode>
'> <town>Inglewood</town>
'> </address>
'> <address>Address8
'> <street>Street3</street>
'> <houseno>14</houseno>
'> <postcode>2728YG</postcode>
'> <town>Douglas</town>
'> </address>
'> </Employee>
'> <sets>Set12
'> <won>1</won>
'> <lost>3</lost>
'> </sets>
'> </match>');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> SELECT MATCHNO, EXTRACTVALUE(MATCH_INFO,
-> '/match/Employee/address[1]/town')
-> AS TOWN
-> FROM MyTable;
+---------+------+
| MATCHNO | TOWN |
+---------+------+
| 1 | NULL |
| 9 | NULL |
| 12 | NULL |
+---------+------+
3 rows in set, 3 warnings (0.00 sec)
mysql>
mysql>
mysql> drop table MyTable;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
Related examples in the same category