Typed vs Untyped XML

Yesterday, we had covered the difference between an attribute-centric XML vs an element centric XML and how those can be generated in SQL Server 2005. Today, let’s talk about the difference between typed and untyped XML.  In SQL Server 2005, one can create a variable, a column or parameters of the XML data-type.  If in the application, you want that XML to be of a certain structure and you want that validation to happen everytime, then you can also associate a collection of XML schemas with these column(s), variable(s) or parameter(s).  When one uses the XML schema collection to enforce such rules, then the XML data type instance is called typed and when a XML schema collection is not used, it is called untyped.  In addition to the schema validation, this also serves the purpose of doing the data type validation as well.  Here is an example:

Let’s create an XML Schema Collection for an HR schema which has the minimal information on the Employee and their Department:

CREATE XML SCHEMA COLLECTION HR AS
'<?xml version="1.0"?>
<xsd:schema
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="EmpDept">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="EmpName">
<xsd:complexType>
<xsd:attribute name="name"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="name"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:schema>'

Now, let’s create an XML data-type instance for this XML and see how we can use it in a stored procedure by passing in that information:

/*Create a dummy stored procedure first*/

create proc test
(
@t    xml(HR)
)
as
begin
select @t
end
go

/*Now, let’s make the call*/

DECLARE @emp xml(HR)
SET @emp =
‘<EmpDept name=”HR”>
<EmpName name=”Donald” />
</EmpDept>’

exec test @t = @emp

The output is:

<EmpDept name=”HR”>
<EmpName name=”Donald” />
</EmpDept>

Now, let’s introduce two defects into this XML: One for the schema and one for the data-type and let’s see whether the validations will catch those two errors or not and what kind of error message is thrown:

/*Changed the schema structure: called the attribute ename instead of name*/

DECLARE @emp xml(HR)
SET @emp =
‘<EmpDept name=”HR”>
<EmpName ename=”Donald” />
</EmpDept>’

The error that gets thrown immediately upon the parsing of the XML is:

Msg 6905, Level 16, State 3, Line 3
XML Validation: Attribute ‘ename’ is not permitted in this context. Location: /*:EmpDept[1]/*:EmpName[1]/@*:ename

/*Create the XML with the  wrong data-type*/

DECLARE @emp xml(HR)
SET @emp =
‘<EmpDept name=”HR”>
<EmpName name=1234 />
</EmpDept>’

The error thrown is:

Msg 9413, Level 16, State 1, Line 2
XML parsing: line 2, character 17, A string literal was expected

As you can see from this simple example, a XML  schema collection can save you a lot of headaches and will act as the gatekeeper to your RDBMS system by ensuring that the XML Schema is proper.  You can put your TRY…CATCH blocks around the code to trap and log the errors.