Query XML in SQL to return list of nodes results in NULLS and empty dataset

Question

I've got an SQL table with one XML column, and I want to query the XML and get the id/@extension and also a list all observation/code. However it always returns empty results. I've isolated an example table with representative test data, see below

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

Here's what I've tried, but this just gives all empty NULL results. When I try to only query the single ID and text descriptions from each study the columns are NULL

-- 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

And when I try to query all templates per study the result is empty, even though the first has 2 templates and the second has 3 templates.

-- 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

I tried searching examples and asking ChatGPT, but I get the feeling the SQL Server examples of querying XML are not very representative of real-world xml examples, or they're missing something(?).

Answer 1

You need to add a default namespace using WITH XMLNAMESPACES, and you have doubled up the observation node.

You can also avoid referring to the root node with another CROSS APLY nodes.

WITH XMLNAMESPACES (DEFAULT 'urn:hl7-org:v3')
SELECT 
    ID,
    Title,
    doc.value('(id/@extension)[1]', 'nvarchar(100)') AS test_id,
    doc.value('(title/text())[1]', 'nvarchar(100)') AS test_title,
    doc.value('(text/text())[1]', 'nvarchar(100)') AS test_text,
    TheTemplate.value('(code/@code)[1]', 'nvarchar(1000)') as tmpl_code
from 
    study123 s
CROSS APPLY
    s.STXML.nodes('MeasurementDocument') x1(doc)
CROSS APPLY
    x1.doc.nodes('component/section/entry[@typeCode="DRIV"]/observation') AS AllTemplates(TheTemplate);

db<>fiddle