Often when working with XML data type we encounter issues with the schema validation for the incoming XML. SQL Server does allow us to have XML datatype without schema binding. However it is a best practice to enforce Schema Binding so that we can improve data validation as well as index performance when querying the XML data later.
The biggest problem SQL Developers encounter when working with XML datatype is that they are unfamiliar with the nature of XML and how to query it. Also the error messages coming from SQL Server aren’t always clear about what the root cause it. Take for example the below scenario
We start out by creating an XML schema for some data coming in
CREATE XML SCHEMA COLLECTION listingAttributeDemo1 AS '<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:element name="attributes"> <xs:complexType> <xs:simpleContent> <xs:extension base="xs:string"> <xs:attribute type="xs:byte" name="ListingId"/> <xs:attribute type="xs:byte" name="Bathrooms"/> <xs:attribute type="xs:byte" name="ParkingSpace"/> <xs:attribute type="xs:byte" name="Bedrooms"/> <xs:attribute type="xs:dateTime" name="CreatedDate"/> <xs:attribute type="xs:dateTime" name="UpdatedDate"/> <xs:attribute type="xs:byte" name="CreatedBy"/> <xs:attribute type="xs:byte" name="UpdatedBy"/> </xs:extension> </xs:simpleContent> </xs:complexType> </xs:element> </xs:schema>'
Notice how the element name in line 3 is attributes is in small case.
Next we create a variable that binds the XML datatype to the above schema.
DECLARE @data XML(listingAttributeDemo1) = N'<Attributes ListingId="2" Bathrooms="1" ParkingSpace="7" Bedrooms="2" CreatedDate="2018-03-01T23:00:28" UpdatedDate="2018-03-04T07:39:03" CreatedBy="12" UpdatedBy="12" />' SELECT @data
Notice how when passing the XML data I changed the Name of the element to Attributes with a Capital A.
In this case I get the below error because the schema validation is case sensitive regardless of collation and other factors.
Msg 6913, Level 16, State 1, Line 29
XML Validation: Declaration not found for element ‘Attributes’. Location: /*:Attributes[1]
To correct is we can fix the schema or change the case of the element as shown below
DECLARE @data XML(listingAttributeDemo1) = N'<attributes ListingId="2" Bathrooms="1" ParkingSpace="7" Bedrooms="2" CreatedDate="2018-03-01T23:00:28" UpdatedDate="2018-03-04T07:39:03" CreatedBy="12" UpdatedBy="12" />' SELECT @data
The correct solution would be to fix the schema so that it can track the data faithfully.
Please Consider Subscribing
