Help with XML to Excel

Hi All,
I have a fairly complex XML file which I wanted to load to a table
using BulkLoad. However, BulkLoad has limitations when it comes to
error in the XML file (if data is bad, it stops).
After doing some research, I found out that if I have a file, say,
Books.xml and I open it in Excel (right click, open with Excel), it
does exactly what I want. I have the entire XML into my excel sheet,
with all the columns headers. It requires no schema at all.
My question is how Excel does this? How does it convert the XML into
the Excel sheet? If I can get that component of Excel that does it,
that will help me a lot. The reason is I do not want to use Excel while
I am loading my XML file into the table. I just want to know how the
Excel does it without schema, and I can do the same to export it into
the table as is.
If Bulk Load did not have the limitation, I would go for Bulk Load, but
the above conversion of xml to excel is even better if I can figure out
how it is done.
I am using Windows XP with MS Excel 2003.
Please help,
Thanks,
Chris
There are several tools that can help you here. Part of GotDotNet, there is
a tool that will generate an XSD schema for your Xml document. The tool is
here:
http://apps.gotdotnet.com/xmltools/xsdinference/
You can later on apply couple of annotations in the XSD to make Bulkload
work with the generated schema. This should be pretty straightforward since
you want to generate a single table from your Xml Document. Just put
sql:is-constant="1" on the container elements if you have any and you are
good to go.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
<undoredo@.gmail.com> wrote in message
news:1113421813.353131.241690@.o13g2000cwo.googlegr oups.com...
> Hi All,
> I have a fairly complex XML file which I wanted to load to a table
> using BulkLoad. However, BulkLoad has limitations when it comes to
> error in the XML file (if data is bad, it stops).
> After doing some research, I found out that if I have a file, say,
> Books.xml and I open it in Excel (right click, open with Excel), it
> does exactly what I want. I have the entire XML into my excel sheet,
> with all the columns headers. It requires no schema at all.
> My question is how Excel does this? How does it convert the XML into
> the Excel sheet? If I can get that component of Excel that does it,
> that will help me a lot. The reason is I do not want to use Excel while
> I am loading my XML file into the table. I just want to know how the
> Excel does it without schema, and I can do the same to export it into
> the table as is.
> If Bulk Load did not have the limitation, I would go for Bulk Load, but
> the above conversion of xml to excel is even better if I can figure out
> how it is done.
> I am using Windows XP with MS Excel 2003.
> Please help,
> Thanks,
> Chris
>