Feeding XML to SQL2000 database

I'm starting research on how I could best approach feeding XML files
to a relational database.
Here's the scenario:
- we receive several hundred XML files per week, all of which adhere
to the same structure (we have complete control over the structure and
the data that goes into the XML).
- upon receipt of an XML file, we should extract the data into a
Sql2000 relational database. Once that's done, we can discard the XML
(only used for transporting the data).
- the data in the database will be used for extensive querying

- we currently have an XSD file (though we don't use it for any other
purpose than to validate the XML files when they come in).
- we have not created the relational database yet.

I'm trying to figure out how I can create the DB schema from the XSD,
and whether that's the best approach, given that the XSD doesn't
contain much info on field types or sizes, relationships, keys,
indexes etc...
Secondly, I'm also trying to figure out how to import the data from
the XML to the DB in a reliable way. Should I create an XSD that
contains all the mapping information (tables, fields, relationships,
keys, etc...), or should I write the import functionality in a .NET
module for more flexibility and logging control?
If the latter approach is more flexible, I assume that I would load
the XML and use the DOM to read nodes and put them in the appropriate
place in the database.

Finally, I'm playing around with XMLSpy. Do any of you have a
recommendation for tools to assist in  what I need to achieve?

If anyone can give some hints on how to approach this endeavor, I'd
greatly appreciate it.

-- Hans.
0
11/29/2007 10:38:54 AM
dotnet.xml 7266 articles. 0 followers. Follow

1 Replies
536 Views

Similar Articles

[PageSpeed] 21

Its called "shredding" not "feeding" so there's a useful search term.

-- 
<%= Clinton Gallagher
         NET csgallagher AT metromilwaukee.com
         URL http://clintongallagher.metromilwaukee.com/


"Froefel" <hansdeschryver@gmail.com> wrote in message 
news:ab3f0d2b-dd7a-4473-8204-5590bd345d05@o42g2000hsc.googlegroups.com...
> I'm starting research on how I could best approach feeding XML files
> to a relational database.
> Here's the scenario:
> - we receive several hundred XML files per week, all of which adhere
> to the same structure (we have complete control over the structure and
> the data that goes into the XML).
> - upon receipt of an XML file, we should extract the data into a
> Sql2000 relational database. Once that's done, we can discard the XML
> (only used for transporting the data).
> - the data in the database will be used for extensive querying
>
> - we currently have an XSD file (though we don't use it for any other
> purpose than to validate the XML files when they come in).
> - we have not created the relational database yet.
>
> I'm trying to figure out how I can create the DB schema from the XSD,
> and whether that's the best approach, given that the XSD doesn't
> contain much info on field types or sizes, relationships, keys,
> indexes etc...
> Secondly, I'm also trying to figure out how to import the data from
> the XML to the DB in a reliable way. Should I create an XSD that
> contains all the mapping information (tables, fields, relationships,
> keys, etc...), or should I write the import functionality in a .NET
> module for more flexibility and logging control?
> If the latter approach is more flexible, I assume that I would load
> the XML and use the DOM to read nodes and put them in the appropriate
> place in the database.
>
> Finally, I'm playing around with XMLSpy. Do any of you have a
> recommendation for tools to assist in  what I need to achieve?
>
> If anyone can give some hints on how to approach this endeavor, I'd
> greatly appreciate it.
>
> -- Hans. 


0
nobody (271)
12/10/2007 5:49:43 PM
Reply:

Similar Artilces:

remove the database link
i have a table which is linked to some other table or database. how would i remove the link to this table? On Thu, 18 Feb 2010 19:11:44 -0800 (PST), subs <subbu1678@gmail.com> wrote: >i have a table which is linked to some other table or database. how >would i remove the link to this table? Simply select the "table" (the link) in the Tables window in the database design window, and press the Delete key. Accept the offer to delete the link. You can also do it programmatically if that's what you're asking. -- John W. Vinson [MVP] O...

eConnect 9.0 and <taPAPOReceiveInvoiceEntryHdr> XML Node
Hi, I have to upgrade an existing integration into Project Accounting from v8 to v9. I have installed the latest eConnect 9.0.2.0 and the following schemas appear to have been removed, although there are not mentioned in the release notes: <taPAPOReceiveInvoiceEntryLine> XML Node <taPAPOReceiveInvoiceEntryTax> XML Node <taPAPOReceiveInvoiceEntrySerialLot> XML Node <taPAPOReceiveInvoiceEntryHdr> XML Node Does anyone know when/why they were removed, and what I should use instead? many thanks, Tim. Hi Tim This is because the Project Accounting Purchasing Tran...

Reading XML file with JScript
Hi, By using AjaxPro I am creating XML file with database data and saving that created file in harddisk, but i have to show the content of that XML in browser. How can I show the content by AjaxMethod which is in .cs file or can we open and show the content of XML file by using JScrip Thanks in advance Shalem wrote: > By using AjaxPro I am creating XML file with database data and saving > that created file in harddisk, but i have to show the content of that > XML in browser. How can I show the content by AjaxMethod which is > in .cs file or can we open and show the content of...

XML Parsing
I have an xml string exactly as follows: <ownerid name="Anderson, Eric" dsc="0" type="8">{CF9F4762-89CE-4470-8019-D208724883BD}</ownerid> this comes from our CRM Database. This was returned when I asked for ownerid. I would like to display in a textbox or datagrid just the name. I know how to get it into a dataset, I'm just not able to extract the information I need. Thanks! Sandy, The following code should get you at the value of what's in the name attribute: Dim xmlDoc as New XMLDocument() Dim xe_ownerID as XMLElement Dim strName...

How to search all columns in all table in a database
recently I've come accross a pocedure written by Narayana Vyas Kondreddi about searching in all tables for a keyword. This procedure returns 2 columns : one with the found keyword and one with the table name , column name where the keyword was found. All sounds perfect and actually works perfect. My problem si how to return all the other fields associated with that column? after I have got the search results back! The procedure is here http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm the results are in this format: [dbo].[ARTICLE].[AC_TITLE] Validity o...

Database help
Hi everyone, I have a slight confusion here, hope someone can shed some light on this cubject for me. calendar month - (tables - general, misc, notes). I have a database with three tables, I have a month calendar view on my form. General, Misc and Notes will be on three different tabs. For every selected date that I input data in the three different tables, I am not understanding how to link the data to be displayed back to the user. Lets say I have information added for November 10, when I click on that date, all the information pertaining to that date will populate the ...

Getting SignedXML to Emit Namespace-Qualified XML
I posted this to the security group with no luck. When I call SignedXML.ComputeSignature, then call SignXML.GetXML, I get XML that is not namespace-qualified. That is, the Signature node is named Signature rather than ds:Signature. Is there a way to make SignXML emit namespace-qualified XML? On Mar 19, 8:05=A0am, Stephen Wood <stephe...@gmail.com> wrote: > I posted this to the security group with no luck. > > When I call SignedXML.ComputeSignature, then call SignXML.GetXML, I > get > XML that is not namespace-qualified. =A0That is, the Signature node is > named Sign...

Opening Excel with template and XML imported file by command line
Hi, I would like to use Excel 2003 to show reports to the customer. I have an Excel template (.xlt) for the presentation and a XML file containing the data with also the XSD inline for the schema description. How could I open Excel with the template *and* importing a XML file from the command line (to be open from a Windows shortcut) ? In the UI that's done manually by opening the template, then importing the XML by selecting the Data menu > XML, and Import XML. The command line options seems very rudimentary. Is there a way to do that with a script or a macro, knowing that there...

RSS Feed List only displays three feeds
I'm using Outlook 2007 for a month now and imported a list of RSS/ATOM feeds. I'm still using my old feed reader in parallel and noticed that since about a week most of the feeds were not updated in Outlook. I opened the send/receive settings to see what's included when doing a send/receive and found that only three feeds were displayed. They worked but all of the other did not. I then created a new group named "RSS feeds" and included the three listed feeds. After several send/receive on this group, I finally received messages from other blogs as well. The settin...

Inserting data from an Excel file to a database
Hi, I have an auto generated excel file(.xlsx) in a particular format . I tried importing the file to the SQL server database using the query below. SELECT * INTO temp_ExcelData2 FROM OPENROWSET('Microsoft.ACE.OLEDB. 12.0','Excel 12.0 Xml;Database=C:\test \DashboardData.xlsx;HDR=No;IMEX=1','SELECT * FROM [Sheet1$]') It is showing an error as Could not locate registry entry for OLE DB provider 'Microsoft.ACE.OLEDB.12.0'. SELECT * INTO temp_ExcelData2 FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0 Xml;Database=C:\test \DashboardData.xlsx;H...

How to export a query to then xml-format file via VBA?
Hi. I would like to transfer my access query to xml-format, but how can I do it via VBA automatically? hanski ...

How to feed the contents of a cell as row index?
Hi, eg. if c5=3 a[c5] should give me the contents of a3 like in any high level language Excel does not treat a column as an array (table) so when C5=3, the formula =A[C5] means nothing. However, the formula =INDIRECT("A"&C5) will be equivalent to =A3 when C5 has value 3 best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "sudhakar" <sudhakar@discussions.microsoft.com> wrote in message news:CD83DC2E-7213-438E-A7CD-F3C5DC7463F4@microsoft.com... > Hi, > eg. if c5=3 > a[c5] should give me the contents of a3 li...

News Feed Folders
I have just purchased office 2007 and use Outlook for my mail. I have a problem with my News Feed Folders as they seem to breed like rabbits and are now up to 20 for the third time. Is there any way to get rid of the NRF completely as I am fed up of them cluttering up my folder section. They are always empty so what is the point? Help pls pls pls :-) Norm Taylor wrote: > I have just purchased office 2007 and use Outlook for my mail. I have a > problem with my News Feed Folders as they seem to breed like rabbits and are > now up to 20 for the third time. Is there any...

Import data to HQ database?
Is there any Add-ons tools to create one major HQ database from different database of different stores? Any help would be very appreciated. QN There are no tools like that actually, but what you can do is use the Store Operations Import Utility (you can get this from Customersource or Partnersource), and use it to import to a Store Operations database and use the database as a template database when creating the HQ db. :) -- "Phu Nguyen" wrote: > Is there any Add-ons tools to create one major HQ database from different > database of different stores? Any help wo...

Re constructing XML file
I have an XML file below:- <?xml version="1.0" standalone="yes"?> <NewDataSet> <CourseEvents> <Code>VEN01</Code> <VenueDescription>Crazy Johns - Internal</VenueDescription> <ReferenceNo>50</ReferenceNo> <Address1>1233 Crazy Drive</Address1> <Address2 /> <Address3 /> <Suburb>Sth. Melb.</Suburb> <Postcode>3030</Postcode> <State>Victoria</State> <firstday>Wednesday, 22-March-2006</firstday> <lastday>Tu...

Multiple User in a Database
I have a database that I put on a Network Drive so that other people can add data to it. How can I have this database to be able to let more than one person add data to it at a time? Or is there a way? Thanks Julie Good question. I hope someone who knows what the heck is going on with access can respond to this! "Julie Gilliam" wrote: > I have a database that I put on a Network Drive so that other people can add > data to it. How can I have this database to be able to let more than one > person add data to it at a time? Or is there a way? Thanks Julie ...

cannot send any attachment after database repair
I ran into some problem with my information store. After a offline repair, all the services start up fine. However, I cannot send any email with attachment. Any idea? Thanks, Jiffy Jiffy When you say "cannot send attachment", what do you mean by it? 1. Do you have a problem WHILE sending an attachment? 2. Does Outlook ALLOW you to send an email with an attachment, but then FAIL to deliver it? if so, what is the error you get? 3. Does the intended recipient eventually receive the mail MINUS the attachment? Regards -- Nagendra Sitharamaiah MCSE, CCNA, MCT, CISSP Microsoft Use ...

XML File and Select
What is the best way to find information within an xml file. I'm using it as a Config file for a site. I'm playing with XMLTextReader. Is there a better way? Thanks Glenn Glenn, > What is the best way to find information within an xml file. I'm using it > as a Config file for a site. I'm playing with XMLTextReader. Is there a > better way? You could also load it into an XmlDocument or XPathDocument and use XPath to query it. That would certainly be easier. -- Tomas Restrepo tomasr@mvps.org Glenn Leifheit wrote: > What is the best way to find infor...

Why are my RSS Feeds not updating
I have three Microsoft feeds, one is Microsoft At Home , one is Microsoft At Work and the last is MSNBC News. They are in my subscribed feeds list but do not update as they do on my desktop. Are they still listed as subscribed when you go to; Tools-> Account Settings...-> RSS Feeds If so, removing them and then adding them back might help to revive them. You can also export the RSS Feeds to an OPML file, remove the subscriptions and then import the OPML file again. For details see; http://www.msoutlook.info/question/406 -- Robert Sparnaaij [MVP-Outlook] Coauthor, C...

How do I display images from an XML doc that are in this format>>
The xml doc has pictures but in the format of a very long character string. I'm not sure what this format is called so I do not know what type of converter I should be looking for. May have something to do with base64. I want to read it from the xml doc and display it in a windows form pictureBox. I'm programming in c#. example(it actually has a lot more characters than this) <Picture>FRwvAAIAAAANAA4AFAAhAP////9CaXRtYXAgSW1hZ2UAUGFpbnQuUGljdHVyZQAB2gAAAAAAAAAAACgKQAAQk2YKQAAAAAAAFYAAAAoAAAAgAAAABAAQAAAAAAAAAAACICwAAiAsAAAgAAAAIAAAAYKQAAAAAAAFYAAAAoAAAAgAAAABAAQAAA</Pi...

XML Signature Reference Object
Hi, I need to sign a specific node in an XML file that contains analytical data & has a structure something like the following: <DOCUMENT> <RESULT_SET> <TESTVAL> </TESTVAL> ... </RESULT_SET> <RESULT_SET> ... </RESULT_SET> <RESULT_SET> ... </RESULT_SET> ... </DOCUMENT> Where each "RESULT_SET" represents a row in a data table of results that can have a number of individual values (1 for each column in the results table) called :TESTVAL in the example...

data feed
i have a data feed into my excel spreadsheet. the feed is to one cell that changes continuously in real time. there is no historical record of the path taken by the numbers displayed - only the most current number is displayed. I would like to record these numbers at periodic intervals so as to create a historical record of the numbers appearing in the data feed cell and make a chart depicting the history of the data. is ther a way to do such a thing in excel?? ... [This followup was posted to microsoft.public.excel.charting with an email copy to paul. Please use the newsgroup f...

Linq to XML?
I have five items of information I need to extract from XML covering upwards of 25 separate devices. I'm obtaining these values via five Linq to XML queries as follows: IEnumerable<string> EIDs = (from env in xdoc.Root.Elements("packet"). Elements(ns1 + "data").Elements(ns2 + "server_group"). Elements(ns2 + "env") select (string)env.Element(ns3 + "eid")); IEnumerable<int> IDs = (from env in xdoc.Root.Elements("packet"). Elements(ns1 + "data").Elem...

close mdb database from calling mdb
I have a launch center application to open other mdb files. I want to close all mdb opened by launch center when user close launch center. I would like to know is it possible to get Windows Handle ID number to identify which applicaitons opened by launch center and close them using Windows Handle ID number. I am not sure am I on the right tracke. If I am, any API function does above tasks like get Windows Handle ID Number and close the application using Windows Handle ID Number? If I am not on the right tracker, any better way to do this? Your help is great appreciated, On ...

xml into collections
Hi all, we have an xml file. Based on this file we have to generate menu. This xml file changes every day. We are loading the xml file into the application memory and transforming the data of xml file into collections (using classes). 1. Pumping the whole xml data into collections is the correct way or not? 2. As xml is already a structured representation of data, is it necessary to convert the data into collections? 3. If we converted the data into collections, is it true we can get better performance rather than querying the xml everytime? 4. Querying the xml everytime consumes more ti...