470,648 Members | 1,324 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

SQL Server 2005 T-SQL XML Parser

radcaesar
759 Expert 512MB
--Start of XML Parser
DECLARE @FileName1 varchar(255)
DECLARE @ExecCmd VARCHAR(255)
DECLARE @FileContents VARCHAR(8000)
DECLARE @idoc int
DECLARE @UserName varchar(60)
DECLARE @Password varchar(60)

CREATE TABLE #tempXML(PK INT NOT NULL IDENTITY(1,1), ThisLine VARCHAR(255))

SET @FileContents = ''
SET @FileName1 = 'd:\CCCLoginConfig.dtsConfig'
SET @ExecCmd = 'type ' + @FileName1

INSERT INTO #tempXML EXEC master.dbo.xp_cmdshell @ExecCmd

DECLARE @AccountID varchar(8000)
DECLARE @getAccountID CURSOR
SET @getAccountID = CURSOR FOR select ThisLine from #tempxml
OPEN @getAccountID
FETCH NEXT FROM @getAccountID INTO @AccountID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @FileContents = @FileContents + isnull(cast(@AccountID as varchar(8000)),'')
FETCH NEXT FROM @getAccountID INTO @AccountID
END
CLOSE @getAccountID
DEALLOCATE @getAccountID
EXEC sp_xml_preparedocument @idoc OUTPUT, @FileContents

SELECT text FROM OPENXML (@idoc,'/DTSConfiguration/Configuration/ConfiguredValue',1) where nodetype=3
SELECT @UserName=text FROM OPENXML (@idoc,'/DTSConfiguration/Configuration/ConfiguredValue',1) where id=27
SELECT @Password=text FROM OPENXML (@idoc,'/DTSConfiguration/Configuration/ConfiguredValue',1) where id=25

print @UserName
print @Password
DROP TABLE #tempXML
--End of XML Parser
Oct 23 '07 #1
0 6825

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by John Silver | last post: by
4 posts views Thread by Patrick Masson | last post: by
5 posts views Thread by Bruce Schechter | last post: by
8 posts views Thread by bryan | last post: by
reply views Thread by suresh.csharp | last post: by
6 posts views Thread by den 2005 | last post: by
1 post views Thread by Korara | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.