Darshan Sighn, publisher of YukonXML posted an interested question on the SQL2005 XML newsgroup yesterday asking how to force an untyped XML instance to be like an normal XML document where there is a single root element. I'm not sure there's a way to do that just yet, short of a column constraint based in an XQuery that Darshan later posted. But our conversation brought out an interesting question from Roger Jennings; "For typed instances, isn't the document modifier superfluous?"
Now that's a great question because the actual behavior of SQL's XML types isn't what we might expect from coming from a DOM background. SQL's XML isn't a DOM instance, rather, its simply a ordered collection of well-formed XML nodes by default -- even when typed. That's important to keep in mind because it means you can multiple documents within any given instance of SQL's XML unless and until you bind the instance to a given schema collection and you specify the DOCUMENT argument when doing so. This is known as the Content mode for SQL's XML.
Because of this, even schema bound instances SQL's XML can contain multiple documents. A true DOM model would never let you do that, of course -- schema bound or not, DOM only supports the concept of a single root element. Don't look at this as bug -- its very much a feature, especially when it comes to being able to use XQuery on given instances. It is different though, so you do need to keep in mind.
A little example might make the difference clearer.
use scratch
go
drop xml schema collection dbo.forestSchema
go
create XML schema collection dbo.forestSchema as
N'<?xml version="1.0" encoding="utf-16"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="forest">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="tree">
<xs:complexType>
<xs:simpleContent>
<xs:extension base="xs:string">
<xs:attribute name="heightInMeters" type="xs:unsignedByte" use="required" />
<xs:attribute name="diameterInMeters" type="xs:decimal" use="required" />
</xs:extension>
</xs:simpleContent>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>'
go
-- Should work, returning 1 and 2.
declare @Sherwood xml(forestSchema)
set @Sherwood = N'<?xml version="1.0" ?><forest>
<tree heightInMeters="7" diameterInMeters=".5">Oak</tree>
<tree heightInMeters="12" diameterInMeters=".35">Pine</tree>
</forest>'
select @Sherwood.value('count(/forest)','int') as totalForests
, @Sherwood.value('count(/forest/tree)','int') as totalTrees
go
-- Should work, returning 2 and 4.
declare @Sherwood xml(forestSchema)
set @Sherwood = N'<forest>
<tree heightInMeters="7" diameterInMeters=".5">Oak</tree>
<tree heightInMeters="12" diameterInMeters=".35">Pine</tree>
</forest><forest>
<tree heightInMeters="7" diameterInMeters=".5">Oak</tree>
<tree heightInMeters="12" diameterInMeters=".35">Pine</tree>
</forest>'
select @Sherwood.value('count(/forest)','int') as totalForests
, @Sherwood.value('count(/forest/tree)','int') as totalTrees
go
-- Shouldn't work, our "Sherrif" (the schema) won't allow bandits in the forest.
declare @Sherwood xml(forestSchema)
set @Sherwood = N'<forest>
<tree heightInMeters="7" diameterInMeters=".5">Oak</tree>
<tree heightInMeters="12" diameterInMeters=".35">Pine</tree>
</forest><bandits><bandit>Hood, Robin</bandit></bandits><forest>
<tree heightInMeters="7" diameterInMeters=".5">Oak</tree>
<tree heightInMeters="12" diameterInMeters=".35">Pine</tree>
</forest>'
select @Sherwood.value('count(/forest)','int') as totalForests
, @Sherwood.value('count(/forest/tree)','int') as totalTrees
go
-- Should work because we have just one forest
declare @Sherwood xml(document forestSchema)
set @Sherwood = N'<forest>
<tree heightInMeters="7" diameterInMeters=".5">Oak</tree>
<tree heightInMeters="12" diameterInMeters=".35">Pine</tree>
</forest>'
select @Sherwood.value('count(/forest)','int') as totalForests
, @Sherwood.value('count(/forest/tree)','int') as totalTrees
go
-- Nope, document won't allow two roots in the same instance.
declare @Sherwood xml(document forestSchema)
set @Sherwood = N'<forest>
<tree heightInMeters="7" diameterInMeters=".5">Oak</tree>
<tree heightInMeters="12" diameterInMeters=".35">Pine</tree>
</forest><forest>
<tree heightInMeters="7" diameterInMeters=".5">Oak</tree>
<tree heightInMeters="12" diameterInMeters=".35">Pine</tree>
</forest>'
select @Sherwood.value('count(/forest)','int') as totalForests
, @Sherwood.value('count(/forest/tree)','int') as totalTrees
go
There's so many XML driven features in SQL Server 2000 and SQL Sever 2005 that sometimes its hard to know which part of the product you're talking about if you just say or write SQLXML. So, here's a brief glossary:
SQL's XML to me means you are talking about SQL Server 2005's XML as a data type feature. When spoken, make sure to clear annunciate the possessive "s" on SQL.
SQLXML is the feature stack for SQL 2000 and SQL 2005 offers an ISAPI filter exposing SQL objects as web services, the OLE DB for XML from SQL Server and XML bulk loading features. No pause is made between SQL and XML.
SQL XML is the most general term, meaning any feature of SQL Server that supports or enables working with XML. Pause between the two words.
So how do you use InfoPath and SQL Server 2005 Service Broker together? Rushi Desai to the rescue: Integerating InfoPath with SQL Server 2005.
Man this guy is cranking out the good stuff!