1

I have some xml data on a MariaDB database and need to do some parsing. I ve tried using the following code to extraxt some values but no matter what i may try i keep getting null as output

SELECT xmlResponse FROM myDataBase.xmlLogging where id = '1' INTO @xml;
SELECT ExtractValue(@xml, 'node2');

tried also count(node2) to try identify if there is somtheting wrong in my syntaxaccording to this

https://mariadb.com/kb/en/mariadb/documentation/sql-structure-and-commands/functions-and-operators/string-functions/extractvalue/

my xml structure looks like this, with namespacess

<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<SOAP-ENV:Body>
<ns:SMnamespace   xmlns:ns="http://somenamespace.com/WS/">
<ns:Error_spcCode>0</ns:Error_spcCode><ns:Error_spcMessage/>
<ListofData xmlns="http://www.vendor.com/namespeceIO">
    <node1>
        <node2>text</node2>
        <node3>text</node3>
        <node4/>
    </node1>
</ListofData>
</ns:SMnamespace>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>'
nikolas
  • 723
  • 2
  • 17
  • 37

2 Answers2

3

MySQL/MariaDB do not support xml namespaces. They support colons in element names however.

The reason that you do not get the node should be an invalid context in you location path try:

SELECT ExtractValue(@xml, '//node2');

Starting an Xpath expression with // means any node in the document.

ThW
  • 19,120
  • 3
  • 22
  • 44
  • i get null even with `//` in XPath expression but i guess if namespaces are not supported it might justify the reason for getting back null – nikolas Jan 14 '15 at 14:16
1

I found this reference

http://bugs.mysql.com/bug.php?id=51425

and it seems that this is the case for me as well. The xml that i demonstrated was a simplier version of my actual one that contains quite big strings. So it seems as soon as i reduced the string size, i could get a result.

nikolas
  • 723
  • 2
  • 17
  • 37