XML Data Type XQuery XML Indexes XML Schema Collection and Http Endpoints in SQL Server 2005
by David Hayden ( Sarasota Florida .NET Developer and Microsoft MVP )
This is an example I presented at the last Sarasota .NET Developer Meeting during my SQL Server 2005 Presentation. It provides a glimpse at two new features in SQL Server 2005: Http Endpoints and the XML Data Type. It also demonstrates XQuery, XML Indexes as well as consuming and using XML Web Services in Visual Studio 2005.
Book Collection Library
The idea here is that I have a vast collection of technical books and often I would like the ability to do a quick query on these books based on a keyword and retrieve a list of titles with their page numbers that talk about the keyword.
For example, I would like to be able to type in "ADO.NET" and have a list of books that talk about ADO.NET be displayed with their page numbers as such:
Architecture
The architecture of my trivial application looks as follows:
I have created an Http Endpoint in SQL Server 2005, called Library_Books. There is a method defined in this Endpoint, called FindBooksByKeyword, that accepts a keyword as a parameter and maps to a stored procedure in the Library Database, called dbo.FindBooksByKeyword. This stored procedure does a select on the Books Table in the database, finding all titles and page numbers that have the keyword and return the rowsets.
Here is a picture that gives you a overview of the players in the database:
Here is the Http Endpoint:
Books Table
The Books Table contains 3 fields
- ISBN - serves as PK
- Title
- BookIndex - XML Data Type containing the book's index
The BookIndex is a simple XML Document. Here is an example of inserting a book in the table that shows you the layout of the XML and the look of the content:
Insert into dbo.Books
(ISBN,Title,BookIndex)
VALUES
('1-59059-589-3','Visual C# 2005 Recipes',
CAST('
<index>
<keyword page="15">AppDomain</keyword>
<keyword page="319">DataTable</keyword>
<keyword page="328">DataSet</keyword>
<keyword page="149">Encrypt</keyword>
<keyword page="167">File IO</keyword>
<keyword page="27">GAC</keyword>
<keyword page="55">Generics</keyword>
</index>' as XML))
XML Schema Collection
One of the beauties of SQL Server 2005 is that we can now add schemas to the database and associate them to a XML Data Type, providing validation, Typed XML, and better performance when doing XQuery.
I let VS 2005 create the schema for me based on test data and then inserted the schema into the database, calling it BookIndex:
CREATE XML SCHEMA COLLECTION BookIndex
AS
N'<xs:schema attributeFormDefault="unqualified"
elementFormDefault="qualified"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="index">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="keyword">
<xs:complexType>
<xs:simpleContent>
<xs:extension base="xs:string">
<xs:attribute name="page" type="xs:int"
use="required" />
</xs:extension>
</xs:simpleContent>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>'
As you can see from the overall image of the database, the BookIndex Column in the Books Table is tied to the schema.
XML Indexes
Because I planned on querying the XML data using XQuery, I decided to create a Primary XML Index on the table at a minimum. I could have also created up to 3 secondary XML Indexes as well.
CREATE PRIMARY XML INDEX
idx_XML_Primary_Books_BookIndex
ON dbo.Books(BookIndex)
The FindBooksByKeyword Stored Procedure
Shown below is a FindBooksByKeyword Stored Procedure that uses XQuery to dive into the BookIndex XML Data Type to look for keywords and return page numbers and titles. My XQuery skills are weak, so the stored procedure shown below may have been written easier or better. However, it does work and shows the integration of XQuery into the stored procedure. I would love to hear from any XML Gurus if they know of a better or more proper way to query the data.
CREATE PROCEDURE dbo.FindBooksByKeyword
@Keyword varchar(25)
AS
BEGIN
SET NOCOUNT ON;
SELECT Title, BookIndex.value('(/index/keyword[
.=sql:variable("@Keyword")]/@page)[1]'
,'varchar(25)') as Page
FROM Books
WHERE BookIndex.exist('/index/keyword[
.=sql:variable("@Keyword")]') = 1
END
GO
Creating the Http Endpoint
We now need an Http Endpoint that exposes our stored procedure as an XML Web Service. I created it as such:
CREATE ENDPOINT Library_Books
STATE = Started
AS HTTP
(
PATH = '/Library/Books',
AUTHENTICATION = (INTEGRATED),
PORTS = (CLEAR),
SITE = '*'
)
FOR SOAP
(
WEBMETHOD 'FindBooksByKeyword'
(NAME = 'Library.dbo.FindBooksByKeyword',
FORMAT = ROWSETS_ONLY),
WSDL = DEFAULT,
DATABASE = 'Library',
NAMESPACE = DEFAULT
)
The WSDL for this Http Endpoint would be:
http://localhost/library/books?wsdl
which can now be consumed in a windows form application.
Calling the Http Endpoint
The code for the winform application is pretty simple:
// Instantiate Service
Library.Library_Books libraryService =
new Library.Library_Books();
// Set Default Credentials
libraryService.Credentials =
CredentialCache.DefaultCredentials;
// Call FindByKeywords
DataSet ds =
libraryService.FindBooksByKeyword(keyword);
// Bind
Books.DataSource =
ds.Tables[0].DefaultView;
Conclusion
Some of the features in SQL Server 2005 are pretty cool. You may or may not want to use them depending on the size of the application, etc., but it is cool to know that they exist and how they work. Hopefully this will help others interested in these new features.
Source: David Hayden ( Sarasota Florida .NET Developer and Microsoft MVP )
SQL Server 2005 Tutorials: