title: SQL XML Parsing Stored Procedure running manually, not running via SQL Server Agent
body: <p>The stored Procedure is running manually but will not run using SQL Server agent. </p>
  
  <p><strong>I get the following error</strong></p>
  
  <blockquote>
    <p>Executed as user: XXX. XML parsing: line 9, character 9, unexpected
    end of input [SQLSTATE 42000] (Error 9400).  The step failed.</p>
  </blockquote>
  
  <p>The stored Procedure takes the xml from a web query, puts it in a single cell in a temp table. Parses the data from the temp table and puts the new easy-to-read information into a permanent table. I need the query to run daily and fetch the data for that day.</p>
  
  <p>I've tried looking up the issue but the only clue I have is that the XML is being chopped somewhere. I have no idea why this is happening for a job only.</p>
  
  <p>Any help you could give would be great.</p>
  
  <p>Thanks   </p>
  
  <p>EDIT1: I've split the procedure into 7 different steps. This is the Step it is falling down on. </p>
  
  <p><code>INSERT XMLData(XMLD)
  SELECT CAST(HTML AS XML) As XMLData FROM TextData</code></p>
  
  <p>EDIT2: Here's the code</p>
  
  <pre><code>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 &lt;&gt; 0 EXEC sp_OAGetErrorInfo @win 
  
  EXEC @hr=sp_OAMethod @win, 'Open',NULL,'GET',@url,'false'
  IF @hr &lt;&gt; 0 EXEC sp_OAGetErrorInfo @win 
  
  EXEC @hr=sp_OAMethod @win,'Send'
  IF @hr &lt;&gt; 0 EXEC sp_OAGetErrorInfo @win 
  
  INSERT #TextData(HTML)
  EXEC @hr=sp_OAGetProperty @win,'ResponseText'
  IF @hr &lt;&gt; 0 EXEC sp_OAGetErrorInfo @win
  
  EXEC @hr=sp_OADestroy @win 
  IF @hr &lt;&gt; 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)))&gt;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)))&gt;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 &lt; 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 @i=@i+1
  END
  
  Drop Table #TextData
  Drop Table #XMLData
  </code></pre>