时间戳

首页 > 解决方案 > 来自 json 字段的 XML 解析问题

snowflake-cloud-data-platform - 来自 json 字段的 XML 解析问题

问题描述

我有一个 json 列，其中包含一个我正在尝试解析的 XML 字段

{
  "guid": "ba410633-d191-deab-fe63-23f732c517aa",
  "id": 1847510,
  "request":<?xml version='1.0' encoding='UTF-8'?><ConnectRequest xmlns='http://www.acme.com/NetConnect' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xsi:schemaLocation='http://www.acme.com/API'><EAI>QWE</EAI><DBHost>TEST</DBHost><ReferenceId>aa</ReferenceId><Request xmlns='http://www.acme.com' version='1.0'><Products><PreciseIDServer><XMLVersion>5.0</XMLVersion><Subscriber><Preamble>ABC</Preamble><OpInitials>BCD</OpInitials><SubCode>2436170</SubCode></Subscriber></PreciseIDServer></Products></Request></ConnectRequest>"
}

XML字段是这样的

<?xml version='1.0' encoding='UTF-8'?>
<ConnectRequest xmlns='http://www.acme.com/NetConnect' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xsi:schemaLocation='http://www.acme.com/API'>
  <EAI>QWE</EAI>
  <DBHost>TEST</DBHost>
  <ReferenceId>aa</ReferenceId>
  <Request xmlns='http://www.acme.com' version='1.0'>
    <Products>
      <PreciseIDServer>
        <XMLVersion>5.0</XMLVersion>
        <Subscriber>
          <Preamble>ABC</Preamble>
          <OpInitials>BCD</OpInitials>
          <SubCode>2436170</SubCode>
        </Subscriber>
      </PreciseIDServer>
    </Products>
  </Request>
</ConnectRequest>

我尝试了以下但没有运气，它一直返回 null

select xmlget(request, 'DBHost'):"$" as DBHost
from (select json_field:request::variant request from table)

是数据类型问题吗？我能够使用相同的 xmlget 从具有带有 xml 数据的变体列的表中解析另一列。

标签： snowflake-cloud-data-platform

解决方案

with 
XML(DBHOST, PRODUCTS, PRECISEIDSERVER, SUBCODE) as
(
select  get(xmlget(parse_xml(json_field:request::string), 'DBHost'), '$')::string as DBHOST,
        get(xmlget(parse_xml(json_field:request::string), 'Request'), '$')::string as PRODUCTS,
        get(xmlget(parse_xml(PRODUCTS::string), 'PreciseIDServer'), '$')::string as PreciseIDServer,
        get(xmlget(parse_json(PreciseIDServer)[1], 'SubCode'), '$')::int     as SourceCode
from MY_TABLE
)
select DBHOST, SUBCODE from XML;

XMLGET 将在仅包含 XML 的变体列上以这种方式工作。在这种情况下，XML 不是变体。JSON 是变体，XML 是属性之一。它需要被视为一个字符串属性，然后作为 XML 进行操作。为此，您需要使用 ::string 将其从 JSON 属性转换。然后，您需要使用 PARSE_XML 将字符串转换为变体。最后，您可以对 PARSE_XML 产生的变体使用 xmlget 并使用带有 $ 最终参数的 GET 来指示您只需要该值，而不是开始和结束标记。最后的 ::string 将最终结果转换为字符串，这样它就不会被双引号括起来。

注意 - 示例 xml 中缺少一个关闭标记，在使用 PARSE_XML 函数时失败。我添加了关闭标签进行测试。XML 必须有效才能从字符串转换为 XML 变体。

编辑：更新为 CTE 以添加对 SubCode 的解析。要获得嵌套了几层深的 XML 节点，一个好的方法是逐步在具有 CTE 的列中选择您想要的那些。

推荐阅读