Export (0) Print
Expand All

Exchanging XML with SQL Server 2000 and Reporting Services through the Web and Web Services

 

Venkat Prasad
Microsoft Corporation

January 2005

Applies to:
   Microsoft SQL Server 2000
   XML Web Services

Summary: Venkat Prasad discusses some programming techniques for saving XML data from Microsoft SQL Servers, Web Servers, XML Web Services, and Microsoft SQL Reporting Servers. Topics include a comparison of traditional techniques used for saving XML streams, using ASP/ASPX and COM, with newer ones using .NET Framework Classes—datasets, XMLTextReaders and XMLTextWriters, XSLTransformation, saving data from SQL Reporting Servers, and uploading XML back to SQL Server. (15 printed pages).

Contents

Introduction
Downloading XML from the Web with ASP and ASPX, COM, and URL Access
Downloading XML Data from the XML Web Service
Downloading XML Data from a SQL Reporting Server
Uploading XML Data to a SQL 2000 Server
Conclusion

Introduction

Business data is increasingly exposed on the Internet and the Intranet, and moved across the wire as XML through traditional Web server calls (HTTP POST), .NET XML Web Services, or even Microsoft SQL Reporting Server. Unlike the data from traditional Web server calls (HTTP POST), data from XML Web Services and SQL Reporting Servers is also accessible from different platforms through SOAP protocols.

SQL developers are often confronted with the challenging task of writing tools that pull large amounts of XML data from SQL Server that are exposed through various Web sources—more challenging is the ability to process and transform the XML data in different formats or shapes as required by the different data consumers.

Traditional Way: Traditional Web server calls (HTTP POST) expose XML data from SQL Server through ASP and ASPX with COM using ADODB streaming with a "FOR XML" clause. Another popular method for displaying data in the browser if you want to avoid coding is "URL Access" (you need to configure SQL XML support in the IIS utility that ships with Microsoft SQL Server 2000) by setting up virtual directories. If you are interested in just saving streams and not displaying data in the browser, Web server is not needed—COM could be wrapped in Visual Basic Script. You further customize the XML stream by applying template files, XSL (Extensible Style Sheet), XPath queries, or even using mapping files to relational schemas. Note that the ActiveX Data Objects (ADO) data model—Recordsets and ADO Streams—is different from the data model in the Microsoft .NET Framework (ADO.NET Datasets and Framework Classes). Apart from type and versioning issues in COM, the data is not universally accessible and is not platform independent.

.NET and XML Web Services: In .NET you could save XML streams from SQL Server (with a "FOR XML" clause) using either ADO.NET Datasets or XmlTextReader and XmlTextWriter classes. You could apply XSL Transformation, for HTML representation, for example, or for changing the shape and format of the XML Document. You can call these from a C# or Visual Basic .NET Client without going through a Web server, but what is more interesting is that these method calls can be better exposed as XML Web Service Methods on the Internet—for platform independent universal data access through SOAP protocols.

I'll cover both traditional and .NET approaches in this article, and for the sake of completeness, I'll also discuss downloading XML data from a SQL Reporting Server—since Reporting Server exposes its Reports through XML Web Service methods—and finally, uploading XML back to SQL Server using the XMLBULKLOAD utility.

Downloading XML from the Web with ASP and ASPX, COM, and URL Access

A traditional way of exposing XML data from a SQL Server is to use the ADODB Streaming method (a "For XML" clause is needed to specify that results be returned as an XML stream) in an ASP or ASPX page, either directly or through a COM Component (IIS Server need not be on the dedicated SQL Server). This technique allows clients to access the data with an HTTP POST or an HTTP GET, passing query parameters by means of query strings.

Note   I am using COM through ASP or ASPX—the same code can be written with plain ASP or ASPX without COM.

I call the Web.asp given below from a C# client. The ASP page instantiates the Visual Basic COM object that uses the SQL query passed to it through HTTP POST, and gets the XML stream and saves it as a XML document to a UNC share or file path. In practice, it is an extremely dangerous thing to execute random SQL queries submitted from a Web request, but we use this approach here to illustrate the concept of receiving input data from the request. (In fact, it is good idea to verify the incoming data and then use it to build a SQL query.) The same technique is used for calling the ASPX page. Note that the ASPX page needs some modifications for compatibility—you need to remove the set, directive, enclose responses and methods in parenthesis, and add <% @Page Explicit="True" aspcompat="true" %>. Both ASP and ASPX pages can run on the same IIS 6.0 server for this test.

The sample Visual Basic COM Object has the following two methods (references "Program Files\Common Files\System\ado\msador15.dll")—ConnectToSQLServer and ExecuteForXMLSQL. I compiled this code as ActiveX.dll and named it "adodbStream.dll."

Public Function ConnectToSQLServer(ByVal SrvName As String,
Optional ByVal DbName As String = "master", Optional ByVal UsrName As String,
Optional ByVal Passwd as String) 
Set cn = New ADODB.Connection
cn.ConnectionTimeout = 0
cn.Provider = "sqloledb"
cn.Properties("Data Source").Value = SrvName
cn.Properties("Initial Catalog").Value = DatabaseName
cn.Properties("Integrated Security").Value = "SSPI"
cn.Open
End Function
Public Function ExecuteForXMLSQL (ByVal MySQLQuery As String,
Optional ByVal isXML As Boolean, Optional ByVal strFileSavePath As String)
Dim mystr1, mystr2 As String
Dim objStream, adostreamquery As ADODB.Stream
On Error GoTo ErrorTrap:
cn.CursorLocation = adUseServer
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn.ConnectionString
cmd.CommandType = adCmdText
Set adostreamquery = New ADODB.Stream
adostreamquery.Open 
adostreamquery.WriteText <?xml version="1.0" standalone="yes" ?>
adostreamquery.WriteText "<ROOT xmlns:sql='urn:schemas-microsoft- com:xml-sql'> ", 
adWriteChar
adostreamquery.WriteText " <sql:query> ", adWriteChar
adostreamquery.WriteText MySQL, adWriteChar
adostreamquery.WriteText " </sql:query> ", adWriteChar
adostreamquery.WriteText "</ROOT> ", adWriteChar
adostreamquery.Position = 0
Set cmd.CommandStream = adostreamquery
''' dialect is different for XPATH command 
cmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
Set objStream = New ADODB.Stream
objStream.Open
cmd.Properties("Output Stream") = objStream
cmd.Execute , , adExecuteStream
objStream.Position = 0
objStream.SaveToFile strFileSavePath, adSaveCreateOverWrite
ExecuteForXMLSQL = "File Saved at Share :" & strFileSavePath
End Function 

The stream returned by "FOR XML" from the SQL Server is a document fragment without a root node—before you can save the SQL stream as an XML Document, you will have to add something like the following.

adostreamquery.WriteText "<ROOT xmlns: sql='urn: schemas-microsoft- com: xml-sql'> ", adWriteChar 

Instead, you can use the XML Root property in an ADO command object, or specify a template file in ADO code.

The following sample Web.asp page is an ASP page that uses the methods exposed by the previous COM object.

<%@ LANGUAGE="VBScript" %>
<% Option Explicit %>
<script LANGUAGE="VBScript" runat = server>
</script><% 
Dim myobject, Mystring, MySQLQuery, strTableName, strColName, strTopNumRecords, 
strSavePath
strTableName = Trim(request.QueryString("TableName"))
strColName = Trim(request.QueryString("ColName"))
strTopNumRecords = Trim(request.QueryString("TopNumRecords"))
strSavePath = Trim(request.QueryString("SavePath"))
set myobject = server.CreateObject("adodata.server")
myobject.Connect "MySQLServer", "MyDatabaseName"
MySQLQuery= "select TOP " &  strTopNumRecords & " " & strColName & " from " & 
strTableName & "(nolock) FOR XML AUTO"
Mystring =myobject.ExecuteSQL(MySQLQuery,"true",strSavePath) 
set myobject = nothing %>

Note   Ideally, in your real-world application, you should not pass queries directly by means of Web URLs, since it is vulnerable to a SQL injection attack. It is a good idea to encapsulate the logic in a template (discussed in the next section) or a stored procedure, and to also use "FOR XML EXPLICIT" to create desired hierarchical view of XML with a group-by clause using a stored procedure (since the plan is cached and the code is pre-compiled).

You can call this Web.asp page (just like doing HTTP POST from a browser) from a C# Client using a Web client class or the HttpWebRequest class—the following C# client does that.

public bool GetDataFromASPXWebPage(string SourceUrl,    
string Savepath){// Create a new 'HttpWebRequest' Object 
try{
  HttpWebRequest myHttpWebRequest=
  (HttpWebRequest)WebRequest.Create(SourceUrl);
  // Set the  'Timeout' property of the HttpWebRequest
  myHttpWebRequest.Timeout=1000;
  myHttpWebResponse=(HttpWebResponse)myHttpWebRequest.GetResponse();
  Stream response = myHttpWebResponse.GetResponseStream();
  Encoding encode = System.Text.Encoding.GetEncoding("utf-8");
  // Pipe the stream to stream reader
  StreamReader readStream = new StreamReader( response, encode );
  Console.WriteLine(readStream.ReadToEnd()); 
  readStream.Close();
  return true;}
catch (Exception e)
{Console.WriteLine("{0} Exception caught.", e);
Console.WriteLine("{0} Exception caught.", e.Message);}
return false;}
// the above c# method is called using the following code:
string URL="http://WebServer/Web.asp?TableName=mytblname&ColName=myColumnname
&TopNumRecords=1000&SavePath=\\\\RemoteServer\\XML\\WebXMLData.xml";
try{NewSourceSavePath = SourceSavePath + "\\" + "WebXMLData.xml";
   T0 = System.DateTime.Now;
   if(GetDataFromASPXWebPage (URL,NewSourceSavePath))
   {T1 = System.DateTime.Now;
Console.WriteLine("\nTime Taken for WebClient Post :{0}" ,diff1 = T1.Subtract(T0));   }}
   catch (Exception e){
   Console.WriteLine("{0} Exception caught.", e);}

Alternatively, you can use a WebClient class for doing a post to a Web page instead of using an HTTPWebRequest. To pass in parameters, a NameValueCollection class can be used as follows.

   WebClient wc=new WebClient();
   wc.Headers.Add("Content-Type", "application/x-www-form-urlencoded");
   wc.Credentials=CredentialCache.DefaultCredentials;
System.Collections.Specialized.NameValueCollection SNS = new 
System.Collections.Specialized.NameValueCollection();
   SNS.Add("TableName","tblnetstatsperds");
   SNS.Add("ColName","ns_client");            
   SNS.Add("TopNumRecords","1000000");
   SNS.Add("SavePath",@"c:\\xmltest\mytest.xml");
   wc.QueryString =(SNS);
   String postData=  "";
   // Apply ASCII Encoding to obtain the string as a byte array.
   Byte[] byteArray =System.Text.Encoding.ASCII.GetBytes(postData);
try{ 
   Byte[] res=wc.UploadData(SourceUrl,"POST",byteArray);
   String XmlFrag=System.Text.Encoding.ASCII.GetString(res);
   XmlDocument mydoc=new XmlDocument();
   mydoc.LoadXml(XmlFrag);
   mydoc.Save(Savepath);
   return true;}

Note   The Web.asp page just returns the status that the file was saved successfully to the C# client. In this example, I did not want to display the stream in the browser; instead, I wanted to save the stream as an XML Document. If you want to display the XML in the Browser, you should add some response.write tags in Web.asp, and change the code in the Visual Basic COM Object method (ExecuteForXMLSQL) to return the ADODB.Stream instead of saving it directly to a file.

URL Access is another way of exposing data from SQL Server, primarily in the browser. This is done by using the "Configure SQL XML Support in IIS" utility, which ships with SQL Server 2000. The Sqlisapi.dll uses the native OLE DB Provider for SQL Server (SQLOLEDB) and communicates with the instance of Microsoft SQL Server identified in the virtual root (XML functionality is implemented in Sqlxmlx.dll). The template files, XML-Data Reduced (XDR) schema files, and Extensible Stylesheet Language (XSL) files reside on the IIS server (they need not be on the SQL Server). Once virtual directories and virtual names are configured (three types are allowed—Template, Schema, or dbobject), you can display data on the browser through URL calls as shown in the following section.

Templates: XSL, XPATH, and XDR

A template is a valid XML document, consisting of one or more SQL queries. In the URL a template name can be specified like the following:

http://WebServer/VirtualDirecotoryName/TemplateVirtualName/MyTemplatefile.xml, where "MyTemplatefile.xml" can have a SQL Query inside it. (Examples here refer to the NorthWind Sample Database that ships with SQL Server 2000.)

<root xmlns:sql="urn:schemas-microsoft-com:xml-sql" >
<sql:query>
   SELECT 1 as Tag, NULL as Parent,Customers.city as [CITY!1!city],
      Customers.ContactTitle as [CITY!1!ContactTitle],   
        Customers.ContactName as [CITY!1!ContactName]
   FROM Customers
      GROUP BY city,ContactName,ContactTitle
      FOR XML EXPLICIT
  </sql:query>
</root>

You can also specify a valid Extensible Style Sheet Language (XSL) in the template. Say I want to further customize the above results—I want to order by contact name, I do not want to display the contact title column, and I want to see customer name as an element and not as an attribute. This is done by applying the following XSL Style Sheet to the previously given template (add this to the first line in MyTemplatefile.xml above sql:xsl="MyXsl.xsl">).

<xsl:stylesheet xmlns:xsl="http://www.w3.org/TR/WD-xsl">
<xsl:template match="/">
    <root>
     <xsl:for-each select="root/CITY" order-by="+@ContactName">
      <City>
<xsl:attribute name="CityName"><xsl:value-of select="@city"/></xsl:attribute> 
<xsl:element name="CustomerName"><xsl:value-of select="@ContactName"/></xsl:element> 
      </City>
     </xsl:for-each>
    </root>
  </xsl:template>
</xsl:stylesheet>

You can write the XPath queries in a template, and define parameters and mapping schemas. Annotated mapping schema or XDR files can be defined to map XML elements and attributes to tables and columns of a relational database. XDR (XML-Data Reduced) schemas are conceptually XML views to which XPath queries can be applied (just like creating SQL views and querying the views). The mapping file defines column names and data types just like a view definition. (By default, an element name in an annotated schema maps to a table (or view) name in the specified database, and the attribute name maps to the column name.) Templates can be pointed to these mapping schemas (<sql:xpath-query mapping-schema="MyView.xdr">), and parameters can be included. For example, if I define a template that points to MyXdr file, as follows,

<root xmlns:sql="urn:schemas-microsoft-com:xml-sql" >
  <sql:xpath-query mapping-schema=" MyView.xdr">
    /CUSTOMER
  </sql:xpath-query>
</root>

where MyView.xdr defines the mapping from attributes to columns, and where CUSTOMER is the element and ID and name are attributes, like the following.

<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"         xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <ElementType name="CUSTOMER" sql:relation="MyCustomers">
   <!-- attribute in XML map to columns-->
    <AttributeType name="ID" />
    <AttributeType name="name" />
      <!-define view columns in SQL Table  -->
    <attribute type="ID" sql:field="CustomerID" />
    <attribute type="name" sql:field="CompanyName" />
   </ElementType>
</Schema>

For all these examples, apart from using URL queries access (IIS Server), ADO code can be used from the client side with appropriate dialect, mapping schema references, and base path properties of ADO's command object, to save the stream as XML document.

Downloading XML Data from the XML Web Service

The XML Web Service exposes data through Web methods that you specify—the data is universally accessible by means of SOAP messaging and is platform independent, unlike ASP/ASPX. In this section, I will walk through two Web methods; one using Datasets (with "FOR XML"), and the other using XmlReaders (If you do not want to display data through the XML Web Service, you could use the same code as plain methods in C# client code).

[WebMethod]
public GetXMLDataStreamDS(string SQLQuery,string SavepathWithFileName)
{ //   XmlTextReader class gets the stream using "FOR XML"
string Myconnetionstring = "Trusted_Connection=Yes;" + "initial catalog=MyDatabase;"
 + "data source=MyServerName";          
SqlConnection SQLConn = new SqlConnection(Myconnetionstring);
SqlCommand selectCMD = new SqlCommand(SQLQuery, SQLConn);
selectCMD.CommandTimeout = 0;
SQLConn.Open();
DataSet myDataSet = new DataSet();
XmlTextReader Xrdr = (XmlTextReader)selectCMD.ExecuteXmlReader();
try{
   myDataSet.ReadXml(Xrdr, XmlReadMode.Fragment);
myDataSet.WriteXml(SavepathWithFileName,XmlWriteMode.IgnoreSchema);}
catch(Exception ex){
throw new ApplicationException("Error: " + ex.Message, ex);}
finally{SQLConn.Close();
Xrdr.Close();}

Datasets support a disconnected cache of data, and include support for features such as XML serialization, reading, writing XML Schemas, and writing relational data as XML. However, the whole dataset needs to be loaded into memory, so make sure there is enough memory on the box (this is similar to the ADO RecordSet in-memory representation). Note that you could also use a Dataset with XmlTextWriter and XSLTransform to save the stream as XML document, like the following.

DA.Fill(myDataSet);
XmlTextWriter xmlw = new XmlTextWriter(SavepathWithFileName, System.Text.Encoding.UTF8);
xmlw.WriteStartElement("ROOT");
xmlw.WriteComment("This is a sample from MSDN");
XPathDocument doc = new XPathDocument(new System.IO.StringReader(myDataSet.GetXml()));
System.Xml.Xsl.XslTransform xslt = new System.Xml.Xsl.XslTransform();
xslt.Load(@"c:\xmltest\mySchema.xslt");
xslt.Transform(doc, null, xmlw,null);
xmlw.Flush();
xmlw.Close();}
catch(Exception ex)
   {throw new ApplicationException("Error: " + ex.Message, ex);}
finally{SQLConn.Close();}   } 

Streaming API Classes like the XmlReader class provide non-cached, forward-only, read-only access—this offers more flexibility and performance, and consumes less memory when compare to datasets. Also, like datasets, Streaming API Classes let you apply XslTransform, use XPath or filter, manipulate the nodes further or skip the node sets that you are not interested in (say, use the While XmlReader.Read() method), or use the XPathNavigator class as the business application requires.

In the following example, I use the XmlTextReader class to get the "FOR XML" stream returned by the command object's ExecuteXmlReader method, instantiate an XmlTextWriter, and write a "ROOT" element (or you could use appropriate XSLT), and then pass XmlReader to an XPath Document's constructor argument, XSLTransform, or apply XPath before saving the data as an XML Document.

[WebMethod]
public GetXMLStreamNoDataset(string SQLQuery,string SavepathWithFileName){ 
string Myconnetionstring = "Trusted_Connection=Yes;" + "initial catalog=MyDatabase;"
 + "data source=MyServerName";          
SqlConnection SQLConn = new SqlConnection(Myconnetionstring);
SqlCommand selectCMD = new SqlCommand(SQLQuery, SQLConn);
XmlTextWriter xmlw = new XmlTextWriter(SavepathWithFileName, System.Text.Encoding.UTF8);
try{selectCMD.CommandTimeout = 0;
   SQLConn.Open();
   XmlTextReader txtrdr= (XmlTextReader)selectCMD.ExecuteXmlReader( ); 
    xmlw.WriteStartElement("ROOT");
xmlw.WriteComment("This is a sample from Venkat");
   XPathDocument doc = new XPathDocument(txtrdr, XmlSpace.Default);
System.Xml.Xsl.XslTransform xslt = new System.Xml.Xsl.XslTransform();
   xslt.Load(@"c:\xmltest\mySchema.xslt");
   xslt.Transform(doc, null, xmlw,null);
   xmlw.Flush();}   
catch (Exception ex){throw new ApplicationException
   ("Error: " + ex.Message, ex);   }
finally{   SQLConn.Close(); 
xmlw.Close();}   }

I call these XML Web Service methods from a C# client code using a proxy class and Web reference (generated by Visual Studio, as in this example).

public bool GetWebServiceXMLDataDS (string SQLQuery, string FilenameWithSavePath){
try{StreamingApp.venkat.MyXMLWebClass MywebObj = new MyXMLWebClass();
MywebObj.Timeout = 1000;
bool Success = MywebObj.GetXMLDataDS(SQLQuery,FilenameWithSavePath);
   if (Success){
Console.WriteLine("File Written successfully to: {0}",FilenameWithSavePath);}
   return true; }
catch (Exception e) {
   Console.WriteLine("{0} Exception caught.", e);
   Console.WriteLine("{0} Exception caught.", e.Message);}
   return false; }
public GetWebServiceXMLDataStream(string SQLQuery, string FilenameWithSavePath){
try{StreamingApp.venkat.MyXMLWebClass MywebObj =  new MyXMLWebClass();
   MywebObj.Timeout = 1000;
bool Success =MywebObj.GetXMLStreamNoDataset(SQLQuery,FilenameWithSavePath);
if (Success){Console.WriteLine("File Written successfully to: {0}",FilenameWithSavePath);}return true;}
catch (Exception e){
Console.WriteLine("{0} Exception caught.", e);
   Console.WriteLine("{0} Exception caught.", e.Message);}
return false;}   
//To time the method calls within the client code use:
try{ T0 = System.DateTime.Now;
if(sc. GetWebServiceXMLDataDS ("select TOP 1000 myColumname from myTableName(nolock)
FOR XML AUTO, XMLDATA ",SourceSavePath + "XMLWebServiceDataStream.xml")){T1 = System.DateTime.Now;
Console.WriteLine("\nTime Taken for WebServiceXMLDataStream :{0}",diff1 = T1.Subtract(T0));}}
catch (Exception e)
   {Console.WriteLine("{0} Exception caught.", e);   }

Note   The above example of a Web service method saving an XML file is done for illustration's sake only—actually, Web services are made to return XML. Alternatively, you could read the stream into a DOM and return the DOM, but you would probably be better off getting the dataset and associating an XmlDomDocument.

You could also download the Web service utility kit (SQLXML) that exposes Microsoft SQL Server 2000 as a Web service on the client side. You can send Web service requests to the server that is running SQLXML. This is not covered in this article, so for more information please see Web Services (SOAP) Support in SQLXML.

Downloading XML Data from a SQL Reporting Server

Data from SQL Reporting, like any XML Web Service, is universally accessible by means of SOAP messaging—that is, it is platform agnostic, unlike data exposed from a traditional Web server call using ASP and ASPX (HHTP POST). Using the C# client, code data from reports can be downloaded as XML files—there is support for other formats, like MHTML for graphs and charts, or .CSV files. In fact, a report definition can be downloaded, too; the actual report is a *.rdl file, which is actually an XML file—it can be opened in notepad—that can be easily customized and re-deployed again. The following code sample shows the download of data from a SQL Reporting server and saving it to a share. This is similar to using the WebClient Class example previously shown.

public bool GetSQLReportingServerData(string Savepath, string Filename)
{string Savepathfilename = Savepath + Filename;
try{
StreamingApp. MyReportServer.ReportingService MywebReportServer =  
new StreamingApp.MyReportServer.ReportingService();
MywebReportServer.Credentials = System.NET.CredentialCache.DefaultCredentials;
// Render arguments
   byte[] result = null;
   string reportPath = "/DBGrowth/USRTechDBGrowthChart";
   string format;
   //byte[] reportDefinition; //if you want Report Definition
   // = MywebReportServer.GetReportDefinition(reportName);
   System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
   //The report definition as a Base 64-encoded byte array. 
   ParameterValue[] parameters = new ParameterValue[1];
   parameters[0] = new ParameterValue();
   parameters[0].Name = "propertyname";
   parameters[0].Value = "My Variable"; 
   DataSourceCredentials[] credentials = null;
   Warning[] warnings = null;
   ParameterValue[] reportHistoryParameters = null;
   string[] streamIDs = null;
   SessionHeader sh = new SessionHeader();
   MywebReportServer.SessionHeaderValue = sh;
try{   // load the binary into an XML document 
   format = "XML";
result = MywebReportServer.Render(reportPath, format, historyID, devInfo, parameters, credentials, 
showHideToggle, out encoding, out mimeType, out reportHistoryParameters, out warnings,   out streamIDs);
   doc.Load(new System.IO.MemoryStream(result));
   doc.Save(Savepath + "report.xml");
    Console.WriteLine( "Result Saved to XML file" );
   //You can specify other formats if you need
//like format = "CSV" or format = "MHTML";
   return true;
catch (Exception e){
   Console.WriteLine("{0} Exception caught.", e);
   Console.WriteLine("{0} Exception caught.", e.Message); }
return false;   }

If you want to further process the results returned, you could apply XSL transformations, or even get the data as a dataset to merge data from other sources.

In the previous example, we load memory stream into the XML Document (the SOAP call returned an encoded byte[] array). You could instead load it in a dataset and further apply XSLT.

byte[] result = MywebReportServer.Render(...);
MemoryStream stream = new MemoryStream(renderResult);
DataSet MyDs = new DataSet();
MyDs.ReadXml(stream);
System.IO.StringReader(MyDs.GetXml()));
System.Xml.Xsl.XslTransform xslt = new System.Xml.Xsl.XslTransform();
xslt.Load(@"c:\xmltest\mySchema.xslt");

Uploading XML Data to a SQL 2000 Server

XML Bulk Load is a stand-alone COM object that allows you to load XML data into a SQL Server table—internally, it uses a mechanism similar to bcp with a native SQLOLEDB provider. Since it is COM, it can also be used with ADO, Visual Basic Script, or in a .NET Application.

The following code sample shows how to load an XML document directly into SQL Server using "SQLXMLBULKLOAD." (Add to your Visual Studio C# project—the reference under COM—"Microsoft XML BulkLoad for SQL Server 3.0 Type Library.") There is one catch here since it is COM—the threading model needs to be STA while calling this function.

For a complete discussion, see Using SQLXML Bulkload in the .NET Framework.

public bool BulkLoad(string SourceSavePath, string ConnectionStr,
string   MyXsdFile, string MyXMLFile )   {
bool test;       
try{
Type Mytype;
   SQLXMLBulkLoad objBL = new SQLXMLBulkLoad();
   objBL.ConnectionString=ConnectionStr;
   objBL.ErrorLogFile = SourceSavePath + "\\" + "error.log";
   objBL.CheckConstraints = true; 
   test = objBL.Transaction;
   objBL.TempFilePath=SourceSavePath;
   objBL.XMLFragment = true;
   objBL.SchemaGen = true;
   objBL.SGDropTables = true;
objBL.Execute(SourceSavePath + "\\" + MyXsdFile, SourceSavePath + "\\" + MyXMLFile);
   return true;   }
catch (Exception e)   {
   Console.WriteLine("{0} Exception caught.", e);   }
   return false;}

For the first time, the table into which the data is uploaded needs to be created on the SQL Server with appropriate keys before you run the code—that is, the table has to pre-exist in SQL Server. When you run the code,

objBL.SchemaGen = true;
objBL.SGDropTables = true;

the table is actually dropped and re-created and the data is loaded from the XML file. After the load process is done, you might want to add indexes and constraints through TSQL as a job step. The table schema is created from the XSD file that you specify (MyXsdFile in the example code). SQLXMLBULKLOAD uses the element, and the attribute names and type information from the XSD schema, to generate tables. It is similar to the mapping and annotated schemas discussed previously in this article (that is, XDR files used to create XML views).

XSD schemas files are often used to describe the content and structure of XML data—SQLXML specific mapping annotations belong to the xmlns:sql="urn:schemas-microsoft-com: mapping-schema namespace.

Say we have an XML file that has a root element name "ROOT," and the file has an element name Customer with two attributes: FirstName and LastName. Our task is to load the customer data from the XML file into an SQL table named SQLCustomerTable that has two columns: SQLColumnFirstName and SQLColumnLastName. In reality, you would have more attributes or more columns in the table.

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
   xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  <xsd:element name=" ROOT " sql:is-constant="true" >
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element ref="Customer" />
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
<xsd:element name="Customer" sql:relation="SQLCustomerTable" >
<xsd:complexType>
<xsd:attribute name="FirstName" sql:field="SQLColumnFirstName" sql:datatype="varchar(100)" /> 
<xsd:attribute name="LastName" sql:field="SQLColumnLastName" sql:datatype="varchar(100)" /> 
</xsd:complexType>
  </xsd:element>
</xsd:schema>

Note   The other alternative, SQL Server 2000 using "OPENXML," has severe limitations, including: scalability and memory issues with MSXML; not being able to directly pass in XML files or UNC file paths; the XML needs to be inline, passed in as strings, or used inside a stored procedure that takes the XML as an Ntext parameter; and so on.

Conclusion

This article covers several techniques for saving SQL data as XML code—data exposed directly from SQL Server, through Web Service, through XML Web Service, and SQL Reporting Server.

Traditional methods include ASP and ASPX with COM, and "URL Access" using the ADO Model. In the .NET Framework, either ADO.NET datasets or XmlTextReader and XmlTextWriter classes can be used to process the XML streams from SQL Server. The alternative technique is to use XmlTextReader and XmlTextWriter streaming API classes with a "FOR XML" clause in a stored procedure. In both cases, XML data could be further processed and formatted using XSLT, XDR Schemas, and XPath as the business application requires. A unique feature of .NET is that XML is exposed through XML Web Service methods and is accessible to different platforms by means of SOAP protocols.

Data from the SQL Reporting Server is also exposed as XML Web services and is universally accessible by means of SOAP messaging—data from reports can be downloaded as XML files. To complete the round trip, for uploading data back to SQL Server, the most efficient way is to use the SQLXMLBULKLOAD object model.

References

ADO and XML

XPath Querying Over Objects with ObjectXPathNavigator

The Best of Both Worlds: Combining XPath with the XmlReader

XPath Querying Over DataSets with the DataSetNavigator

Manipulate XML Data Easily with the XPath and XSLT APIs in the .NET Framework

Chapter 9-Improving XML Performance

 

About the author

Venkat Prasad, MCDBA, MCSD is a database developer and engineer at MSN working on Monitoring Tools, and ETL and Data Warehousing Projects that run on SQL Server and SQL Reporting Servers.

Show:
© 2014 Microsoft