Update xml value : Update « XML « SQL / MySQL






Update xml value

    
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> UPDATE   MyTable
    -> SET      MATCH_INFO =
    ->          UPDATEXML(MATCH_INFO,
    ->          '/match/sets/lost',
    ->          '<lost>2</lost>')
    -> WHERE    MATCHNO = 1
    -> ;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>
mysql> SELECT   EXTRACTVALUE(MATCH_INFO,
    ->          '/match/sets/lost') AS LOST
    -> FROM     MyTable
    -> WHERE    MATCHNO = 1;
+------+
| LOST |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> drop table MyTable;
Query OK, 0 rows affected (0.00 sec)

mysql>

   
    
    
    
  








Related examples in the same category