Sample Full-text Query Using File Content and Database Data

The first query returns the title and publication year of qualifying books that are represented by files in the virtual directory with the alias /pubs. To satisfy the query, a book must cost less than $20.00 and text in the Characterization property must indicate that the book is about ice hockey. It is known that the year portion of the Create property is always the publication year of the book. The customer has defined the BookCost property (of type money), which filters out the cost of each book.

SELECT Q.DocTitle, DATEPART(YEAR, Q.Create) 
FROM OPENQUERY(FileSystem,
               'SELECT DocTitle, Create
                FROM SCOPE('' "/pubs" '')
                WHERE BookCost <= 20.00
                  AND CONTAINS( Characterization, '' "ice hockey" '' )
               ') AS Q  

The table alias value Q has been assigned to the table returned by the OPENQUERY function. This alias is then used to qualify the items in the outer select list. Here, the SQL Server DATEPART() function is used to pass on only the year portion of the create datetime value.

This second query returns the same information as the previous one. The difference is that the price of a book is obtained from the document_cost column in the BookCost table in the database, rather than from a property in the file system. The primary key of the BookCost table is the combination of document_author and document_title.

SELECT Q.DocTitle, DATEPART(YEAR, Q.Create) 
FROM OPENQUERY(FileSystem,
               'SELECT DocTitle, Create, DocAuthor, DocTitle 
                FROM SCOPE('' "/pubs" '')
                  AND CONTAINS( Characterization, '' "ice hockey" '' )
               ') AS Q, 
     BookCost as B
WHERE Q.DocAuthor = B.document_author
  AND Q.DocTitle = B.document_title
  AND B.document_cost <= 20.00 

The table returned by the OPENQUERY function is joined to the real BookCost table in the database, then rows with a suitable cost are filtered out for inclusion in the outer SELECT.

This last query also joins data from the file system and the database and, this time, data from both appears in the outer select list. Furthermore, the Rank property, which indicates how well the selected rows met the selection criteria, appears in the select list and is used to ensure that higher-ranking rows appear before lower-ranking rows in the outer SELECT. In this example, the wording on the plaques in the Hockey Hall of Fame is recorded on files. There is a file for each plaque, and the plaque number can be obtained with the DocSubject property. The HockeyHall table contains PlaqueNo, PlayerName, StartYear, and LastYear columns with the primary key being PlaqueNo. The query returns the PlayerName and PlaqueNo from the table and the Rank and DocComments properties from the file. Only players who might have played for the Canadian or U.S. teams in the early 1900s are returned.

SELECT HH.PlayerName, HH.PlaqueNo, Q.Rank, Q.DocComments
FROM OPENQUERY(FileSystem,
               'SELECT DocSubject, DocComments, Rank 
                FROM SCOPE('' "/hall_of_fame" '')
                WHERE CONTAINS( Contents, '' Canada OR "United States" '' )
               ') AS Q, 
     HockeyHall as HH
WHERE Q.DocSubject = HH.PlaqueNo
  AND HH.StartYear < 1915 AND HH.EndYear < 1899
ORDER BY Q.Rank DESC