0

I have xml files that describe data I want to retrieve out of an MS SQL database. e.g.

<Customer Type="Entity" ID="12345">
  <Territory Type="Entity" Table="Territory" Join="Territory.TerritoryID = Customer.TerritoryID"></Territory>  
  <Addresses Type="Collection" Table="Address" Join="Address.AddressID = Customer.AddressID"></Addresses>
  <Orders Type="Collection" Table="Order" Join="Order.CustomerID = Customer.CustomerID">
    <BillToAdress Type="Entity" Table="Address" Join="Address.AddressID = Order.BillToAddressID"></BillToAdress>
    <ShipToAddress Type="Entity" Table="Address" Join="Address.AddressID = Order.ShipToAddressID"></ShipToAddress>
    <OrderLines Type="Collection" Table="OrderLine" Join="OrderLine.OrderID = Order.OrderID">
      <Product Type="Entity" Table="Product" Join="Product.ProductID = OrderLine.ProductID">
        <ProductParts Type="Collection" Table="ProductPart" Join="ProductPart.ProductID = Product.ProductID"></ProductParts>
      </Product>
    </OrderLines>
  </Orders>
</Customer>

Importantly, the only ID contained in the xml is in the root node (CustomerID). All other data would be retrieved from joins to related tables, the relationships of which are defined in the xml.

I am looking for a tool that takes in xml in similar format and can output a single SQL script to retrieve all data below in one hit.

All the ORM tools I know of issue queries sequentially, so e.g. results from the Customer table need to be returned to the application before the Territory query is run (using the TerritoryID returned from the Customer query).

Does anyone know of such a tool/library? Thanks

Customer

CustomerID Name TerritoryID
12345 Acme Ltd 67

Territory

TerritoryID Name
67 California

Address

AddressID CustomerID Address1 Address2 City Zip
890123 12345 12 Some Street Beverly Hills 90210
890124 12345 23 Some Road Inglewood 90310
890125 12345 34a Another Place Santa Monica 90410

Order

OrderID BillToAddressID ShipToAddressID ProductID Quantity Price
6789012 890123 890124 9876 50 200
6780123 890123 890125 7654 8 990

OrderLine

OrderLineID OrderID ProductID Quantity Price
98765432 6789012 9876 50 90
98765433 6789012 9890 20 120
98789012 6780123 9890 8 990

Product

ProductID Description Unit
9876 Widget Sml Each
9890 Widget Lge Each

ProductPart

ProductPartID ProductID Description
1234567 9890 Gadget A
1234568 9890 Fidget B

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.