Well, I've now saved a sample ADO recordset in ADO recordset XML format and
opened that .XML file with Excel. Because there was no XML schema associated
with the .XML file, Excel created a schema and opened a new workbook.
The ADO recordset .XML looked like this (sorry about what line wrap may do)
<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'
rs:UniqueTable='tblActiveSafeObservers' rs:CustomResync='EXEC
sp_resyncexecutesql N'SELECT * FROM dbo.fn_cml_CardRateByAreaMonth ( 2009,
8);' , NULL, ? '
rs:ReshapeName='DSRowset1'>
<s:AttributeType name='EntryYear' rs:number='1' rs:writeunknown='true'>
<s:datatype dt:type='int' dt:maxLength='4' rs
recision='10'
rs:fixedlength='true' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='EntryMonth' rs:number='2' rs:writeunknown='true'>
<s:datatype dt:type='int' dt:maxLength='4' rs
recision='10'
rs:fixedlength='true' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='AreaName' 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='ObsNum' rs:number='4' rs:writeunknown='true'>
<s:datatype dt:type='int' dt:maxLength='4' rs
recision='10'
rs:fixedlength='true' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='CardCount' rs:number='5' rs:nullable='true'
rs:writeunknown='true'>
<s:datatype dt:type='int' dt:maxLength='4' rs
recision='10'
rs:fixedlength='true'/>
</s:AttributeType>
<s:AttributeType name='CardRate' rs:number='6' rs:nullable='true'>
<s:datatype dt:type='float' dt:maxLength='8' rs
recision='15'
rs:fixedlength='true'/>
</s:AttributeType>
<s:extends type='rs:rowbase'/>
</s:ElementType>
</s:Schema>
<rs:data>
<z:row EntryYear='2009' EntryMonth='8' AreaName='CAS' ObsNum='19'
CardCount='7' CardRate='0.37'/>
<z:row EntryYear='2009' EntryMonth='8' AreaName='MH' ObsNum='26'
CardCount='1' CardRate='4.0000000000000001E-2'/>
<z:row EntryYear='2009' EntryMonth='8' AreaName='TXOL' ObsNum='12'/>
</rs:data>
</xml>
What appeared in Excel isn't easy to reproduce here, but it had the
following 24 column headings:
id
name
content
ns3:CommandTimeout
ns3:UniqueTable
ns3:CustomResync
ns3:ReshapeName
name2
ns3:number
ns3:writeunknown
ns3:nullable
ns1:type
ns1:maxLength
ns3
recision
ns3:fixedlength
ns3:maybenull
ns3:dbtype
type
EntryYear
EntryMonth
AreaName
ObsNum
CardCount
CardRate
Where the first 18 had what appears to be information/metadata about what
the actual values - which showed up in the last 6 columns.
The first 18 columns had entries in the first 6 rows (corresponding to the
number of actual data columns) and data in the last 6 rows. The last 6
columns had no data / metadata in the first 6 rows, but had values in last 4
rows, which corresponds to the four records that the ADO recordset had in it.
Now - still being totally out of my depth, is there any way to edit the
schema that opening the .XML file in Excel created so that the extra metadata
is used (to format columns and whatever) but not actually written to the
cells?
That is, what I'm looking for is a nice 6 column by 5 rows (one row
headings, 4 rows data) import that echos what was in the recordset.
Any of that help, or just confuse the issue more?
James