Snowflake中带子节点的XML解析问题求助

原始XML数据 #

<row id="C2786">
  <custID>C2786</custID>
  <name>NEW Pensions Ltd </name>
  <RegName>NEW Pensions Ltd</RegName>
  <ADDress>22 Broomfield Road</ADDress>
  <ADDress m="2">Birmingham EAST</ADDress>
  <ADDress m="3">Birmingham</ADDress>
  <ADDress m="3">B1 1AY</ADDress>
  <ADDress m="3">United Kingdom</ADDress>
  <PhoneNo m="2">+44 20 6776577</PhoneNo>
  <PhoneNo m="2" s="2">+44 20 4576787</PhoneNo>
  <CustType>Corporate</CustType>
</row>

期望输出 #

输出1：客户地址信息表 #

输出2：客户电话信息表 #

当前错误代码 #

Select
     XMLGET( XMLrecord, 'custID' ):"$"::STRING AS CustID,
     XMLGET( XMLrecord, 'name' ):"$"::STRING AS CustName,
     XMLGET( XMLrecord, 'Address' ):"$"::STRING AS AddressLine1,
     XMLGET( XMLrecord, 'Address' ):"$m=2"::STRING AS AddressLine2,
     XMLGET( XMLrecord, 'Address' ):"$m=3"::STRING AS City,
     XMLGET( XMLrecord, 'Address' ):"$m-4"::STRING AS PostCode,
     XMLGET( XMLrecord, 'Address' ):"$m=5"::STRING AS Country,
  
  FROM XMLTable;

  Select
       XMLGET( XMLrecord, 'custID' ):"$"::STRING AS CustID,
       XMLGET( XMLrecord, 'name' ):"$"::STRING AS CustName,
       XMLGET( XMLrecord, 'PhoneNo' ):"$m=2"::STRING AS Landline,
       XMLGET( XMLrecord, 'PhoneNo' ):"$m=2 s=2"::STRING AS Mobile,

    FROM XMLTable;

问题分析与修正代码 #

问题点说明 #

1. XMLGET局限性：XMLGET仅返回第一个匹配的XML元素，无法获取同标签下的多个元素（如多个<ADDress>、<PhoneNo>），需用XMLTABLE展开元素并结合属性筛选。
2. 属性引用语法错误：获取XML属性需用@属性名，而非$m=2这类错误写法。
3. 地址元素顺序处理：多个<ADDress>标签中m="3"的元素需按出现顺序映射到Addressline3、PostCode、Country。

修正后的地址信息查询 #

WITH parsed_address AS (
    SELECT
        XMLGET(xmlrecord, 'custID'):"$"::STRING AS CustID,
        TRIM(XMLGET(xmlrecord, 'name'):"$"::STRING) AS CustName,
        x.value:"$"::STRING AS addr_value,
        x.value:"@m"::STRING AS addr_m,
        ROW_NUMBER() OVER (PARTITION BY XMLGET(xmlrecord, 'custID'):"$"::STRING, x.value:"@m" ORDER BY x.index) AS addr_seq
    FROM XMLTable,
         LATERAL XMLTABLE('//ADDress' PASSING xmlrecord 
                         COLUMNS 
                             value XML PATH '.',
                             index INT PATH '@index'
                         ) x
)
SELECT
    CustID,
    CustName,
    MAX(CASE WHEN addr_m IS NULL THEN addr_value END) AS Addressline1,
    MAX(CASE WHEN addr_m = '2' THEN addr_value END) AS Addressline2,
    MAX(CASE WHEN addr_m = '3' AND addr_seq = 1 THEN addr_value END) AS Addressline3,
    MAX(CASE WHEN addr_m = '3' AND addr_seq = 2 THEN addr_value END) AS PostCode,
    MAX(CASE WHEN addr_m = '3' AND addr_seq = 3 THEN addr_value END) AS Country
FROM parsed_address
GROUP BY CustID, CustName;

修正后的电话信息查询 #

SELECT
    XMLGET(xmlrecord, 'custID'):"$"::STRING AS CustID,
    TRIM(XMLGET(xmlrecord, 'name'):"$"::STRING) AS CustName,
    MAX(CASE WHEN x.value:"@s" IS NULL THEN x.value:"$"::STRING END) AS "Phone No1",
    MAX(CASE WHEN x.value:"@s" = '2' THEN x.value:"$"::STRING END) AS "Phone No 2"
FROM XMLTable,
     LATERAL XMLTABLE('//PhoneNo' PASSING xmlrecord 
                     COLUMNS 
                         value XML PATH '.'
                     ) x
GROUP BY CustID, CustName;

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