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

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

--构造数据declare @xmlField1 xml, @xmlField2 xmlset @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 @Table1select 1, 'Mary', @xmlField1union allselect 2, 'Nancy', @xmlField2select * 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 allselect '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 across 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