Display data from XML and SQL Server in the same DataGridView

[tofuTnT]

Hi All,
I have data contained in XML file and SQL database. What I'd like to do is to create a DataSet with a DataTable containing data coming from the XML file, and retrieve from the SQL server only data that match a field in the XML datatable, for example DocID.
The xml might look like
<Document>
<DOCID>1</DOCID>
<SCORE>Title 1</SCORE>
</Document>
<Document>
<DOCID>5</DOCID>
<SCORE>Title 5</SCORE>
</Document>
It is generated by 3rd party software.
The table in SQL server might have fields, DocID, DocAuthor,DocTitle. That table could contains a very large number of records. In the DataGridView, I'd like to be able to display DocID, DocAuthor,DocTitle,Score and sort by any of the column.

What would be the best approach to create the DataSet and to retrieve data from the SQL server?

Thanks in advance!

xkxTnT

 

[jmeckley]

load the xml file into a datatable. you may be able to use [tt]DataTable.ReadXML([filename])[/tt] if the xml is formatted correctly. If not load the xml file into an XMLDocument. cycle through nodes/attributes as needed and manually create the datatable.

then cycle through the datatable to collect your predicate information(docId and what ever else you may need). query sql server, and populate a new datatable.

then place both tables in a dataset, create the relationship between the 2 tables.

now your dataset is ready for use.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[tofuTnT]

Thanks Jason!

If the XML contains 1000 DocID, to get the predicate information, I would have to use some query like
SELECT * FROM Documnts WHERE DocID IN [list of DocID from XML]
The problem is that that DocID list from XML could potentially be long..

I wonder if there is anyway I can have a query that joins the DocID from the XML to the the DocID from the Documents table in SQL server..

xkxTnT

 

[jmeckley]

if there are potentially 100s or 1,000s of records you may want to concider a temporary table in the db to process your data.

maybe not a true [tt]create table #tempTable...[/tt]. but a holding table for the xml to execute queries against. something along the lines of

1. load xml file into datatable
2. use bulk copy to upload xmlDataTable to database
3. select data joining your perminent table to temporary holding table
4. use data in application.
5. when your finished using xml data delete temporary data from the db

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[tofuTnT]

Let me try that...Thanks a lot!