Load XML file into flat table

[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

[Marc Gravell]

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

[Michael Nemtsev]

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


"ry************@norubbishgmail.com" wrote:

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

[ryanbreakspear]

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

Michael Nemtsev wrote:

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


"ry************@norubbishgmail.com" wrote:

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