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