{"id":482,"date":"2012-06-20T15:12:00","date_gmt":"2012-06-20T15:12:00","guid":{"rendered":"\/blogs\/bobb\/post\/Does-everybody-get-that-(XML-or-relational-for-multi-table-documents).aspx"},"modified":"2013-01-03T23:59:22","modified_gmt":"2013-01-04T07:59:22","slug":"does-everybody-get-that-xml-or-relational-for-multi-table-xml-documents","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/does-everybody-get-that-xml-or-relational-for-multi-table-xml-documents\/","title":{"rendered":"Does everybody get that? (XML or relational for “multi-table” XML documents)"},"content":{"rendered":"

\nThis post is an example of the "bring your own problem" approach in the classroom, inspired by upcoming SQLskills Immersion Events<\/a> in August. See previous posting<\/a> for the reason behind the blog post title.\n<\/p>\n

\nSQL Server is a relational database that supports XML. But just because your data is received in XML format doesn't necessarily mean you should store it that way for querying. And if you intend to do any serious querying of your XML, be prepared to use at least a primary XML index.\n<\/p>\n

\nI'd had students who planned to use XML in SQL Server for a big application with lots of queries (in the hundreds of thousands per day) and lots of inserts. Think of your typical OLTP system does with XML documents instead of multi-table joins. A single document might contain information from 4-5 or more relational tables. Is it a good idea to store what's usually relational data as XML?\n<\/p>\n

\nXML is stored in SQL Server in a propriatary binary format. SQL Server's XQuery is not standalone, but always combined with SQL statements. To be useable in these statements the XML is decomposed (or the part of its that's needed to satisfy the XML predicate is) into relatonal in-memory structures. See the TVF steps XmlReader or XmlReader with XPath Filter iterators in the query plan. That's more-or-less the structure that is persisted on disk (as an internal table) when you create XML indexes. The primary XML index and secondary XML indexes are simply clustered and nonclustered indexes over the internal table. With these indexes SQL Server has a reasonably fast implimentation of XQuery, for a relational database, because its using the relational engine. But "raw" XML has no statistics on the element and attribute values to help the relational engine select a best plan. You'll see huge costs for this decomposition in SQL query plans, but bear in mind that the costs may be overestimated because of overestimating of cardinality, even with schema-valid XML.\n<\/p>\n

\nSo you can do partial decomposition once-or-more per-query or decompose once (during insert of a row with an XML column) and query the structure using the XML index many times. Either way, you're using relational queries… but the overhead of the decomposition is "non-trivial". And the table structure is non-trivial, taking up between 2-6 times the space of the original XML. \n<\/p>\n

\nIf you want to store XML but need to query only one or two element or attribute values, consider persisted computed columns, based on XQuery. You can even persist a subset of the XML document into a separate table (think instead-of trigger) and query that, to cut down on the size of the XML index(es). Remember though, you will be doing one or more XML queries against non-indexed data with each insert into the base table.\n<\/p>\n

\nThe upshot of all this is, if you have data that can be modeled as relational, use relational tables. If you'd thought of using XML columns and queries because it is a ubiquitous format that everyone uses (the "its cool" factor), well…you'll thank me later. And if you do go with XQuery, don't think "I won't use XML indexes, they are too big". Use 'em…and\/or persisted computed columns.\n<\/p>\n

\nDoes everybody get that?\n<\/p>\n

\n@bobbeauch<\/p>\n","protected":false},"excerpt":{"rendered":"

This post is an example of the "bring your own problem" approach in the classroom, inspired by upcoming SQLskills Immersion Events in August. See previous posting for the reason behind the blog post title. SQL Server is a relational database that supports XML. But just because your data is received in XML format doesn't necessarily […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[37],"tags":[],"class_list":["post-482","post","type-post","status-publish","format-standard","hentry","category-sql-server-xml"],"yoast_head":"\nDoes everybody get that? (XML or relational for "multi-table" XML documents) - 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\/does-everybody-get-that-xml-or-relational-for-multi-table-xml-documents\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Does everybody get that? (XML or relational for "multi-table" XML documents) - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"This post is an example of the "bring your own problem" approach in the classroom, inspired by upcoming SQLskills Immersion Events in August. See previous posting for the reason behind the blog post title. SQL Server is a relational database that supports XML. But just because your data is received in XML format doesn't necessarily […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/does-everybody-get-that-xml-or-relational-for-multi-table-xml-documents\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2012-06-20T15:12:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-01-04T07:59:22+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\/does-everybody-get-that-xml-or-relational-for-multi-table-xml-documents\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/does-everybody-get-that-xml-or-relational-for-multi-table-xml-documents\/\",\"name\":\"Does everybody get that? (XML or relational for \\\"multi-table\\\" XML documents) - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2012-06-20T15:12:00+00:00\",\"dateModified\":\"2013-01-04T07:59:22+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/does-everybody-get-that-xml-or-relational-for-multi-table-xml-documents\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/does-everybody-get-that-xml-or-relational-for-multi-table-xml-documents\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/does-everybody-get-that-xml-or-relational-for-multi-table-xml-documents\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server XML\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/sql-server-xml\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Does everybody get that? (XML or relational for “multi-table” XML documents)\"}]},{\"@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":"Does everybody get that? (XML or relational for \"multi-table\" XML documents) - 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\/does-everybody-get-that-xml-or-relational-for-multi-table-xml-documents\/","og_locale":"en_US","og_type":"article","og_title":"Does everybody get that? (XML or relational for \"multi-table\" XML documents) - Bob Beauchemin","og_description":"This post is an example of the "bring your own problem" approach in the classroom, inspired by upcoming SQLskills Immersion Events in August. See previous posting for the reason behind the blog post title. SQL Server is a relational database that supports XML. But just because your data is received in XML format doesn't necessarily […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/does-everybody-get-that-xml-or-relational-for-multi-table-xml-documents\/","og_site_name":"Bob Beauchemin","article_published_time":"2012-06-20T15:12:00+00:00","article_modified_time":"2013-01-04T07:59:22+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\/does-everybody-get-that-xml-or-relational-for-multi-table-xml-documents\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/does-everybody-get-that-xml-or-relational-for-multi-table-xml-documents\/","name":"Does everybody get that? (XML or relational for \"multi-table\" XML documents) - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2012-06-20T15:12:00+00:00","dateModified":"2013-01-04T07:59:22+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/does-everybody-get-that-xml-or-relational-for-multi-table-xml-documents\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/does-everybody-get-that-xml-or-relational-for-multi-table-xml-documents\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/does-everybody-get-that-xml-or-relational-for-multi-table-xml-documents\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/"},{"@type":"ListItem","position":2,"name":"SQL Server XML","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/sql-server-xml\/"},{"@type":"ListItem","position":3,"name":"Does everybody get that? (XML or relational for “multi-table” XML documents)"}]},{"@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\/482","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=482"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/482\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=482"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=482"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=482"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}