Re: XML Bulk Loading with Nested Elements
From: Frank DeLuccia (fdeluccia-RemoveThisInsert-_at_edifice-ims.com)
Date: 06/02/04
- Next message: Michael Rys [MSFT]: "Re: Loading XML data using DTS."
- Previous message: mizwhite: "Re: extracting results of XML stored proc to file"
- In reply to: Graeme Malcolm: "Re: XML Bulk Loading with Nested Elements"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 2 Jun 2004 08:32:05 -0400
Thanks for the guidance! I'll see what I can do.
-Frank
"Graeme Malcolm" <graemem_cm@hotmail.com> wrote in message
news:uJMO20%23REHA.1312@TK2MSFTNGP12.phx.gbl...
> I'm afraid you'd still have to apply an XSLT to flatten the XML before
bulk
> loading. This isn't really a "problem" in the sense of a bug, it's just
the
> nature of what you're trying to do; relational databases are tabular in
> nature, whereas XML is inherently more hierarchical - a hierarchy that
> consists of a mixture of attributes, elements, sub-elements, and values
can
> never be easily mapped to a single table of columns.
>
> If you look at your data, what you're trying to do doesn't really make
much
> sense in terms of a single "record" in a row-based data schema - you have
a
> single instance of an Interchange that contains multiple Details, each of
> which can contain a variable number of UPC, QA, and QS entries. If you can
> alter the XML structure to be more "tabular", then you can get it to work;
> but from the look of your data might be better creating multiple tables to
> avoid data duplication. I can't tell from your example whether or not a
> single Interchange can contain multiple STs - if not, you could create 2
> tables ("Interchanges" and "InterchangeDetails") - you'd also have to
alter
> your XML slightly to seperate the InterchangeDetails rows. If an
interchange
> can contain multiple STs, then you'd need to add a table for that to the
> hierarchy.
>
> Cheers,
> Graeme
> --
> ----
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
> www.microsoft.com/mspress/books/6137.asp
>
>
> "Frank DeLuccia" <fdeluccia-RemoveThisInsert-@edifice-ims.com> wrote in
> message news:eNkdJZ9REHA.3608@TK2MSFTNGP10.phx.gbl...
> > Hello All,
> >
> > I'm very new to XML and have been toying with many examples. I
> haven't
> > seen many examples of nested elements though. The XML data will look
> > something like this:
> >
> > <ROOT>
> > <EDI>
> > <INTERCHANGE>123</INTERCHANGE>
> > <GS>12</GS>
> > <ST ID="1" DATE="06/01/2004">
> > <DETAIL LOCATION="0123">
> > <UPC>123</UPC>
> > <QA>5</QA>
> > <QS>6</QS>
> > <UPC>894</UPC>
> > <QA>2</QA>
> > <QS>2</QS>
> > </DETAIL>
> > <DETAIL LOCATION="223">
> > <UPC>678</UPC
> > <QA>1</QA>
> > <QS>0</QS>
> > </DETAIL>
> > </ST>
> > </EDI>
> > </ROOT>
> >
> > How would I write the schema to insert the data into 1 table with the
> > follwing results:
> >
> > Interchange GS ID DATE Location UPC QA QS
> > 123 12 1 06/01/2004 0123 123 5
> > 6
> > 123 12 1 06/01/2004 0123 894 2
> > 2
> > 123 12 1 06/01/2004 223 678
1
> > 0
> >
> > Or better yet, is this even possible? I saw an article dating back to
> 2001
> > saying some XSLT had to be done to remove the nests and put it into a
> usable
> > format for the XML Bulk Insert. I would certainly hope by 2004, this
> > problem has been resolved. Right now we're in the research phase of the
> > project and I can alter the XML Data if need be. These documents are
> going
> > to be huge, so speed and efficiency will also be issues.
> >
> > Thanks for your time in reading this post. Any help is greatly
> appreciated.
> >
> > -Frank
> >
> >
>
>
- Next message: Michael Rys [MSFT]: "Re: Loading XML data using DTS."
- Previous message: mizwhite: "Re: extracting results of XML stored proc to file"
- In reply to: Graeme Malcolm: "Re: XML Bulk Loading with Nested Elements"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
