The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Stored procedure to validate XML.

I am validating some XML against a XSD document in VB.NET and then I put it into SQL Server -- it goes into a varchar(MAX) field. I would really like to put it into the database first and then check whether it is valid. And get a stored procedure to check the XML.

Can you get a SQL Server 2005 stored procedure to validate XML ?

Thanks,
Craig
Craig HB Send private email
Tuesday, November 28, 2006
 
 
Since you are using SQL 2005 and are using the varchar(max) datatype you are not going to make it available to SQL 2000 users anyway, how about a CLR stored procedure where I am sure you could do it...

I don't know if you can do it with a TSQL stored procedure.
Jared
Tuesday, November 28, 2006
 
 
You can do it if you want to badly enough, but why would you want to?

Database engines optimize stored procedures to process records, not characters within an individual record's field. Conceptually, you can write a very elaborate conditional that parses each character (which you'll have to do anyway to verify that tags are properly closed) but the more evaluation branches you provide within your stored procedure, the harder it is for the database engine to come up with a satisfactory explain plan.

I think you're better off in the long run inserting the virgin XML, selecting it back out, validating it outside of the database, and then updating the original record with the status. Or some variation thereof.
TheDavid
Tuesday, November 28, 2006
 
 
You database should validate *datatypes* not valid data.  That's what business logic is for.
KC Send private email
Tuesday, November 28, 2006
 
 
Create an xml schema collection based on the xsd you have. Then create a procedure that takes an xml parameter and pass the value of that parameter into an xml parameter defined with the schema collection.

The only problem I encountered with this so far is that processContents="lax" is not implemented.

The code below should be completed with your schema to make it work.

CREATE XML SCHEMA COLLECTION xsd_message AS
N'<?xml version="1.0"?>
<xsd:schema .....</xsd:schema>' ;
GO
CREATE PROCEDURE p_validate_xmlinput(@message XML)
AS

declare @validatedxml XML (xsd_message)

BEGIN TRY
    SELECT @validatedxml = @message
END TRY
BEGIN CATCH
    set @err = ERROR_MESSAGE();
    raiserror('%s: Error validating: %s, %s', 16, 1, @proc_name, @message, @err);
    return(1);
END CATCH

RETURN (0)

GO
lonely consultant
Wednesday, November 29, 2006
 
 
+1 to lonely consultant and TheDavid. We just recently ran a couple hundred thousand xml docs through a validation process, and I have some C# code that you can have if you want it. It was mostly hacked together just to solve this particular problem, so it's not elegant or anything, but it works. The tough part is getting the XSD right in the SCHEMACOLLECTION.
Lou Schilling Send private email
Saturday, December 02, 2006
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz