Systems Engineering and RDBMS

Typed vs Untyped XML

Posted by decipherinfosys on September 9, 2007

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.

About these ads

4 Responses to “Typed vs Untyped XML”

  1. [...] Server 2005 had introduced the XML Schema Collection which were used for validating the XML data by enforcing compliance with one or more XSD schemas. [...]

  2. [...] in the integration need the XSD to get an idea of the schema, secondly, one can then enforce the XML schema collection at their end to ensure that the schema that they are getting is proper – this prevents any [...]

  3. [...] that could very easily be achieved by using an XML SCHEMA COLLECTION like we had shown over here.   In this case, we could then do validations for both the variables/values against that schema [...]

  4. [...] query() method: This method also allows us to perform XQuery on a xml instance and return back an untyped XML to us.   We had covered this in our post on the ReportServer DB Queries – here.  And [...]

Sorry, the comment form is closed at this time.

 
Follow

Get every new post delivered to your Inbox.

Join 80 other followers

%d bloggers like this: