{"id":969,"date":"2005-01-27T19:03:00","date_gmt":"2005-01-27T19:03:00","guid":{"rendered":"\/blogs\/bobb\/post\/Fun-with-static-XQuery-evaluation-4-answers-and-wrapup.aspx"},"modified":"2013-09-25T13:03:53","modified_gmt":"2013-09-25T20:03:53","slug":"fun-with-static-xquery-evaluation-4-answers-and-wrapup","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/fun-with-static-xquery-evaluation-4-answers-and-wrapup\/","title":{"rendered":"Fun with static XQuery evaluation – 4 – answers and wrapup"},"content":{"rendered":"

Here’s the answers to the question from Fun With static XQuery evaluation – 2<\/p>\n

— start with a schema collection<\/p>\n

CREATE XML SCHEMA COLLECTION ages AS
\n‘<xs:schema
\nxmlns:xs=”http:\/\/www.w3.org\/2001\/XMLSchema<\/a>”
\ntargetNamespace=”urn:ages”
\nxmlns:tns=”urn:ages”>
\n<xs:element name=”age” type=”xs:int”\/>
\n<\/xs:schema>
\n‘
\nGO<\/p>\n

DECLARE @x xml(ages)
\nSET @x = ‘<age>12<\/age>’
\n— fails ??!
\nSELECT @x.query(‘string(\/age)’)
\nGO<\/p>\n

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

— These work —<\/p>\n

— this query restricts it to the first age element
\nDECLARE @x xml(ages)
\nSET @x = ‘<age>12<\/age>’
\nSELECT @x.query(‘string(\/age[1])’)
\nGO<\/p>\n

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

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:<\/p>\n

declare @x xml(content ages)\u00a0\u00a0\u00a0 — use ages xml schema collection, allow fragments or documents
\ndeclare @x xml(document ages) — disallow fragments; documents only
\ndeclare @x xml(ages)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 — equals using “content”<\/p>\n

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:<\/p>\n

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

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\u00a0is in the excellent XML Best Practices for Microsoft SQL Server 2005\u00a0<\/a>document.<\/p>\n

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\u00a0July 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.<\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28,37],"tags":[],"class_list":["post-969","post","type-post","status-publish","format-standard","hentry","category-sql-server-2005","category-sql-server-xml"],"yoast_head":"\nFun with static XQuery evaluation - 4 - answers and wrapup - Bob Beauchemin<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/fun-with-static-xquery-evaluation-4-answers-and-wrapup\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Fun with static XQuery evaluation - 4 - answers and wrapup - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"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 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/fun-with-static-xquery-evaluation-4-answers-and-wrapup\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2005-01-27T19:03:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-09-25T20:03:53+00:00\" \/>\n<meta name=\"author\" content=\"Bob Beauchemin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Bob Beauchemin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/fun-with-static-xquery-evaluation-4-answers-and-wrapup\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/fun-with-static-xquery-evaluation-4-answers-and-wrapup\/\",\"name\":\"Fun with static XQuery evaluation - 4 - answers and wrapup - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2005-01-27T19:03:00+00:00\",\"dateModified\":\"2013-09-25T20:03:53+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/fun-with-static-xquery-evaluation-4-answers-and-wrapup\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/fun-with-static-xquery-evaluation-4-answers-and-wrapup\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/fun-with-static-xquery-evaluation-4-answers-and-wrapup\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server 2005\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/sql-server-2005\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Fun with static XQuery evaluation – 4 – answers and wrapup\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\",\"name\":\"Bob Beauchemin\",\"description\":\"SQL Server Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\",\"name\":\"Bob Beauchemin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"caption\":\"Bob Beauchemin\"},\"sameAs\":[\"http:\/www.sqlskills.com\/blogs\/bobb\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Fun with static XQuery evaluation - 4 - answers and wrapup - Bob Beauchemin","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/bobb\/fun-with-static-xquery-evaluation-4-answers-and-wrapup\/","og_locale":"en_US","og_type":"article","og_title":"Fun with static XQuery evaluation - 4 - answers and wrapup - Bob Beauchemin","og_description":"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 […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/fun-with-static-xquery-evaluation-4-answers-and-wrapup\/","og_site_name":"Bob Beauchemin","article_published_time":"2005-01-27T19:03:00+00:00","article_modified_time":"2013-09-25T20:03:53+00:00","author":"Bob Beauchemin","twitter_misc":{"Written by":"Bob Beauchemin","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/fun-with-static-xquery-evaluation-4-answers-and-wrapup\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/fun-with-static-xquery-evaluation-4-answers-and-wrapup\/","name":"Fun with static XQuery evaluation - 4 - answers and wrapup - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2005-01-27T19:03:00+00:00","dateModified":"2013-09-25T20:03:53+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/fun-with-static-xquery-evaluation-4-answers-and-wrapup\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/fun-with-static-xquery-evaluation-4-answers-and-wrapup\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/fun-with-static-xquery-evaluation-4-answers-and-wrapup\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/"},{"@type":"ListItem","position":2,"name":"SQL Server 2005","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/sql-server-2005\/"},{"@type":"ListItem","position":3,"name":"Fun with static XQuery evaluation – 4 – answers and wrapup"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/","name":"Bob Beauchemin","description":"SQL Server Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e","name":"Bob Beauchemin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","caption":"Bob Beauchemin"},"sameAs":["http:\/www.sqlskills.com\/blogs\/bobb\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/969","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/comments?post=969"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/969\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=969"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=969"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=969"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}