Regex for extracting a xml node from a xml feild

[ssekar]

Hello Expert,

Please help me arrive on a regex to extract a xml node in a xml field.

I have a field value like below

<Reponse status="failure">
 <messages>
        <message id="Payload">
            <UpdateAccountRq>
                <AccountId>123465</AccountId>
                <NewStatus>Active</NewStatus>
            </UpdateAccountRq>
        </message>
    </messages>
</Reponse>

And I want to extract the below xml node and display it in a separate field.

 <UpdateAccountRq>

        <AccountId>123465</AccountId>

       <NewStatus>Active</NewStatus>

</UpdateAccountRq>

I tried many ways, but nothing works.

Attempt 1:  rex field=Action "messages>(?<Payload>.+)<\/messages" | table Action, Payload

Attempt 2:  rex field=Action "\<message id=\"Payload\">(?<Payload>[^<\/message]+)" | table Action, Payload

Please help. Thanks

[ssekar]

Thanks for the help. All three are good answers. 

[ITWhisperer]

| rex field=Action "(?ms)messages>(?<Payload>.+)<\/messages"

[richgalloway · Accepted solution]

@yuanliu is correct, but for a pure rex solution, try this regex

\<message id=\"Payload\">(?<Payload>[\s\S]+?)\<\/message

[yuanliu]

If your document is conformant XML, you should use builtin commands such as spath; regex will be difficult to maintain if the format, or syntax, or schema change.  In your case, assuming you have a field named "xml", you can do

| spath input=xml path=Reponse.messages.message.UpdateAccountRq

Your sample data gives the following

Reponse.messages.message.UpdateAccountRq	xml
<AccountId>123465</AccountId> <NewStatus>Active</NewStatus>	<Reponse status="failure"> <messages> <message id="Payload"> <UpdateAccountRq> <AccountId>123465</AccountId> <NewStatus>Active</NewStatus> </UpdateAccountRq> </message> </messages> </Reponse>