There's been lots of puzzled faces lately when I try to explain doing XQuery against strongly typed XML (XML typed by a SCHEMA COLLECTION) vs untyped XML. The largest FAQ is why when I have this document:

<person>
  <name>bob</name>
  <age>51</age>
</person>

using the value function (after assignment to @person) @person.value('/person/age', 'int') returns my favorite error:

Msg 2389, Level 16, State 1, Line 6
XQuery [value()]: Operator 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

Huh? Although you know by looking at the document that there is only one age element, the XQuery parser uses static evaluation. It doesn't read your document (or read your mind) and assumes there can be more than one age element. After all, there's no schema to enforce the singleton age element, I could have 3 or 4 ages. It doesn't want to guess and be wrong at execution time. Using:

@person.value('/person[1]/age[1]', 'int')

works. I can see why age[1] is required, but why person[1]? Doesn't XML have a single root element? Actually, no. SQL Server 2005 supports fragments (well-formed, multiple root) as well as documents. Fragment support is part of the XQuery 1.0/XPath 2.0 data model.

Most people get by that. The real fun starts when you do examples using untyped XML and XPath expressions with the text() node test. text() works just fine when using untyped XML, but fails against typed XML with simple content. Here's an example (the result of a discussion with Dan Sullivan):

CREATE XML SCHEMA COLLECTION root AS
'<xs:schema
   xmlns:xs="http://www.w3.org/2001/XMLSchema"
   targetNamespace="urn:geo"
   xmlns:tns="urn:geo">
<xs:element name="Root" type="xs:string"/>
</xs:schema>
'
GO

– UNTYPED
– this works
DECLARE @x  xml
set @x = '<g:Root xmlns:g="urn:geo">asdf</g:Root>'
select @x.query('
 declare namespace g="urn:geo"
 /g:Root/text()')

– TYPED
– Msg 9312, Level 16, State 1, Line 4
– XQuery [query()]: 'text()' is not supported on simple typed
– or 'http://www.w3.org/2001/XMLSchema#anyType' elements,
– found 'element(g{urn:geo}:Root,xs:string) *'.

DECLARE @x  xml(root)
– same document
set @x = '<g:Root xmlns:g="urn:geo">asdf</g:Root>'
select @x.query('declare namespace g="urn:geo"
/g:Root[1]/text()')

But why? Isn't text() a node test that returns the value of a text() node. After casting about in XQuery specs, and SQL BOL, I finally came across this in the XML Best Practices paper.

– snip –

Data(),text() and string() accessors

XQuery has a function fn:data() to extract scalar, typed values from nodes, a node test text() to return text nodes, and the function fn:string() that returns the string value of a node. Their usages are sometimes confusing. Guidelines for their proper use in SQL Server 2005 are as follows. Consider the XML instance <age>12</age>.

Untyped XML: The path expression /age/text() returns the text node "12". The function fn:data(/age) returns the string value "12" and so does fn:string(/age).

Typed XML: The expression /age/text() returns static error for any simple typed <age> element. On the other hand, fn:data(/age) returns integer 12, while fn:string(/age) yields the string "12".

– snip –

Well, that was confusing. But now I think I get it. When does a element not have a text() node (or more preicsely, not allow the text() node test)?? When it's a strong-typed query using a simple type element…that's when. But why? Although I know the rules now, I'm still somewhat baffled.

This is getting pretty long, more on this topic in a bit…