SQL Query XML column

 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.

 

 

More SQL对Xml字段的操作 Sample  Copy From : http://www.cnblogs.com/youring2/archive/2008/11/27/1342288.html