Datastudio : How to extract value inside HTML format using Presto (MySQL)-CodePudding

I am looking to extract the value inside any HTML format (e.g., value ).

Here's is the example of HTML: <payment><no_code>88888</no_code><signature>fbf2c1e</signature><customer_no>4150</customer_no><amount>80</amount><admin>7</admin><total>87</total><transaction_code>0000894</transaction_code></payment>

The output I am trying to fetch is the value inside the admin, which is 7.

Any help would be appreciated!

CodePudding user response：

We can use REGEXP_EXTRACT() here:

SELECT REGEXP_EXTRACT(html_string, '<admin>(.*?)</admin>', 1) AS admin
FROM yourTable;

CodePudding user response：

use ExtractValue xpath, eg

SELECT ExtractValue(
'<payment><no_code>88888</no_code><signature>fbf2c1e</signature><customer_no>4150</customer_no><amount>80</amount><admin>7</admin><total>87</total><transaction_code>0000894</transaction_code></payment>',
 '/payment/admin'
)

=> 7