perfectxml.com
 Basic Search  Advanced Search   
Topics Resources Free Library Software XML News About Us
 home Info Bank Articles » Exporting SQL Data as XML Saturday, 23 February 2008
Back to Articles Page      

Exporting SQL Data as XML

Author: Darshan Singh -- Managing Editor, perfectxml.com


Article Date: September 2002
Updated: Monday, September 09, 2002
Abstract: In my last article, Importing XML into SQL Server 2000, we looked at various techniques to import the XML data into SQL Server. To complement that, this article will focus on some of the methods that you can use to export relational SQL data to the XML format.

More specifically, we'll explore the following options: All the examples in this article are based on the Northwind sample database that ships with SQL Server 2000.

Related Articles: Importing XML into SQL Server 2000
OPENXML
SQLXML and MSXML - an alternative approach to traditional data access

Using ADO's XML Persistence Feature

ADO 2.5 and above can be used to persist the Recordset as the hierarchical XML stream. Let's start with a very simple Visual Basic 6.0 Example.

Visual Basic 6.0 Sample

Start Visual Basic 6.0, create a new standard EXE project, and add reference (Project | References) to ADO (2.5 or higher), double click the form and write the following code:


Option Explicit
Private Sub Form_Load()
    Dim objADORS As New Recordset
    
    objADORS.Open "SELECT ContactName From Customers", _
        "PROVIDER=SQLOLEDB.1;SERVER=.;UID=sa;PWD=;DATABASE=Northwind;"
    
    objADORS.Save "c:\Contacts.xml", adPersistXML
    
    objADORS.Close
    Set objADORS = Nothing
    
    Unload Me
End Sub

The above code connects to the Northwind sample database on the local SQL Server instance. Next, we open a Recordset containing ContactName field from the Customers table and save the Recordset as the XML document file named c:\Contacts.xml. The second parameter to the Save method is important here instead of using ADO's native Advanced Data TableGram (ADTG) format, we are asking it to save the Recordset as the XML stream. Make sure that c:\Contacts.xml files does not already exists, else you'll get an error while saving the Recordset as XML.

Run the above code and then open c:\Contacts.xml in Internet Explorer and you'll see the following XML document:


<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
	xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
	xmlns:rs='urn:schemas-microsoft-com:rowset'
	xmlns:z='#RowsetSchema'>

<s:Schema id='RowsetSchema'>
	<s:ElementType name='row' content='eltOnly' rs:CommandTimeout='30'>
		<s:AttributeType name='ContactName' rs:number='1' rs:nullable='true' rs:writeunknown='true'>
			<s:datatype dt:type='string' dt:maxLength='30'/>
		</s:AttributeType>
		<s:extends type='rs:rowbase'/>
	</s:ElementType>
</s:Schema>

<rs:data>
	<z:row ContactName='Maria Anders'/>
	<z:row ContactName='Ana Trujillo'/>
	<z:row ContactName='Antonio Moreno'/>
	<!-- And so on -->
</rs:data>

</xml>

The generated XML document contains XDR schema followed by the actual data nodes.

ASP Sample

Let's now look at an ASP example. Start Visual Interdev or Notepad (any text editor), and write following code:


<%
Option Explicit

Response.ContentType = "text/xml"

Dim ObjADORS
Const adPersistXML = 1

Set ObjADORS = Server.CreateObject("ADODB.Recordset")

ObjADORS.Open "SELECT ContactName FROM Customers", _
	"PROVIDER=SQLOLEDB.1;SERVER=.;UID=sa;PWD=;DATABASE=Northwind;"

ObjADORS.Save Response, adPersistXML 

ObjADORS.Close 
Set ObjADORS = Nothing
%>

Save the above page under an IIS virtual directory and browse to the page, you'll see the XML document similar to c:\Contacts.xml in the earlier example.

The above code is very similar to the first VB application, except this time, the XML persistence destination in a stream (ASP Response stream), instead of a disk file.

ADO XML Persistence and MSXML DOM

The ADO native XML persistence feature does not provide any control over the format of XML being generated. However, we can use MSXML DOM in conjunction with ADO XML persistence to further massage the exported XML data.

In the first example earlier in this article, we saved the XML-formatted Recordset onto a disk file; in the second example, we streamed it to the ASP Response stream, the third possibility (illustrated below) is to save the XML-formatted Recordset into a DOMDocument object.

In the following example, we'll persist the XML-formatted Recordset directly into MSXML DOMDocument object, and then use MSXML DOM to update the document (we'll remove the schema node and unused namespace declarations in this case).

Start Visual Basic 6.0, create a new standard EXE project, add reference to MSXML 4.0 SP1 and ADO 2.5 or higher and write the following code:


Option Explicit

Private Sub Form_Load()
    Dim objADORS As New Recordset
    Dim objXMLDOM As New MSXML2.DOMDocument40
    Dim schemaNode As MSXML2.IXMLDOMNode
    
    objADORS.Open "SELECT ContactName From Customers", _
        "PROVIDER=SQLOLEDB.1;SERVER=.;UID=sa;PWD=;DATABASE=Northwind;"
    
    'Save the recordset as a DOMDocument object
    objADORS.Save objXMLDOM, adPersistXML
    
    'Updating the generated XML document using MSXML
    With objXMLDOM
    
        'Removing schema node and hence unused namespace declarations
        'First select the Schema node and then call removeChild
        .setProperty "SelectionNamespaces", _
            "xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'"
        Set schemaNode = .selectSingleNode("//s:Schema")
        
        With .documentElement
            .removeChild schemaNode
            
            .removeAttribute "xmlns:s"
            .removeAttribute "xmlns:dt"
        End With
    
        'Using MSXML DOM Save method to save the updated document
        .Save "c:\ContactsData.xml"
    End With
    
    objADORS.Close
    Set objADORS = Nothing
    
    Unload Me
End Sub


The first parameter to Recordset Save method is a DOMDocument object. Once the Recordset XML is loaded in this DOMDocument object, we then select the schema node and remove it (by calling removeChild), and then remove the namespace declarations attributes and finally save the XML document using MSXML Save method. This method has another small benefit: even if the file c:\ContactsData.xml already exists, it is overwritten and no error is produced (unlike as in ADO Recordset Save method, which generates an error if the file already exists).

The above code creates the following XML document (c:\ContactsData.xml):


<xml	xmlns:rs='urn:schemas-microsoft-com:rowset'
	xmlns:z='#RowsetSchema'>

<rs:data>
	<z:row ContactName='Maria Anders'/>
	<z:row ContactName='Ana Trujillo'/>
	<z:row ContactName='Antonio Moreno'/>
	<!-- And so on -->
</rs:data>

</xml>

Compare the above output with the output from the first example, and you'll see that there is no schema information in the XML document.

Once the Recordset is persisted as XML and loaded in MSXML DOMDocument, we can do various things with it, such as apply the transformation, query it, update it, merge it with some other XML document, and so on.

Let's see an example of applying the stylesheet on the ADO persisted XML. The very first example in this article used ADO adPersistXML to create C:\Contacts.xml. Let's say we have to apply the stylesheet to convert this C:\Contacts.xml XML document into the following format:


<?xml version="1.0"?>
<NWCustomers>
	<Customer>Maria Anders</Customer>
	<Customer>Ana Trujillo</Customer>
	<Customer>Antonio Moreno</Customer>
	<!-- And so on -->
</NWCustomers>

You can use the follwing XSLT stylesheet to transform C:\Contacts.xml into the above XML format.


<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
	xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
	xmlns:rs='urn:schemas-microsoft-com:rowset'
	xmlns:z='#RowsetSchema'>

	<xsl:output method="xml" />

	<xsl:template match="/">
		<xsl:element name="NWCustomers">
		<xsl:for-each select="//rs:data/z:row">
			<xsl:element name="Customer">
			<xsl:value-of select="@ContactName" />
			</xsl:element>
		</xsl:for-each>
		</xsl:element>
	</xsl:template>
	
</xsl:stylesheet>

The important point to note in the above stylesheet is that the xsl:stylesheet root element includes the namespaces declarations (with prefixes) from the ADO persisted XML document. It allows us to use the namespace prefix inside the XPath expressions (such as xsl:for-each select="//rs:data/z:row") in our stylesheet.

Try using MSXML to load the C:\Contacts.xml, and then load and apply the above stylesheet.

Related links:

ADO and DOM

In this example, we'll not use ADO's XML persistence feature, but use ADO for regular data-access. Once we have the ADO Recordset, we'll use MSXML to transform the relational data into the hierarchical format.

Let's do DTS VBScript this time! We'll write a DTS ActiveX Script Task that will first use ADO to call a stored procedure that returns relational data (a Recordset) and then we'll use MSXML 4.0 to create a (hierarchical) XML document from the Recordset.

The first step is to create the following stored procedure in the Northwind sample database:


USE Northwind
GO

CREATE PROCEDURE sproc_Contacts_Orders
AS
	SELECT  
		FirstL = Left(c.CompanyName,1), 
		c.CustomerID, c.CompanyName, 
		c.ContactName, c.ContactTitle, 
		c.Phone, c.Fax, o.OrderID 
	FROM 
		Customers c LEFT OUTER JOIN Orders o 
		ON  c.CustomerID = o.CustomerID
	
	ORDER BY FirstL
GO

The above stored procedure, when run, produces the following results:



The results are sorted on the first letter from the CompanyName, and for each company we have contact details, and OrderIDs.

Let's say if we want to export this relational data into the following XML format:



Start SQL Server Enterprise Manager; right click on Data Transformation Services node in the tree, and select New Package. In the package designer, either drag-drop the ActiveX Script Task OR right click in the package designer window, select Add Task | ActiveX Script Task... and write the following code as the Visual Basic ActiveX Script:


'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Function Main()
	Dim objADORS
	Dim objXMLDoc

	Dim strLastLetter
	Dim strLastCustomerID

	Dim strCurrentLetter
	Dim strCurrentCustomerID
	
	Dim nodeTemp
	Dim nodeRoot
	Dim nodeLetter
	Dim nodeCustomer
	Dim nodeOrders

	'Create ADO and MSXML DOMDocument Objects
	Set objADORS = CreateObject("ADODB.Recordset")
	Set objXMLDoc = CreateObject("MSXML2.DOMDocument.4.0")

	'Run the stored procedure and load the Recordset
	objADORS.Open "EXEC sproc_Contacts_Orders", _
		"PROVIDER=SQLOLEDB.1;SERVER=.;UID=sa;PWD=;DATABASE=Northwind;" 

	'Prepare the XML Document
	objXMLDoc.loadXML "<Contacts_Orders />"
	Set nodeRoot = objXMLDoc.documentElement

	strLastLetter          = ""
	strLastCustomerID = ""

	'For each record in the Recordset
	While Not objADORS.EOF
		strCurrentLetter = objADORS.Fields("FirstL").Value
		strCurrentCustomerID = objADORS.Fields("CustomerID").Value

		'If the letter has changed, create the Letter node
		If strCurrentLetter <> strLastLetter Then
			Set nodeLetter = objXMLDoc.createElement(strCurrentLetter)
			nodeRoot.appendChild nodeLetter
		End If

		'If the CustomerID has changed, create the Customer node
		If strCurrentCustomerID <> strLastCustomerID Then
			Set nodeCustomer = objXMLDoc.createElement("Customer")
			nodeLetter.appendChild nodeCustomer

			Set nodeTemp = objXMLDoc.createElement("CustomerID")
			nodeTemp.nodeTypedValue = strCurrentCustomerID
			nodeCustomer.appendChild nodeTemp

			Set nodeTemp = objXMLDoc.createElement("CompanyName")
			nodeTemp.nodeTypedValue = objADORS.Fields("CompanyName").Value
			nodeCustomer.appendChild nodeTemp

			If NOT IsNull(objADORS.Fields("OrderID").Value) Then
				Set nodeOrders = objXMLDoc.createElement("Orders")
				nodeCustomer.appendChild nodeOrders
			End If

		End If

		If (NOT nodeOrders IS Nothing) AND (NOT IsNull(objADORS.Fields("OrderID").Value)) Then
			'Attach the OrderID node
			Set nodeTemp = objXMLDoc.createElement("OrderID")
			nodeTemp.nodeTypedValue = CStr(objADORS.Fields("OrderID").Value)
			nodeOrders.appendChild nodeTemp
		End If

		strLastLetter     = objADORS.Fields("FirstL").Value
		strLastCustomerID = objADORS.Fields("CustomerID").Value

		objADORS.moveNext
	Wend

	objADORS.Close
	Set objADORS = Nothing

	'Save the created XML document
	objXMLDoc.Save "c:\ContactsOrders.xml"

	Main = DTSTaskExecResult_Success
End Function

Click OK, right click on the task and select Execute Step, and if ran successfully, browse to c:\ContactOrders.xml and you'll see the XML tree with alphabet nodes first, Customer nodes below them and OrderIDs nodes below the Customer nodes. We can then schedule this DTS package to periodically export the relational data to the XML file.

In this example, we saw how ADO flat Recordset can be converted to hierarchical XML document using MSXML DOM. DOM is a memory- and resource-intensive when working with large XML documents. SAX is a better API while dealing with large XML documents. In addition, if the sheer goal is to just export the relational data into XML format, it is not required to load the entire XML document into memory (using DOM) before saving it to a file or before streaming it. Better, we can use SAX here.

ADO and SAX

MSXML SAX2 implementation provides a class named MXXMLWriter that provides support for handling the output generated by SAX events, and which can be used to generate XML documents. In other words, we can set MXXMLWriter object as the SAX event handler, then manually generate SAX events, and the XML document will be created and available as the output property in the MXXMLWriter class.

To learn more about MSXML SAX, visit http://www.perfectxml.com/msxmlSAX.asp.

Let's write an ASP page that connects to Northwind database and exports the data from the Shippers table using ADO and SAX:


<%
Option Explicit

Response.ContentType = "text/xml"

Dim ObjADORS
Dim objWriter 
Dim saxContentHandler
Dim objADOField
Dim strFldName
Dim objAttributes 
Dim objRSFields

Set ObjADORS  = Server.CreateObject("ADODB.Recordset")
Set objWriter = Server.CreateObject("MSXML2.MXXMLWriter.4.0")
Set objAttributes = Server.CreateObject("Msxml2.SAXAttributes.4.0")

'Get the relational data, open the recordset
ObjADORS.Open "SELECT * FROM Shippers", _
	"PROVIDER=SQLOLEDB.1;SERVER=.;UID=sa;PWD=;DATABASE=Northwind;"
	
'Prepare MXXMLWriter object
Set saxContentHandler = objWriter
objWriter.indent = True
objWriter.standalone  = True

'Send the writer output to the ASP response stream
objWriter.output = Response

'Generate SAX events
saxContentHandler.startDocument
saxContentHandler.startElement "", "", "Shippers", objAttributes

Set objRSFields = ObjADORS.Fields

'For each record
While Not ObjADORS.EOF
	'Create ShipperRecord element
	saxContentHandler.startElement "", "", "ShipperRecord", objAttributes
	
	'For each field
	For Each objADOField In objRSFields
		'Create the element with the same name as the field name
		strFldName =  objADOField.Name

		saxContentHandler.startElement "", "", strFldName, objAttributes
		saxContentHandler.characters CStr(objADOField.Value)
		saxContentHandler.endElement "", "", strFldName
	Next
	ObjADORS.MoveNext
	
	saxContentHandler.endElement "", "", "ShipperRecord"
Wend

saxContentHandler.endElement "", "", "Shippers"
saxContentHandler.endDocument

ObjADORS.Close 
Set ObjADORS = Nothing
%>

We first use ADO to run a SQL statement and create a Recordset; and then manually generate SAX events. These SAX events are handled by MXXMLWriter, whose output property is set to the ASP Response stream. As we generate SAX events, MXXMLWriter handles it, creates XML documents and streams it to the Response stream. This approach is a very lightweight and does not require much memory and system resources (as generated XML document is not cached or loaded in memory, but is directly sent to the Response stream). For large data, this method would work best, when compared to exporting data using DOM.

The above code produces the following XML document:


<?xml version="1.0" encoding="UTF-16" standalone="yes"?>
<Shippers>
	<ShipperRecord>
		<ShipperID>1</ShipperID>
		<CompanyName>Speedy Express</CompanyName>
		<Phone>(503) 555-9831</Phone>
	</ShipperRecord>
	<ShipperRecord>
		<ShipperID>2</ShipperID>
		<CompanyName>United Package</CompanyName>
		<Phone>(503) 555-3199</Phone>
	</ShipperRecord>
	<ShipperRecord>
		<ShipperID>3</ShipperID>
		<CompanyName>Federal Shipping</CompanyName>
		<Phone>(503) 555-9931</Phone>
	</ShipperRecord>
</Shippers>

Using SQL Server 2000 XML Features

SQL Server 2000 introduced XML support and allows retrieving relational data directly in the XML format via the FOR XML clause with the SELECT statement. The OPENXML function is provided to turn XML data into the relational rowset. And finally, HTTP support was added so that relational data can be accessed directly over HTTP. See http://www.perfectxml.com/SQLXML.asp for more details on this.

In this example, we'll use SQL Server 2000 XML Features to export SQL data as XML.

Using sp_makewebtask System Stored Procedure

SQL Server provides a stored procedure called sp_makewebtask that can be used to run the queries and generate HTML files. The FOR XML clause with the SELECT statement in SQL Server 2000 can be used in conjunction with sp_makewebtask to create an XML file. Here is how it works:

Create a text file named C:\temp\Shippers.txt as below:


<?xml version="1.0"?>
<Shippers>
<%begindetail%>
<%insert_data_here%>
<%enddetail%>
</Shippers>

Start Query Analyzer, type the following T-SQL command and execute it:


Use Northwind
GO

EXEC sp_makewebtask
	@outputfile = 'c:\temp\Shippers.xml',
	@query = 'SELECT * FROM Shippers FOR XML AUTO',
	@templatefile ='c:\temp\Shippers.txt'

The above command runs the SELECT..FOR XML query, uses the Shippers.txt template file and creates c:\temp\Shippers.xml output XML file. Open c:\temp\Shippers.xml and you should see:


<?xml version="1.0"?>
<Shippers>
  <Shippers ShipperID="1" CompanyName="Speedy Express" Phone="(503) 555-9831"/>
  <Shippers ShipperID="2" CompanyName="United Package" Phone="(503) 555-3199" />
  <Shippers ShipperID="3" CompanyName="Federal Shipping" Phone="(503) 555-9931" />
</Shippers>

The above method is simplest way of exporting relational data as the XML format, however:
  • The above method will work only with SQL Server 2000 and above.
  • Works best for small result set, as SELECT...FOR XML tends to wrap the result XML text, creating XML document that are no longer well-formed.
  • Special permissions are required to run the sp_makewebtask system stored procedure.
Tip: In SQL Query Analyzer, to view the "pretty-printed" XML text, when the SELECT...FOR XML query is run, run the following command first and make sure the results are in Text mode (and not in the grid mode).


DBCC TRACEON(257)

And then run the SELECT...FOR XML query; you'll see that the XML returned is somewhat formatted, and better readable.

Using MSXML XMLHTTP Component

As mentioned earlier, SQL Server 2000 added support for accessing the relational data directly over HTTP. A tool called as "IIS Virtual Directory Management for SQL Server" was added that allows creating IIS virtual directories and mapping them to the SQL Server database. When this virtual directory is accessed, a special IIS extension DLL processes the URL, uses OLE DB to connect to the mapped database and to run the query, and returns results to the client.

MSXML contains two classes that allow sending HTTP requests and retrieving data. XMLHTTP is designed to be used on the client side, while ServerXMLHTTP is designed to be used on the server-side (to send HTTP requests to another HTTP server). See http://www.perfectxml.com/MSXMLHTTP.asp for more details on this.

We can configure the IIS Virtual Directory for SQL Server, and then use XMLHTTP to send requests to this virtual directory, receive the XML data and save it locally.

The first step is to configure the IIS Virtual Directory for SQL Server. Click on Start | Programs | Microsoft SQL Server | Configure SQL XML Support in IIS and then right click on Default Web site and select New | Virtual Directory. Name the virtual directory as NWVirDir and specify the physical path; specify the login details under the Security tab; choose the Northwind database in the Data Source tab; Select "Allow sql=... or template... URL queries" and click OK.

Just to make sure the above configuration is correct and working, start Internet Explorer and type the following in the Address bar:


http://localhost/NWVirDir?sql=SELECT * FROM Customers FOR XML AUTO&root=Customers

If you see the Customers table data in the XML format, the SQL IIS virtual directory is configured properly. Let's now write client-side MSXML code to access the above URL.

Let's Start Visual Basic 6.0, create a new standard EXE project, add reference to MSXML 4.0, and write the following code:


Option Explicit

Const strURL = "http://localhost/NWVirDir?sql=SELECT * FROM Customers " & _
    " FOR XML AUTO&root=Customers"

Private Sub Form_Load()
    Dim objXH As New MSXML2.XMLHTTP40
    
    With objXH
        .open "GET", strURL, False
    
        .send
    
        If .Status = 200 Then
            .responseXML.save "c:\CustomersData.xml"
        End If
    End With
    Unload Me
End Sub

Save and run the above code and browse to c:\CustomersData.xml to see the relational data in the XML format.

The above code creates a XMLHTTP object and sends a GET request to the NWVirDir SQLXML Virtual Directory. The XMLHTTP responseXML property is of type DOMDocument and contains the received XML document loaded as a DOM tree. We can use this property as a DOMDocument object to modify it, transform it, save it and so on.

The above code is written as a VB application; it can be easily converted to VBScript and called from inside a DTS ActiveX Script Task or as an external .vbs script file.

Using ADO Command Dialect and the FOR XML Clause

The SQL Server 2000 OLE DB provider supports the extended ADO Command object, which allows running the SQLXML template queries and receiving the XML results as a stream. The stream output can be persisted or directly loaded into a MSXML DOMDocument object.

Let's write an ASP page that uses this ADO Command Dialect to run the SQLXML template query that contains the FOR XML clause. The ADO command execution result is loaded in a DOMDocument object, which is transformed to the HTML format using the XSLT stylesheet.

Here is the ASP code:


<%
Option Explicit

Const adExecuteStream = 1024

Dim objADOCmd
Dim objXMLDoc
Dim objXSLDoc

'Create objects
Set objADOCmd = Server.CreateObject("ADODB.Command")
Set objXMLDoc = Server.CreateObject("MSXML2.DOMDocument.4.0")
Set objXSLDoc = Server.CreateObject("MSXML2.DOMDocument.4.0")

'Initialize Command Object
objADOCmd.ActiveConnection = "PROVIDER=SQLOLEDB.1;SERVER=.;UID=sa;PWD=;DATABASE=Northwind;"
objADOCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"

'The template query
objADOCmd.CommandText = "<EmpPhoneDir xmlns:sql='urn:schemas-microsoft-com:xml-sql'>" & _
	"<sql:query>" & _ 
  		"SELECT TitleOfCourtesy + FirstName + ' ' + LastName AS EmpName, Extension " &_
		"FROM Employees " & _
		"ORDER BY FirstName " & _
		"FOR XML AUTO " & _
	"</sql:query>" & _
	"</EmpPhoneDir>"
	
'Output the XML stream into DOMDocument object
objADOCmd.Properties("Output Stream") = objXMLDoc

'Execute the command
objADOCmd.Execute , , adExecuteStream


'Load the stylesheet
objXSLDoc.async = False
objXSLDoc.validateOnParse = False
objXSLDoc.setProperty "ServerHTTPRequest", False
objXSLDoc.load Server.MapPath("EmpPhoneDir.xsl")

'Transform to generate HTML and stream it into Response stream
objXMLDoc.transformNodeToObject objXSLDoc, Response

%>

The above ASP code uses the new ADO Command dialect to execute the FOR XML query, saves the result XML stream into a DOMDocument object, on which we apply the following XSLT stylesheet (EmpPhoneDir.xsl):


<?xml version="1.0"?>
<xsl:stylesheet
  version="1.0"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

  <xsl:output method="html" />
  
  <xsl:template match="/">
    <html>
      <head>
        <title>Employee Directory</title>
      </head>
      <body>
        <table width="300" bgcolor="#DCDCDC" cellpadding="4" cellspacing="1" border="0">
          <tr>
            <td bgcolor="#000000" width="220">
              <font color="#FFFFFF"><b>Employee</b></font>
            </td>
            <td  bgcolor="#000000" width="80">
              <font color="#FFFFFF"><b>Extension</b></font>
            </td>
          </tr>

          <xsl:for-each select="//Employees">
            <tr>
              <td bgcolor="#EEEEEE" width="220">
                <xsl:value-of select="@EmpName" />
              </td>
              <td  bgcolor="#FFFFFF" width="80">
                <xsl:value-of select="@Extension" />
              </td>
            </tr>
          </xsl:for-each>
        </table>
      </body>
    </html>
  </xsl:template>
</xsl:stylesheet>

And here is what we see in the browser:



In this case, we are applying the XSLT stylesheet on the DOMDocument object created as result of the Command execution; we can do various other things like updating the document, querying, saving and so on.

Also note that the above ASP code was used just to illustrate the ADO Command Dialect and MSXML integration; you can get the same browser output by using the SQLXML virtual directory HTTP access and by passing the xsl=xslfilename URL parameter as illustrated below:


http://localhost/NWVirDir?sql=SELECT FirstName as EmpName, 
           Extension FROM Employees FOR XML AUTO&root=EmpPhoneDir&xsl=EmpPhoneDir.xsl

BCP and FOR XML Clause

The bcp command-line utility allows copying data between SQL Server 2000 and an external file. To this utility, we can pass the queryout parameter and a SQL query, to bulk copy data returned by a query into a file.

Let's first create a small stored procedure:


Use Northwind
GO

CREATE PROCEDURE sproc_GetShippers
AS
	SELECT *
	FROM Shippers
	FOR XML AUTO, ELEMENTS 

We'll call the above stored procedure from the bcp command-line:


bcp "EXEC Northwind..sproc_GetShippers" queryout c:\temp\bcpOut.xml -S. -Usa -P -c -r -t

Once you run the above command on the DOS command prompt, open c:\temp\bcpOut.xml in either notepad or Internet Explorer, and you'll see that it is missing the root node, and hence is not a well-formed XML. Here is a solution:

Start notepad and create a text file named c:\temp\header.txt:


<ShippersInfo>

Create another text file named c:\temp\footer.txt:


</ShippersInfo>

Now run the bcp command first, and then run the following command:


C:\temp>Copy header.txt+bcpOut.xml+footer.txt result.xml /B

We are essentially merging three files; the first file contains the begin element, second file is our XML output from the database, and the third file contains the end tag. Browse to c:\temp\result.xml, and you'll see a well-formed XML document.

FOR XML and Binary Data

The last example in this section illustrates exporting binary (image data type) data. As XML documents are text documents, binary data can be included after encoding it using hex OR base64 encoding. We can use ADO and MSXML to export binary data and save it into a XML document using hex/base64 encoding, see my article XML and Binary Data for an example of this.

In the following example, we'll use the BINARY BASE64 clause available with the SELECT..FOR XML AUTO statement to export the binary data.

Let's once again see an example of a DTS Package. Start SQL Server Enterprise Manager, right click on Data Transformation Services and select New Package. In the designer, create a new ActiveX Script Task, and write the following code:


'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Function Main()
  Dim objADOStream
  Dim objADOCmd

  Const adExecuteStream = 1024

  Set objADOCmd = CreateObject("ADODB.Command")

  Set objADOStream = CreateObject("ADODB.Stream")
  objADOStream.Type = 2 'Text
  objADOStream.Open

  objADOCmd.ActiveConnection = "PROVIDER=SQLOLEDB.1;SERVER=.;UID=sa;PWD=;DATABASE=Northwind;"
  objADOCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"

  objADOCmd.CommandText = "<EmpWithPhotos xmlns:sql='urn:schemas-microsoft-com:xml-sql'>" & _
    "<sql:query>" & _ 
        "SELECT EmployeeID, LastName, FirstName, Photo  FROM Employees FOR XML AUTO, BINARY BASE64 " & _
    "</sql:query>" & _
    "</EmpWithPhotos>"
  
  objADOCmd.Properties("Output Stream") = objADOStream

  objADOCmd.Execute , , adExecuteStream

  objADOStream.SaveToFile "c:\temp\EmpWithPhotos.xml"
   
  objADOStream.Close
  Set objADOStream = Nothing

  Main = DTSTaskExecResult_Success
End Function

Click OK, right click on the Sctipt Task and select Execute Step to run the above code. Open "c:\temp\EmpWithPhotos.xml" in Internet Explorer and you'll see that the binary data (Photo field) is saved as Base64 encoded text value.

The above VBScript code once again uses the ADO Command Dialect to run the template query that contains FOR XML AUTO, BINARY BASE64 clause. The result of this command execution is saved into a ADO stream which is then saved to a disk file named "c:\temp\EmpWithPhotos.xml". Make sure this file does not exist before you run this ActiveX Script Task; otherwise an error will be raised.

Related link: http://www.perfectxml.com/msxmlAnswers.asp?Row_ID=60

The .NET Way

The improved data model (ADO.NET) and the support for XML (System.Xml namespace) in the .NET Framework are worth exploring! With .NET there are many ways in which relational data can be exported as XML.

Let's write a Visual Basic .NET console application:

Start Visual Studio .NET, create a new Visual Basic Project of type Console Application, name the project as ExportSQLData and write the following code:


Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes

Module Module1

  Sub Main()
    Dim SQLDataset As New DataSet("Shippers")
    Dim DBConnection As New SqlConnection("SERVER=.;UID=sa;PWD=;DATABASE=Northwind;")

    DBConnection.Open()
    Dim DBAdapter As New SqlDataAdapter("Select * FROM Shippers", DBConnection)

    DBAdapter.Fill(SQLDataset)

    SQLDataset.WriteXml("c:\temp\DotNetShippers.xml", XmlWriteMode.WriteSchema)

    DBConnection.Close()
  End Sub

End Module

The above VB .NET code connects to the local SQL Server using the SqlConnection object, and then a SqlDataAdapter object is initialized with the SELECT SQL statement and a Connection object. This SqlDataAdapter object is used to fill the DataSet object. The DataSet is then used to write the SQL data as XML to the file using its WriteXml method. The second parameter (XmlWriteMode.WriteSchema)to the WriteXml method specifies that the output XML document should contain the XSD schema before the actual data.

There are many other techniques with relational data and XML in .NET, using the ADO.NET namespaces and the System.Xml namespace. In addition, SQLXML 3.0 adds introduces few new managed classes that further enhance the XML support in SQL Server 2000 and .NET. We recommend the books Essential ADO.NET and Professional XML for .NET Developers to learn more about ADO.NET and XML in .NET Framework.

Summary

It has become clear that XML is the best channel to transmit data from one end to the other. The platform-independent textual nature of XML makes it highly portable. Developers are using XML as the data format to transfer relational data from one database server (example: Oracle) to a totally different database server (example: SQL server 2000). Database vendors are continuously adding and enhancing the XML support in their products.

In this article, we explored some of the ways in which relational data can be turned into hierarchical XML format, either to be sent to the Web client or to be saved as a disk file. We saw various example written using Visual Basic applications, DTS packages, BCP, and so on. And towards the end, we saw a .NET example of exporting SQL data to an external XML file.

Email any questions or comments to the author of this article, Darshan Singh (Managing Editor, perfectxml.com).

  

Back to Articles Page      

  Contact Us | E-mail Us | Site Guide | About PerfectXML | Advertise ©2004 perfectxml.com. All rights reserved. | Privacy