Spark SQL如何将DataFrame二进制列转为XML并解决Databricks运行报错问题

Databricks Spark SQL 不支持SQL Server专属的原生XML类型及.value()节点操作语法，你写的CAST(FORM_DATA as XML).value(...)属于SQL Server独有语法，无法在Spark环境中解析，因此触发语法报错。

步骤1：将binary类型的XML列解码为字符串 #

首先需要把二进制存储的XML内容按对应编码解码为字符串格式，Spark的XML解析函数仅支持接收字符串类型的XML输入：

from pyspark.sql.functions import decode

# 读取源文件
sourceDf = spark.read.csv(sourceFilePath, sep=',', header=True, inferSchema=True)
# 按实际编码替换第二个参数，常用编码为UTF-8、GBK等
sourceDf = sourceDf.withColumn("FORM_DATA_STR", decode("FORM_DATA", "UTF-8"))
# 创建临时表
sourceDf.createOrReplaceTempView("sourceTable")

步骤2：使用Spark内置xpath函数解析XML内容 #

你原来的逻辑是取XML根节点的文本内容，可以用xpath_string函数实现相同效果：

%sql
SELECT ID 
  ,ORIGINATOR_ID
  ,INCIDENT_NUMBER 
  ,ATTACHMENT_TYPE
  ,FORM_NAME
  ,FORM_DATA
  ,xpath_string(FORM_DATA_STR, '.') AS form_data_content
  ,START_DATE
  ,END_DATE
  ,OPERATOR_ID 
FROM sourceTable

如果需要提取XML中特定节点的值，直接修改第二个参数的xpath表达式即可，例如提取根节点下user子节点的name属性，写法为xpath_string(FORM_DATA_STR, '/root/user/@name')。

如果解码后出现乱码，需要确认原binary内容的实际编码，替换decode函数中的编码参数即可。

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