Using FOR XML Results in Application Code

By using FOR XML clauses with SQL queries, you can retrieve and even cast query results as XML data. This functionality allows you to do the following when FOR XML query results can be used in XML application code:

This topic provides examples that demonstrate these approaches.

Retrieving FOR XML Data with ADO and XML Data Islands

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.

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. Specifically, the query looks for the instance value of this column for the row where the CustomerID is equal to 3.

<!-- BeginRecordAndStreamVBS -->
<%@ LANGUAGE = VBScript %>
<!-- %  Option Explicit      % -->
<!-- 'Request.ServerVariables("SERVER_NAME") &amp; ";" & _ -->
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Developer Studio"/>
<META HTTP-EQUIV="Content-Type" content="text/html"; charset="iso-8859-1">
<TITLE>FOR XML Query Example</TITLE>
<STYLE>
   BODY
   {
      FONT-FAMILY: Tahoma;
      FONT-SIZE: 8pt;
      OVERFLOW: auto
   }
   H3
   {
      FONT-FAMILY: Tahoma;
      FONT-SIZE: 8pt;
      OVERFLOW: auto
   }
</STYLE>

<!-- #include file="adovbs.inc" -->
<%
   Response.Write "<H3>Server-side processing</H3>"
   Response.Write "Page Generated @ " & Now() & "<BR/>"
   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 & "<BR/>"
   adoConn.ConnectionString = sConn
   adoConn.CursorLocation = adUseClient
   adoConn.Open
   Response.write "ADO Version = " & adoConn.Version & "<BR/>"
   Response.write "adoConn.State = " & adoConn.State & "<BR/>"
   Dim adoCmd
   Set adoCmd = Server.CreateObject("ADODB.Command")
   Set adoCmd.ActiveConnection = adoConn
   Dim sQuery
   sQuery = "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:query>SELECT Demographics from Sales.Store WHERE CustomerID = 3 FOR XML AUTO</sql:query></ROOT>"
   Response.write "Query String = " & sQuery & "<BR/>"
   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 "  & "<BR/>"
   adoCmd.Properties("Output Stream") = Response
   Response.write "<XML ID='MyDataIsle'>"
   adoCmd.Execute , , 1024
   Response.write "</XML>"
%>
<SCRIPT language="VBScript" For="window" Event="onload">
   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 & "<LI><B>" & child.nodeName &  ":</B>&nbsp;&nbsp;" & child.Text  & "</LI>"
   Next
   MsgBox xmlDoc.xml
</SCRIPT>
</HEAD>
<BODY>
   <H3>Client-side processing of XML Document MyDataIsle</H3>
   <UL id=log>
   </UL>
</BODY>
</HTML>
<!-- EndRecordAndStreamVBS -->

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. This XML data island is then returned in the browser for additional client-side processing. Additional client-side VBScript code is then used to process the contents of the XML data island. 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

  1. Verify that IIS is installed and that the AdventureWorks sample database for SQL Server 2005 has been installed.

    This example requires that Internet Information Services (IIS) version 5.0, or later versions, are installed with ASP support enabled. Also, the AdventureWorks sample database has to be installed for SQL Server 2005. For more information, see Installing AdventureWorks Sample Databases and Samples.

  2. Copy the code example that was previously provided and paste it into the XML or text editor that you use. Save the file as RetrieveResults.asp in the root directory that is used for IIS. Typically, this is C:Inetpub\wwwroot.

  3. Open the ASP page in a browser window by using the following URL. First, replace 'MyServer' with either "localhost" or the actual name of the server where SQL Server 2005 and IIS are installed.

    http://MyServer/RetrieveResults.asp
    

The generated HTML page results that appear will be similar to the following sample output:

Server-side processing

Page Generated @ 3/11/2005 3:36:02 PM

Connect String = Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=AdventureWorks;Integrated Security=SSPI;

ADO Version = 2.8

adoConn.State = 1

Query String = SELECT Demographics from Sales.Store WHERE CustomerID = 3 FOR XML AUTO

Pushing XML to client for processing

Client-side processing of XML Document MyDataIsle
  • AnnualSales:  1500000
  • AnnualRevenue:  150000
  • BankName:  Primary International
  • BusinessType:  OS
  • YearOpened:  1974
  • Specialty:  Road
  • SquareFeet:  38000
  • Brands:  3
  • Internet:  DSL
  • NumberEmployees:  40

The VBScript message box will then show the following original, unfiltered XML data island contents that were returned by the FOR XML query results.

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <Sales.Store>
    <Demographics>
      <StoreSurvey xmlns="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey">
        <AnnualSales>1500000</AnnualSales>
        <AnnualRevenue>150000</AnnualRevenue>
        <BankName>Primary International</BankName>
        <BusinessType>OS</BusinessType>
        <YearOpened>1974</YearOpened>
        <Specialty>Road</Specialty>
        <SquareFeet>38000</SquareFeet>
        <Brands>3</Brands>
        <Internet>DSL</Internet>
        <NumberEmployees>40</NumberEmployees>
      </StoreSurvey>
    </Demographics>
  </Sales.Store>
</ROOT>

Retrieving FOR XML Data with ASP.NET and the .NET Framework

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. 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.

In this example, the following Microsoft .NET Framework managed APIs are used to accomplish the return and rendering of the FOR XML query results:

  1. SqlConnection is used to open a connection to SQL Server, based on the contents of a specified connection string variable, strConn.

  2. 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.

  3. 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.

  4. 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.

    <%@ Page Language="VB" %>
    <HTML>
    <HEAD>
    <TITLE>FOR XML Query Example</TITLE>
    <STYLE>
       BODY
       {
          FONT-FAMILY: Tahoma;
          FONT-SIZE: 8pt;
          OVERFLOW: auto
       }
       H3
       {
          FONT-FAMILY: Tahoma;
          FONT-SIZE: 8pt;
          OVERFLOW: auto
       }
    </STYLE>
    </HEAD>
    <BODY>
    <%
    Dim s as String
    s = "<H3>Server-side processing</H3>" & _
        "Page Generated @ " & Now() & "<BR/>"
    
    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 & "<P>SqlConnection opened.</P>" 
    
    MySqlAdapter.Fill(MyDataSet)
    s = s & "<P>" & MyDataSet.GetXml  & "</P>"
    
    MySqlConn.Close()
    s = s & "<P>SqlConnection closed.</P>" 
    
    Message.InnerHtml=s
    %>
    <SPAN id="Message" runat=server />
    </BODY>
    </HTML>
    

To test this example

  1. Verify that IIS is installed and that the AdventureWorks sample database for SQL Server 2005 has been installed.

    This example requires that Internet Information Services (IIS) version 5.0, or later versions, are installed with ASP.NET support enabled. Also, the AdventureWorks sample database has to be installed for SQL Server 2005. For more information, see Installing AdventureWorks Sample Databases and Samples.

  2. Copy the code that was previously provided and paste it into the XML or text editor that you use. Save the file as RetrieveResults.aspx in the root directory used for IIS. Typically, this is C:Inetpub\wwwroot.

  3. Open the ASP.NET page in a browser window using the following URL. First, replace 'MyServer' with either "localhost" or the actual name of the server where SQL Server 2005 and IIS are installed.

    http://MyServer/RetrieveResults.aspx
    

The generated HTML page results that appear will be similar to the following sample output:

Server-side processing
Page Generated @ 3/11/2005 3:36:02 PM

SqlConnection opened.

<Sales.Store><Demographics><StoreSurvey xmlns="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"><AnnualSales>1500000</AnnualSales><AnnualRevenue>150000</AnnualRevenue><BankName>Primary International</BankName><BusinessType>OS</BusinessType><YearOpened>1974</YearOpened><Specialty>Road</Specialty><SquareFeet>38000</SquareFeet><Brands>3</Brands><Internet>DSL</Internet><NumberEmployees>40</NumberEmployees></StoreSurvey></Demographics></Sales.Store>

SqlConnection closed.

Note

The xml Data Type support provided in SQL Server 2005 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. When the TYPE directive is used in FOR XML queries, it provides programmatic access to the FOR XML results similar to that shown in Working with the xml Data Type in Applications.

See Also

Reference

Constructing XML Using FOR XML

Help and Information

Getting SQL Server 2005 Assistance