本文介绍了如何在应用程序代码中使用 SQL Server 的 FOR XML 子句来检索和处理 XML 数据。示例展示了如何使用 ADO 和 XML 数据岛在服务器端处理 XML，以及如何使用 ASP.NET 和 .NET Framework 处理 FOR XML 查询结果。
在应用程序代码中使用 FOR XML 结果Use FOR XML Results in Application Code
03/14/2017
本文内容
适用于：Applies to: 
SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions) Azure SQL 数据库Azure SQL DatabaseAzure SQL 数据库Azure SQL Database适用于：Applies to: SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions) Azure SQL 数据库Azure SQL DatabaseAzure SQL 数据库Azure SQL Database
通过在 SQL 查询中使用 FOR XML 子句，可以检索查询结果，甚至可以将其转换为 XML 数据。By using FOR XML clauses with SQL queries, you can retrieve and even cast query results as XML data. 当 FOR XML 查询结果可以在 XML 应用程序代码中使用时，您可以使用此功能执行以下操作：This functionality allows you to do the following when FOR XML query results can be used in XML application code:
Query SQL tables for instances of XML Data (SQL Server) values
Apply the TYPE Directive in FOR XML Queries to return the result of queries that contain text or image typed data as XML
本主题介绍了说明这些方法的示例。This topic provides examples that demonstrate these approaches.
用 ADO 和 XML 数据岛检索 FOR XML 数据Retrieving FOR XML Data with ADO and XML Data Islands
使用 FOR XML 查询时，支持 COM IStream 接口的 ADO Stream 对象或其他对象 [例如，Active Server Page (ASP) Request 和 Response 对象] 可用于包含结果。The ADO Stream object or other objects that support the COM IStream interface, such as the Active Server Pages (ASP) Request and Response objects, can be used to contain the results when you are working with FOR XML queries.
例如，以下 ASP 代码显示在 AdventureWorks 示例数据库的 Sales.Store 表中查询 xml 数据类型列 Demographics 的结果。For example, the following ASP code shows the results of querying an xml data type column, Demographics, in the Sales.Store table of the AdventureWorks sample database. 确切地说，该查询在 CustomerID 等于 3 的行中查找此列的实例值。Specifically, the query looks for the instance value of this column for the row where the CustomerID is equal to 3.
FOR XML Query ExampleBODY
{
FONT-FAMILY: Tahoma;
FONT-SIZE: 8pt;
OVERFLOW: auto
}
H3
{
FONT-FAMILY: Tahoma;
FONT-SIZE: 8pt;
OVERFLOW: auto
}
Response.Write "
Server-side processing
"Response.Write "Page Generated @ " & Now() & "
"
Dim adoConn
Set adoConn = Server.CreateObject("ADODB.Connection")
Dim sConn
sConn = "Provider=SQLOLEDB;Data Source=(local);" & _
"Initial Catalog=AdventureWorks;Integrated Security=SSPI;"
Response.write "Connect String = " & sConn & "
"
adoConn.ConnectionString = sConn
adoConn.CursorLocation = adUseClient
adoConn.Open
Response.write "ADO Version = " & adoConn.Version & "
"
Response.write "adoConn.State = " & adoConn.State & "
"
Dim adoCmd
Set adoCmd = Server.CreateObject("ADODB.Command")
Set adoCmd.ActiveConnection = adoConn
Dim sQuery
sQuery = "SELECT Demographics from Sales.Store WHERE CustomerID = 3 FOR XML AUTO"
Response.write "Query String = " & sQuery & "
"
Dim adoStreamQuery
Set adoStreamQuery = Server.CreateObject("ADODB.Stream")
adoStreamQuery.Open
adoStreamQuery.WriteText sQuery, adWriteChar
adoStreamQuery.Position = 0
adoCmd.CommandStream = adoStreamQuery
adoCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
Response.write "Pushing XML to client for processing " & "
"
adoCmd.Properties("Output Stream") = Response
Response.write ""
adoCmd.Execute , , 1024
Response.write ""
%>
Dim xmlDoc
Set xmlDoc = MyDataIsle.XMLDocument
Dim root
Set root = xmlDoc.documentElement.childNodes.Item(0).childNodes.Item(0).childNodes.Item(0)
For each child in root.childNodes
dim OutputXML
OutputXML = document.all("log").innerHTML
document.all("log").innerHTML = OutputXML & "
" & child.nodeName & ": " & child.Text & ""Next
MsgBox xmlDoc.xml
Client-side processing of XML Document MyDataIsle
此示例 ASP 页包含使用 ADO 执行 FOR XML 查询并在 XML 数据岛 (MyDataIsle) 中返回 XML 结果的服务器端 VBScript。This example ASP page contains server-side VBScript that uses ADO to execute the FOR XML query and return the XML results in an XML data island, MyDataIsle. 然后，在浏览器中返回此 XML 数据岛以进行其他客户端处理。This XML data island is then returned in the browser for additional client-side processing. 其他客户端 VBScript 代码用于处理 XML 数据岛的内容。Additional client-side VBScript code is then used to process the contents of the XML data island. 先将这些内容显示为产生的 DHTML 的一部分并打开消息框来显示 XML 数据岛的预处理内容，再执行此过程。This process is performed before displaying the contents as part of the resultant DHTML and opening a message box to show the preprocessed contents of the XML data island.
测试此示例To test this example
验证是否安装了 IIS 以及 SQL ServerSQL Server 的 AdventureWorks 示例数据库。Verify that IIS is installed and that the AdventureWorks sample database for SQL ServerSQL Server has been installed.
此示例需要安装 Internet Information Services (IIS) 5.0 版或更高版本并启用 ASP 支持。This example requires that Internet Information Services (IIS) version 5.0, or later versions, are installed with ASP support enabled. 此外，必须安装 AdventureWorks 示例数据库。Also, the AdventureWorks sample database has to be installed.
复制以前提供的代码示例，并将其粘贴到您使用的 XML 或文本编辑器中。Copy the code example that was previously provided and paste it into the XML or text editor that you use. 在 IIS 所使用的根目录中将文件另存为 RetrieveResults.asp。Save the file as RetrieveResults.asp in the root directory that is used for IIS. 此目录通常为 C:Inetpub\wwwroot。Typically, this is C:Inetpub\wwwroot.
使用以下 URL 在浏览器窗口中打开 ASP 页。Open the ASP page in a browser window by using the following URL. 首先，将“MyServer”替换为“localhost”或安装了 SQL ServerSQL Server 和 IIS 的服务器的实际名称。First, replace 'MyServer' with either "localhost" or the actual name of the server where SQL ServerSQL Server and IIS are installed.
https://MyServer/RetrieveResults.asp
您所看到的生成的 HTML 页结果与以下示例输出类似：The generated HTML page results that appear will be similar to the following sample output:
服务器端处理Server-side processing
Page Generated @ 3/11/2006 3:36:02 PMPage Generated @ 3/11/2006 3:36:02 PM
Connect String = Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=AdventureWorks;Integrated Security=SSPI；Connect String = Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=AdventureWorks;Integrated Security=SSPI;
ADO Version = 2.8ADO Version = 2.8
adoConn.State = 1adoConn.State = 1
Query String = SELECT Demographics from Sales.Store WHERE CustomerID = 3 FOR XML AUTOQuery String = SELECT Demographics from Sales.Store WHERE CustomerID = 3 FOR XML AUTO
将 XML 推送到客户端进行处理Pushing XML to client for processing
XML 文档 MyDataIsle 的客户端处理Client-side processing of XML Document MyDataIsle
AnnualSales： 1500000AnnualSales: 1500000
AnnualRevenue： 150000AnnualRevenue: 150000
BankName： Primary InternationalBankName: Primary International
BusinessType： OSBusinessType: OS
YearOpened： 1974YearOpened: 1974
Specialty： 道路Specialty: Road
SquareFeet： 38000SquareFeet: 38000
Brands： 3Brands: 3
Internet： DSLInternet: DSL
NumberEmployees： 40NumberEmployees: 40
然后，VBScript 消息框将显示以下由 FOR XML 查询结果返回的原始、未筛选的 XML 数据岛内容。The VBScript message box will then show the following original, unfiltered XML data island contents that were returned by the FOR XML query results.
1500000
150000
Primary International
OS
1974
Road
38000
3
DSL
40
用 ASP.NET 和 .NET Framework 检索 FOR XML 数据Retrieving FOR XML Data with ASP.NET and the .NET Framework
与上一个示例一样，以下 ASP.NET 代码显示在 AdventureWorks 示例数据库的 Sales.Store 表中查询 xml 数据类型列 Demographics 的结果。As in the previous example, the following ASP.NET code shows the results of querying an xml data type column, Demographics, in the Sales.Store table of the AdventureWorks sample database. 同样与上一个示例相似，该查询在 CustomerID 等于 3 的行中查找此列的实例值。As in the previous example, the query looks for the instance value of this column for the row where the CustomerID is equal to 3.
在此示例中，以下 Microsoft .NET Framework 托管 API 用于完成返回并呈现 FOR XML 查询结果：In this example, the following Microsoft .NET Framework managed APIs are used to accomplish the return and rendering of the FOR XML query results:
SqlConnection 用于根据指定的连接字符串变量 strConn 的内容连接到 SQL Server。SqlConnection is used to open a connection to SQL Server, based on the contents of a specified connection string variable, strConn.
然后，将SqlDataAdapter 用作数据适配器，它将使用 SQL 连接和指定的 SQL 查询字符串执行 FOR XML 查询。SqlDataAdapter is then used as the data adapter and it uses the SQL connection and a specified SQL query string to execute the FOR XML query.
执行查询后，为了用 FOR XML 查询的输出填充数据集，将调用 SqlDataAdapter.Fill 方法并传递一个 DataSet, (MyDataSet) 实例。After the query has executed, the SqlDataAdapter.Fill method is then called and passed an instance of a DataSet, MyDataSet, in order to fill the data set with the output of the FOR XML query.
然后，调用 DataSet.GetXml 方法将查询结果作为可以在服务器生成的 HTML 页中显示的字符串返回。The DataSet.GetXml method is then called to return the query results as a string that can be displayed in the server-generated HTML page.
FOR XML Query ExampleBODY
{
FONT-FAMILY: Tahoma;
FONT-SIZE: 8pt;
OVERFLOW: auto
}
H3
{
FONT-FAMILY: Tahoma;
FONT-SIZE: 8pt;
OVERFLOW: auto
}
Dim s as String
s = "
Server-side processing
" & _"Page Generated @ " & Now() & "
"
Dim SQL As String
SQL = "SELECT Demographics from Sales.Store WHERE CustomerID = 3 FOR XML AUTO"
Dim strConn As String
strConn = "Server=(local);Database=AdventureWorks;Integrated Security=SSPI;"
Dim MySqlConn As New System.Data.SqlClient.SqlConnection(strConn)
Dim MySqlAdapter As New System.Data.SqlClient.SqlDataAdapter(SQL,MySqlConn)
Dim MyDataSet As New System.Data.DataSet
MySqlConn.Open()
s = s & "
SqlConnection opened.
"MySqlAdapter.Fill(MyDataSet)
s = s & "
" & MyDataSet.GetXml & "
"MySqlConn.Close()
s = s & "
SqlConnection closed.
"Message.InnerHtml=s
%>
测试此示例To test this example
验证是否安装了 IIS 以及 SQL ServerSQL Server 的 AdventureWorks 示例数据库。Verify that IIS is installed and that the AdventureWorks sample database for SQL ServerSQL Server has been installed.
此示例需要安装 Internet Information Services (IIS) 5.0 版或更高版本并启用 ASP.NET 支持。This example requires that Internet Information Services (IIS) version 5.0, or later versions, are installed with ASP.NET support enabled. 此外，必须安装 AdventureWorks 示例数据库。Also, the AdventureWorks sample database has to be installed.
复制以前提供的代码，并将其粘贴到您使用的 XML 或文本编辑器中。Copy the code that was previously provided and paste it into the XML or text editor that you use. 在 IIS 所使用的根目录中将文件另存为 RetrieveResults.aspx。Save the file as RetrieveResults.aspx in the root directory used for IIS. 此目录通常为 C:Inetpub\wwwroot。Typically, this is C:Inetpub\wwwroot.
使用以下 URL 在浏览器窗口中打开 ASP.NET 页。Open the ASP.NET page in a browser window using the following URL. 首先，将“MyServer”替换为“localhost”或安装了 SQL ServerSQL Server 和 IIS 的服务器的实际名称。First, replace 'MyServer' with either "localhost" or the actual name of the server where SQL ServerSQL Server and IIS are installed.
https://MyServer/RetrieveResults.aspx
您所看到的生成的 HTML 页结果与以下示例输出类似：The generated HTML page results that appear will be similar to the following sample output:
服务器端处理Server-side processing
Page Generated @ 3/11/2006 3:36:02 PM
SqlConnection opened.
1500000150000Primary InternationalOS1974Road380003DSL40
SqlConnection closed.
备注
利用 SQL ServerSQL Serverxml 数据类型支持，你可以通过指定 TYPE 指令 要求将 FOR XML 查询的结果作为 xml数据类型返回，而不是作为字符串或图像类型化数据返回。The SQL ServerSQL Serverxml data type support lets you request that the result of a FOR XML query be returned as xml data type, instead of as string or image typed data, by specifying the TYPE directive. 在 FOR XML 查询中使用 TYPE 指令时，该指令将提供对 FOR XML 结果(与 在应用程序中使用 XML 数据中显示的结果类似)的编程访问权限。When the TYPE directive is used in FOR XML queries, it provides programmatic access to the FOR XML results similar to that shown in Use XML Data in Applications.
另请参阅See Also
扫一扫
822
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
