MS SQL XML - translate xml into select statement

Author

translate xml into select statement

deja@2bytes.co.uk

2006-11-28, 7:23 pm

we have a system that has data relating to the same business objects in
different formats/databases. So for example, we might have Customers
with their Orders in a SQL Server database and also some information
about Customers and their Orders in a separate Oracle database (legacy
system - two companies merged together).

Anyway, the physical storage of this data may be different in each
database (maybe Order_Date in SQL Server but OrderDt in Oracle - that
kind of thing. Maybe CustomerLocation is in a separate table in SQL
Server whereas in Oracle it is in the same table).

The requirement is to be able to create a generic query that can then
be run into each database as necessary and pull out the required info.
The current proposal is for a standard XML Format that indicates the
Business Entities, the joins of the Business Entities and the filters.
It is proposed that this is sent to a database stored procedure which
will then interpret this XML and return the correct results (back in a
business standard XML).

I think it would be better to:
a) translate the standard XML query to a database specific query using
XSL (because it is easier to maintain AND provides better scaleability
- don't really want the database doing a whole load of
parsing/interpreting to create a dynamic sql statement - especially
since it is an OLTP system and that would impact other users)

b) not sure whether to use the XSL to translate to standard SQL for XML
query or to translate to a mapping-schema which can then be posted to
the virtual directory etc (can it use an in-memory schema rather than
one in the virtual directory, otherwise it means I have to check for a
unique filename first) - any thoughts?

just looking for some suggestions/improvements/confirmation on these
ideas.

thanks
Phil

Mike C#

2006-12-10, 5:15 am

<deja@2bytes.co.uk> wrote in message
news:1164713070.621350.239430@80g2000cwy.googlegroups.com...
<snip>

> The requirement is to be able to create a generic query that can then
> be run into each database as necessary and pull out the required info.
> The current proposal is for a standard XML Format that indicates the
> Business Entities, the joins of the Business Entities and the filters.
> It is proposed that this is sent to a database stored procedure which
> will then interpret this XML and return the correct results (back in a
> business standard XML).

Create Views in one database that reflect the same structure as the other
database. For instance, a view that aliases the OrderDt column in Oracle as
Order_Date. Would appear to be a bit easier, and possibly a lot more
efficient, than passing structural metadata to all your SP's via XML (if SQL
Server 2000 I would strongly recommend looking at the limitations before
trying to pass in and parse XML in your stored proc).

> I think it would be better to:
> a) translate the standard XML query to a database specific query using
> XSL (because it is easier to maintain AND provides better scaleability
> - don't really want the database doing a whole load of
> parsing/interpreting to create a dynamic sql statement - especially
> since it is an OLTP system and that would impact other users)

If you really want to generate all your SQL dynamically, you're probably
much better off doing it using XSL on the client-side than trying to do it
client-side with SQL 2000. I have no idea about Oracle. However, if you
don't properly parameterize your queries you'll lose a lot of the
performance benefit of execution plan caching and reuse on SQL Server.
Something to keep in mind if you run the same query many times in a short
time period, and performance factors into your considerations.

> b) not sure whether to use the XSL to translate to standard SQL for XML
> query or to translate to a mapping-schema which can then be posted to
> the virtual directory etc (can it use an in-memory schema rather than
> one in the virtual directory, otherwise it means I have to check for a
> unique filename first) - any thoughts?

Mapping-schema? How about views?

Sponsored Links