mysql extractvalue_ExtractValue with MySQL

问题

I'm attempting to extract a username value from XML output that was loaded into a database column (file_output). My query is bringing back a null value and not performing as I expect it to. Your help is appreciated.

XML output:

username_prod

password

Query:

SELECT DISTINCT

RR.file_output

ExtractValue (file_output, '/soap:Envelope/soap:Header/wsse:Security/wsse:UsernameToken/wsse:Username') AS"Username"

FROM schema.Records

WHERE create_DTM >'2015-10-25';

Expected value is username_prod

回答1:

The problem that you’re running into is a bug with MySQL. The XML that you’re trying to parse is most likely too long. I’m pretty sure it’s related to this bug: https://bugs.mysql.com/bug.php?id=62429. To get around this bug, you can just extract the portion of the XML that you’re trying to navigate or just use another method to extract the value that you're looking for. Anyways, the solution I chose was to extract the XML that you were trying to navigate through XPath and use the XPath query that you provided to extract the results.

SELECT DISTINCT RR.consumer_ID

, RR.file_output

, RR.response_message

, RR.external_ID

, RR.create_DTM

, E.client_license_ID

, ExtractValue(CONCAT(LEFT(RR.file_output, (INSTR(LEFT(RR.file_output,1000), "") + 14)),""), '//wsse:Security/wsse:UsernameToken/wsse:Username') AS Username

FROM kettle_data_transfer.Records RR

JOIN kettle_data_transfer.Event_Mappings EM ON RR.event_mapping_ID = EM.event_mapping_ID AND RR.data_transfer_ID = EM.data_transfer_ID

JOIN efn.Events E on EM.event_ID = E.event_ID

WHERE 0=0

AND RR.data_transfer_ID = 43

AND RR.failure_code = 0

AND RR.mode = 'production'

AND RR.`ignore` = 0

AND RR.create_DTM > '2015-10-25';

Anyways, that should resolve your problem.

回答2:

The XML sample you have is malformed as it is missing the closing tag.

SQL Fiddle

回答3:

Even I encountered the same problem.

But If you are not convinced with using the "extracting the portion of the required xml", you can shift to MYSQL Server version 5.6 or above. That issue/bug has been fixed in the version 5.6 and above.

That should solve your problem.

来源：https://stackoverflow.com/questions/33395829/extractvalue-with-mysql