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

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

 

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

转载于:https://www.cnblogs.com/zencorn/archive/2011/05/19/2051078.html