0

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

Tone
  • 765
  • 10
  • 24
  • 51

3 Answers3

0

The XML sample you have is malformed as it is missing the closing </soap:Envelope> tag.

SQL Fiddle

JRD
  • 1,957
  • 1
  • 12
  • 17
  • I did not include the entire XML file as it exists in the table. All tags are closed. – Tone Oct 28 '15 at 16:28
  • Does the fiddle example run successfully against your db? Does it work if you use the exact XML as in your question? – JRD Oct 28 '15 at 16:33
0

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.

Win T
  • 66
  • 1
  • 2
0

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.

Aswin
  • 71
  • 1
  • 1
  • 7