在Databricks中如何将字符串类型的XML列解析为多个SQL列

嘿，我来帮你搞定这个问题！在Databricks里把字符串格式的结构化数据解析成SQL多列其实挺简单的，不过我注意到你提供的示例数据看起来更像JSON而非XML，我会分别针对两种情况给出解决方案，先结合你给出的样本结构来一步步操作。

一、如果你的列实际是JSON格式字符串（匹配你的样本数据） #

Databricks SQL提供了get_json_object和from_json两种常用方法来解析JSON字符串，前者适合提取单个字段，后者适合批量解析嵌套结构，效率更高。

方法1：使用get_json_object快速提取字段 #

这种方法适合快速提取少量字段，语法简单直接：

-- 创建临时视图存储解析后的结果
CREATE OR REPLACE TEMP VIEW ParsedJSONData AS
SELECT
  -- 提取Response中的IdNumber
  get_json_object(XMLData, '$.Response.IdNumber') AS IDNumber,
  -- 提取Subject下的FirstName
  get_json_object(XMLData, '$.Response.SearchKeys.Subject.FirstName') AS FirstName,
  get_json_object(XMLData, '$.Response.SearchKeys.Subject.MiddleName') AS MiddleName,
  -- 样本中的Surname对应你期望输出的LastName
  get_json_object(XMLData, '$.Response.SearchKeys.Subject.Surname') AS LastName,
  -- 将时间戳格式的DateOfBirth转换为日期类型
  to_date(get_json_object(XMLData, '$.Response.SearchKeys.Subject.DateOfBirth')) AS DateOfBirth,
  get_json_object(XMLData, '$.Response.SearchKeys.Address.AddressLine1') AS AddressLine1,
  get_json_object(XMLData, '$.Response.SearchKeys.Phone') AS Phone
FROM default.SampleData;

-- 查询解析后的结果
SELECT * FROM ParsedJSONData;

方法2：使用from_json定义完整Schema解析（推荐用于大数据量） #

如果你的数据量较大，或者需要解析的字段较多，推荐先定义完整的JSON Schema，再批量解析：

-- 定义匹配样本结构的JSON Schema
DECLARE json_schema STRING = '''
{
  "type": "struct",
  "fields": [
    {"name": "Status", "type": {"type": "struct", "fields": [{"name": "Code", "type": "string"}, {"name": "Message", "type": "string"}, {"name": "Errors", "type": "string"}]}},
    {"name": "RequestReference", "type": "string"},
    {"name": "Response", "type": {"type": "struct", "fields": [
      {"name": "IdNumber", "type": "string"},
      {"name": "SearchKeys", "type": {"type": "struct", "fields": [
        {"name": "Subject", "type": {"type": "struct", "fields": [
          {"name": "FirstName", "type": "string"},
          {"name": "MiddleName", "type": "string"},
          {"name": "Surname", "type": "string"},
          {"name": "DateOfBirth", "type": "timestamp"},
          {"name": "Gender", "type": "string"}
        ]}},
        {"name": "Address", "type": {"type": "struct", "fields": [
          {"name": "AddressLine1", "type": "string"},
          {"name": "AddressLine2", "type": "string"},
          {"name": "AddressLine3", "type": "string"},
          {"name": "AddressLine4", "type": "string"},
          {"name": "AddressLine5", "type": "string"},
          {"name": "Postcode", "type": "string"},
          {"name": "Country", "type": "string"}
        ]}},
        {"name": "Phone", "type": "string"},
        {"name": "DrivingLicence", "type": "string"},
        {"name": "Bank", "type": "string"},
        {"name": "ConsentFlag", "type": "boolean"}
      ]}}
    ]}}
  ]
}
''';

-- 解析JSON并提取目标字段
SELECT
  parsed_data.Response.IdNumber AS IDNumber,
  parsed_data.Response.SearchKeys.Subject.FirstName AS FirstName,
  parsed_data.Response.SearchKeys.Subject.MiddleName AS MiddleName,
  parsed_data.Response.SearchKeys.Subject.Surname AS LastName,
  to_date(parsed_data.Response.SearchKeys.Subject.DateOfBirth) AS DateOfBirth,
  parsed_data.Response.SearchKeys.Address.AddressLine1 AS AddressLine1,
  parsed_data.Response.SearchKeys.Phone AS Phone
FROM default.SampleData
CROSS JOIN from_json(XMLData, json_schema) AS parsed_data;

二、如果你的列确实是XML格式字符串 #

如果你的数据真的是XML结构（比如类似下面的格式），可以使用Databricks SQL的xpath_string函数来提取字段：

<Root>
  <Status>
    <Code>Ok</Code>
    <Message/>
    <Errors/>
  </Status>
  <RequestReference/>
  <Response>
    <IdNumber>295</IdNumber>
    <SearchKeys>
      <Subject>
        <FirstName>John</FirstName>
        <MiddleName/>
        <Surname>Ross</Surname>
        <DateOfBirth>1900-01-31</DateOfBirth>
        <Gender>M</Gender>
      </Subject>
      <Address>
        <AddressLine1>123 old street</AddressLine1>
      </Address>
      <Phone/>
    </SearchKeys>
  </Response>
</Root>

对应的解析SQL如下：

CREATE OR REPLACE TEMP VIEW ParsedXMLData AS
SELECT
  xpath_string(XMLData, '/Root/Response/IdNumber') AS IDNumber,
  xpath_string(XMLData, '/Root/Response/SearchKeys/Subject/FirstName') AS FirstName,
  xpath_string(XMLData, '/Root/Response/SearchKeys/Subject/MiddleName') AS MiddleName,
  xpath_string(XMLData, '/Root/Response/SearchKeys/Subject/Surname') AS LastName,
  to_date(xpath_string(XMLData, '/Root/Response/SearchKeys/Subject/DateOfBirth')) AS DateOfBirth,
  xpath_string(XMLData, '/Root/Response/SearchKeys/Address/AddressLine1') AS AddressLine1,
  xpath_string(XMLData, '/Root/Response/SearchKeys/Phone') AS Phone
FROM default.SampleData;

SELECT * FROM ParsedXMLData;

注意事项 #

• 如果你使用from_json，Schema需要严格匹配你的JSON结构，字段类型要对应，否则会解析失败或返回NULL；
• 处理日期类型时，记得用to_date或to_timestamp转换为SQL支持的日期格式；
• 对于XML解析，要确保XPath表达式准确匹配节点路径。

备注：内容来源于stack exchange，提问作者John Bryan