{"id":972,"date":"2005-01-26T23:22:00","date_gmt":"2005-01-26T23:22:00","guid":{"rendered":"\/blogs\/bobb\/post\/Fun-with-static-XQuery-evaluation-1.aspx"},"modified":"2005-01-26T23:22:00","modified_gmt":"2005-01-26T23:22:00","slug":"fun-with-static-xquery-evaluation-1","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/fun-with-static-xquery-evaluation-1\/","title":{"rendered":"Fun with static XQuery evaluation – 1"},"content":{"rendered":"

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

\n<person>
\n  <name>bob<\/name>
\n  <age>51<\/age>
\n<\/person>\n<\/p>\n

\nusing the value function (after assignment to @person) @person.value('\/person\/age', 'int') returns my favorite error:\n<\/p>\n

\nMsg 2389, Level 16, State 1, Line 6
\nXQuery [value()]: Operator 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'\n<\/p>\n

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

\n@person.value('\/person[1]\/age[1]', 'int')\n<\/p>\n

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

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

\nCREATE XML SCHEMA COLLECTION root AS
\n'<xs:schema
\n   xmlns:xs="http:\/\/www.w3.org\/2001\/XMLSchema<\/a>"
\n   targetNamespace="urn:geo"
\n   xmlns:tns="urn:geo">
\n<xs:element name="Root" type="xs:string"\/>
\n<\/xs:schema>
\n'
\nGO\n<\/p>\n

\n— UNTYPED
\n— this works
\nDECLARE @x  xml
\nset @x = '<g:Root xmlns:g="urn:geo">asdf<\/g:Root>'
\nselect @x.query('
\n declare namespace g="urn:geo"
\n \/g:Root\/text()')\n<\/p>\n

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

\nDECLARE @x  xml(root)
\n— same document
\nset @x = '<g:Root xmlns:g="urn:geo">asdf<\/g:Root>'
\nselect @x.query('declare namespace g="urn:geo"
\n\/g:Root[1]\/text()')\n<\/p>\n

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

\n— snip —\n<\/p>\n

\nData(),text() and string() accessors\n<\/p>\n

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

\nUntyped 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).\n<\/p>\n

\nTyped 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".\n<\/p>\n

\n— snip —\n<\/p>\n

\nWell, 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.\n<\/p>\n

\nThis is getting pretty long, more on this topic in a bit…<\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/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-972","post","type-post","status-publish","format-standard","hentry","category-sql-server-2005","category-sql-server-xml"],"yoast_head":"\nFun with static XQuery evaluation - 1 - 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-1\/\" \/>\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 - 1 - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"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 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/fun-with-static-xquery-evaluation-1\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2005-01-26T23:22:00+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=\"3 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-1\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/fun-with-static-xquery-evaluation-1\/\",\"name\":\"Fun with static XQuery evaluation - 1 - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2005-01-26T23:22:00+00:00\",\"dateModified\":\"2005-01-26T23:22:00+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-1\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/fun-with-static-xquery-evaluation-1\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/fun-with-static-xquery-evaluation-1\/#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 – 1\"}]},{\"@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 - 1 - 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-1\/","og_locale":"en_US","og_type":"article","og_title":"Fun with static XQuery evaluation - 1 - Bob Beauchemin","og_description":"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 […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/fun-with-static-xquery-evaluation-1\/","og_site_name":"Bob Beauchemin","article_published_time":"2005-01-26T23:22:00+00:00","author":"Bob Beauchemin","twitter_misc":{"Written by":"Bob Beauchemin","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/fun-with-static-xquery-evaluation-1\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/fun-with-static-xquery-evaluation-1\/","name":"Fun with static XQuery evaluation - 1 - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2005-01-26T23:22:00+00:00","dateModified":"2005-01-26T23:22:00+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-1\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/fun-with-static-xquery-evaluation-1\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/fun-with-static-xquery-evaluation-1\/#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 – 1"}]},{"@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\/972","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=972"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/972\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=972"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=972"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=972"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}