Free Trial

Safari Books Online is a digital library providing on-demand subscription access to thousands of learning resources.

  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 7. FOR XML

Chapter 7. FOR XML

Starting in Chapter 4, “Accessing SQL 2000 via HTTP,” we introduced the FOR XML extension to SQL Server 2000’s Transact-SQL SELECT statement, showing how it could be used to generate XML documents from a relational database. We also went through numerous examples of how to accomplish this transformation. These examples ranged from placing the SQL query directly in an URL to writing a template file to contain the query to execute.

The FOR XML extension also has an XML mode category that we used by specifying FOR XML AUTO, which generated an attribute-centric XML document. I mentioned then that there are additional parameters to the FOR XML directive, and we’ll talk about them now in this chapter.

One of the additional modes we’ll cover in this chapter, the EXPLICIT mode, is one of the more difficult and complicated subjects we’ll cover in this book.

Many of the examples in this chapter utilize the Query Analyzer. By default, the column size of returned results is limited to a 256-character width. This is too small for some of the XDR schema results that are returned in the examples. To enlarge the width, open the Query Analyzer, select Tools, and then select Options. Click the Results tab. Change Maximum Characters Per Column to a larger number (say, 2,000) and you should be okay (see Figure 7.1).

Figure 7.1. Changing the results column width.

This chapter will cover the following topics:

  • The limitations of the FOR XML extension

  • FOR XML RAW mode and what it accomplishes

  • FOR XML AUTO mode and what it accomplishes

  • FOR XML EXPLICIT mode and why it is the most complicated of the modes

  • Overflow data fields and how to use and manipulate them


You are currently reading a PREVIEW of this book.


Get instant access to over $1 million worth of books and videos.


Start a Free 10-Day Trial

  • Safari Books Online
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint