Using the SQL Server FOR XML Clause

Chia sẻ: Qweqwdasd Qweqdasda | Ngày: | Loại File: PDF | Số trang:12

Thêm vào BST

Báo xấu

101
lượt xem 13
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Sử dụng SQL Server FOR XML khoản Với một tuyên bố tiêu chuẩn SELECT SQL, bạn gửi câu lệnh SELECT của bạn vào cơ sở dữ liệu để thực hiện và nhận được kết quả trở lại dưới hình thức hàng. SQL Server mở rộng các câu lệnh SELECT để cho phép bạn truy vấn cơ sở dữ liệu và nhận được kết quả lại như XML. Để làm điều này, bạn thêm một FOR XML khoản vào cuối câu lệnh SELECT của bạn. ...

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Using the SQL Server FOR XML Clause

Using the SQL Server FOR XML Clause With a standard SQL SELECT statement, you submit your SELECT statement to the database for execution and get results back in the form of rows. SQL Server extends the SELECT statement to allow you to query the database and get results back as XML. To do this, you add a FOR XML clause to the end of your SELECT statement. The FOR XML clause specifies that SQL Server is to return results as XML. The FOR XML clause has the following syntax: FOR XML {RAW | AUTO | EXPLICIT} [, XMLDATA] [, ELEMENTS] [, BINARY BASE64] The RAW, AUTO, and EXPLICIT keywords indicate the XML mode. Table 16.1 shows a description of the keywords used in the FOR XML clause. In the next sections, you'll examine some examples of the use of the FOR XML clause. Table 16.1: FOR XML KEYWORDS KEYWORD DESCRIPTION RAW Specifies that each row in your result set is returned as an XML element. The column values for each row in the result set become attributes of the element. AUTO Specifies that each row in the result set is returned as an XML element The name of the table is used as the name of the tag in the row elements. EXPLICIT Indicates your SELECT statement specifies a parent-child relationship. This relationship is then used by SQL Server to generate XML with the appropriate nested hierarchy. XMLDATA Specifies that the XML schema is to be included in the returned XML. ELEMENTS Specifies that the column values are returned as subelements of the row; otherwise the columns are returned as attributes of the row. You can use this option only with the AUTO mode. BINARY Specifies that any binary data returned by your SELECT statement is BASE64 encoded in base 64. If you want to retrieve binary data using either the RAW or EXPLICIT mode, then you must use the BINARY BASE64 option.
Using the RAW Mode You use the RAW mode to specify that each row in the result set returned by your SELECT statement is returned as an XML element. The column values for each row in the result set become attributes of the element. Listing 16.1 shows an example SELECT statement that retrieves the top three rows from the Customers table. The results of the SELECT are returned as XML using the FOR XML RAW clause. Listing 16.1: FORXMLRAW.SQL USE Northwind SELECT TOP 3 CustomerID, CompanyName, ContactName FROM Customers ORDER BY CustomerID FOR XML RAW Note This SELECT statement is contained in a T-SQL script named ForXmlRaw.sql, which is located in the sql directory for this chapter. You can load the ForXmlRaw.sql T-SQL script into Query Analyzer by selecting File ➣ Open from the menu. You then run the script by selecting Query ➣ Execute, or by pressing the F5 key. Figure 16.1 shows the result of running the script in Query Analyzer. You'll notice that the XML is shown on one line, and that the line is truncated. Figure 16.1: Running a SELECT statement containing a FOR XML RAW clause in Query Analyzer Note By default, the maximum number of characters displayed by Query Analyzer per column is 256. Any results longer than 256 characters will be truncated. For the examples in this section, you'll need to increase the maximum number of characters to 8,192. To do this, you select Tools ➣ Options in Query Analyzer and set the
Maximum Characters Per Column field to 8,192. Here's the XML line returned by the example, which I copied from Query Analyzer and added some return characters to make it easier to read: Notice that each customer is placed within a tag. Also, the column values appear as attributes within each row; for example, in the first row, the CustomerID attribute is ALFKI. Using the AUTO Mode You use the AUTO mode to specify that each row in the result set is returned as an XML element. The name of the table is used as the name of the tag in the row elements. Listing 16.2 shows an example SELECT statement that retrieves the top three rows from the Customers table. The results are returned as XML using the FOR XML AUTO clause. Listing 16.2: FORXMLAUTO.SQL USE Northwind SELECT TOP 3 CustomerID, CompanyName, ContactName FROM Customers ORDER BY CustomerID FOR XML AUTO The XML returned by this example is as follows:
CompanyName="Alfreds Futterkiste" ContactName="Maria Anders"/> Notice that each customer appears within a tag instead of a generic tag, as was the case in the previous RAW mode example. Using the EXPLICIT Mode You use the EXPLICIT mode to indicate that your SELECT statement specifies a parent- child relationship. This relationship is then used by SQL Server to generate XML with the appropriate nested hierarchy. When using the EXPLICIT mode, you must provide at least two SELECT statements. The first SELECT specifies the parent row (or rows), and the second specifies the child rows. The rows retrieved by the two SELECT statements are related through special columns named Tag and Parent. Tag specifies the numeric position of the element, and Parent specifies the Tag number of the parent element (if any). Let's consider an example that uses two SELECT statements. The first SELECT retrieves the CustomerID, CompanyName, and ContactName for the row from the Customers table that has a CustomerID of ALFKI. The second SELECT additionally retrieves the OrderID and OrderDate from the row in the Orders table that also has a CustomerID of ALFKI. The first SELECT statement is as follows: SELECT 1 AS Tag, 0 AS Parent, CustomerID AS [Customer!1!CustomerID], CompanyName AS [Customer!1!CompanyName], ContactName AS [Customer!1!ContactName], NULL AS [Order!2!OrderID!element], NULL AS [Order!2!OrderDate!element] FROM Customers WHERE CustomerID = 'ALFKI'
The Tag column specifies the numeric position of the row in the XML hierarchy. The Parent column identifies the parent, which is 0 in the previous SELECT statement; that's because this row is the parent, or root, in the XML hierarchy. Note You can also use a Tag value of NULL to indicate the root. The CustomerID, CompanyName, and ContactName columns in the previous SELECT are supplied an alias using the AS keyword, followed by a string that uses the following format: [elementName!tag!attributeName!directive] where • elementName specifies the name of the row element in the returned XML. • tag specifies the Tag number. • attributeName specifies the name of the column elements in the returned XML. • directive (optional) specifies how the element is to be treated in the XML. The directives are shown in Table 16.2. Table 16.2: DIRECTIVES DIRECTIVE DESCRIPTION element Indicates that the column value appears as a contained row element within the outer row element, rather than an embedded attribute of the outer row element. The element directive may be combined with ID, IDREF, or IDREFS. hide Indicates that the column value doesn't appear in the returned XML. xml Similar to the element directive except that the column value isn't coded as an entity in the returned XML. This means that the special characters &, ', >,
Table 16.2: DIRECTIVES DIRECTIVE DESCRIPTION ntext. ID Indicates that the column value is an ID attribute. An IDREF and IDREFS attribute can point to an ID attribute, allowing you to create links within the XML. IDREF Indicates that the column value is an IDREF attribute. IDREFS Indicates that the column value is an IDREFS attribute. Let's consider an example: CustomerID AS [Customer!1!CustomerID] specifies that the CustomerID column value will appear within the Customer row element with the attribute name of CustomerID. After the ContactName in the previous SELECT clause, appear two NULL columns; these are used as placeholders for the OrderID and OrderDate columns that are retrieved by the second SELECT statement, which you'll see next. These two columns use the element directive, which indicates that the column values are to appear as contained elements within the Customer row element. The second SELECT statement retrieves the rows from the Orders table that has a CustomerID of ALFKI: SELECT 2 AS Tag, 1 AS Parent, C.CustomerID, C.CompanyName, C.ContactName, O.OrderID, O.OrderDate FROM Customers C, Orders O WHERE C.CustomerID = O.CustomerID AND C.CustomerID = 'ALFKI' Notice that the Parent column is set to 1, which indicates that the parent is the row previously retrieved by the first SELECT statement shown earlier. Listing 16.3 shows a complete example that uses the two SELECT statements shown in this section. Listing 16.3: FORXMLEXPLICIT.SQL
USE Northwind SELECT 1 AS Tag, 0 AS Parent, CustomerID AS [Customer!1!CustomerID], CompanyName AS [Customer!1!CompanyName], ContactName AS [Customer!1!ContactName], NULL AS [Order!2!OrderID!element], NULL AS [Order!2!OrderDate!element] FROM Customers WHERE CustomerID = 'ALFKI' UNION ALL SELECT 2 AS Tag, 1 AS Parent, C.CustomerID, C.CompanyName, C.ContactName, O.OrderID, O.OrderDate FROM Customers C, Orders O WHERE C.CustomerID = O.CustomerID AND C.CustomerID = 'ALFKI' FOR XML EXPLICIT Note The UNION ALL clause causes the results retrieved by the two SELECT statements to be merged into one result set. The combined result set produced by the UNION ALL clause is then converted to XML by the FOR XML EXPLICIT clause. The XML returned by the example is as follows:
10643 1997-08-25T00:00:00 10692 1997-10-03T00:00:00 10702 1997-10-13T00:00:00 10835 1998-01-15T00:00:00 10952 1998-03-16T00:00:00 11011 1998-04-09T00:00:00 Notice that the OrderID and OrderDate elements appear as row elements contained in the outer Order element. That's because the element directive was specified for the OrderID and OrderDate elements in the first SELECT statement. If the element directive is omitted from the OrderID and OrderDate elements, then the returned XML is as follows:
Notice that the OrderID and OrderDate elements are embedded as attributes of the outer Order element. Using the XMLDATA Option You use the XMLDATA option to specify that the XML schema document type definition (DTD) is to be included in the returned XML. The XML schema contains the name and type of the column attributes. Listing 16.4 shows an example that uses the XMLDATA option to return the XML schema along with the ProductID, ProductName, and UnitPrice columns for the top two rows from the Products table. Listing 16.4: FORXMLAUTOXMLDATA.SQL USE Northwind SELECT TOP 2 ProductID, ProductName, UnitPrice FROM Products ORDER BY ProductID FOR XML AUTO, XMLDATA Note In this example, I use columns from the Products table rather than the Customers table because the Customers table contains only string column values, and I want you to see some of the different types returned in an XML schema. The Products table contains column values that consist of both strings and numbers. The ProductID column is of the SQL Server int type, ProductName is of the nvarchar type, and UnitPrice is of the money type. The XML returned by this example is as follows:
ProductID="1" ProductName="Chai" UnitPrice="18.0000"/> Notice the different XML types of the ProductID, ProductName, and UnitPrice attributes specified in the AttributeType tag near the start of the previous XML. For more information, see XML Schemas by Chelsea Valentine, Lucinda Dykes, and Ed Tittel (Sybex, 2002). Using the ELEMENTS Option You use the ELEMENTS option to indicate that the column values are returned as subelements of the row; otherwise the column values are returned as attributes of the row. Tip You can use the ELEMENTS option only with the AUTO mode. Listing 16.5 shows an example that uses the ELEMENTS option when retrieving the top two rows from the Customers table. Listing 16.5: FORXMLAUTOELEMENTS.SQL USE Northwind SELECT TOP 2 CustomerID, CompanyName, ContactName FROM Customers ORDER BY CustomerID FOR XML AUTO, ELEMENTS The XML returned by this example is as follows: ALFKI Alfreds Futterkiste Maria Anders ANATR Ana Trujillo Emparedados y helados
Ana Trujillo Notice that the column values are returned as subelements within the Customers rows. Using the BINARY BASE64 Option You use the BINARY BASE64 option to specify that any binary data returned by your SELECT statement is encoded in base 64. Note If you want to retrieve binary data using either the RAW or EXPLICIT mode, then you must use the BINARY BASE64 option. In the examples in this section, I'll use the Employees table of the Northwind database. This table contains details of the employees that work for the fictional Northwind Company and contains a column named Photo. The Photo column is of the SQL Server image type and contains binary data with an image of the employee. Figure 16.2 shows a SELECT statement run in Query Analyzer that retrieves the EmployeeID (the primary key), FirstName, LastName, and Photo columns from the Employees table. Notice that the binary data is retrieved as hexadecimal digits (base 16). Figure 16.2: Retrieving rows from the Employees table In AUTO mode, binary data is returned by default as a reference to the data rather than the actual data itself. The following example retrieves the EmployeeID and Photo columns for the top two rows from the Employees table using the AUTO mode: USE Northwind SELECT TOP 2 EmployeeID, Photo FROM Employees ORDER BY EmployeeID FOR XML AUTO
This example returns the following XML: The reference to the binary data contained in the Photo column is actually an XPath expression. (You'll learn about XPath in the next section.) To get the binary data itself, rather than the reference to it, you need to use the BINARY BASE64 option. Listing 16.6 shows an example that uses the BINARY BASE64 option when retrieving the EmployeeID and Photo columns for the top two rows from the Employees table. Listing 16.6: FORXMLAUTOBINARYBASE64.SQL USE Northwind SELECT TOP 2 EmployeeID, Photo FROM Employees ORDER BY EmployeeID FOR XML AUTO, BINARY BASE64 The XML returned by this example is as follows: Note I've shown only the first 10 digits of binary data. To view the binary data in Query Analyzer, you'll need to set the Default results target to Results To Text in the Options dialog box. You select Tools ➣ Options from the menu to get to this dialog box.