{"id":461,"date":"2012-12-12T10:30:00","date_gmt":"2012-12-12T10:30:00","guid":{"rendered":"\/blogs\/bobb\/post\/Returning-Document-Property-Values-in-Full-Text-Seach.aspx"},"modified":"2014-01-20T12:22:30","modified_gmt":"2014-01-20T20:22:30","slug":"how-to-return-document-property-values-in-full-text-search","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/how-to-return-document-property-values-in-full-text-search\/","title":{"rendered":"How To Return Document Property Values in Full-Text Search"},"content":{"rendered":"

\nAbout a week ago I got involved with a question via Twitter that was posted originally to Stack Exchange<\/a>. The question was "Is it possible to list document properties via FTS"? The question refers to SQL Server 2012's new support or property-based search with a syntax that looks like this:\n<\/p>\n

\nSELECT name DocumentName, file_stream.GetFileNamespacePath() Path
\nFROM Documents
\nWHERE CONTAINS(PROPERTY(file_stream, 'Title'), 'data OR SQL');\n<\/p>\n

\nThis functionality has nice synergy with another SQL Server 2012 feature, the filetable feature. So you can store documents in a filetable and search with FTS. Including document properties. There's just one thing missing: no way to get the actual property values after you've searched for them. So, as I wrote in this suggestion on Connect<\/a>:\n<\/p>\n

\n"Suppose I have three documents that have authors of "Bob Beauchemin", "Bob Newhart" and "Bob Gupta". I can do a CONTAINS-based search for documents with authors that have a keyword "Bob", but can't bring back the entire propery value, which would be very useful.\n<\/p>\n

\nCurrently, the closest I can get is using sys.dm_fts_index_keywords_by_property. But think only produces the keywords, not the property values. So, in the case above, I wouldn't know if the actual author name was "Bob Newhart" or "Newhart Bob". and keywords wouldn't include noisewords."\n<\/p>\n

\nWell, now there IS a solution. I'd just written a blog post <\/a>about a standalone component for full-text search called ThinkHighlight<\/a>. Wrote to the folks over at Interactive Thoughts and… voila! Their latest version supports returning not only highlighted text, but an XML format column that includes highlighted text AND property name-value pairs called HitHighlightEx. It includes all the properties as the canonical propertyset\/propertyid, and if you're FTS index uses a SEARCH PROPERTY LIST, it will resolve the propertyset\/propertyid to the property name from the SEARCH PROPERTY LIST.\n<\/p>\n

\nAs an example, let's use the demo script from the FTS blog. I can search for the keyword "compression" and return highlighted text and properties:\n<\/p>\n

\nDECLARE @query nvarchar(50) = 'compression';
\nDECLARE @context varbinary(8000) = dbo.HitHighlightContext('Documents', 'file_stream', @query, -1);
\nSELECT TOP 10 Name, dbo.HitHighlightEx(@context, 'top-fragment', 200, stream_id) as description
\nFROM Documents
\nWHERE CONTAINS(file_stream, @query);\n<\/p>\n

\nBut better, I can take the property XML apart and return individual columns as properties:\n<\/p>\n

\nDECLARE @query nvarchar(50) = 'compression';
\nDECLARE @context varbinary(8000) = dbo.HitHighlightContext('Documents', 'file_stream', @query, -1);
\nSELECT Name,
\n  Description.value('(\/document\/properties\/property[@name="Title"]\/@value)[1]', 'nvarchar(max)') as Title,
\n  Description.value('(\/document\/properties\/property[@name="Author"]\/@value)[1]', 'nvarchar(max)') as Author,
\n  Description.value('(\/document\/properties\/property[@name="Tags"]\/@value)[1]', 'nvarchar(max)') as Tags
\nFROM
\n(
\nSELECT TOP 10 Name, dbo.HitHighlightEx(@context, 'top-fragment', 200, stream_id) as description
\nFROM Documents
\nWHERE CONTAINS(file_stream, @query)
\n) a;\n<\/p>\n

\nAnd even get properties that aren't in my SEARCH PROPERTY LIST. So, to get the "LastAuthor" property (Property\/Details in Windows Explorer calls this "Last Saved By"):\n<\/p>\n

\nDECLARE @query nvarchar(50) = 'compression';
\nDECLARE @context varbinary(8000) = dbo.HitHighlightContext('Documents', 'file_stream', @query, -1);
\nSELECT Name,
\n  Description.value('(\/document\/properties\/property[@name="Title"]\/@value)[1]', 'nvarchar(max)') as Title,
\n  Description.value('(\/document\/properties\/property[@name="Author"]\/@value)[1]', 'nvarchar(max)') as Author,
\n  Description.value('(\/document\/properties\/property[@name="Tags"]\/@value)[1]', 'nvarchar(max)') as Tags,
\n  Description.value('(\/document\/properties\/property[@set="f29f85e0-4ff9-1068-ab91-08002b27b3d9" and @id="8"]\/@value)[1]', 'nvarchar(max)') as LastAuthor
\nFROM
\n(
\nSELECT TOP 10 Name, dbo.HitHighlightEx(@context, 'top-fragment', 200, stream_id) as description
\nFROM Documents
\nWHERE CONTAINS(file_stream, @query)
\n) a;\n<\/p>\n

\nAs always, you can find a complete list of canonical Windows properties here<\/a>.\n<\/p>\n

\nSo there you have it. To get in on the beta and make suggestions, download the ThinkHighlight beta here. BTW: I have NO affliation of any kind with Interactive Thoughts. But I DO like the way they respond to enhancement requests.\n<\/p>\n

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

About a week ago I got involved with a question via Twitter that was posted originally to Stack Exchange. The question was "Is it possible to list document properties via FTS"? The question refers to SQL Server 2012's new support or property-based search with a syntax that looks like this: SELECT name DocumentName, file_stream.GetFileNamespacePath() Path […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17,18,31],"tags":[],"class_list":["post-461","post","type-post","status-publish","format-standard","hentry","category-filetable","category-full-text-search","category-sql-server-2012"],"yoast_head":"\nHow To Return Document Property Values in Full-Text Search - 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\/how-to-return-document-property-values-in-full-text-search\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How To Return Document Property Values in Full-Text Search - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"About a week ago I got involved with a question via Twitter that was posted originally to Stack Exchange. The question was "Is it possible to list document properties via FTS"? The question refers to SQL Server 2012's new support or property-based search with a syntax that looks like this: SELECT name DocumentName, file_stream.GetFileNamespacePath() Path […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/how-to-return-document-property-values-in-full-text-search\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2012-12-12T10:30:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-01-20T20:22:30+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\/how-to-return-document-property-values-in-full-text-search\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/how-to-return-document-property-values-in-full-text-search\/\",\"name\":\"How To Return Document Property Values in Full-Text Search - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2012-12-12T10:30:00+00:00\",\"dateModified\":\"2014-01-20T20:22:30+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/how-to-return-document-property-values-in-full-text-search\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/how-to-return-document-property-values-in-full-text-search\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/how-to-return-document-property-values-in-full-text-search\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"FileTable\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/filetable\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"How To Return Document Property Values in Full-Text Search\"}]},{\"@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":"How To Return Document Property Values in Full-Text Search - 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\/how-to-return-document-property-values-in-full-text-search\/","og_locale":"en_US","og_type":"article","og_title":"How To Return Document Property Values in Full-Text Search - Bob Beauchemin","og_description":"About a week ago I got involved with a question via Twitter that was posted originally to Stack Exchange. The question was "Is it possible to list document properties via FTS"? The question refers to SQL Server 2012's new support or property-based search with a syntax that looks like this: SELECT name DocumentName, file_stream.GetFileNamespacePath() Path […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/how-to-return-document-property-values-in-full-text-search\/","og_site_name":"Bob Beauchemin","article_published_time":"2012-12-12T10:30:00+00:00","article_modified_time":"2014-01-20T20:22:30+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\/how-to-return-document-property-values-in-full-text-search\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/how-to-return-document-property-values-in-full-text-search\/","name":"How To Return Document Property Values in Full-Text Search - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2012-12-12T10:30:00+00:00","dateModified":"2014-01-20T20:22:30+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/how-to-return-document-property-values-in-full-text-search\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/how-to-return-document-property-values-in-full-text-search\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/how-to-return-document-property-values-in-full-text-search\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/"},{"@type":"ListItem","position":2,"name":"FileTable","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/filetable\/"},{"@type":"ListItem","position":3,"name":"How To Return Document Property Values in Full-Text Search"}]},{"@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\/461","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=461"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/461\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=461"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=461"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=461"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}