| Editors Note: This can be accomplished a lot easier with the Import-Export tool or use the SDK (API) available for download from the Developers Center.
One of the key strengths of any application is its ability to share data with other applications. A technology that is getting a lot hype hype is XML (SOAP). If you're a developer, you can't turn a page in any magazine without hearing something about XML, SOAP and Microsoft's .NET strategy.
Exporting XML through Visual Basic code.
- Step 1: For additional functionality,
 download the latest Microsoft XML Parser (MSXML) 
- Step 2: The idea here is to pull data from a data source, and save it to XML, so it can be easily imported into other applications that do support XML. Its uses the latest MDAC SDK which contains functionality for saving an ADO recordset to an XML document. From within Visual Basic, make sure you have references to "Microsoft Active X Data Objects 2.5" and the "Microsoft XML 2.0" (or greater) type library. Here is the code you need to generate an XML file:
Visual Basic code to save to an XML file begins here:
declare some variables for ADO
Dim rs As ADODB.Recordset, conn As ADODB.Connection
open a connection to SQL Server, specifying the server, and the default database
Set conn = New ADODB.Connection
conn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;Initial Catalog=DOVICO00;Data Source=SERVER", "sa", ""
create a new recordset, and open it with the data you wish to save into XML
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM TRANS", conn, adOpenForwardOnly, adLockReadOnly, adCmdText
rs.Save "c:\test.xml", adPersistXML
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing ' End Visual Basic code Instead of saving the data to an XML file, you may wish to load the XML straight into the MSXML parser for programmatic manipulation. To do so, you need to do the following steps:
- a) Declare a MSXML.DOMDocument object. (Dim msxmlDOM As New MSXML.DOMDocument)
- b) When it comes time to save the recordset, persist it to the MSXML.DOMDocument object instead of a file. (rs.Save xDOM, adPersistXML)
- c) Now you can use the MSXML parser to manipulate the XML data any way you need.
Here is a Visual Basic project that makes use of this example (ZIP)
Happy Coding,
DOVICO
Software Developer
|
