SQL Server 200 XML解析游标问题 [英] SQL Server 200 XML Parsing Cursor Problem
问题描述
我正在使用T-SQL读取xml文件。我想要在openxml中使用xml数据。所以我在这里使用光标。
这里我将xml数据连接成一个名为@FileContents的变量。当我在CURSOR的WHILE FetchStatus中打印此变量内容时，其打印正常。如果我在光标执行结束后打印，它就不会显示任何内容和它的空白。
我在哪里缺少？
DECLARE @FileName varchar（255）
DECLARE @ExecCmd VARCHAR（255）
DECLARE @y INT
DECLARE @x INT
DECLARE @FileContents VARCHAR（8000）
DECLARE @idoc int
CREATE TABLE #tempXML（PK INT NOT NULL IDENTITY（ 1,1），ThisLine VARCHAR（255））
SET @FileContents =''''
SET @FileName =''d：\ CCCLoginConfig .xml''
SET @ExecCmd =''type''+ @FileName
INSERT INTO #tempXML EXEC master.dbo.xp_cmdshell @ExecCmd
DECLARE @AccountID varchar（8000）
DECLARE @getAccountID CURSOR
SET @getAccountID = CURSOR从#tempxml
OPEN @getAccountID
FETCH NEXT FROM @getAccountID INTO @AccountID
WHILE @@ FETCH_STATUS = 0
BEGIN
SET @FileContents = @FileContents + cast（@AccountID as varchar（8000））
FETCH NEXT FROM @getAccountID INTO @AccountID
- -print @FileContents
结束
关闭@getAccountID
DEALLOCATE @getAccountID
select @FileContents
EXEC sp_xml_preparedocument @idoc OUTPUT，@ FileContents
SELECT * FROM OPENXML（@idoc，''/ DTSConfiguration / ConfiguredValue''，2）WITH（ConfiguredValue varchar（10））
- ContactName varchar（20））
DROP TABLE #tempXML
Hi,
I am reading a xml file using T-SQL. I want the xml data to use in openxml. So i was using cursor here.
Here i am concatenating the xml data into a variable called @FileContents. When i print this variable content inside the WHILE FetchStatus of CURSOR, its printing fine. If i print after the cursor execution s over, it doen''t shows anything and its blank.
Where am i missing ?
DECLARE @FileName varchar(255)
DECLARE @ExecCmd VARCHAR(255)
DECLARE @y INT
DECLARE @x INT
DECLARE @FileContents VARCHAR(8000)
DECLARE @idoc int
CREATE TABLE #tempXML(PK INT NOT NULL IDENTITY(1,1), ThisLine VARCHAR(255))
SET @FileContents = ''''
SET @FileName = ''d:\CCCLoginConfig.xml''
SET @ExecCmd = ''type '' + @FileName
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 + cast(@AccountID as varchar(8000))
FETCH NEXT FROM @getAccountID INTO @AccountID
--print @FileContents
END
CLOSE @getAccountID
DEALLOCATE @getAccountID
select @FileContents
EXEC sp_xml_preparedocument @idoc OUTPUT, @FileContents
SELECT * FROM OPENXML (@idoc,''/DTSConfiguration/ConfiguredValue '',2) WITH (ConfiguredValue varchar(10))
-- ContactName varchar(20))
DROP TABLE #tempXML
推荐答案
我正在使用T-SQL读取xml文件。我想要在openxml中使用xml数据。所以我在这里使用光标。
这里我将xml数据连接成一个名为@FileContents的变量。当我在CURSOR的WHILE FetchStatus中打印此变量内容时，其打印正常。如果我在光标执行结束后打印，它就不会显示任何内容和它的空白。
我在哪里缺少？
DECLARE @FileName varchar（255）
DECLARE @ExecCmd VARCHAR（255）
DECLARE @y INT
DECLARE @x INT
DECLARE @FileContents VARCHAR（8000）
DECLARE @idoc int
CREATE TABLE #tempXML（PK INT NOT NULL IDENTITY（ 1,1），ThisLine VARCHAR（255））
SET @FileContents =''''
SET @FileName =''d：\ CCCLoginConfig .xml''
SET @ExecCmd =''type''+ @FileName
INSERT INTO #tempXML EXEC master.dbo.xp_cmdshell @ExecCmd
DECLARE @AccountID varchar（8000）
DECLARE @getAccountID CURSOR
SET @getAccountID = CURSOR从#tempxml
OPEN @getAccountID
FETCH NEXT FROM @getAccountID INTO @AccountID
WHILE @@ FETCH_STATUS = 0
BEGIN
SET @FileContents = @FileContents + cast（@AccountID as varchar（8000））
FETCH NEXT FROM @getAccountID INTO @AccountID
- -print @FileContents
结束
关闭@getAccountID
DEALLOCATE @getAccountID
select @FileContents
EXEC sp_xml_preparedocument @idoc OUTPUT，@ FileContents
SELECT * FROM OPENXML（@idoc，''/ DTSConfiguration / ConfiguredValue''，2）WITH（ConfiguredValue varchar（10））
- ContactName varchar（20））
DROP TABLE #tempXML
Hi,
I am reading a xml file using T-SQL. I want the xml data to use in openxml. So i was using cursor here.
Here i am concatenating the xml data into a variable called @FileContents. When i print this variable content inside the WHILE FetchStatus of CURSOR, its printing fine. If i print after the cursor execution s over, it doen''t shows anything and its blank.
Where am i missing ?
DECLARE @FileName varchar(255)
DECLARE @ExecCmd VARCHAR(255)
DECLARE @y INT
DECLARE @x INT
DECLARE @FileContents VARCHAR(8000)
DECLARE @idoc int
CREATE TABLE #tempXML(PK INT NOT NULL IDENTITY(1,1), ThisLine VARCHAR(255))
SET @FileContents = ''''
SET @FileName = ''d:\CCCLoginConfig.xml''
SET @ExecCmd = ''type '' + @FileName
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 + cast(@AccountID as varchar(8000))
FETCH NEXT FROM @getAccountID INTO @AccountID
--print @FileContents
END
CLOSE @getAccountID
DEALLOCATE @getAccountID
select @FileContents
EXEC sp_xml_preparedocument @idoc OUTPUT, @FileContents
SELECT * FROM OPENXML (@idoc,''/DTSConfiguration/ConfiguredValue '',2) WITH (ConfiguredValue varchar(10))
-- ContactName varchar(20))
DROP TABLE #tempXML
复制了这个我的结局后，我怀疑你遇到了一个取消变量赋值的null的串联。
之后我放了''print''在我的结束时取消分配，它适用于此修订
Having replicated this my end I suspect you''re encountering a concatenation of a null that is cancelling out the variable assignment.
I have placed the ''print'' after the deallocation at my end and it works with this amendment
如果Jim是正确的，并且您在结尾处为null，请执行以下操作： br />
If Jim is right and you have null at the end then do following:
我讨厌游标。
也许你想尝试一次阅读整个文件。
我没有时间为你改变这段代码（去度假几周）但你可以尝试插入变量而不是表格或在初始插入变量后读取。
I hate cursors.
Maybe you want to try reading whole file at once.
I don''t have time changing this code for you (going on vacation for couple of weeks) but you can try inserting into a variable instead of a table or read after initial insert into a variable.
这篇关于SQL Server 200 XML解析游标问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
