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

got same output, i dont get it at all.

Erik Urrutia Santamaria
– Erik Urrutia Santamaria

2014-06-24 22:04:53 +00:00
Commented Jun 24, 2014 at 22:04 — Erik Urrutia Santamaria
– Erik Urrutia Santamaria, Commented Jun 24, 2014 at 22:04
how i should force to get a NULL value?

Erik Urrutia Santamaria
– Erik Urrutia Santamaria

2014-06-24 23:17:22 +00:00
Commented Jun 24, 2014 at 23:17 — Erik Urrutia Santamaria
– Erik Urrutia Santamaria, Commented Jun 24, 2014 at 23:17

Ravinder Reddy · Accepted Answer · 2014-06-25 04:52:47Z

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.

edited Jun 25, 2014 at 4:52

answered Jun 24, 2014 at 18:01

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Erik Urrutia Santamaria Over a year ago

thanks, it works, but i tried to do in another way, based in you answer, i have same output with codeSELECT if( length( @v:=ExtractValue( '<?xml version="1.0" encoding="UTF-8"?><lista><socios><inscripcion xsi:nil="true" /></socios></lista>', '/lista/socios/inscripcion' ) ) = 0, NULL, @v ) as value;code

Collectives™ on Stack Overflow

ExtractValue mysql XML return empty instead of NULL

mysql> \s

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

mysql> \s

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

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related