Quick suggestion
😎
use mysql1
;WITH XMLNAMESPACES('http://schemas.openehr.org/v1' as bp,'http://www.w3.org/2001/XMLSchema-instance' as xsi,'OBSERVATION' as type)
select * from (
select
m.c.value('(/bp:magnitude/text())','int') as systolisch
from
[schema_name].[table_name] [alias]
cross apply [alias].[xml column name].nodes('/bp:content/bp:data/bp:events/bp:data/bp:items/bp:value') as m(c))m;