HANDLE ERRORS IN SP_XML_PREPAREDOCUMENT

I'm working on a procedure to import XML-data in a database. First I want to
verify that the XML is correct. My idea was to use SP_XML_PREPAREDOCUMENT an
d
handle any error messages returned.
The problem is, when the procedure reports a parse error my procedure stops.
I can't get my procedure to go on and just report the error. ...
My (simplified) code looks like this:
CREATE PROCEDURE SP_ADD_XML
@.XML_XMLSTR TEXT
AS
BEGIN
DECLARE @.FILEHANDLE INT
DECLARE @.RESULT INT
EXEC @.RESULT=SP_XML_PREPAREDOCUMENT @.FILEHANDLE output, @.XML_XMLSTR
...
END
GO
If I try to parse a bad XML code the procedure stops when executing
SP_XML_PREPAREDOCUMENT with an error:
"Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Line
2
XML parsing error: End tag 'pv_best' does not match the start tag 'typ'. "
Can I prevent MY procedure from stopping when I send bad XML-data to
SP_XML_PREPAREDOCUMENT?Hi
I am guessing you are using SQL Server 2000.
You could try creating a wrapper sproc for the sp_xml_preparedocument, that
returns the handle number, calling it in your outer sproc and handling error
s
generated where your wrapper sproc fails.
The errors will still be thrown but your outer sproc will carry on. A word
of caution - make sure you clean up after it. with sp_xml_removedocument
otherwise you will have trouble with memory getting full of junk XML DOMs.
In fact this may happen anyway with bad XML since you will not get a valid
handle back from sp_xml_preparedocument if it fails and it will have grabbed
some memory. I found the only way to avoid this is to reboot your server
when you start getting memory errors.
Regards
John Harrison
"LakritZtrollet" wrote:

> I'm working on a procedure to import XML-data in a database. First I want
to
> verify that the XML is correct. My idea was to use SP_XML_PREPAREDOCUMENT
and
> handle any error messages returned.
> The problem is, when the procedure reports a parse error my procedure stop
s.
> I can't get my procedure to go on and just report the error. ...
> My (simplified) code looks like this:
> CREATE PROCEDURE SP_ADD_XML
> @.XML_XMLSTR TEXT
> AS
> BEGIN
> DECLARE @.FILEHANDLE INT
> DECLARE @.RESULT INT
> EXEC @.RESULT=SP_XML_PREPAREDOCUMENT @.FILEHANDLE output, @.XML_XMLSTR
> ...
> END
> GO
>
> If I try to parse a bad XML code the procedure stops when executing
> SP_XML_PREPAREDOCUMENT with an error:
> "Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Li
ne
> 2
> XML parsing error: End tag 'pv_best' does not match the start tag 'typ'. "
> Can I prevent MY procedure from stopping when I send bad XML-data to
> SP_XML_PREPAREDOCUMENT?