Snowflake中提取混合内容XML元素纯文本值（不含子元素）

当XML元素包含混合内容（自身纯文本+子元素）时，Snowflake的PARSE_XML会将元素的"$"属性解析为包含文本字符串和子元素对象的数组。比如你提供的Level3元素，Level3.value:"$"返回的数组同时包含纯文本和Level4子元素对象，现在可以通过以下方式单独提取Level3自身的纯文本，排除子元素内容。

解决方案 #

利用Snowflake的ARRAY_FILTER函数筛选数组中的字符串类型元素（纯文本节点），再通过ARRAY_TO_STRING将多个可能的文本节点拼接为单个字符串（处理文本分散在子元素前后的场景）。

修改后的完整SQL #

SELECT 
    InlineXML.src:"@"      as Level1_ElementName
    ,InlineXML.src:"@name" as Level1_NameAttributeValue
    
    ,Level2.value:"@"      as Level2_ElementName        
    ,Level2.value:"@name"  as Level2_NameAttributeValue
    
    ,Level3.value:"@"      as Level3_ElementName
    ,Level3.value:"@name"  as Level3_NameAttributeValue
    -- 提取Level3自身的纯文本，排除子元素
    ,ARRAY_TO_STRING(
        ARRAY_FILTER(TO_ARRAY(Level3.value:"$"), x -> TYPEOF(x) = 'STRING'),
        ''
    ) as Level3_ElementContent
    
    ,Level4.value:"@"      as Level4_ElementName
    ,Level4.value:"@name"  as Level4_NameAttributeValue
    ,Level4.value:"$"      as Level4_ElementContent
FROM (
    SELECT PARSE_XML('
<Level1 name="L1">
  <Level2 name="L1.1">
      <Level3 name="L1.1.1">
          Hello World! This is the value of the first level 3 element.
          <Level4 name="L1.1.1.1">
              My value is L1.1.1.1
          </Level4>
          <Level4 name="L1.1.1.2">
          </Level4>          
      </Level3>
      <Level3 name="L1.1.2">
          <Level4 name="L1.1.2.1">
          </Level4>
      </Level3>
  </Level2>
</Level1>'
) as src) InlineXML
,LATERAL flatten(TO_ARRAY(InlineXML.src:"$")) AS Level2
,LATERAL flatten(TO_ARRAY(Level2.value:"$")) AS Level3
,LATERAL flatten(TO_ARRAY(Level3.value:"$")) AS Level4
WHERE GET(Level2.value, '@')::string = 'Level2' 
AND GET(Level3.value, '@')::string = 'Level3'

关键逻辑说明 #

• TO_ARRAY(Level3.value:"$")：将Level3元素的所有子节点转换为数组，包含纯文本和子元素对象
• ARRAY_FILTER(..., x -> TYPEOF(x) = 'STRING')：过滤数组中类型为字符串的元素，仅保留Level3自身的纯文本节点，排除子元素对象
• ARRAY_TO_STRING(..., '')：如果Level3的纯文本分散在多个位置（比如子元素前后各有一段），将这些文本拼接成完整字符串；如果没有纯文本，则返回空字符串

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