Combining multiple ADO recordsets into hierarchical XML

[Mark Davis]

Greetings:

I have from 1 to x ADO recordsets returned from calls to stored procedures.
The recordsets are built in a totally data driven system and records from
each recordset are related by keys in the data. The backend is Sybase 12.0
so I can't use any Microsoft SQL Server syntax for retrieving a hierarchical
recordset created in XML format. Sybase does not support the SHAPE & APPEND
T-SQL statements. I can persist the individual recordsets to XML but not
in a hierarchical format. I can build an XML file dynamically with code but
I would prefer being able to use

rs.save filename/datastream, adPersistXML

so the XML schema is automatically created and inserted into the XML document.

Anyone have any ideas short of building an XML file dynamically and reading
is back into a single ADO recordset control then save it again to get the
schema?

Thanks

Mark

[Jimmy Jack]

Just to clarify, it is the Data Shaping Provider that does all of the work
to create the hierarchical recordsets, not the underlying data provider.
SHAPE and APPEND are not T-SQL statements, they are Data Shaping Provider
clauses that are independent of the underlying data provider. You should be
able to use the Data Shaping Provider with Sybase because the SQL statements
in the SHAPE and APPEND clauses are not Microsoft SQL Server specific.

So you can just as easily run the following statement against SQL Server,
Sybase, MS Access, ODBC or Oracle as long as you supply the appropriate ADO
connection string (for example, Provider=MSDataShape;Data
Provider=MSDAORA;Data Source=serverName;User ID=userName;
Password=userPassword; ... for an Oracle implementation):

SHAPE {SELECT * FROM Customers WHERE Balance > 0}
APPEND
({SELECT * FROM Orders} RELATE CustID TO CustID) AS Orders

You can then use the rs.save filename/stream, adPersistXML to persist the
recordset.

If you send a SQL command to the Data Shaping Provider without the SHAPE
clause, it will simply pass the SQL command to the data provider without
creating a hierarchical recordset.

Hope this helps

JJ

"Mark Davis" <noemail@noemail.com> wrote in message
news:3ad67fcc$1@news.devx.com...
>
> Greetings:
>
> I have from 1 to x ADO recordsets returned from calls to stored
procedures.
> The recordsets are built in a totally data driven system and records from
> each recordset are related by keys in the data. The backend is Sybase 12.0
> so I can't use any Microsoft SQL Server syntax for retrieving a
hierarchical
> recordset created in XML format. Sybase does not support the SHAPE &
APPEND
> T-SQL statements. I can persist the individual recordsets to XML but not
> in a hierarchical format. I can build an XML file dynamically with code
but
> I would prefer being able to use
>
> rs.save filename/datastream, adPersistXML
>
> so the XML schema is automatically created and inserted into the XML
document.
>
> Anyone have any ideas short of building an XML file dynamically and
reading
> is back into a single ADO recordset control then save it again to get the
> schema?
>
> Thanks
>
> Mark
>