{"id":959,"date":"2005-03-13T12:54:00","date_gmt":"2005-03-13T12:54:00","guid":{"rendered":"\/blogs\/bobb\/post\/FOR-XMLXMLSCHEMA-and-schema-validation.aspx"},"modified":"2013-01-04T00:00:59","modified_gmt":"2013-01-04T08:00:59","slug":"for-xml-xmlschema-and-schema-validation","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/for-xml-xmlschema-and-schema-validation\/","title":{"rendered":"FOR XML…XMLSCHEMA and schema validation"},"content":{"rendered":"

\nWhen reading the CTPNotes file from the new Feb CTP build I stumbled across the fact that the XML schema that contains SQL data types (http:\/\/schemas.microsoft.com\/sqlserver\/2004\/sqltypes<\/a>) is now built-in to the server. Although this may not mean much to most people, it gave me the chance to try something that Dan Sullivan thought up for the first rev of our SQL Server 2005 class. It works now.\n<\/p>\n

\nOne of the enhancements to SELECT…FOR XML is ability to request that the XML it produces be prepended by an XML schema that describes it. A recent change allows you to choose the namespace for that schema. Dan's idea was to add the prepended schema to create a schema collection. After storing the FOR XML outside in an XML schema-valid column, you could make updates to the column that would be validated by the schema. You'd set this up like this:\n<\/p>\n

\ndeclare @x xml
\nselect @x = (select * from authors for xml auto, type, xmlschema('urn:authors')).query('*[1]')
\ncreate xml schema collection authorsxsd
\nas @x
\ngo\n<\/p>\n

\ncreate table authorsxml (
\n id int primary key identity, — primary key required if XML index needed
\n authors xml(authorsxsd))
\ngo\n<\/p>\n

\ndeclare @x xml(authorsxsd)
\nset @x = (select * from authors for xml auto, type, xmlschema('urn:authors')).query('\/*[position()>1]')
\ninsert authorsxml values(@x)\n<\/p>\n

\nThe XML Schema produced in the first step will now validate any information entered or updated in the table.\n<\/p>\n

\nWhy the Feb CTP change makes this work is FOR XML….XMLSCHEMA uses the SQL data types schema that's now built in. In previous betas, you could use this schema (error: not built in) or add the schema manually (error: it is built in [but it wasn't]). Thanks SQL Server 2005 XML folks, for this.<\/p>\n","protected":false},"excerpt":{"rendered":"

When reading the CTPNotes file from the new Feb CTP build I stumbled across the fact that the XML schema that contains SQL data types (http:\/\/schemas.microsoft.com\/sqlserver\/2004\/sqltypes) is now built-in to the server. Although this may not mean much to most people, it gave me the chance to try something that Dan Sullivan thought up for […]<\/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-959","post","type-post","status-publish","format-standard","hentry","category-sql-server-2005","category-sql-server-xml"],"yoast_head":"\nFOR XML...XMLSCHEMA and schema validation - 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\/for-xml-xmlschema-and-schema-validation\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"FOR XML...XMLSCHEMA and schema validation - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"When reading the CTPNotes file from the new Feb CTP build I stumbled across the fact that the XML schema that contains SQL data types (http:\/\/schemas.microsoft.com\/sqlserver\/2004\/sqltypes) is now built-in to the server. Although this may not mean much to most people, it gave me the chance to try something that Dan Sullivan thought up for […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/for-xml-xmlschema-and-schema-validation\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2005-03-13T12:54:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-01-04T08:00:59+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\/for-xml-xmlschema-and-schema-validation\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/for-xml-xmlschema-and-schema-validation\/\",\"name\":\"FOR XML...XMLSCHEMA and schema validation - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2005-03-13T12:54:00+00:00\",\"dateModified\":\"2013-01-04T08:00:59+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/for-xml-xmlschema-and-schema-validation\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/for-xml-xmlschema-and-schema-validation\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/for-xml-xmlschema-and-schema-validation\/#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\":\"FOR XML…XMLSCHEMA and schema validation\"}]},{\"@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":"FOR XML...XMLSCHEMA and schema validation - 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\/for-xml-xmlschema-and-schema-validation\/","og_locale":"en_US","og_type":"article","og_title":"FOR XML...XMLSCHEMA and schema validation - Bob Beauchemin","og_description":"When reading the CTPNotes file from the new Feb CTP build I stumbled across the fact that the XML schema that contains SQL data types (http:\/\/schemas.microsoft.com\/sqlserver\/2004\/sqltypes) is now built-in to the server. Although this may not mean much to most people, it gave me the chance to try something that Dan Sullivan thought up for […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/for-xml-xmlschema-and-schema-validation\/","og_site_name":"Bob Beauchemin","article_published_time":"2005-03-13T12:54:00+00:00","article_modified_time":"2013-01-04T08:00:59+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\/for-xml-xmlschema-and-schema-validation\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/for-xml-xmlschema-and-schema-validation\/","name":"FOR XML...XMLSCHEMA and schema validation - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2005-03-13T12:54:00+00:00","dateModified":"2013-01-04T08:00:59+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/for-xml-xmlschema-and-schema-validation\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/for-xml-xmlschema-and-schema-validation\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/for-xml-xmlschema-and-schema-validation\/#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":"FOR XML…XMLSCHEMA and schema validation"}]},{"@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\/959","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=959"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/959\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=959"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=959"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=959"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}