Query xml data with path : XPath « XML « SQL / MySQL

1. SQL / MySQL
2. XML
3. XPath

Query xml data with path

    
mysql>
mysql> CREATE TABLE MyTable
    ->       (MATCHNO      INTEGER NOT NULL PRIMARY KEY,
    ->        MATCH_INFO   TEXT);
Query OK, 0 rows affected (0.01 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/team/division')
    ->                       AS DIVISION
    -> FROM     MyTable;
+---------+----------+
| MATCHNO | DIVISION |
+---------+----------+
|       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>

   
    
    
    
  

Related examples in the same category