MySQL的ExtractValue [英] ExtractValue with MySQL

问题描述

我正在尝试从XML输出中提取用户名值，该XML输出已加载到数据库列(file_output)中.我的查询返回一个空值，并且没有按我期望的那样执行.感谢您的帮助.

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输出:

    <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>

查询:

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';

预期值为username_prod

Expected value is username_prod

推荐答案

您遇到的问题是MySQL的错误.您尝试解析的XML很可能太长.我非常确定它与以下错误有关: https://bugs.mysql.com/bug.php?id = 62429 .要解决此错误，您可以提取要导航的XML部分，也可以使用另一种方法提取要查找的值.无论如何，我选择的解决方案是提取您试图通过XPath导航的XML，并使用您提供的XPath查询提取结果.

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.

这篇关于MySQL的ExtractValue的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！