--查询一个类型为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