SQL Query XML column. 　　SQL　查询　ｘｍｌ　字段

本文详细介绍了使用SQL查询获取XML字段值的方法，包括获取XML列数据、特定节点名称的值及使用条件过滤查询结果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Work by Jianfei is licensed under a Creative Commons wei 3.0 Unported License.

１. As we knew SQL Server has provided a serices of XML execution function, we can using SQL query to get the field values directly. Please see following steps to create your own SQL xml query statement.

-- Get the XML column data, please case the ns0:Record statement is not a root node . See output #1

select Data from sct_DataSourceDatasetRecord where (DataSourceDatasetID = '117' or DataSourceDatasetID = '118' )

-- Get the XML node named as Investment,the pattern of "/" is a node filter. See output #2

SELECT DataSourceDatasetRecordID, Data.query( ' //Investment ' ) as InvesValue from sct_DataSourceDatasetRecord where (DataSourceDatasetID = ' 117 ' or DataSourceDatasetID = ' 118 ' )

-- Get the XML node value using data function. See output #3
SELECT   DataSourceDatasetRecordID, Data.query( ' data(//Investment) ' ) as InvesValue from sct_DataSourceDatasetRecord where (DataSourceDatasetID = ' 117 ' or DataSourceDatasetID = ' 118 ' )

-- Get the XML node value query result using where condition. See output #3
select DataSourceDatasetRecordID , InvesValue   from
( SELECT   DataSourceDatasetRecordID, Data.query( ' data(//Investment) ' ) as InvesValue from sct_DataSourceDatasetRecord where (DataSourceDatasetID = ' 117 ' or DataSourceDatasetID = ' 118 ' )) as result
where Cast (InvesValue as varchar ) = ' c1 '

２．　SQL query result.