Snowflake中提取XML的Name节点返回空值，求原因及解决方法

我尝试从以下XML片段中选取name节点，但返回结果为空，请问原因是什么？

目标XML片段（author节点） #

<author>
  <time/>
  <assignedEntity>
     <representedOrganization>
        <id extension="194123980" root="1.3.6.1.4.1.519.1"/>
        <name>Physicians Total Care, Inc.</name>
        <assignedEntity>
           <assignedOrganization>
              <assignedEntity>
                 <assignedOrganization>
                    <id extension="194123980" root="1.3.6.1.4.1.519.1"/>
                    <name>Physicians Total Care, Inc.</name>
                 </assignedOrganization>
                 <performance>
                    <actDefinition>
                       <code code="C73607" codeSystem="2.16.840.1.113883.3.26.1.1" displayName="relabel"/>
                    </actDefinition>
                 </performance>
              </assignedEntity>
           </assignedOrganization>
        </assignedEntity>
     </representedOrganization>
  </assignedEntity>
</author>

我使用的Snowflake查询代码 #

SELECT 
  XMLGET(name.value, 'name'):"$"::string  name
FROM 
  dailymed_xml,
  LATERAL FLATTEN(GET(src_xml, '$')) author,
  LATERAL FLATTEN(GET(author.value, '$')) time,
  LATERAL FLATTEN(GET(time.value, '$')) assignedEntity,
  LATERAL FLATTEN(GET(assignedEntity.value, '$')) representedOrganization,
  LATERAL FLATTEN(GET(representedOrganization.value, '$')) id,
  LATERAL FLATTEN(GET(id.value, '$')) name
  
WHERE 
  GET(author.value, '@') = 'author' AND 
  GET(time.value, '@') = 'time' AND
  GET(assignedEntity.value, '@') = 'assignedEntity' AND 
  GET(representedOrganization.value, '@') = 'representedOrganization' AND 
  GET(id.value, '@') = 'id' AND 
  GET(name.value, '@') = 'name'; 

原因分析 #

1. 层级遍历错误：name节点和id节点是同级关系，都属于representedOrganization的子节点，而不是id的子节点。原查询里从id.value中FLATTEN找name，相当于在id节点下找子节点，自然找不到任何内容。
2. XMLGET逻辑冗余：即使层级正确，原代码里XMLGET(name.value, 'name')也是错误的——因为name.value本身就是name节点对象，不需要再用XMLGET去查找name节点，直接取其值即可。

修正后的查询代码 #

方式1：直接获取representedOrganization下的name节点

SELECT 
  XMLGET(representedOrganization.value, 'name'):"$"::string AS name
FROM 
  dailymed_xml,
  LATERAL FLATTEN(GET(src_xml, '$')) author
WHERE GET(author.value, '@') = 'author',
  LATERAL FLATTEN(GET(author.value, '$')) assignedEntity
WHERE GET(assignedEntity.value, '@') = 'assignedEntity',
  LATERAL FLATTEN(GET(assignedEntity.value, '$')) representedOrganization
WHERE GET(representedOrganization.value, '@') = 'representedOrganization';

方式2：递归获取所有层级的name节点（含嵌套的子节点）

如果需要提取XML中所有层级的name节点，包括嵌套在子assignedOrganization里的内容，可以用递归遍历：

WITH RECURSIVE xml_traversal AS (
  SELECT 
    author.value AS node,
    GET(author.value, '@') AS node_name
  FROM dailymed_xml,
    LATERAL FLATTEN(GET(src_xml, '$')) author
  WHERE GET(author.value, '@') = 'author'
  
  UNION ALL
  
  SELECT 
    child.value AS node,
    GET(child.value, '@') AS node_name
  FROM xml_traversal,
    LATERAL FLATTEN(GET(xml_traversal.node, '$')) child
)
SELECT 
  node:"$"::string AS name
FROM xml_traversal
WHERE node_name = 'name';

内容的提问来源于stack exchange，提问作者Jeff