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:

  1. Create "Microsoft.XMLDOM" Object
  2. Load the XML from the global Variable GlobalVariables("o_XmlString").Value or GlobalVariables("o_fileName").Value
  3. Get the errorcode
  4. 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:

  1. Create a DTS package object
  2. Instantiate connection to SQL Server
  3. 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
  4. If the xml is from a String then set the global variable o_XmlString of the DTS Package to the input XML String
  5. Execute the package
  6. Get the return status from the Global Variable o_XmlStatus from the DTS Package
  7. Destroy the created DTS package Object
  8. return the value 0(Valid XML) or -1 (Invalid XML)

Step 5

Execute the procedure as follows


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)

Related Articles

Parsing XML from a text column using sp_xml_preparedocument (5 December 2003)

Importing Large XML Documents using TEXTCOPY.EXE (3 December 2003)

SQLXML 3.0 SP1 (21 November 2002)

SQLXML 3.0 SP1 (10 June 2002)

Microsoft SQL Server 2000 Web Services Toolkit (19 February 2002)

SQLXML 3.0 Beta available for download (31 December 2001)

XML dominates database file formats (with Yukon info) (26 November 2001)

SQLXML 2.0 (XML for SQL Server 2000) (12 November 2001)

Other Recent Forum Posts

Using case statement in where clause (12h)

Combine records based on date in between date columns as a continuous single record (1d)

Need Help in SQL query optimization (4d)

Info needed on field "user_scans" of table "sys.dm_db_index_usage_stats" (4d)

Suspect Parameter Sniffing? (5d)

See values of a proc when it is called from another proc? (5d)

Varchar() datatype and C# SqlDataReader truncates string (6d)

Find the extra rows (6d)

- Advertisement -