Using the SQL Server FOR XML Clause

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. ...
Nội dung trích xuất từ tài liệu:
Using the SQL Server FOR XML ClauseUsing the SQL Server FOR XML ClauseWith a standard SQL SELECT statement, you submit your SELECT statement to thedatabase for execution and get results back in the form of rows. SQL Server extends theSELECT statement to allow you to query the database and get results back as XML. Todo this, you add a FOR XML clause to the end of your SELECT statement. The FORXML 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 showsa description of the keywords used in the FOR XML clause. In the next sections, youllexamine some examples of the use of the FOR XML clause. Table 16.1: FOR XML KEYWORDSKEYWORD DESCRIPTIONRAW 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 isBASE64 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 ModeYou use the RAW mode to specify that each row in the result set returned by yourSELECT statement is returned as an XML element. The column values for eachrow in the result set become attributes of the element.Listing 16.1 shows an example SELECT statement that retrieves the top three rows fromthe Customers table. The results of the SELECT are returned as XML using the FORXML RAW clause.Listing 16.1: FORXMLRAW.SQLUSE NorthwindSELECT TOP 3 CustomerID, CompanyName, ContactNameFROM CustomersORDER BY CustomerIDFOR XML RAWNote 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 bypressing the F5 key. Figure 16.1 shows the result of running the script in Query Analyzer.Youll 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 inQuery AnalyzerNote 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, youll 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.Heres the XML line returned by the example, which I copied from Query Analyzer andadded some return characters to make it easier to read:Notice that each customer is placed within a tag. Also, the column values appearas attributes within each row; for example, in the first row, the CustomerID attribute isALFKI.Using the AUTO ModeYou use the AUTO mode to specify that each row in the result set is returned as an XMLelement. 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 fromthe Customers table. The results are returned as XML using the FOR XML AUTOclause.Listing 16.2: FORXMLAUTO.SQLUSE NorthwindSELECT TOP 3 CustomerID, CompanyName, ContactNameFROM CustomersORDER BY CustomerIDFOR XML AUTOThe XML returned by this example is as follows:CompanyName=Alfreds FutterkisteContactName=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 ModeYou 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 withthe 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 childrows. The ...