在SQL中查询SQL以返回NULLS和空数据集的 node 列表

我有一个带有一个ML列的SQL表，我想查询该ML并获取id/@extension以及所有observation/code的列表.然而，它总是返回空结果.我已经分离了一个具有代表性测试数据的示例表，请参阅下文

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_Diabetics', '<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>
');

这是我try 过的方法，但这只会给出所有空空的结果.当我try 仅查询每个研究的单个ID和文本描述时，列为空

-- test columns are all NULL(?)
SELECT 
    ID,
    Title,
    STXML,
    STXML.value('(/MeasurementDocument/id/@extension)[1]', 'nvarchar(100)') AS test_id,
    STXML.value('(/MeasurementDocument/title)[1]', 'nvarchar(100)') AS test_title,
    STXML.value('(/MeasurementDocument/text)[1]', 'nvarchar(100)') AS test_text
FROM 
    study123

当我try 查询每个研究的所有模板时，结果是空的，尽管第一个有2个模板，第二个有3个模板.

-- no records, empty result
select * from 
(select 
    ID,
    Title,
    TheTemplate.value('(./observation/code/@code)[1]', 'varchar(1000)') as tmpl_code
from 
    study123 CROSS APPLY
    STXML.nodes('MeasurementDocument/component/section/entry[@typeCode="DRIV"]/observation') AS AllTemplates(TheTemplate)
)  as Result

我try 搜索示例并询问ChatGPT，但我感觉查询ML的SQL服务器示例并不能很好地代表现实世界的html示例，或者它们缺少一些东西(？).