Hi,
I create a Dataset to link three tables in my Access
database. E.g. The three tables are [Order], [Customer],
[Product]. I then create 2 DataRelations, 1st relation
(Order_Cust_Rel) links [Order] and [Customer] using
CustomerID field that exists in both tables and 2nd
relation (Order_Product_Rel) links [Order] and [Product]
using ProductID field. I set the nested property of the
relations to True so that the output will be nested. I
then use WriteXML to output the dataset to a StreamWriter.
Please see the code below:
Imports System.Data
Imports System.Data.OleDb
Imports System.Xml
Imports System.IO
Private Sub WriteXMLToFile()
Dim conn As OleDbConnection = New OleDbConnection
("Provider=Microsoft.Jet.OLEDB.4.0; Data
source=C:\Sales.mdb")
Dim OrderDA As OleDbDataAdapter = New OleDbDataAdapter
("SELECT * FROM [Order]", conn)
Dim CustomerDA As OleDbDataAdapter = New
OleDbDataAdapter("SELECT * FROM [Customer]", conn)
Dim ProductDA As OleDbDataAdapter = New
OleDbDataAdapter("SELECT * FROM [Product]", conn)
Dim DS As DataSet = New DataSet("SalesOrder")
OrderDA.Fill(DS, "Order")
CustomerDA.Fill(DS, "Customer")
ProductDA.Fill(DS, "Product")
Dim Order_Cust_Rel As DataRelation =
DS.Relations.Add("OrderCust", DS.Tables("Order").Columns
("CustomerID"), DS.Tables("Customer").Columns
("CustomerID"))
Order_Cust_Rel.Nested = True
Dim Order_Product_Rel As DataRelation =
DS.Relations.Add("OrderProduct", DS.Tables
("Order").Columns("ProductID"), DS.Tables
("Product").Columns("ProductID"))
Order_Product_Rel.Nested = True
Dim filename As String = "c:\XmlFile.xml"
Dim xmlSW As StreamWriter = New StreamWriter
(filename, True)
DS.WriteXml(xmlSW)
End Sub
By default, the three tables data will be output one
after another, that is [Order], follow by [Customer],
follow by [Product]. As below:
<SalesOrder>
<Order>
<OrderID>1001</OrderID>
<OrderDate>2003-11-10</OrderDate>
<CustomerID>10250</CustomerID>
<ProductID>SE-071-001</ProductID>
<Qty>100</Qty>
<DeliverDate>2003-12-15</DeliverDate>
<Customer>
<CustomerID>10250</CustomerID>
<Name>CreaInnov Inc.</Name>
</Customer>
<Product>
<ProductID>SE-071-001</ProductID>
<ProductName>Secure Email Gateway</ProductName>
<UnitPrice>2700</UnitPrice>
</Product>
</Order>
</SalesOrder>
However, I need the [Customer] and [Product] tables to
appear in the middle of [Order] table. I.e. When [Order]
table links with [Customer] table using CustomerID, I
need the Customer table fields appear right after the
CustomerID field, and so for Product table. I also need
to hide those linking fields, i.e. CustomerID,
ProductID . I have to follow this format as this is
required by the vendor.
<SalesOrder>
<Order>
<OrderID>1001</OrderID>
<OrderDate>2003-11-10</OrderDate>
<Customer>
<Name>CreaInnov Inc.</Name>
</Customer>
<Product>
<ProductName>Secure Email Gateway</ProductName>
<UnitPrice>2700</UnitPrice>
</Product>
<Qty>100</Qty>
<DeliverDate>2003-12-15</DeliverDate>
</Order>
</SalesOrder>
Can anyone let me know how to achieve this? Do I use XSD
schema or Mapping machanism to do this, or ?? Please
help.. thanks.
