XML —

Outputting Data as XML Using ADO 2.5

In this example, we will retrieve data from the SQL Server 7.0 Northwind Traders database and save the data as XML in a text file. We'll use ADO 2.5 in a Visual Basic application to perform this task. To create the example application, follow these steps:

  1. Open Visual Basic, create a standard EXE application, and change the name of the default form to frmADOXML.
  2. Choose Reference from the Project menu, and add a reference to Microsoft ActiveX Data Objects 2.5 Library.
  3. Add a command button called cmdSave to the form with a caption Save.
  4. Add the following code to the click event handler of the command button cmdSave:

  5.   Private Sub cmdSave_Click()
      Dim objNWRecordset As ADODB.Recordset
      Dim objNWConnection As ADODB.Connection
      Set objNWRecordset = New ADODB.Recordset
      Set objNWConnection = New ADODB.Connection
      objNWConnection.CursorLocation = adUseClient
      'You will need to replace IES-FUJI with the appropriate data
      'source in the following statement.
      objNWConnection.Open "Provider=SQLOLEDB.1; " & _
         "Integrated Security=SSPI;Persist Security Info=False;" & _
         "User ID=sa;Initial Catalog=Northwind;"
         "Data Source=IES-FUJI"
      objNWRecordset.CursorLocation = adUseClient
      objNWRecordset.CursorType = adOpenStatic
      Set objNWRecordset.ActiveConnection = objNWConnection
      objNWRecordset.Open "Products"
      'Save the recordset to a file as XML.
      objNWRecordset.Save "C:\Products.xml", adPersistXML
  End Sub

This code initially creates an ADO Connection object called objNWConnection and a Recordset object called objNWRecordset, and then sets the properties for these objects and opens them. A Connection object provides a connection to any data source. A Recordset object is a virtual table in memory that contains the data that is retrieved from a data source. The CursorLocation property of the Recordset object determines whether the data will be located on the client or on the server. The CursorLocation property also determines whether the connection must be maintained with the database (server cursor) or the connection can be broken (client cursor) while creating a disconnected recordset. The Open method of the ADO Connection object contains the connection string as a parameter. The connection string includes the catalog, which is the database that is going to be used, the data source, which is the name of the SQL Server, and the user ID, which is a valid user name to use when opening the connection. This connection string is connecting to a SQL Server database. You will have to change the name of the data source to the name of your SQL Server database that contains the Northwind Traders database.

The ADO Connection object connects to the Northwind Traders database, and the Recordset object connects to the Products table of the Northwind Traders database. Once this is done, the Save method of the Recordset object is called to save the data as XML.

As you can see, the Save method uses the adPersistXML parameter to save the data as XML. The XML file that is created will have two main sections. The first section contains a BizTalk schema for the data, and the second section contains the actual data. There are four namespace prefixes that are used in the file. The first namespace prefix is s, which is used to prefix the schema definition for the data. The second namespace prefix is dt, which is used for the datatype definitions in the schema. The third namespace prefix is rs, which references the properties and methods of the ADO recordset. The fourth namespace prefix is z, which references the actual data. The Products.xml XML file that was generated from the above code looks as follows:

  <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'>
        <s:AttributeType name='ProductID' rs:number='1'>
           <s:datatype dt:type='int' dt:maxLength='4'
              rs:precision='10' rs:fixedlength='true'
              rs:maybenull='false'/>
        </s:AttributeType>
        <s:AttributeType name='ProductName' rs:number='2'
           s:writeunknown='true'>
           <s:datatype dt:type='string' dt:maxLength='40'
              rs:maybenull='false'/>
        </s:AttributeType>
        <s:AttributeType name='SupplierID' rs:number='3'
           rs:nullable='true' rs:writeunknown='true'>
           <s:datatype dt:type='int' dt:maxLength='4' rs:precision='10'
              rs:fixedlength='true'/>
        </s:AttributeType>
        <s:AttributeType name='CategoryID' rs:number='4'
           rs:nullable='true' rs:writeunknown='true'>
           <s:datatype dt:type='int' dt:maxLength='4' rs:precision='10'
              rs:fixedlength='true'/>
        </s:AttributeType>
        <s:AttributeType name='QuantityPerUnit' rs:number='5'
           rs:nullable='true' rs:writeunknown='true'>
           <s:datatype dt:type='string' dt:maxLength='20'/>
        </s:AttributeType>
        <s:AttributeType name='UnitPrice' rs:number='6'
           rs:nullable='true' rs:writeunknown='true'>
           <s:datatype dt:type='i8' rs:dbtype='currency'
              dt:maxLength='8' rs:precision='19'
              rs:fixedlength='true'/>
        </s:AttributeType>
        <s:AttributeType name='UnitsInStock' rs:number='7'
           rs:nullable='true' rs:writeunknown='true'>
           <s:datatype dt:type='i2' dt:maxLength='2' rs:precision='5'
              rs:fixedlength='true'/>
        </s:AttributeType>
        <s:AttributeType name='UnitsOnOrder' rs:number='8'
           rs:nullable='true' rs:writeunknown='true'>
           <s:datatype dt:type='i2' dt:maxLength='2' rs:precision='5'
              rs:fixedlength='true'/>
        </s:AttributeType>
        <s:AttributeType name='ReorderLevel' rs:number='9'
           rs:nullable='true' rs:writeunknown='true'>
           <s:datatype dt:type='i2' dt:maxLength='2'
              rs:precision='5' rs:fixedlength='true'/>
        </s:AttributeType>
        <s:AttributeType name='Discontinued' rs:number='10'
           rs:writeunknown='true'>
           <s:datatype dt:type='boolean' dt:maxLength='2'
              rs:fixedlength='true' rs:maybenull='false'/>
        </s:AttributeType>
        <s:extends type='rs:rowbase'/>
     </s:ElementType>
  </s:Schema>
  <rs:data>
  <z:row ProductID='1' ProductName='Chai' SupplierID='1'
     CategoryID='1' QuantityPerUnit='10 boxes x 20 bags'
     UnitPrice='18' UnitsInStock='39' UnitsOnOrder='0'
     ReorderLevel='10' Discontinued='False'/>
  <z:row ProductID='2' ProductName='Chang' SupplierID='1'
     CategoryID='1' QuantityPerUnit='24 - 12 oz bottles'
     UnitPrice='19' UnitsInStock='17' UnitsOnOrder='40'
     ReorderLevel='25' Discontinued='False'/>
  
  </rs:data>
  </xml>

Using ADO 2.5, we have created an XML document that contains the schema and the data for the Products table of the Northwind Traders database.

NOTE
For more information about ADO 2.5, look at the Microsoft Data Access Components (MDAC) 2.5 SDK on Microsoft's Web site.

In some cases, you will have to make changes to the generated data before it can be used as XML. For example, if there is an invalid character in the column name, such as a space, you will have to change the name to a valid XML name. You would do this by changing the name attribute and adding an rs:name attribute that contains the original name of the field. Thus, if you had a column in the database called Shipper Name you could end up with the following AttributeType:

  <s:AttributeType name='Shipper Name' rs:number='9'
     rs:nullable='true' rs:writeunknown='true'>
     <s:datatype dt:type='i2' dt:maxLength='2' rs:precision='50'
        rs:fixedlength='true'/>
  </s:AttributeType>

You would have to change the AttributeType as follows:

  <s:AttributeType name='ShipperName' rs:name='Shipper Name'
     rs:number='9' rs:nullable='true' rs:writeunknown='true'>
     <s:datatype dt:type='i2' dt:maxLength='2' rs:precision='50'
        rs:fixedlength='true'/>
  </s:AttributeType>

This document can then be presented in Internet Explorer 5 as XML or transformed to XHTML or other formats using an XSL document.

The original file that is generated can be used only to create a read-only ADO recordset. If you want to create an updatable client-side disconnected recordset, you must add an rs:updatable attribute to the ElementType definition. A client-side disconnected recordset has no connection to the original data source. A user can review, edit, delete, update, and add records to the recordset, but a connection to the database must be reestablished in order for the changes to be saved to the database. To save the changes, the UpdateBatch method of an ADO recordset must be called after the disconnected recordset is reconnected to the database. The UpdateBatch method is used to send multiple recordset updates to the server in one call.

To make the data updatable, you would change the ElementType definition to the following:

  <s:ElementType name='row' content='eltOnly' rs:updatable='true'>