Exporting SQL Data as XML
| Author: |
-- Managing Editor, perfectxml.com |
Using ADO's XML Persistence FeatureADO 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 SampleStart 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 SampleLet'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 DOMThe 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:
- HOWTO: Bind to XML Data with Internet Explorer (Q258295)
- adPersistXML related KB Articles
ADO and DOMIn 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 SAXMSXML 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 https://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 FeaturesSQL 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 https://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 ProcedureSQL 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).
And then run the SELECT...FOR XML query; you'll see that the XML returned is somewhat formatted, and better readable.
Using MSXML XMLHTTP ComponentAs 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 https://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 ClauseThe 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 ClauseThe 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:
Create another text file named c:\temp\footer.txt:
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 DataThe 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: https://perfectxml.com/msxmlAnswers.asp?Row_ID=60
The .NET WayThe 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.
SummaryIt 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, (Managing Editor, perfectxml.com).
|
Back to Articles Page
|
