Load XML file into flat table

  • Thread starter Thread starter ryanbreakspear
  • Start date Start date
R

ryanbreakspear

Hi,

I've been doing a bit of reading on the Internet, but can't work out
what is the best way to load an XML file into a table. The XML file is
normalised, so for each record I create in my database table, I'll need
to grab data from the nodes above.

e.g.

<sex>
<title>Male</title>
<eyecolourgroup>
<colour>blue</colour>
<person>
<name>John</name>
</person>
<person>
<name>Bill</name>
</person>
<colour>brown</colour>
<person>
<name>Fred</name>
</person>
</eyecolourgroup>
</sex>
<sex>
<title>Female</title>
<eyecolourgroup>
<colour>blue</colour>
<person>
<name>Jane</name>
</person>
</eyecolourgroup>
</sex>

I want the data to look like:

Sex colour name
male blue John
male blue Bill
male brown Fred
female blue Jane

Obviously I can traverse the XML and build up the values myself, but I
wondered if there was some sort of magic way I can get the values out.
I've tried loading the data into a Datagrid, but I'm not sure how I can
treat it all as one row.

Any ideas would be much appreciated.

Thanks

Ryan
 
Well, if (as seems to be suggested) there is a way to load xml into a
datagrid when it is in a simple (?xml?) format, then why not run your
existing xml through an xslt stylesheet and use this to re-write the data
into a simpler structure that the datagrid will understand? If the xml is
large, you should do this treating the xml as a stream (e.g. FileStream if
on the hdd), not a DOM (e.g. XmlDocument).

It would be interesting to see how this approach compares (performance wise)
with manually parsing the xml...

Marc
 
Where is XML located? File on disk, or where?
There are some cases
- use XML bulk load to insert data from xml file to table, smth like
INSERT into MyXMLDocs(MyXMLDoc) SELECT * FROM OPENROWSET
(Bulk 'c:\temp\MyXMLDoc.xml', SINGLE_CLOB) as x
- use XSLT to transfer data into INSERT INTO query

--
WBR,
Michael Nemtsev :: blog: http://spaces.msn.com/laflour

"At times one remains faithful to a cause only because its opponents do not
cease to be insipid." (c) Friedrich Nietzsche
 
Thanks guys,

Ths file is on disk (about 5-10Mb). Like the idea of the XSLT, never
used it before, but just had a quick play, and managed to get some data
out of my XML file. It looks like there might be a lot to learn
though.

Marc, would love to try both methods, but as I'm fairly new to this, I
think the XSLT might take me enough time on it's own!

Thanks for both your help.

Ryan
 
Back
Top