Use of extractValue
set long 10000
create table test_xmltype_tab of xmltype;
insert into test_xmltype_tab values
(xmltype('<?xml version="1.0" encoding="UTF-8"?>
<ns1:activateWebUserResponse xmlns:ns1="http://fakecompany.co.uk/service/webuser/v6">
<siAudTransactionId xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
<valid>true</valid>
<webUser>
<username>myusername</username>
<emailAddress>donotreply@fakecompany.co.uk</emailAddress>
<activatedInd>true</activatedInd>
<webUserType>Client</webUserType>