CAD/MCSE/MCDBA Self-Paced Training Kit [Electronic resources]: Microsoft SQL Server 2000 Database Design and Implementation, Second Edition (Exam 70-229) نسخه متنی

Objective 3.5: Extract data in XML format.

One of the new features in SQL Server 2000 is the ability to extract data in XML format. Results can be returned to a client in XML format by adding the FOR XML clause to the SELECT statement. You can also return result sets in XML format using templates and XPath queries. SQL Server 2000 can also be queried directly from a URL.

FOR XML returns results in a number of different ways, providing great flexibility. FOR XML is valid only for the SELECT statement, and its usage has a number of restrictions. It cannot be used within a view, with cursors, or with the COMPUTE BY or GROUP BY clauses, and it cannot be used in subqueries.

FOR XML can be used in the following ways:

SELECT…FOR XML RAW-This returns each row in the result set as one element called 'row,' with columns indicated in the SELECT statement as attribute=value pairs. For example, the query SELECT CUSTOMER.CUSTOMERNAME, ORDERS.ORDERID FROM CUSTOMER, ORDERS WHERE CUSTOMER. CUSTOMERID=ORDERS.CUSTOMERID might return the following XML:

<row CUSTOMERNAME="Brown" ORDERID="101" /> 
<row CUSTOMERNAME="Brown" ORDERID="105" /> 
<row CUSTOMERNAME="Yates" ORDERID="225" /> 

Notice that the result is nonhierarchical and looks very much like the result sets you are used to in SQL Server. Because Brown has two orders, the name is returned twice.

SELECT…FOR XML AUTO-This returns hierarchical data, depending on the number of tables in the join and the order of the columns. Generally, an element is created for each table, with the columns returned as attribute=value pairs. The same query as in the preceding example, SELECT CUSTOMER.CUSTOMERNAME, ORDERS.ORDERID FROM CUSTOMER, ORDERS WHERE CUSTOMER. CUSTOMERID=ORDERS.CUSTOMERID, would return the following XML:

<CUSTOMER CUSTOMERNAME="Brown" > 
<ORDERS ORDERID="101" /> 
<ORDERS ORDERID="105" /> 
</CUSTOMER > 
<CUSTOMER CUSTOMERNAME="Yates" > 
<ORDERS ORDERID="225" /> 
</CUSTOMER > 

This hierarchical data return is very useful. Note that the column order in the SELECT clause is significant in determining the hierarchy.

SELECT… FOR XML EXPLICIT-This option provides much more flexibility, but requires the query to be written in a specific way to produce the expected output. It is more functional than the RAW or AUTO clauses, but also more complicated. This option allows you to control what data is returned as elements and what is returned as attributes, which are fixed in the AUTO and RAW options.

The XMLDATA option can be added to any of the SELECT statements described in this list to return an XML schema generated from the table structure. The schema will be returned before the actual results. XML schemas provide information about the table structure.

The entire XML functionality is implemented in Sqlxmlx.dll. When SQLOLEDB determines that the command is an XML command, the provider passes that command to Sqlxmlx.dll, which executes the command and returns the result to SQLOLEDB. There is obviously some overhead in producing XML. It is important to determine when it is necessary to extract data from SQL Server in XML format.

Objective 3.5 Questions

<row stor_name="Eric the Read Books" qty="5"/> 
<row stor_name="Bookbeat" qty="10"/> 

SELECT Stores.Stor_Name, Sales.Qty 
FROM Stores JOIN Sales 
ON Stores.Stor_ID = Sales.Stor_ID 
WHERE Title_ID = 'BU1032' 

<Stores Stor_Name="Eric the Read Books"> 
<Sales Qty="3" Title_ID="1013"> 
<Titles Title="Is Anger The Enemy?"/> 
</Sales> 
</Stores> 
<Stores Stor_Name="Doc-U-Mat: Quality Laundry and Books"> 
<Sales Qty="20" Title_ID="1013"> 
<Titles Title="Is Anger the Enemy?"/> 
</Sales> 
<Sales Qty="25" Title_ID="1043"> 
<Titles Title="Life Without Fear"/> 
</Sales> 
</Stores>

Answers