Other SQL Server XML Support

SQL Server 2005 has many features that utilize XML capabilities either directly or indirectly. This chapter discusses some of the primary storage and coding mechanisms that utilize XML, and you are likely to see questions covering these topics on the 70-431 exam. The exam may also cover a couple other topics; you may see the odd theory-based question centering around the XML functionality described in the following sections.

Indexing XML Data

SQL Server allows you to create indexes over XML data. xml data type instances are stored in a database as large binary objects (BLOBs). The XML can be large (up to 2GB), and without an index, querying, evaluating, and altering these columns can be inefficient and time-consuming.

If an application relies heavily on XML storage and the manipulation and querying of this data, you will find it helpful to index the XML columns. There is a significant performance hit and associated resource cost associated with such an index during data changes.

There are two types of XML indexes. A primary XML index is the first index placed on an XML column. After the primary index has been created, any of three types of secondary indexes can be created: PATH, VALUE, or PROPERTY. Secondary indexes may help improve query performance with some types of queries.

Creating Primary and Secondary Indexes

When you create a primary XML index, you assign the index name and provide the associated XML to be indexed. The primary index alone is helpful when you have queries that specify the exist() method in the WHERE clause. When you use a primary XML index, you avoid having to shred the XML at runtime. An example of the index creation would look similar to the following:

CREATE PRIMARY XML INDEX PXMLIndex  ON ONE.dbo.EmployeeProspect(Education) 

You must create the primary index before creating a secondary index. To create a secondary index, you assign either the PATH, VALUE, or PROPERTY directive and define the secondary index as being associated to the primary, as shown in the following:

 CREATE XML INDEX IXMLPathIndex  ON ONE.dbo.EmployeeProspect(Education)  USING XML INDEX PXMLIndex FOR PATH 

If your application uses queries that specify path expressions, using a PATH index may speed up searches. Having a secondary index built on the path and node values can speed up index searches. If your XML searches involve looking for specific values without knowing the element or attribute where they would be located, using VALUE indexes may be helpful. Queries that retrieve more than one value from individual XML instances may benefit from the use of PROPERTY indexes.

Native XML Web Service Support

By using Native XML Web Services, you can send requests over HTTP to an instance of SQL Server 2005 to run T-SQL batch statements (with or without parameters), stored procedures, extended stored procedures, and scalar-valued user-defined functions (UDFs).

To use Web Services in SQL Server, an endpoint must be established at the server. This endpoint is the gateway through which clients can query the server over HTTP. After an endpoint is established, stored procedures or UDFs can be made available to users through the endpoint. These procedures and UDFs are referred to as web methods, and collectively when the methods are used together, they are called a web service.

The endpoint definition determines the state of the endpoint, the type of authentication, whether batches are permitted through the endpoint, the Web Service Description Language (WSDL) to be used, and the processes that are exposed through the endpoint. You configure all these options by using the CREATE ENDPOINT statement, and you can change them by using the ALTER ENDPOINT statement.

Exam Alert

You may see endpoint configuration on the 70-431 exam. It is important to know how to start and stop an endpoint and how to configure authentication and batch use. You also need to know what processes are permitted as web methods.

The state of the endpoint can be set to one of the following:

• STARTED Actively listens for connections.

• STOPPED Listens for requests returning error messages to clients.

• DISABLED Does not listen for and does not respond to requests.

You can enable or disable SQL batches to determine whether ad hoc SQL or parameterized queries are permitted. SQL ad hoc queries are disabled by default.

You can set user authentication for access to SQL Server through the endpoint to any of the following:

• BASIC The lowest level, used as a last resort. You must run through SSL if using this setting.

• DIGEST No support for local user accounts. Enables MD5 over Windows Server 2003 domain controllers only.

• NTLM Challenge/response authentication where the username and password are asked for and supplied at the time the connection is made. Supported by Windows 95, 98, and NT 4.0.

• KERBEROS Internet standard supported by Windows 2000 and later.

• INTEGRATED Can use Kerberos or NTLM for authentication, depending on what the client supports.

Web services can be described using the WSDL format generated by SQL Server and returned to SOAP clients for any HTTP endpoints on which WSDL is enabled. If required, you can use DEFAULT to make the WSDL format a custom solution instead of one generated by SQL Server. You can also use NONE to configure the endpoint to not answer WSDL requests. You can also assign WSDL as a custom solution associated with a stored procedure to provide the functionality.