Parse specific XML tag value

Question

I have an SQLite 3.38.2 table that has XML tags in a column:

with cte(xml_tag) as (values 
  ('<Event time="Sat Apr 22 1:01:51.887" type="Debug" thread="2164: Main CIM worker thread" elapsed="1" function="Geodatabase.Cursor" code="EndCursor">'), 
  ('<Event time="Sat Apr 22 1:01:51.883" type="Debug" thread="2164: Main CIM worker thread" elapsed="23" function="Geodatabase.Cursor" code="EndCursor">'), 
  ('<Event time="Sat Apr 22 1:01:51.874" type="Debug" thread="2164: Main CIM worker thread" elapsed="456" function="Geodatabase.Cursor" code="EndCursor">'), 
  ('<Event time="Sat Apr 22 1:01:51.846" type="Debug" thread="2164: Main CIM worker thread" elapsed="7890" function="Geodatabase.Cursor" code="EndCursor">'))
select * from cte

db<>fiddle

I want to extract the values from the elapsed tag as numbers:

elapsed
-------
      1
     23
    456
   7890

How can I extract the values from that XML tag using a SQL query?

Use the facilities of the language you're making sql queries from. — Shawn, Apr 22 '23 at 06:23
Does [this](https://stackoverflow.com/q/38877856/5841306) help you answer the question ? — Barbaros Özhan, Apr 22 '23 at 07:46

score 1 · Accepted Answer · answered Apr 22 '23 at 08:05

Assuming that all xml_tag's values contain the substring 'elapsed=' (only once) you can use string functions:

SELECT SUBSTR(xml_tag, INSTR(xml_tag, 'elapsed=') + LENGTH('elapsed=') + 1) + 0 AS elapsed
FROM cte;

By adding 0 to the extracted string it is implicitly converted to a number.

See the demo.

Parse specific XML tag value

1 Answers1