Processing XML on the Client Side (SQLXML Managed Classes)

This example illustrates the use of the ClientSideXml property. The application executes a stored procedure on the server. The result of the stored procedure (a two-column rowset) is processed on the client side to produce an XML document.

The following GetContacts stored procedure returns FirstName and LastName of employees in the Person.Person table in the AdventureWorks2008R2 database.

USE AdventureWorks2008R2;
GO
CREATE PROCEDURE GetContacts @LastName varchar(20)
AS
SELECT FirstName, LastName
FROM   Person.Person
WHERE LastName = @LastName;
Go

This C# application executes the stored procedure and specifies the FOR XML AUTO option in specifying the CommandText value. In the application, the ClientSideXml property of the SqlXmlCommand object is set to true. This allows you to execute preexisting stored procedures that return a rowset and apply an XML transformation to it on the client.

Note

In the code, you must provide the name of the instance of Microsoft SQL Server in the connection string.

using System;
using Microsoft.Data.SqlXml;
using System.IO;
class Test
{
    static string ConnString = "Provider=SQLOLEDB;Server=(local);database=AdventureWorks2008R2;Integrated Security=SSPI";
      public static int testParams()
      {
         //Stream strm;
         SqlXmlParameter p;
         SqlXmlCommand cmd = new SqlXmlCommand(ConnString);
         cmd.ClientSideXml = true;
         cmd.CommandText = "EXEC GetContacts ? FOR XML NESTED";
         p = cmd.CreateParameter();
         p.Value = "Achong";
         using (Stream strm = cmd.ExecuteStream()) 
         {
            using (StreamReader sr = new StreamReader(strm))
                  {
               Console.WriteLine(sr.ReadToEnd());
            }
         }
         return 0;
      }

public static int Main(String[] args)
{
    testParams();
    return 0;
}
}

To test the application

1. Create the stored procedure.

2. Save the C# code (DocSample.cs) that is provided in this example in a folder. Edit the code to specify appropriate login and password information.

3. Compile the code. To compile the code at the command prompt, use:

   csc /reference:Microsoft.Data.SqlXML.dll DocSample.cs
   

   This creates an executable (DocSample.exe).

4. At the command prompt, execute DocSample.exe. To test this example, you must have the Microsoft .NET Framework installed on your computer.