Combining XML feed and SQL into one dataset

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

Hi all,

I apologise if this is not the correct group.

Using C#;

I have an XML feed and a local SQL query.

I need to put them both into a dataset and set a
relationship, so that when it goes out into a datagrid, I
have the combined rows where there is a match.

When I fill a dataset from a dataAdapter, I can create a
table name, a-la...

sqlDa.fill(DataSetName, "DataSetTableName");

When I get my XML, I plonk it direct into a dataset, not
being given the chance to create a table name.

DataSetName.ReadXML(XMLFromStream);

I need to have both table names to create a relationship
between them.

It may be just simple and I am missing something. If so,
can anyone help?

Seasons Greetings,
Dave Colliver.
http://www.BlackpoolFOCUS.com
 
Here is what I would do (although there may be a shortcut to refine this
process).

1. Load the XML into a different DataSet
2. Create a table in the DataSet in question and use CopyTo() to copy rows
to the table you want to relate
3. Relate the tables

This gives you a lot of control, but adds a bit of wieght, so here goes ...

The other option I can see offhand is to load the XML table and determine
table name by querying the table name by ordinal position. The first method
will be inefficient if you are using a rather large XML data set. If only a
few thousand rows, or less, you are fine.

---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************
 
Thanks Gregory.

I sort of have it working, after I thought about it some
more (I did do a load of googling to find an answer
before I asked the question...) I thought that maybe if I
just plop both sets of data into the dataset, then I
should be able to get the table by index number.

hence...
DataRelation XML2SQLRelation = new DataRelation
("MyRelation", SchoolData.Tables[0].Columns["DFES"],
SchoolData.Tables["SchoolsList"].Columns["DFES Number"],
true);

This sort of works...

This has led me to another problem though...

Neither table suits being parent or child. I have had to
put the constraint to false (or was it true, I can't
remember now) in order to get teh page to at least come
up.

My XML table has a list of numbers. My SchoolsList table
has some data with matching numbers, though it may have
some data without matching numbers.

e.g.

XML
111
112
113
115
117

SchoolsList
111
112
118
119

I need to drop the XML data and the SchoolsList data
where there are no match between them, hence in this
case, leaving behind 111 and 112.

The only way I can think of is to me, wasteful and time
consuming. That is to create a loop with the XML data and
feed this in an "IN" statement into SQL Query.

Is there a prettier and faster way to do it.

Thanks.

Dave Colliver.
http://www.BournemouthFOCUS.com
 
Thanks Gregory.

I sort of have it working, after I thought about it some
more (I did do a load of googling to find an answer
before I asked the question...) I thought that maybe if I
just plop both sets of data into the dataset, then I
should be able to get the table by index number.

hence...
DataRelation XML2SQLRelation = new DataRelation
("MyRelation", SchoolData.Tables[0].Columns["DFES"],
SchoolData.Tables["SchoolsList"].Columns["DFES Number"],
true);

This sort of works...

This has led me to another problem though...

Neither table suits being parent or child. I have had to
put the constraint to false (or was it true, I can't
remember now) in order to get teh page to at least come
up.

My XML table has a list of numbers. My SchoolsList table
has some data with matching numbers, though it may have
some data without matching numbers.

e.g.

XML
111
112
113
115
117

SchoolsList
111
112
118
119

I need to drop the XML data and the SchoolsList data
where there are no match between them, hence in this
case, leaving behind 111 and 112.

The only way I can think of is to me, wasteful and time
consuming. That is to create a loop with the XML data and
feed this in an "IN" statement into SQL Query.

Is there a prettier and faster way to do it.

Thanks.

Dave Colliver.
http://www.BournemouthFOCUS.com
 
Back
Top