XML Validator

By Guest Authors on 09 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

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

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)

Discuss this article: 0 Comments so far. Print this Article. This page has been read 15,650 times.

If you like this article you can sign up for our newsletter. We send it out each week that we post a new article. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

Email This • Subscribe to this feed • Kick it • Save to del.icio.us • View blog reactions