SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE study123 (
    ID integer,
    Title varchar(25),
    STXML XML
);

INSERT INTO study123 (
    ID,
    Title,
    STXML
) VALUES
(1, 'ST_Cardio', '<MeasurementDocument xmlns="urn:hl7-org:v3" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <id extension="68c65a01-2188-486c-adde-a854453ff817" />
  <code code="ST_Cardio" displayName="CardioLife60+" />
  <title>CardioLife 60+</title>
  <text>Prospective validation of CardioLife algorithms age 60+</text>
  <component>
    <section>
      <entry typeCode="DRIV">
        <observation classCode="OBS" moodCode="CRT">
          <code code="TMPL_1120000364" displayName="CV TEE/CARDIO DAILYLOG" />
        </observation>
      </entry>
      <entry typeCode="DRIV">
        <observation classCode="OBS" moodCode="CRT">
          <code code="TMPL_1120000365" displayName="CV TEE/CARDIO VITAL SIGNS CHECKLIST" />
        </observation>
      </entry>
    </section>
  </component>
</MeasurementDocument>'),
(2, 'ST_Cardio', '<MeasurementDocument xmlns="urn:hl7-org:v3" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <id extension="a3be767f-1315-4e2b-ab4e-11a238d7f9a1" />
  <code code="ST_Diabetics" displayName="DiabeticsObs" />
  <title>Diabetics Observation 2023-2024</title>
  <text>Diabetics Observation 2023-2024 Extended II</text>
  <component>
    <section>
      <entry typeCode="DRIV">
        <observation classCode="OBS" moodCode="CRT">
          <code code="TMPL_1173900045" displayName="CVR PHYSIC DIABETICS" />
        </observation>
      </entry>
      <entry typeCode="DRIV">
        <observation classCode="OBS" moodCode="CRT">
          <code code="TMPL_1177400741" displayName="CVR DIABETICS OBSERVATIONWEEK" />
        </observation>
      </entry>
      <entry typeCode="DRIV">
        <observation classCode="OBS" moodCode="CRT">
          <code code="TMPL_1189600034" displayName="CVR DIABETICS REVALIDATION CHECKLIST" />
        </observation>
      </entry>
    </section>
  </component>
</MeasurementDocument>
');

2 rows affected

ID	Title	test_id	test_title	test_text	tmpl_code
1	ST_Cardio	68c65a01-2188-486c-adde-a854453ff817	CardioLife 60+	Prospective validation of CardioLife algorithms age 60+	TMPL_1120000364
1	ST_Cardio	68c65a01-2188-486c-adde-a854453ff817	CardioLife 60+	Prospective validation of CardioLife algorithms age 60+	TMPL_1120000365
2	ST_Cardio	a3be767f-1315-4e2b-ab4e-11a238d7f9a1	Diabetics Observation 2023-2024	Diabetics Observation 2023-2024 Extended II	TMPL_1173900045
2	ST_Cardio	a3be767f-1315-4e2b-ab4e-11a238d7f9a1	Diabetics Observation 2023-2024	Diabetics Observation 2023-2024 Extended II	TMPL_1177400741
2	ST_Cardio	a3be767f-1315-4e2b-ab4e-11a238d7f9a1	Diabetics Observation 2023-2024	Diabetics Observation 2023-2024 Extended II	TMPL_1189600034