XML Validator

By Guest Authors on 9 February 2003 | 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

Step 1

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

Step 2

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)

Step 3

Save the DTS package as XMLValidator by providing both owner and user password.

Step 4

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)

Step 5

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)