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:

    <soap:Envelope xmlns:ones="http://onesource.gmtorque.com" xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">
      <soap:Header>
        <wsse:Security>
          <wsse:UsernameToken>
            <wsse:Username>username_prod</wsse:Username>
            <wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">password</wsse:Password>
          </wsse:UsernameToken>
        </wsse:Security>
      </soap:Header>
</soap:Envelope>

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

Solution

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), "</soap:Header>") + 14)),"</soap:Envelope>"), '//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.