-XML分析器的启动
宣告@ FileName1 varchar(255)
宣告@ExecCmd VARCHAR(255)
宣告@FileContents VARCHAR(8000)
声明@idoc int
十进制@UserName varchar(60)
声明@Password varchar(60)
创建表#tempXML(PK INT非空IDENTITY(1,1),ThisLine VARCHAR(255))
SET @FileContents =''
SET @ FileName1 ='d:\ CCCLoginConfig.dtsConfig'
SET @ExecCmd ='类型'+ @ FileName1
插入#tempXML EXEC master.dbo.xp_cmdshell @ExecCmd
宣告@AccountID varchar(8000)
宣告@getAccountID游标
SET @getAccountID = CURSOR FOR从#tempxml中选择ThisLine
OPEN @getAccountID
从@getAccountID获取下一个到@AccountID
@@ FETCH_STATUS = 0时
开始
设置@FileContents = @FileContents + isull(cast(@AccountID as varchar(8000)),'')
从@getAccountID获取下一个到@AccountID
结束
CLOSE @getAccountID
取消分配@getAccountID
EXEC sp_xml_preparedocument @idoc输出,@ FileContents
从OPENXML(@idoc,'/ DTSConfiguration / Configuration / ConfiguredValue',1)中选择文本,其中nodetype = 3
从OPENXML中选择@ UserName = text(@idoc,'/ DTSConfiguration / Configuration / ConfiguredValue',1)其中id = 27
从OPENXML中选择@ Password = text(@idoc,'/ DTSConfiguration / Configuration / ConfiguredValue',1)其中id = 25
打印@UserName
打印@Password
删除表#tempXML
-XML分析器的结尾
From: https://bytes.com/topic/sql-server/insights/727106-sql-server-2005-t-sql-xml-parser