Using the XPath provided in XML validation error messages

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)
  GOTO NoLoc

 -- 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 = @query + ' SET @var = ''' + CONVERT(nvarchar(MAX), @x) + ''';'
 SET @query = @query + 'SELECT @var.query(
'''+@Xpath + ''')'
 -- print the error message for the benefit of the user
 PRINT @err
 -- Run the query to retrieve the location
 GOTO Done

 PRINT @err
 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 '
<schema xmlns=""

 <element name="root" type="ns:rootType"/>

 <complexType name="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)

  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[3]/*:a[7]

<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

Skip to main content