5

I am getting an error while executing the query in a SQL Server job. But if I execute it directly, it works fine.

Executed as user: NT AUTHORITY\SYSTEM.

XML parsing: line 10, character 33
Unexpected end of input [SQLSTATE 42000] (Error 9400). The step failed.
SQL Severity 16, SQL Message ID 9400

Code:

    declare  @URL VARCHAR(max) 

    set @url = 'http://www.spa.gov.sa/english/rss.xml'

    declare  @xmlT TABLE (yourXML XML)
    DECLARE @Response nvarchar(max)
    DECLARE @XML xml
    DECLARE @Obj int 
    DECLARE @Result int 
    DECLARE @HTTPStatus int 
    DECLARE @ErrorMsg nvarchar(MAX)

    EXEC @Result = sp_OACreate 'MSXML2.XMLHttp', @Obj OUT 

    EXEC @Result = sp_OAMethod @Obj, 'open', NULL, 'GET', @URL, false
    EXEC @Result = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'
    EXEC @Result = sp_OAMethod @Obj, send, NULL, ''
    EXEC @Result = sp_OAGetProperty @Obj, 'status', @HTTPStatus OUT 

    INSERT @xmlT ( yourXML )
    EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml'--, @Response OUT 


    INSERT into Tlb(discp, tit, datee, linkk)

--below statement creates problem when i remove this statement it works fine everywhere

        SELECT   
            N.C.value('description[1]', 'nvarchar(max)') discp,   
            N.C.value('title[1]', 'varchar(999)') tit,
            N.C.value('pubDate[1]', 'varchar(99)') datee,
            N.C.value('link[1]', 'varchar(999)') linkk
        FROM
            @xmlT 
        CROSS APPLY 
            yourXML.nodes('//channel/item') N(C)
Shakeel Hussain Mir
  • 286
  • 2
  • 20
user2659502
  • 61
  • 1
  • 5

2 Answers2

7

Do you know about the Sql Agent TEXTSIZE?

Specify directly in the procedure TEXTSIZE 512 and exec, you'll get the same error in this case

SET TEXTSIZE -1

OR other value, i use -1 for unlimited size by default sql server textsize on the SQL Agent is 512

Leandro Santos
  • 71
  • 1
  • 1
  • This should be the answer. As shown in this [thread](https://stackoverflow.com/questions/31063553/increase-text-size-in-sql-server-agent), simply adding "SET TEXTSIZE -1" before "EXEC [PROCEDURE]" works. – Weihui Guo Jan 09 '18 at 19:17
2

Your code how to get a flat file via URL works fine...

After this

INSERT @xmlT ( yourXML )
EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml'

... your declared table variable contains one row with a valid XML.

"line 10 character 33" might point to

  1. totally cut due to a to small varchar variable somewhere in between
    • in my test the line 10 doesn't even have so many characters...
    • Might be, that the content of the file varies, but the first 10 lines seem to carry quite steady data...
    • Try to change your @xmlT to store a VARCHAR(MAX) and do a pure SELECT. The file comes as pre-formatted, "pretty" xml... So: the reported line and character numbers should be OK...
  2. invalid XML (e.g. missing closing tag)
    • If the XML is invalid from the source it should not make a difference how you call it...
  3. invalid character
    • Might be, that there is some problem with the encoding

My favorite is 1): As the reflected error speaks about "XML Parsing - Unexpected end of input" I'd suppose, that the XML you read is cut somewhere...

Find out

  • if you get the same error independing of the XML's content.
  • if you get it always and always with the same line and character number
Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • 1
    issue only comes when i put above statements in SQL JOB else it works fine – user2659502 Jan 30 '16 at 04:31
  • 1
    @user2659502 Strange... Do you always get the same error message (same line number and character position)? I'd define a table in my database and use an INSERT to store the actual XML right before you try to read from it. As your table `@xmlT` defines the column as XML, the parsing error should occur in the moment of inserting it and not later, when you try to read it... really strange... – Shnugo Feb 02 '16 at 00:16
  • This has nothing to do with XML parsing since the procedure works when not using in the SQL Server Agent job. It's simply due to the limit of "TEXT SIZE" as answered below. – Weihui Guo Jan 09 '18 at 18:40