The Web Server Environment - Database Processing: Fundamentals, Design, and Implementation - page 501

the entity-relationship model requires the definition of an ID-dependent entity. That entity will
later be transformed into a table in the relational model. We will ignore this issue here. This
notation is shown only so that you can see how multivalued elements are documented in an
XML Schema.
Figure 11-54(b) shows how XMLSpy graphically represents the PhoneType global element,
and Figure 11-54(c) illustrates the way that the PhoneType reference is shown for Customer
and Salesperson.

By ThE WAy In the VRG table structure we specified in Chapter 7 and used in Chapters
10, 10A, 10B, and 10C, no SALESPERSON table was defined. The XML
Schema in Figure 11-54, however, suggests how we could add a SALESPERSON table
to the VRG database.

Creating XML Documents from Database Data

SQL Server, Oracle Database, and MySQL have facilities for generating XML documents from
database data. The Oracle Databasse XML features require the use of Java. Because we do not
assume that you are a Java programmer, we will not discuss those features further in this chap-
ter. If you are a Java programmer, you can learn more about Oracle Database's XML features at
www.oracle.com .
The facilities in SQL Server, Oracle Database, and MySQL are undergoing rapid develop-
ment. In the case of SQL Server, version 7.0 added the expression FOR XML to SQL SELECT
syntax. That expression was carried forward to SQL Server 2000. In 2002, the SQL Server group
extended the SQL Server capabilities with the SQLXML class library. SQLXML, which was pro-
duced by the SQL Server group, is different from ADO.NET. All of these features and functions
were merged together in SQL Server 2005 and are carried forward in SQL Server 2008, 2008 R2,
and now 2012.

Using the SQL SELECT . . . FOR XML Statement
SQL Server 2012 uses the SQL SELECT . . . FOR XML statement to work with XML. Consider
the following SQL statement:

/* *** SQL-Query-CH11-01 *** */
SELECT *
FROM ARTIST

FOR XML RAW;

The expression FOR XML RAW tells SQL Server to place the values of the columns as attri-
butes in the resulting XML document. Figure 11-55(a) shows an example of a FOR XML RAW
query in the Microsoft SQL Server Management Studio. The results of the query are displayed
in a single cell. Clicking this cell displays the results as shown in Figure 11-55(b). As expected,
each column is placed as an attribute of the element named row . The complete output, edited
as it would appear in an XML document (and with extra spaces in the attribute values re-
moved), is shown in Figure 11-55(c).
It is also possible to cause SQL Server to place the values of the columns into elements
rather than attributes by using FOR XML AUTO, ELEMENTS. For example, we can display the
data in the ARTIST table using the SQL query:

/* *** SQL-Query-CH11-02 *** */
SELECT *
FROM ARTIST

FOR XML AUTO, ELEMENTS;