SQL XML解析存储过程手动运行，未通过SQL Server代理运行

Question

存储过程正在手动运行，但不会使用SQL Server代理运行。

我收到以下错误

作为用户执行：XXX。 XML解析：行9，字符9，意外 输入结束[SQLSTATE 42000]（错误9400）。该步骤失败。

存储过程从Web查询中获取xml，将其放入临时表中的单个单元格中。分析临时表中的数据，并将新的易于阅读的信息放入永久表中。我需要查询每天运行并获取当天的数据。

我试过查找这个问题，但我唯一的线索是XML被切碎的地方。我不知道为什么这只发生在一份工作上。

你可以给的任何帮助将是伟大的。

感谢

EDIT1：我已经将过程拆分为7层不同的步骤。这是它正在落下的一步。

INSERT XMLData(XMLD) SELECT CAST(HTML AS XML) As XMLData FROM TextData

EDIT2：下面的代码

DECLARE 

@url varchar(2048), 
@win integer, 
@hr integer , 
@text varchar(MAX), 
@XMLdata XML, 
@Date date, 
@SearchDate nvarchar(50) 


Set @Date = GETDATE() 

set @SearchDate = CAST(@Date as nvarchar(50)) 

set @SearchDate = REPLACE(@SearchDate,'-','') 


--set @SearchDate='20130405' 

/*-- Create Temporary tables to be used to store the xml data--*/ 
/*-- We need 2 tables as the raw data from SEMO is Unicode 'UTF-8' but SQL only handles 'UTF-16'--*/ 
/*-- We must therefore store the data first as Text and then CAST it to XML datatype for easy querying later --*/ 
CREATE TABLE #TextData(HTML text NULL) 
CREATE TABLE #XMLData(XMLD xml NULL) 

/*-- This url will have to be dynamically generated each day based on GetDate() query or similiar - to be cracked out fully - random date picked for now--*/ 
Select @url = 'http://.......' 

/* Use OLE Automation Objects to go out and get the data--*/  
EXEC @hr=sp_OACreate 'WinHttp.WinHttpRequest.5.1',@win OUT 
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win 

EXEC @hr=sp_OAMethod @win, 'Open',NULL,'GET',@url,'false' 
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win 

EXEC @hr=sp_OAMethod @win,'Send' 
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win 

INSERT #TextData(HTML) 
EXEC @hr=sp_OAGetProperty @win,'ResponseText' 
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win 

EXEC @hr=sp_OADestroy @win 
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win 

/*-- Now CAST the text data to XML data type which makes it much easier to query back--*/ 
INSERT #XMLData(XMLD) 
SELECT CAST(HTML AS XML) As XMLData FROM #TextData 



/*-- WE NEED TO ADD IN A SECTION HERE NOW TO PARSE THE XML DATA AND EXTRACT THE INFO WE NEED INTO A DEDICATED TABLED--*/ 

/*-- Replace the : in the #XMLData table with _ as the colons were causing errors --*/ 
UPDATE #XMLData 
SET XMLD = REPLACE(CAST(XMLD as varchar(max)),':' ,'_') 
FROM #XMLData 
WHERE CHARINDEX(':' ,CAST(XMLD as varchar(max)))>0 

UPDATE #XMLData 
SET XMLD = REPLACE(CAST(XMLD as varchar(max)),'DataSet xmlns="http_//localhost/SemoReporting/SemoAutomatedDataCollection/Datasets"' ,'DataSet') 
FROM #XMLData 
WHERE CHARINDEX('DataSet xmlns="http_//localhost/SemoReporting/SemoAutomatedDataCollection/Datasets"' ,CAST(XMLD as varchar(max)))>0 

Select @XMLdata = XMLD 
FROM #XMLData 

/*-- This is to show the colons have been replaced --*/ 
SELECT * FROM #XMLData 

Declare @i as int 
set @i = 1 

While @i < 97 
BEGIN 
/*Insert Data into SEMO Historical Table*/ 

INSERT INTO XMLParsing.dbo.T1301_SEMO_HISTORICAL_DATA 
/*-- This selects the relevant data from table1 element --*/ 
SELECT 
a.b.value('Table1[sql:variable("@i")][1]/CURRENCY_FLAG[1]','nvarchar(50)') AS [Currency], 
a.b.value('Table1[sql:variable("@i")][1]/TRADE_DATE[1]','date') AS [Trade Date], 
a.b.value('Table1[sql:variable("@i")][1]/DELIVERY_DATE[1]','date') AS [Delivery Date], 
a.b.value('Table1[sql:variable("@i")][1]/DELIVERY_HOUR[1]','int') AS [Delivery Hour], 
a.b.value('Table1[sql:variable("@i")][1]/DELIVERY_INTERVAL[1]','int') AS [Delivery Interval], 
a.b.value('Table1[sql:variable("@i")][1]/RUN_TYPE[1]','nvarchar(50)') AS [Run Type], 
a.b.value('Table1[sql:variable("@i")][1]/SMP[1]','decimal(10,4)') AS [SMP], 
a.b.value('Table1[sql:variable("@i")][1]/LAMBDA[1]','decimal(10,4)') AS [Lambda], 
a.b.value('Table1[sql:variable("@i")][1]/SYSTEM_LOAD[1]','decimal(10,4)') AS [System Load], 
a.b.value('Table1[sql:variable("@i")][1]/CMS_TIME_STAMP[1]','nvarchar(150)') AS [CMS Time Stamp] 
FROM @XMLdata.nodes('DataSet/diffgr_diffgram/EA_RESULTS') a(b) 
--where 
--not exists (Select * from SEMO_Historical_Data where [Trade Date] = @Date) 
set @[email protected]+1 
END 

Drop Table #TextData 
Drop Table #XMLData 

Answer 1

只是一个更新。这是不正常工作的原因是从网站采取的XML被截断。我从来没有完全弄清楚为什么会发生这种情况，但是我用wget代替了WinHttp.WinHttpRequest.5.1，它使用了网站上的所有数据，并直接将它传递给我可以解析数据的网页。