Snowflake中从JSON内嵌XML提取IATA_Number返回NULL的解决方法

问题原因 #

你的查询返回NULL主要有两个核心原因：

1. XML命名空间未处理：目标元素IATA_Number所在的OrderReshopRQ节点带有默认命名空间http://www.iata.org/IATA/EDIST/2017.2，同时外层的Envelope和Body节点带有s前缀的命名空间，直接调用xmlget会因为命名空间不匹配无法定位元素。
2. 节点层级定位错误：IATA_Number并非XML根节点的直接子元素，它嵌套在Envelope > Body > OrderReshopRQ > Party > Sender > TravelAgencySender多层节点下，需要逐层遍历才能定位。

修正后的查询代码 #

CREATE TABLE sample_xml(id INT, json_data VARIANT);

INSERT INTO sample_xml(id, json_data)
select  2, parse_json($$
  {
"typ": "info",
  "hostname": "lee007",
  "platform": "lee007",
  "side": "b",
  "requestObject": "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\
<s:Envelope xmlns:s=\"http://schemas.xmlsoap.org/soap/envelope/\"><s:Body xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\"><OrderReshopRQ Version=\"17.2\" xmlns=\"http://www.iata.org/IATA/EDIST/2017.2\"><Document><Name>BA</Name></Document><Party><Sender><TravelAgencySender><IATA_Number>33895934</IATA_Number><AgencyID>FAREPORTAL</AgencyID></TravelAgencySender></Sender><Participants xsi:nil=\"true\"/><Recipient xsi:nil=\"true\"/></Party><Query><OrderID>TUHVO8</OrderID><Reshop><OrderServicing><Delete><OrderItem OrderItemID=\"TUHVO8\"/></Delete></OrderServicing></Reshop></Query></OrderReshopRQ></s:Body></s:Envelope>",
  "ip": "192.000.000.0",
  "level": "INFO",
  "sheryname": "taltech"
}
$$);

WITH xml_parsed AS (
    SELECT 
        id,
        PARSE_XML(json_data:requestObject) AS xml_data
    FROM sample_xml
)
SELECT 
    id,
    -- 逐层定位节点，同时指定对应命名空间
    xmlget(
        xmlget(
            xmlget(
                xmlget(
                    xmlget(
                        xmlget(xml_data, 'Envelope', 'http://schemas.xmlsoap.org/soap/envelope/'),
                        'Body', 'http://schemas.xmlsoap.org/soap/envelope/'
                    ),
                    'OrderReshopRQ', 'http://www.iata.org/IATA/EDIST/2017.2'
                ),
                'Party', 'http://www.iata.org/IATA/EDIST/2017.2'
            ),
            'Sender', 'http://www.iata.org/IATA/EDIST/2017.2'
        ),
        'TravelAgencySender', 'http://www.iata.org/IATA/EDIST/2017.2'
    ):IATA_Number:"$"::STRING AS iata_number
FROM xml_parsed;

代码解释 #

1. 保留PARSE_XML结果：不需要用TO_OBJECT转换XML对象，直接使用PARSE_XML返回的XML类型处理更准确。
2. 指定命名空间参数：xmlget函数的第三个参数用于指定元素的命名空间，确保匹配XML中定义的命名空间：
   • Envelope和Body使用s前缀对应的命名空间http://schemas.xmlsoap.org/soap/envelope/
   • 从OrderReshopRQ开始的节点使用默认命名空间http://www.iata.org/IATA/EDIST/2017.2
3. 逐层遍历节点：从根节点Envelope开始，依次定位到Body、OrderReshopRQ、Party、Sender、TravelAgencySender，最后提取IATA_Number的文本值。

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