Yesterday’s post regarding the addition of a location in validation error messages brought back a conversation I’d had with our Program Manager regarding the use of that XPath to automatically retrieve invalid XML fragments when validation fails
I’ve decided to try it and since it works, I’m going to share it with you.
The idea behind this is very simple. Through a TRY-CATCH construct, it is possible to retrieve the error message when validation fails. Once you have this error message, you can do some string manipulation on it to extract the XPath. Your XML instance might be invalid with respect to your schema but as long as it is valid XML you can store it in an untyped XML variable. Then you can use the XPath to build a query that is run against your instance to retrieve the faulty XML fragment. In this case, I’ve decided to retrieve the parent of the faulty node pointed to by the XPath.
First I am going to create a stored procedure that, given an error message @err and an XML instance @x will return the parent of the node the XPath points to
CREATE PROCEDURE sp_fragment @x XML, @err nvarchar(MAX)
-- first find where the location starts in the error message
DECLARE @index int
SET @index = CHARINDEX('Location: ',@err)
-- if no location we cannot do anything
IF (@index <= 0)
-- move to the beginning of the XPath expression
SET @index = @index + 10
DECLARE @Xpath nvarchar(MAX)
-- capture the XPath and use the parent axis
SET @Xpath = SUBSTRING(@err, @index , LEN(@err) - @index + 1) + '/..'
-- build the rest of the query
DECLARE @query nvarchar(MAX)
SET @query = 'SET QUOTED_IDENTIFIER ON; DECLARE @var XML;'
SET @query = @query + ' SET @var = ''' + CONVERT(nvarchar(MAX), @x) + ''';'
SET @query = @query + 'SELECT @var.query('''+@Xpath + ''')'
-- print the error message for the benefit of the user
-- Run the query to retrieve the location
PRINT 'Error Message does not specify an XPath location'
As you can see, this is fairly simple. Now, let’s see how we can use this stored procedure in conjunction with validation.
First we’re going to need a schema collection and a table with a typed XML column.
create xml schema collection SC_test AS '
<element name="root" type="ns:rootType"/>
<element name="a" minOccurs="2" maxOccurs="6" type="string"/>
<element name="b" minOccurs="0" maxOccurs="unbounded"/>
<attribute name="order" type="int"/>
create table T(xmlCol XML(SC_test))
Then we’re going to build a stored procedure that, given an XML instance @var will insert it into table T
CREATE PROCEDURE insert_into_T @var XML
DECLARE @err nvarchar(MAX)
INSERT INTO T VALUES (@var);
SET @err = ERROR_MESSAGE()
EXEC sp_fragment @var, @err;
As you can see, I am making use of the TRY-CATCH construct. If an error occurs as we insert the value into T we retrieve the error message and pass it, as well as the XML instance, to the sp_fragment procedure.
Now let’s use procedure insert_into_T with an XML instance that is invalid with respect the the SC_test collection and see what happens.
EXEC insert_into_T '<x:root xmlns:x="myNS" order="1"><a/><a/><a/></x:root><x:root xmlns:x="myNS" order="2"><a/><a/><a/><b>1</b><b>2</b></x:root><x:root xmlns:x="myNS" order="3"><a/><a/><a/><a/><a/><a/><a/><a/><b>0</b></x:root><x:root xmlns:x="myNS" order="4"><a/><a/><a/></x:root><x:root xmlns:x="myNS" order="5"><a/><a/><a/></x:root><x:root xmlns:x="myNS" order="6"><a/><a/><a/></x:root>'
We get the following output (I simplified the formatting)
XML Validation: Invalid content. Expected element(s):b where element 'a' was specified. Location: /*:root/*:a
<x:root xmlns:x="myNS" order="3"><a/><a/><a/><a/><a/><a/><a/><a/><b>0</b></x:root>
Now I can use any clue I want (such as the value of the ‘order’ attribute on the ‘root’ element) to quickly find the problem spot in my instance.
I hope this quick example inspires you to use the XPath we’ve added to the validation error messages. Don’t hesitate to drop me a comment or an email if you have interesting solutions of your own that you are willing and able to share.
This posting is provided “AS IS” with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm.