时间戳

首页 > 解决方案 > 从存储 XML 且数据类型为 CLOB 的字段中检索数据

sql-server - 从存储 XML 且数据类型为 CLOB 的字段中检索数据

问题描述

我想检索存储在名称为 inpatienttype 的 PKDATA 数据字段中的 XML 值，希望返回值 262784091 作为 inpatienttype

输出

一种 乙 住院型
11,212 2587165 262784091

数据集 图像

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ns2:DataSet xmlns:ns2="http://www.test.com/t/cn/el">
    <EnumObject>
        <name>InpatientType</name>
        <prompt>InpatientType</prompt>
        <value>262784091</value>
        <radiobutton>false</radiobutton>
    </EnumObject>
    <StringObject>
        <name>xxx</name>
        <prompt></prompt>
        <value>/widget.jsp</value>
        <width>99</width>
    </StringObject>
</ns2:DataSet>

我使用了以下查询，但没有工作

SELECT XMLQUERY(
  '/EnumObject/name'
  PASSING XMLTYPE(e.pkdata)
  RETURNING CONTENT
  ) AS name
FROM EXTDATA as e

select x.*
from [dbo].[EXTRADATA] rt
cross join xmltable(
  '/EnumObject/name'
  passing xmltype(rt.packeddata)
  columns name number path 'name/@value'
) x

收到此错误

第 3 行第 3 列的解析错误：“PASSING”附近的语法不正确。

有人可以在这里帮助我吗

标签： sql-serverxmltsqlxquery

解决方案

在 Microsoft SQL Server 中，您可以通过nodes()、query()和value()函数使用 XQuery。

如果源数据涉及自定义 XML 名称空间，您还可以使用xmlnamespaces来对 XQuery 的节点进行名称空间限定，例如：

with xmlnamespaces (
  'http://www.test.com/t/cn/el' as cnel
)
select
  EnumObject.value('(./name/text())[1]', 'varchar(50)') as name,
  EnumObject.value('(./prompt/text())[1]', 'varchar(50)') as prompt,
  EnumObject.value('(./value/text())[1]', 'varchar(50)') as value
from dbo.TOCEXTRADATA
cross apply packeddata.nodes('/cnel:DataSet/EnumObject') p(EnumObject);
姓名 迅速的 价值
住院类型 住院类型 262784091

以下查询避免了根节点上的自定义命名空间并返回等效结果：

select
  EnumObject.value('(./name/text())[1]', 'varchar(50)') as name,
  EnumObject.value('(./prompt/text())[1]', 'varchar(50)') as prompt,
  EnumObject.value('(./value/text())[1]', 'varchar(50)') as value
from dbo.TOCEXTRADATA
cross apply packeddata.nodes('//EnumObject') p(EnumObject);
姓名 迅速的 价值
住院类型 住院类型 262784091

编辑：当packeddata列的数据类型不是xml时，您需要将其转换为正确的类型。上面的两个示例查询需要通过以下方式进行修改：

with xmlnamespaces (
  'http://www.test.com/t/cn/el' as cnel
)
select
  EnumObject.value('(./name/text())[1]', 'varchar(50)') as name,
  EnumObject.value('(./prompt/text())[1]', 'varchar(50)') as prompt,
  EnumObject.value('(./value/text())[1]', 'varchar(50)') as value
from dbo.TOCEXTRADATA
outer apply ( select cast(packeddata as xml) as xmlData ) oa
cross apply xmlData.nodes('/cnel:DataSet/EnumObject') p(EnumObject);

select
  EnumObject.value('(./name/text())[1]', 'varchar(50)') as name,
  EnumObject.value('(./prompt/text())[1]', 'varchar(50)') as prompt,
  EnumObject.value('(./value/text())[1]', 'varchar(50)') as value
from dbo.TOCEXTRADATA
outer apply ( select cast(packeddata as xml) as xmlData ) oa
cross apply xmlData.nodes('//EnumObject') p(EnumObject);

推荐阅读