Google BigQuery - simple function to read XML tags with Regex

 1. Simple SQL function to read XML tags from string using regex

CREATE OR REPLACE FUNCTION `PROJECT.TEST.readXML`(temp1 STRING, tag STRING) RETURNS STRING AS (

REGEXP_SUBSTR(temp1,CONCAT("<",tag,">(.*?)<\\/",tag,">"))

);

 

2. Usage of function

DECLARE example STRING DEFAULT "'<TELEGRAM><Equipment>Welding machine</Equipment><State>SENT</State></TELEGRAM>'";

SELECT 

TEST.readXML(example,"Equipment") as Equipment,

TEST.readXML(example,"State") as State,

TEST.readXML(example,"TELEGRAM") as TELEGRAM