EXTRACTVALUE(MATCH_INFO,'/match/@number') : EXTRACTVALUE « XML « SQL / MySQL





EXTRACTVALUE(MATCH_INFO,'/match/@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>
mysql>
mysql> SELECT   MATCHNO, EXTRACTVALUE(MATCH_INFO,'/match/@number')
    ->          AS XML_MATCHNO
    -> FROM     MyTable;
+---------+-------------+
| MATCHNO | XML_MATCHNO |
+---------+-------------+
|       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>

   
    
    
    
  










Related examples in the same category

1.Using EXTRACTVALUE function
2.Using EXTRACTVALUE function with xml data
3.Replace result from EXTRACTVALUE
4.EXTRACTVALUE function and * wild card
5.EXTRACTVALUE fuction and //
6.EXTRACTVALUE function and //tagName
7.EXTRACTVALUE(MATCH_INFO, '/match//*')
8.EXTRACTVALUE(MATCH_INFO,'//town|//won')
9.Concatenate value from EXTRACTVALUE function
10.EXTRACTVALUE function and calculation
11.Using EXTRACTVALUE function in where clause
12.Match two values from one EXTRACTVALUE function