Get the match number and team number. : Element « XML « SQL / MySQL






Get the match number and team number.

    
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,
    ->          '/child::match/child::team/child::number ')
    ->          AS NUMBERS
    -> FROM     MyTable;
+---------+---------+
| MATCHNO | NUMBERS |
+---------+---------+
|       1 | NULL    |
|       9 | NULL    |
|      12 | NULL    |
+---------+---------+
3 rows in set, 3 warnings (0.00 sec)

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

mysql>
mysql>

   
    
    
    
  








Related examples in the same category

1.The value of the first element
2.Choose element by value
3.Xml data column
4.Get the match number and the town of the first address
5.Get the last phone number of the relevant Employee.
6.Get all address information for the corresponding Employee.
7.Get all the data of the Employee concerned.