How To Get XML Representation Of an ADO Recordset in Visual Basic (252767)


The information in this article applies to:

  • Microsoft Data Access Components 2.5
This article was previously published under Q252767

SUMMARY

When you want an XML representation of a recordset you can use the new ADODB.Stream object to retrieve a string with the XML. In ADO 2.5 it is not necessary to save on a disk using the save method to retrieve the XML string generated by the recordset.

MORE INFORMATION

To retrieve the XML for a recordset, use the following code. Be sure to have a reference to "ActiveX Data Objects 2.5 Library". The code assumes the recordset is open and populated. 
    Dim oStream As ADODB.Stream
    Set oStream = New ADODB.Stream
        
    oRecordset.Save oStream, adPersistXML
    
    Dim sXML As String
    sXML = oStream.ReadText
    
    oStream.Close
    Set oStream = Nothing

For example, you can use your sXML string to write it out in an ASP page, or to parse it with an XSL transformation.

The XML generated is similar to the following. This is a sample of the Authors table in the Pubs database in SQL Server:
<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='au_id' rs:number='1' rs:writeunknown='true'>
            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='11'
             rs:maybenull='false'/>
        </s:AttributeType>
        <s:AttributeType name='au_lname' rs:number='2' rs:writeunknown='true'>
            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='40'
             rs:maybenull='false'/>
        </s:AttributeType>
        <s:AttributeType name='au_fname' rs:number='3' rs:writeunknown='true'>
            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='20'
             rs:maybenull='false'/>
        </s:AttributeType>
        <s:AttributeType name='phone' rs:number='4' rs:writeunknown='true'>
            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='12'
             rs:fixedlength='true' rs:maybenull='false'/>
        </s:AttributeType>
        <s:AttributeType name='address' rs:number='5' rs:nullable='true'
             rs:writeunknown='true'>
            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='40'/>
        </s:AttributeType>
        <s:AttributeType name='city' rs:number='6' rs:nullable='true'
             rs:writeunknown='true'>
            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='20'/>
        </s:AttributeType>
        <s:AttributeType name='state' rs:number='7' rs:nullable='true'
             rs:writeunknown='true'>
            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='2'
             rs:fixedlength='true'/>
        </s:AttributeType>
        <s:AttributeType name='zip' rs:number='8' rs:nullable='true'
             rs:writeunknown='true'>
            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='5'
             rs:fixedlength='true'/>
        </s:AttributeType>
        <s:AttributeType name='contract' rs:number='9' 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 au_id='172-32-1176' au_lname='White' au_fname='Johnson'
         phone='408 496-7223' address='10932 Bigge Rd.' city='Menlo Park'
         state='CA' zip='94025' contract='True'/>
    <z:row au_id='213-46-8915' au_lname='Green' au_fname='Marjorie'
         phone='415 986-7020' address='309 63rd St. #411' city='Oakland'
         state='CA' zip='94618' contract='True'/>
    <z:row au_id='238-95-7766' au_lname='Carson' au_fname='Cheryl'
         phone='415 548-7723' address='589 Darwin Ln.' city='Berkeley'
         state='CA' zip='94705' contract='True'/>
    <z:row au_id='267-41-2394' au_lname='O&#x27;Leary' au_fname='Michael'
         phone='408 286-2428' address='22 Cleveland Av. #14' city='San Jose'
         state='CA' zip='95128' contract='True'/>
    <z:row au_id='274-80-9391' au_lname='Straight' au_fname='Dean'
         phone='415 834-2919' address='5420 College Av.' city='Oakland'
         state='CA' zip='94609' contract='True'/>
    <z:row au_id='341-22-1782' au_lname='Smith' au_fname='Meander'
         phone='913 843-0462' address='10 Mississippi Dr.' city='Lawrence'
         state='KS' zip='66044' contract='False'/>
    <z:row au_id='409-56-7008' au_lname='Bennet' au_fname='Abraham'
         phone='415 658-9932' address='6223 Bateman St.' city='Berkeley'
         state='CA' zip='94705' contract='True'/>
    <z:row au_id='427-17-2319' au_lname='Dull' au_fname='Ann'
         phone='415 836-7128' address='3410 Blonde St.' city='Palo Alto'
         state='CA' zip='94301' contract='True'/>
    <z:row au_id='472-27-2349' au_lname='Gringlesby' au_fname='Burt'
         phone='707 938-6445' address='PO Box 792' city='Covelo'
         state='CA' zip='95428' contract='True'/>
    <z:row au_id='486-29-1786' au_lname='Locksley' au_fname='Charlene'
         phone='415 585-4620' address='18 Broadway Av.' city='San Francisco'
         state='CA' zip='94130' contract='True'/>
    <z:row au_id='527-72-3246' au_lname='Greene' au_fname='Morningstar'
         phone='615 297-2723' address='22 Graybar House Rd.' city='Nashville'
         state='TN' zip='37215' contract='False'/>
    <z:row au_id='648-92-1872' au_lname='Blotchet-Halls'
         au_fname='Reginald' phone='503 745-6402' address='55 Hillsdale Bl.'
         city='Corvallis' state='OR' zip='97330' contract='True'/>
    <z:row au_id='672-71-3249' au_lname='Yokomoto' au_fname='Akiko'
         phone='415 935-4228' address='3 Silver Ct.' city='Walnut Creek'
         state='CA' zip='94595' contract='True'/>
    <z:row au_id='712-45-1867' au_lname='del Castillo' au_fname='Innes'
         phone='615 996-8275' address='2286 Cram Pl. #86' city='Ann Arbor'
         state='MI' zip='48105' contract='True'/>
    <z:row au_id='722-51-5454' au_lname='DeFrance' au_fname='Michel'
         phone='219 547-9982' address='3 Balding Pl.' city='Gary'
         state='IN' zip='46403' contract='True'/>
    <z:row au_id='724-08-9931' au_lname='Stringer' au_fname='Dirk'
         phone='415 843-2991' address='5420 Telegraph Av.' city='Oakland'
         state='CA' zip='94609' contract='False'/>
    <z:row au_id='724-80-9391' au_lname='MacFeather' au_fname='Stearns'
         phone='415 354-7128' address='44 Upland Hts.' city='Oakland'
         state='CA' zip='94612' contract='True'/>
    <z:row au_id='756-30-7391' au_lname='Karsen' au_fname='Livia'
         phone='415 534-9219' address='5720 McAuley St.' city='Oakland'
         state='CA' zip='94609' contract='True'/>
    <z:row au_id='807-91-6654' au_lname='Panteley' au_fname='Sylvia'
         phone='301 946-8853' address='1956 Arlington Pl.' city='Rockville'
         state='MD' zip='20853' contract='True'/>
    <z:row au_id='846-92-7186' au_lname='Hunter' au_fname='Sheryl'
         phone='415 836-7128' address='3410 Blonde St.' city='Palo Alto'
         state='CA' zip='94301' contract='True'/>
    <z:row au_id='893-72-1158' au_lname='McBadden' au_fname='Heather'
         phone='707 448-4982' address='301 Putnam' city='Vacaville'
         state='CA' zip='95688' contract='False'/>
    <z:row au_id='899-46-2035' au_lname='Ringer' au_fname='Anne'
         phone='801 826-0752' address='67 Seventh Av.' city='Salt Lake City'
         state='UT' zip='84152' contract='True'/>
    <z:row au_id='998-72-3567' au_lname='Ringer' au_fname='Albert'
         phone='801 826-0752' address='67 Seventh Av.' city='Salt Lake City'
         state='UT' zip='84152' contract='True'/>
</rs:data>
</xml>
Check the references below for more detailed information.

REFERENCES

More usage scenarios can be found at MSDN information on Streams and persistence, from the Records And Streams documentation.

If you are interested in XML check the MSDN XML Developer Center for in-depth and beginner-to-advanced information.
Take look at the http://www.biztalk.org/ site for more information on the BizTalk initiative.

Also check http://msdn.microsoft.com/dataaccess for more information regarding MDAC and ADO.
Modification Type:MinorLast Reviewed:7/1/2004
Keywords:kbhowto kbMSXMLnosweep KB252767
©2004 Microsoft Corporation. All rights reserved.