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,
-> 'Value1
'> Team1
'> 1
'> first
'>
'> Emp1
'> 6
'> Name1
'> Link
'> R
'>
'> Address1
'> Street1
'> 80
'> 1234KK
'> Stratford
'>
'>
'> Set1
'> 3
'> 1
'>
'> ')
-> ;
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO MyTable VALUES (9,
-> 'Match2
'> Team2
'> 2
'> second
'>
'> Emp2
'> 27
'> Name2
'> Smith
'> DD
'>
'> Address2
'> Street2
'> 804
'> 8457DK
'> Eltham
'>
'> Phone1
'> 1234567
'> 1111111
'> 2222222
'> 3333333
'>
'>
'> Set2
'> 3
'> 2
'>
'> ')
-> ;
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO MyTable VALUES (12,
-> 'Value12
'> Team2
'> 2
'> second
'>
'> Emp9
'> 8
'> Name8
'> Mary
'> B
'>
'> Street4
'> Station Road
'> 4
'> 6584RO
'> Inglewood
'>
'> Address8
'> Street3
'> 14
'> 2728YG
'> Douglas
'>
'>
'> Set12
'> 1
'> 3
'>
'> ');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> UPDATE MyTable
-> SET MATCH_INFO =
-> UPDATEXML(MATCH_INFO,
-> '/match/Employee/address',
-> 'The new address of 8
'> Jolly Lane
'> 30
'> 5383GH
'> Douglas
'> ')
-> WHERE MATCHNO = 1
-> ;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT EXTRACTVALUE(MATCH_INFO,
-> '/match/Employee/address/*') AS NEW_ADDRESS
-> FROM MyTable
-> WHERE MATCHNO = 1;
+-------------+
| NEW_ADDRESS |
+-------------+
| NULL |
+-------------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql>
mysql> drop table MyTable;
Query OK, 0 rows affected (0.00 sec)
mysql>