Fun with static XQuery evaluation – 4 – answers and wrapup

Here’s the answers to the question from Fun With static XQuery evaluation – 2

— start with a schema collection

CREATE XML SCHEMA COLLECTION ages AS
‘<xs:schema
xmlns:xs=”http://www.w3.org/2001/XMLSchema
targetNamespace=”urn:ages”
xmlns:tns=”urn:ages”>
<xs:element name=”age” type=”xs:int”/>
</xs:schema>

GO

DECLARE @x xml(ages)
SET @x = ‘<age>12</age>’
— fails ??!
SELECT @x.query(‘string(/age)’)
GO

This fails because there can be more than one <age> element and fn:string requires a singleton or empty sequence.

— These work —

— this query restricts it to the first age element
DECLARE @x xml(ages)
SET @x = ‘<age>12</age>’
SELECT @x.query(‘string(/age[1])’)
GO

— this restricts the variable to XML documents. Fragments disallowed.
— This means there can be only ONE (or zero) age elements.
— No subscript is needed on the query then.
DECLARE @x xml(document ages)
SET @x = ‘<age>12</age>’
SELECT @x.query(‘string(/age)’)
GO

The second one was a bit harder if you haven’t run across the (document schemacollection) construct. Remember that XML data type can contain documents or fragments. Putting “document ” before the schema collection name in any typed XML declaration restricts instances to an XML document (ie, single root element). The default is “content” so:

declare @x xml(content ages)    — use ages xml schema collection, allow fragments or documents
declare @x xml(document ages) — disallow fragments; documents only
declare @x xml(ages)               — equals using “content”

Note that you can only enforce “document only” using this keyword with TYPED XML. It’s not supported on untyped XML instances. You can do the equivalent enforcement with an untyped XML column in a table by using an XML check constraint, like this:

create table foo (
xmlcol xml constraint mycontr
xmlcol.value(‘count(/*)’, ‘int’) = 1 and xmlcol.exist(‘/text()’)=0

Hope you’ve enjoy this foray into static typing and XQuery. Because this is a “implementation decision” you won’t find much about this in the W3C spec. The best information about this is in the excellent XML Best Practices for Microsoft SQL Server 2005 document.

BTW, in case you collect W3C specs for your own offline reference (like I do), bear in mind that the final SQL Server 2005 implementation of XQuery will be aligned with the W3C July 2004 XQuery spec series. XQuery is still a W3C “work in progress”. SQL Server 2005 implements a subset of the functions and operators, adds functions to access T-SQL variables and SQL columns, and also implements static typing. So it’s not a 1-to-1 match with the spec, but if you like W3C specs, July 2004 is the one you want. For now.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.