SQL2008 操作XML 单字段

USE [NF_UserData]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		IsaacZhang
-- Create date: 2012-09-04
-- Description:	创建插入hubble索引中间表数据的触发器
-- =============================================
CREATE TRIGGER  Trigger_UserDataBibliography_Insert 
   ON  Bibliography
   AFTER INSERT
AS 
BEGIN
	/*声明需要到的变量@hubbleID int,*/
	DECLARE 
	 @BibliographyId uniqueidentifier,
	 @BibliographyIntId  int,--暂时没有这个字段
	 @Title  varchar(255),
	 @AuthorsXml  XML, --需要处理
	 @Authors varchar(500),--等待XML文件处理完成后写入字段
	 @SharePersonName   varchar (100),--暂时无这个
	 @ShareDate   datetime ,
	 @Media   varchar (255),
	 @IsFullFile   bit ,--需要在外键中处理
	 @FileId uniqueidentifier,--获取全文ID
	 @SharePersonId   int ,--暂时无这个
	 @BibAbstract   nvarchar (max),
	 @DOI   nvarchar (128),
	 @PubulishYear   int ,
	 @Volume   nvarchar (32),
	 @Issue   nvarchar (32),
	 @PageScope   nvarchar (50),
	 @PageCount   int ,
	 @TagStr   nvarchar (1000),--外键表中找
	 @TagIDStr   nvarchar (1000),--外键表中找，与标签字串一一对应
	 @Keywords	nvarchar(1000),--
	 @KeywordsXML	XML,
	 @UserID uniqueidentifier
	 
	 --从插入记录中取出一部分数据
	 SELECT @BibliographyId = BibliographyId,@Title=Title,@AuthorsXml=CAST(Authors AS XML)
	 ,@UserID =UserID,@ShareDate = ShareDate,@Media=Media,@BibAbstract = Abstract
	 ,@DOI=DOI,@PubulishYear = [Year],@Volume = Volume,@Issue = Issue
	 ,@PageScope = PageScope,@PageCount= [PageCount],@KeywordsXML=CAST(Keywords AS XML)
	  FROM INSERTED
		
	 --判断是否有全文
	 SET @IsFullFile = 0
	 DECLARE @i INT
	 SELECT @i = COUNT(0)FROM NPU_File.dbo.[File] WHERE FileId = @FileId
	 IF @i>0
	 	SET @IsFullFile = 1
	 --转换作者信息
	 SET @Authors = '';
	 DECLARE @isHas INT;
	 select  @isHas = @AuthorsXml.exist('declare default element namespace "http://services.notefirst.com/Type/Author";//FullName');
	 IF @isHas > 0
	 BEGIN 
		 --声明一个游标
		DECLARE authorCursor CURSOR    
		FOR 
		select T.C.value('declare default element namespace "http://services.notefirst.com/Type/Author";(../FullName)[1]','varchar(200)') 
		as authorstr
		from @AuthorsXml.nodes('declare default element namespace "http://services.notefirst.com/Type/Author";(//FullName)') as T(C)
		OPEN authorCursor
		--循环一个游标
		DECLARE @tempAuthor NVARCHAR(50);
		FETCH NEXT FROM  authorCursor INTO @tempAuthor
		WHILE @@FETCH_STATUS = 0
		BEGIN
		--拼接作者信息，用逗号隔开
		SET @Authors += @tempAuthor+',';
		FETCH NEXT FROM  authorCursor INTO @tempAuthor
		END  
	 	SET @Authors = substring(@Authors,1,len(rtrim(@Authors))-1)
		--关闭游标
		CLOSE authorCursor
		--释放资源
		DEALLOCATE authorCursor
		
	 END -- 结束作者信息IF
	 
	 --查看标签信息
	 SET @TagIDStr = '';
	 SET @TagStr = '';
	 IF(SELECT COUNT(0) FROM dbo.[Relationship-Bibliography^SysTag] WHERE BibliographyId=@BibliographyId)>0
	 BEGIN
		DECLARE tagCursor CURSOR    
		FOR SELECT TagId FROM dbo.[Relationship-Bibliography^SysTag] WHERE BibliographyId=@BibliographyId
		OPEN tagCursor
		--循环一个游标
		DECLARE @tempTag NVARCHAR(50),@tempTagID NVARCHAR(50);
		FETCH NEXT FROM  tagCursor INTO @tempTagID
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @TagIDStr += @tempTagID+',';
			SET @TagStr += @tempTag+',';
		END
		SET @TagIDStr = substring(@TagIDStr,1,len(rtrim(@TagIDStr))-1)
		SET @TagStr = substring(@TagStr,1,len(rtrim(@TagStr))-1)
		--关闭游标
		CLOSE tagCursor
		--释放资源
		DEALLOCATE tagCursor
	 END
	 
	 --查询关键词信息
	 SET @Keywords = '';
	 DECLARE @KeywordsTemp nvarchar(50);
	 DECLARE @isHasKeywords INT;
	 select  @isHasKeywords = @KeywordsXml.exist('//string');
	 IF @isHasKeywords > 0
	 BEGIN 
		--统计有多少关键词
	 	DECLARE @countKeyword Int;
		SET  @countKeyword = 0;
		select @countKeyword = T.S.value('count(//string)','Int') 
		from @KeywordsXml.nodes('//ArrayOfString') as T(S)
		WHILE @countKeyword>0
		BEGIN
			
			select @KeywordsTemp = T.S.value('(//string[sql:variable( "@countKeyword")])[1]','Int') 
			from @KeywordsXml.nodes('//ArrayOfString') as T(S)
			
			SET @Keywords += @KeywordsTemp+','
			SET @countKeyword = @countKeyword-1;
		END
		SET @Keywords = substring(@Keywords,1,len(rtrim(@Keywords))-1)
	 END -- 结束关键词信息IF
	 
	 INSERT INTO dbo.Hubble_UserDataBibliography
			([BibliographyId]
           ,[BibliographyIntId]
           ,[Title]
           ,[Authors]
           ,[SharePersonName]
           ,[ShareDate]
           ,[Media]
           ,[IsFullFile]
           ,[FileId]
           ,[SharePersonId]
           ,[BibAbstract]
           ,[DOI]
           ,[PubulishYear]
           ,[Volume]
           ,[Issue]
           ,[PageScope]
           ,[PageCount]
           ,[TagStr]
           ,[TagIDStr]
           ,[Keywords]
           ,[UserID])
           VALUES(@BibliographyId,
           0,
           @Title,
           @Authors,
           '',
           @ShareDate,
           @Media,
           @IsFullFile,
           @FileId,
           0,
           @BibAbstract,
           @DOI,
           @PubulishYear,
           @Volume,
           @Issue,
           @PageScope,
           @PageCount,
           @TagStr,
           @TagIDStr,
           @Keywords,
           @UserID);
END

GO