sql2008 r2版本 win sever 2003环境
预先建立了一个recorderror表,用来记录解析过程中遇到的错误
xml数据存在test1130表中
利用try……catch来捕捉错误,并记录,但执行后,还是遇到错误时,整个脚本就停止运行了,recorderror表中也没有任何记录,
为什么呢?
错误记录:
消息 9436,级别 16,状态 1,第 75 行
XML 分析: 行 2,字符 1245,结束标记与开始标记不匹配
以下是利用脚本解析XML数据,并捕捉错误
declare @XML xml
declare @id nvarchar(50)
declare cur cursor for
select [id],replace(replace([source],'<?xml version="1.0" encoding="UTF-8"?>',''),'''','''''')
FROM [mds_qikaneSourcezlid20151105].[dbo].[test1130]
order by cast(querystr as int)
open cur
fetch next from cur into @id,@xml
while (@@FETCH_STATUS=0)
begin
begin try
print @id
INSERT INTO [mds_qikaneSourcezlid20151105].[dbo].[enqikan]
([_ID]
,[Identifier_SelfID]
,[DOI]
,[ISSN]
,[EISSN]
,[TITLE]
,[AUTHOR]
,[ORG]
,[KEYWORD]
,[CID]
,[ABSTRACT]
,[JOUCN]
,[VOL]
,[PER]
,[PG]
,[YEAR]
,[DOWNLOAD]
,[PUBTYPE]
,[YNFREE]
,[SCI]
,[EI]
,[DATELINK]
,[FUND]
,ID)
select
T.c.value('(_ID/text())[1]','nvarchar(MAX)')
,T.c.value('(Identifier_SelfID/text())[1]','nvarchar(MAX)')
,T.c.value('(DOI/text())[1]','nvarchar(MAX)')
,T.c.value('(ISSN/text())[1]','nvarchar(MAX)')
,T.c.value('(EISSN/text())[1]','nvarchar(MAX)')
,T.c.value('(TITLE/text())[1]','nvarchar(MAX)')
,T.c.value('(AUTHOR/text())[1]','nvarchar(MAX)')
,T.c.value('(ORG/text())[1]','nvarchar(MAX)')
,T.c.value('(KEYWORD/text())[1]','nvarchar(MAX)')
,T.c.value('(CID/text())[1]','nvarchar(MAX)')
,T.c.value('(ABSTRACT/text())[1]','nvarchar(MAX)')
,T.c.value('(JOUCN/text())[1]','nvarchar(MAX)')
,T.c.value('(VOL/text())[1]','nvarchar(MAX)')
,T.c.value('(PER/text())[1]','nvarchar(MAX)')
,T.c.value('(PG/text())[1]','nvarchar(MAX)')
,T.c.value('(YEAR/text())[1]','nvarchar(MAX)')
,T.c.value('(DOWNLOAD/text())[1]','nvarchar(MAX)')
,T.c.value('(PUBTYPE/text())[1]','nvarchar(MAX)')
,T.c.value('(YNFREE/text())[1]','nvarchar(MAX)')
,T.c.value('(SCI/text())[1]','nvarchar(MAX)')
,T.c.value('(EI/text())[1]','nvarchar(MAX)')
,T.c.value('(DATELINK/text())[1]','nvarchar(MAX)')
,T.c.value('(FUND/text())[1]','nvarchar(MAX)')
,@id
from @xml.nodes('/SearchResult/Hit') as T(c)
end try
begin catch
insert into [mds_qikaneSourcezlid20151105].[dbo].[recorderror] (id) values(@id)
end catch
fetch next from cur into @id,@xml
end
close cur
deallocate cur
print 'game over'