Export (0) Print
Expand All

Retrieving XML Documents by Using FOR XML on the Client Side

SQL Server 2000

Microsoft® SQLXML provides two ways to format an XML document:

  • Server-side formatting

  • Client-side formatting

This topic includes information about formatting XML documents on either the client side or the server side from the rowsets that are generated by queries executed against a database in Microsoft SQL Server™ 2000.

It is assumed that you are familiar with the FOR XML clause that was introduced in SQL Server 2000. This topic provides information about using FOR XML on the client side. For complete information about FOR XML, see SQL Server Books Online.

Formatting XML Documents on the Server Side

In SQL Server 2000, you can store and retrieve XML documents to and from database tables. To retrieve an XML document, use the FOR XML query extension in a SELECT query.

For example, assume a client application executes a command against SQL Server 2000 that consists of the following Transact-SQL query:

SELECT FirstName, LastName
FROM   Employees
FOR XML AUTO

The server executes the query in two steps. First, the server executes this SELECT statement:

SELECT FirstName, LastName
FROM   Employees

Then the server applies the FOR XML transformation to the generated rowset. The resulting XML is then sent to the client as a one-column rowset. In this documentation, this process is referred to as server-side XML formatting.

On the server side, you can specify the following modes with a FOR XML clause:

  • RAW

  • AUTO

  • EXPLICIT

For information about the architecture of client-side and server-side formatting, see Architecture of Client-Side and Server-Side XML Formatting.

Formatting XML Documents on the Client Side

In addition to server-side XML formatting, SQLXML supports client-side XML formatting. (Client-side formatting refers to the formatting of XML on the middle tier.) When a client application executes the following query:

SELECT FirstName, LastName
FROM   Employees
FOR XML RAW

Only this part of the query is sent to the server:

SELECT FirstName, LastName
FROM   Employees

The server executes the query and returns a rowset (which contains FirstName and LastName columns) to the client. The middle tier then applies the FOR XML transformation to the rowset and returns XML formatting to the client.

Similarly, when you execute an XPath query, the server returns the rowset to the client and the FOR XML EXPLICIT transformation is applied to the rowset on the client, generating the desired XML formatting.

The following table shows the modes you can specify with client-side FOR XML.

Client-side FOR XML mode Comment
RAW Produces identical results when specified in client-side or server-side FOR XML.
NESTED Is similar to FOR XML AUTO mode on the server-side. For more information, see Comparing Client-Side XML Formatting to Server-Side XML Formatting.
EXPLICIT Is similar to server-side FOR XML EXPLICIT mode. For more information, see Comparing Client-Side XML Formatting to Server-Side XML Formatting.

If you specify AUTO mode and request client-side XML formatting, the entire query is sent to the server; that is, XML formatting occurs on the server. This is done for convenience, but note that the NESTED mode returns base table names as element names in the XML document that is generated. Some of the applications you write might require base table names. For example, you might execute a stored procedure and load the resulting data in a Dataset (in the Microsoft .NET Framework), and then later generate a DiffGram to update data in the tables. In such a case, you will need the base table information and you must use the NESTED mode.

These are benefits of client-side XML formatting:

  • If you have stored procedures on the server that return a single rowset, you can request client-side FOR XML transformation to generate an XML. For example, consider the following stored procedure that returns the first and last names of employees from the Employees table in the Northwind database:
    IF EXISTS (SELECT name FROM sysobjects
       WHERE name = 'GetEmployees' AND type = 'P')
       DROP PROCEDURE GetEmployees
    GO
    CREATE PROCEDURE GetEmployees
    AS
        SELECT   FirstName, LastName
        FROM     Employees
    

    The following sample XML template executes the stored procedure. The FOR XML clause is specified after the stored procedure name.

    <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
      <sql:query client-side-xml="1">
        exec GetEmployees FOR XML NESTED
      </sql:query>
    </ROOT>
    

    Because the client-side-xml attribute is set to 1 (true) in the template, the stored procedure is executed on the server and the two-column rowset that is returned by the server is transformed into XML on the middle tier and returned to the client. (Only a partial result is shown here.)

     <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
      <Employees FirstName="Nancy" LastName="Devolio" /> 
      <Employees FirstName="Andrew" LastName="Fuller" />
    </ROOT>

    When you are using the SQLXMLOLEDB Provider or SQLXML Managed Classes, you can use the ClientSideXml property to request client-side XML formatting.

  • Because the client does the XML formatting, the workload is balanced between the server and client, freeing the server to do other things.
Supporting Client-Side XML Formatting

To support the client-side XML formatting functionality, SQLXML provides:

  • SQLXMLOLEDB Provider

  • SQLXML Managed Classes

  • Enhanced XML template support

  • Enhanced Virtual Directory Management for the SQLXML 3.0 utility
SQLXML Managed Classes

Using the SQLXML Managed Classes, you can request client-side or server-side XML formatting by setting the provider-specific property ClientSideXml to true. For more information, see SQLXML Managed Classes.

Enhanced XML Template Support

The XML template in SQL Server 2000 has been enhanced with the addition of the client-side-xml attribute. If this attribute is set to TRUE, XML is formatted on the client. Note that this template attribute is identical in functionality to the SQLXMLOLEDB Provider-specific ClientSideXML property.

If you execute an XML template in an ADO application that is using the SQLXMLOLEDB Provider and you specify the client-side-xml attribute in the template and the provider ClientSideXML property, the value that is specified in the template takes precedence. For more information, see Enhancements to XML Templates.

Enhanced Virtual Directory Management for the SQLXML 3.0 utility

Options have been added to the IIS Virtual Directory Management for SQL Server utility that shipped with SQL Server 2000. The Run on the Client check box was added to the Settings tab. You can select this option set to indicate client-side processing. For more information, see IIS Virtual Directory Management for SQL Server.

For information about the architecture of client-side and server-side formatting, see Architecture of Client-Side and Server-Side XML Formatting.

If you specify the client-side-xml attribute in the template and also set the Run on the Client option on the virtual directory in the Settings tab, the value that is specified in the template takes precedence.

FOR XML Security Issues

Show:
© 2014 Microsoft