ExtractValue mysql XML return empty instead of NULL

Question

I tried to get a NULL value but output is always empty, i don't know if is about mysql configuration,

queries;

mysql> SELECT ExtractValue('
  <?xml version="1.0" encoding="UTF-8"?>
    <lista>
      <socios>
         <inscripcion xsi:nil="true"></inscripcion>
      </socios>
    </lista>','lista/socios/inscripcion') as value;
+-------+
| value |
+-------+
|       |
+-------+
1 row in set (0.00 sec)


mysql> SELECT ExtractValue('
<?xml version="1.0" encoding="UTF-8"?>
  <lista>
    <socios>
      <inscripcion xsi:nil="true" />
    </socios>
  </lista>','lista/socios/inscripcion') as value;
+-------+
| value |
+-------+
|       |
+-------+
1 row in set (0.00 sec)

mysql> \s

mysql Ver 14.14 Distrib 5.5.37, for debian-linux-gnu (x86_64) using readline 6.2

Connection id: 53 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.5.37-0+wheezy1 (Debian) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /var/run/mysqld/mysqld.sock Uptime: 2 hours 21 min 33 sec

Threads: 3 Questions: 3531 Slow queries: 0 Opens: 453 Flush tables: 1 Open tables: 135 Queries per second avg: 0.415

Answer 1

As per MySQL XML Functions Documentation:

If no matching text node is found for the expression (including the implicit /text())—for whatever reason, as long as xpath_expr is valid, and xml_frag consists of elements which are properly nested and closed—an empty string is returned. No distinction is made between a match on an empty element and no match at all. This is by design.

However, you can determine whether there was actually a matching element using the following:

SELECT ExtractValue('
  <?xml version="1.0" encoding="UTF-8"?>
    <lista>
      <socios>
         <inscripcion xsi:nil="true"></inscripcion>
      </socios>
    </lista>', 'count(lista/socios/inscripcion)') as value;

Output would be:

+-------+
| value |
+-------+
| 1     |
+-------+

---

Edit 1:

got same output, i dont get it at all. how i should force to get a NULL value?

If the returned (trimmed) string is of length zero, then you can explicitly return a NULL.

Example:

SELECT if( length( trim( @v:=ExtractValue( '
  <?xml version="1.0" encoding="UTF-8"?>
    <lista>
      <socios>
         <inscripcion xsi:nil="true">  </inscripcion>
      </socios>
    </lista>', '/lista/socios/inscripcion' ) ) ) = 0, NULL, @v ) as value;

+-------+
| value |
+-------+
| NULL  |
+-------+

You can remove using trim if return a string with only spaces is acceptable.