XML file to SQL Server

[JBaileys]

I have an xml file with header and detail. Each XML file represents one header file and multiple detail. What is the best way to design my database and replicate data from the xml file to the db?

 

[CubeE101]

Can you give a short example of what you are dealing with...?

Are you working with 1 file with many headers, or many files with 1 header?


PROGRAMMER: Red-eyed, mumbling mammal capable of conversing with inanimate objects.

 

[JBaileys]

I am dealing with one header per file. We can accept multiple files. XML Example...

<Data>
<Header>
<Version>1.0</Version>
<RecordType>BT3737</RecordType>
<Content>Customer Data</Content>
</Header>
<Person>
<ID>174281500022</ID>
<FirstName>Robert</FirstName>
<MiddleInit>Q</MiddleInit>
<LastName>Eihausen</LastName>
<Address1>407 Cherry St</Address1>
<Address2 />
<City>Deep Freeze</City>
<State>AK</State>
<Zip>62025-2049</Zip>
<Country>USA</Country>
<Birthdate>1956-08-11</Birthdate>
<Gender>Male</Gender>
<LastUpdated>2002-10-02</LastUpdated>
</Person>
<Person>
<ID>174281500023</ID>
<FirstName>Charles</FirstName>
<MiddleInit>E</MiddleInit>
<LastName>Gall</LastName>
<Address1>324 Longhi Rd</Address1>
<Address2 />
<City>Deep Freeze</City>
<State>AK</State>
<Zip>62234-6924</Zip>
<Country>USA</Country>
<Birthdate>1935-03-21</Birthdate>
<Gender>Male</Gender>
<LastUpdated>2002-10-02</LastUpdated>
</Person>

 

[chiph]

In this case, it looks like the header is telling you what sort of data the XML file contains. Your insert program can use it, but I don't see a need to store it in the database.

The only possible reason to store it would be in a log table: "Imported file of this type on 1/12/2005".

Chip H.


____________________________________________________________________
Click here to learn Ways to help with Tsunami Relief
If you want to get the best response to a question, please read FAQ222-2244 first

 

[JBaileys]

Chip,

The question is NOT what data should I keep. The question is what is the best way to load data from an XML file into sql server?

 

[chiph]

Use SAX to loop thru the detail records. When SAX throws it's EndElement event for the Person element, you know you have a complete Person record. You can then pass that onto some database code to do an insert/update as appropriate.

Chip H.


____________________________________________________________________
Click here to learn Ways to help with Tsunami Relief
If you want to get the best response to a question, please read FAQ222-2244 first

 

[JBaileys]

SAX ?!? What is that?

Is there a simple export process (maybe using DTS) to load the data into SQL Server. Can I assume that I should have two SQL tables one representing header info and one presenting person info with a field representing the relationship?

 

[chiph]

SAX is one of the two main XML parsers, DOM being the other one.

SAX is a forward-only XML reader that raises events as it sees various parts of an XML document (there's a startDocument & matching endDocument event, and in between you'll get startElement, endElement, characters, etc. events). It's useful for reading XML files of unknown size (I've used it for XML up to 500mb without problems).

DOM is an object-based XML parser. You point it at a piece of XML (either a string or a file), and it reads it, constructing an object tree in memory which you can then navigate via collections & properties. It's great for smaller documents (less than 500 records).

I don't think DTS knows how to read an XML file.

There are tools will will do a database load for you from an XML file, but they're probably more expensive than you or your boss would care to pay (BizTalk is $25,000 per cpu, Data Junction & others are comparable). So writing some code is almost always required.

Chip H.


____________________________________________________________________
Click here to learn Ways to help with Tsunami Relief
If you want to get the best response to a question, please read FAQ222-2244 first