查询存贮在表中的XML的结点值。

--查询一个类型为XML的字段的值. 且只查这个XML字段值中的某一个结点的值。

--构造数据
declare @xmlField1 xml, @xmlField2 xml
set @xmlField1 = '<Publish xmlns="http://soa.OTHTest.com/SOA/USA/InfrastructureService/V30/PubSubService">
  <Node>
    <MessageHead>
      <Version>1.0</Version>      
    </MessageHead>    
  </Node>
</Publish>'


set @xmlField2 = '<Publish xmlns="http://soa.OTHTest.com/SOA/USA/InfrastructureService/V30/PubSubService">
  <Node>
    <MessageHead>
      <Version>2.0</Version>      
    </MessageHead>    
  </Node>
</Publish>'


declare @Table1 table( ID int , Name char(10), Msg xml )
insert into @Table1
select 1, 'Mary', @xmlField1
union all
select 2, 'Nancy', @xmlField2


select * from @Table1

--查询XML字段中的xml的结点值。

--引用的命名空间写在里面
select top 1 Msg, 
Msg.value('declare default element namespace "http://soa.OTHTest.com/SOA/USA/InfrastructureService/V30/PubSubService"; 
                        (/Publish/Node/MessageHead/Version)[1]',N'char(3)') as version1 
from @Table1


--引用的命名空间用with语句写在查询语句外面
;with xmlnamespaces(default N'http://soa.OTHTest.com/SOA/USA/InfrastructureService/V30/PubSubService') 
select top 1 Msg, 
Msg.value('(/Publish/Node/MessageHead/Version)[1]',N'char(3)') as version1 
from @Table1 

--查询一个类型为XML的字段中的XML中的结点值。

select top 1 SSBMessage, 
SSBMessage.value('declare default element namespace "http://soa.OTHTest..com/SOA/USA/InfrastructureService/V30/PubSubService"; 
                        (/Publish/Node/MessageHead/Version)[1]',N'char(3)') as version1 
from dbo.LogForUser with(nolock)


;with xmlnamespaces(default N'http://soa.OTHTest..com/SOA/USA/InfrastructureService/V30/PubSubService') 
select top 1 SSBMessage, 
SSBMessage.value('(/Publish/Node/MessageHead/Version)[1]',N'char(3)') as version1 
from dbo.LogForUser with(nolock) 


--xml字段作为哪里条件的写法：
;WITH XMLNAMESPACES (DEFAULT 'http://soa.OTHTest..com/SOA/USA/InfrastructureService/V30/PubSubService')
select top 10 *
from dbo.LogForUser with(nolock)
where Indate > '2011/10/24 18:38'
and  ssbMessage.value('(/Publish/Node/MessageHead/TransactionCode/text())[1]', 'VARCHAR(20)') = '09-102-0-846'
and  ssbMessage.value('(Publish/Node/MessageHead/Action/text())[1]','VARCHAR(20)') = 'SendMessage'
and  ssbMessage.value('(Publish/Node/MessageHead/SellerID/text())[1]','VARCHAR(20)') = 'A001'
order by indate desc

--如果结点中有一个结点有多组值的查询方法如下：

declare @table1 table(id int identity, name varchar(50), courses xml )

insert @table1(name,courses )
select 'Angelia', '<info>
                    <T_Grade>300</T_Grade>
                    <Rank>13</Rank>
                    <courses>
                       <score>
                           <Times>first</Times>
						   <math>98</math>
						   <language>88</language>
                       </score>
                       <score>
                           <Times>second</Times>
						   <math>90</math>
						   <language>87</language>
                       </score>
                       <score>
                           <Times>third</Times>
						   <math>88</math>
						   <language>97</language>
                       </score>
                    </courses>
                 </info>'
union all
select 'Mary', '<info>
                    <T_Grade>280</T_Grade>
                    <Rank>25</Rank>
                    <courses>
                       <score>
                       <Times>first</Times>
                       <math>67</math>
                       <language>76</language>
                       </score>
                       <score>
                       <Times>second</Times>
                       <math>78</math>
                       <language>77</language>
                       </score>
                       <score>
                       <Times>third</Times>
                       <math>76</math>
                       <language>80</language>
                       </score>
                    </courses>
                 </info>'


select * from @table1

--查询XML中的结点
select id
       ,name
,TotalGrade = courses.value('(/info/T_Grade/text())[1]','int')
,[Rank]= courses.value('(/info/Rank/text())[1]','int')
from @table1

--查询结点中结点中有多组数据

select id
       ,name
,TotalGrade = courses.value('(/info/T_Grade/text())[1]','int')
,[Rank]= courses.value('(/info/Rank/text())[1]','int')
,b.Times
,b.math
,b.[language]
from @table1 a
cross apply
( 
	select
          Times = subjects.value('(Times/text())[1]','varchar(10)')
          ,math = subjects.value('(math/text())[1]','int')
          ,[language] = subjects.value('(language/text())[1]','int')
    from a.courses.nodes('/info/courses/score') subjectsList(subjects)
)
b