June 6, 2011 at 7:17 am
Hi
We have created an SP that accepts an XML parameter that is validated using an XSD.
We create a transaction, delete the old data and then load in the new data then commit. A minor irritant, it doesn't actually do the validation until we come to insert the data if the XML is not valid it then has to rollback the transaction.
Does anyone know of a way to validate the XML being passed at the start of the proc so we can just abort there if necessary?
Thanks.
June 6, 2011 at 9:46 am
S t e f (6/6/2011)
HiWe have created an SP that accepts an XML parameter that is validated using an XSD.
Are you sure you bound the schema to the proc parameter for validation correctly? The proc will not accept the data at all if it does not pass schema validation similar to how a proc would generate an error if passed the letter a into a parameter of type INT.
Can you post the proc code, the XML schema definition and the actual XML you're trying?
Here is a proof of concept to demonstrate:
USE tempdb
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.insert_some_things')
AND type IN (N'P', N'PC') )
DROP PROCEDURE dbo.insert_some_things ;
GO
IF EXISTS ( SELECT *
FROM sys.xml_schema_collections c,
sys.schemas s
WHERE c.schema_id = s.schema_id
AND (QUOTENAME(s.name) + '.' + QUOTENAME(c.name)) = N'[dbo].[my_schema]' )
DROP XML SCHEMA COLLECTION [dbo].[my_schema]
GO
CREATE XML SCHEMA COLLECTION my_schema AS
'<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="item">
<xs:complexType>
<xs:attribute name="name" type="xs:string" use="required" />
<xs:attribute name="value" type="xs:string" use="required" />
</xs:complexType>
</xs:element>
</xs:schema>'
GO
CREATE PROC dbo.insert_some_things
(
@things XML(DOCUMENT my_schema)
)
AS
BEGIN
SELECT @things
END
GO
EXEC dbo.insert_some_things
@things = NULL ;
GO
EXEC dbo.insert_some_things
@things = N'<item name="some_thing" value="some_value"/>' ;
GO
EXEC dbo.insert_some_things
@things = N'<item wrong_name="some_thing" wrong_value="some_value"/>' ;
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply