Snowflake中移除三级内层标签，提取最内层嵌套XML标签求助

给定如下XML数据：

<Report>
<Inquiries>
  <Inquiry>
    <MemberCode>57</MemberCode>
    <MemberName>INC.</MemberName>
    <Date>30</Date>
  </Inquiry>
  <Inquiry>
    <MemberCode>53</MemberCode>
    <MemberName>CO.</MemberName>
    <Date>10</Date>
  </Inquiry>
  <Inquiry>
    <MemberCode>49</MemberCode>
    <MemberName>VE</MemberName>
    <Date>13</Date>
  </Inquiry>
  <Inquiry>
    <MemberCode>39</MemberCode>
    <MemberName>DESJA</MemberName>
    <Date>09</Date>
  </Inquiry>
  <Inquiry>
    <MemberCode>54</MemberCode>
    <MemberName>DA</MemberName>
    <Date>21</Date>
  </Inquiry>
  <Inquiry>
    <MemberCode>82</MemberCode>
    <MemberName>LOANS</MemberName>
    <Date>17</Date>
  </Inquiry>
</Inquiries>
</Report>

需要提取每个<Inquiry>下的所有子标签，示例输出如下：

<MemberCode>57</MemberCode>
<MemberName>INC.</MemberName>
<Date>30</Date>

当前使用的Snowflake查询如下，但查询返回的second_level和third_level值相同，无法正确获取目标标签：

SELECT  top 100
                KEY,
                b.VALUE
                ,b.this
                
                ,GET(XMLGET(XML_DATA,'Report'),'$')::string as first_level
                ,XMLGET(XMLGET(XML_DATA,'Report'),'Inquiries') as second_level
                ,XMLGET(XMLGET(XMLGET(XML_DATA,'Report'),'Inquiries'),'Inquiry') as third_level
                
               ,GET(b.VALUE,'@')::STRING as name
            ,CONCAT(XMLGET(b.VALUE,'MemberCode'):"$",
            XMLGET(b.VALUE,'MemberName'):"$",
            XMLGET(b.VALUE,'Date'):"$") AS MAIN_VALUE,
            
            from
                 FULL_XML,
                LATERAL FLATTEN(XMLGET(XMLGET(XML_DATA,'Report'),'Inquiries'):"$") b;

问题根源：

• XMLGET默认仅返回匹配到的第一个节点，嵌套调用时只会拿到第一个<Inquiry>，导致second_level和third_level结果异常。
• 原查询的FLATTEN逻辑未明确针对<Inquiry>节点展开，导致节点层级处理混乱。

调整后的查询可正确提取目标内容：

SELECT TOP 100
    -- 提取单个字段值
    XMLGET(inquiry.value, 'MemberCode'):"$"::STRING AS MEMBER_CODE,
    XMLGET(inquiry.value, 'MemberName'):"$"::STRING AS MEMBER_NAME,
    XMLGET(inquiry.value, 'Date'):"$"::STRING AS DATE,
    -- 拼接成完整的子标签XML格式
    '<MemberCode>' || XMLGET(inquiry.value, 'MemberCode'):"$" || '</MemberCode>' || CHAR(10) ||
    '<MemberName>' || XMLGET(inquiry.value, 'MemberName'):"$" || '</MemberName>' || CHAR(10) ||
    '<Date>' || XMLGET(inquiry.value, 'Date'):"$" || '</Date>' AS FULL_INQUIRY_TAGS
FROM
    FULL_XML,
    -- 展开Report下的Inquiries节点
    LATERAL FLATTEN(XMLGET(XML_DATA, 'Report'):"$") AS inquiries,
    -- 展开Inquiries下的所有Inquiry节点
    LATERAL FLATTEN(inquiries.value:"$") AS inquiry
WHERE
    -- 过滤确保只处理目标节点
    GET(inquiries.value, '@')::STRING = 'Inquiries'
    AND GET(inquiry.value, '@')::STRING = 'Inquiry';

关键说明 #

• 通过两次LATERAL FLATTEN分层展开节点：先处理<Report>下的<Inquiries>，再展开<Inquiries>下的所有<Inquiry>。
• WHERE子句过滤节点名称，避免无关节点干扰结果。
• 可根据需求选择提取单个字段，或拼接成完整的子标签XML结构。

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