By Guest Authors
on 9 February 2003
| 0 Comments
| Tags: XML
This article was written by Vaiyapuri Subramanian. He writes "In SQL Server 2000, XML can be parsed using the extended stored procedure sp_xml_preparedocument. This works fine as long as a well-formed xml is provided. Then do whatever needs to be done and you can call the extended procedure sp_xml_removedocument to remove the document from memory. But what if the xml is not well-formed? How do you validate it?"
Passing poorly-formed document to the sp_xml_preparedocument procedure will raise fatal error
Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Line 2
XML parsing error: Invalid at the top level of the document.
and stop the execution of the sql.
In order to get the @@ERROR you need to query again in the same session and then go ahead with whatever needs to be done.
Here is a way to validate the xml even before calling the extended procedure sp_xml_preparedocument.
This process can validate a simple XML String or a file from a disk or URL using a DTS Package and Stored Procedure With OLE Automation
Let me explain step by step
Create a new package and create the following three global variables:
- o_XmlString -- Variable to hold the XML
- o_XmlStatus -- Variable to hold The Return Status
- o_fileName -- Variable to hold the name of file or URL
Drag and drop the ActiveX task and add the ActiveX Task Script. This script does the following:
- Create "Microsoft.XMLDOM" Object
- Load the XML from the global Variable GlobalVariables("o_XmlString").Value or GlobalVariables("o_fileName").Value
- Get the errorcode
- If errorCode = 0 Then return 0 (Valid XML) else return -1 (Invalid XML)
Save the DTS package as XMLValidator by providing both owner and user password.
Create the stored procedure given in the attached file usp_validate_xml.sql. This stored procedure does the following:
- Create a DTS package object
- Instantiate connection to SQL Server
- If the xml is from a file or URL then set the global variable o_fileName of the DTS Package to the input file name
- If the xml is from a String then set the global variable
o_XmlString of the DTS Package to the input XML String
- Execute the package
- Get the return status from the Global Variable o_XmlStatus
from the DTS Package
- Destroy the created DTS package Object
- return the value 0(Valid XML) or -1 (Invalid XML)
Execute the procedure as follows
DECLARE @status INT
EXEC @status = usp_validate_xml
@serverName = <your server name>, -- Put your servername in here
@packageName = 'XMLValidator',
@serverSecurity = 1,
-- 1 for Windows NT Authentication, 0 for SQL Server Authentication
@serverPassword='', -- Required if @serverSecurity is 0
@packagePassword='dummy', -- May or may not be there
@loadMethod = 'U', -- 'S' for String, 'D' for flat file, 'U' for URL
@msgText='hi', -- required only if @loadMethod = 'S'
@fileName = 'http://www.w3schools.com/dom/note.xml'
-- required only if @loadMethod = 'D' or @loadMethod = 'U'
PRINT 'XML Status = ' + STR(@status)