azure - 我想为在 MS Azure Synapse 中存储为 varchar(max) 的匹配标记提取 XML 值
问题描述
我是新 Azure 之前使用 SAS,现在我们正在转向 azure synapse 在当前环境中,我想提取存储在 C 列(varcharmax)中的 XML 标记值作为变量。[数据集][1][1]:https://i.stack.imgur.com/tbSIF.png 下面的XML保存在C列(PKDATA)
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ns2:DataSet xmlns:ns2="http://www.test.com/t/cn/el">
<EnumObject>
<name>Inpatient</name>
<value>262784067</value>
<radiobutton>false</radiobutton>
</EnumObject>
<StringObject>
<name>xxx</name>
<prompt></prompt>
<value>/widget.jsp</value>
<width>99</width>
</StringObject>
</ns2:DataSet>
如果名称是住院病人,那么 262784067 作为住院病人类型
输出
| 一个 | 乙| 住院类型 | | 11212 | 2587140 | 262784067 |
我使用以下代码从 dbo.extdata 中选择 a,b, pkdata.value('/EnumObject/name') 作为住院类型
我收到以下错误找不到列“pkkddata”或用户定义的函数或聚合“pkdata.value”,或者名称不明确。
我尝试使用以下查询,但给了我错误消息 104220,级别 16,状态 1,第 26 行找不到数据类型“xml”。SELECT a,b,(pkdata).value('(/EnumObject/name/text())[1]', 'varchar(100)') FROM [dbo].extdata CROSS APPLY (SELECT CAST(pkdata AS xml) ) AS x(pkdata)
使用以下代码时出现以下错误 XMLDT 方法“节点”只能在 xml 类型的列上调用。我尝试使用以下内容,但在通过 select x.* from [dbo].[EXTDATA] rt cross join xmltable( '/EnumObject/name' passing xmltype(rt.pkdata) columns name number path 'name/@值') x
不知道如何进行
Azure SQL 版本 Microsoft Azure SQL 数据仓库 - 10.0.16003.0 Apr 28 2021 04:55:16 版权所有 (c) Microsoft Corporation
解决方案
Azure Synapse Analytics,特别是专用的 SQL 池不支持 XML 数据类型或它附带的任何函数,包括FOR XML
、.nodes
、.value
、.query
等.modify
。
如果您需要这种类型的处理,您可以使用传统的 SQL Server,例如 SQL Server 2019 或 Azure SQL DB。一种选择是使用 Synapse Pipelines 将数据移动到那里。作为替代方案,您可以考虑使用 Synapse Notebooks 和一些自定义 Python / Scala / c# 代码,但我只对此做了一个简单的测试。
Scala中的简单示例:
单元格 1
// Get the table with the XML column from the database and expose as temp view
val df = spark.read.synapsesql("yourPool.dbo.someXMLTable")
df.createOrReplaceTempView("someXMLTable")
单元格 2
%%sql
-- Use SparkSQL to interrogate the XML
-- https://spark.apache.org/docs/2.3.0/api/sql/index.html#xpath
SELECT
colA,
colB,
xpath_string(pkData,'/DataSet/EnumObject[name="Inpatient"]/value') xvalue
FROM someXMLTable
单元格 3
val df2 = spark.sql("""
SELECT
colA,
colB,
xpath_string(pkData,'/DataSet/EnumObject[name="Inpatient"]/value') xvalue
FROM someXMLTable
""")
df2.show
单元格 4
// Write that dataframe back to the dedicated SQL pool
df2.write.synapsesql("yourPool.dbo.someXMLTable_processed", Constants.INTERNAL)
XML 现在有点过时了——你有没有想过切换到 JSON?此外,如果您的数据量不是那么大,那么仅使用 Azure SQL DB 而不是 Synapse 会便宜很多。
推荐阅读
- php - 如何在 PHP Codeigniter 中发送动态消息(SMS)
- spring-kafka - 为每个 KafkaTemplate 指定 client.id
- python - 使用 Python 查找特定的重叠模式
- emv - 解释 EMV MDK 密钥 Enc MAC AC
- javascript - 引导表跟随 div 内的滚动
- angular - 在按钮单击时选择 PrimeNg 多选选项,Angular
- javascript - Javascript Regex - 从标签字符串中获取特定的 html 标签
- java - 未经授权的用户访问某些 url
- symfony - Symfony 异常 - 目录不可写
- amazon-web-services - 在 AWS SDK 中同时处理多个用户