Bulk Load vs Shredding

Do both methodologies achieve the same goal; migrating data from a given XML
element to its corresponding column in a relational table?
If that is correct, I would appreciate your comments asserting why one
methodology should be used rather than the other.
<%= Clinton Gallagher
METROmilwaukee "Regional Information Services"
NET csgallagher AT metromilwaukee.com
URL http://clintongallagher.metromilwaukee.com/XML Bulkload is generally faster and more scaling for large amounts of data.
However, it it less flexible, since it needs an annotated schema, whereas
OpenXML's shredding is more flexible with XPath, but since it loads all the
data apriori, is not as fast and scalable...
Best regards
Michael
"clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
news:%23aGRb9R9EHA.3708@.TK2MSFTNGP14.phx.gbl...
> Do both methodologies achieve the same goal; migrating data from a given
> XML
> element to its corresponding column in a relational table?
> If that is correct, I would appreciate your comments asserting why one
> methodology should be used rather than the other.
>
> --
> <%= Clinton Gallagher
> METROmilwaukee "Regional Information Services"
> NET csgallagher AT metromilwaukee.com
> URL http://clintongallagher.metromilwaukee.com/
>|||Thank you. More reading has given me more information. No surprise there
huh? ;-)
I'm trying to kill several birds with as few stones as possible; using the
XmlTextWriter to write a file on the disk, get the data from that file and
map its data to table(s) in SQL Server and then based on a choice selected
in the form used to construct the XML apply a given XSLT to HTML or display
the raw XML in the browser.
<%= Clinton Gallagher
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:e0g$XZh9EHA.3708@.TK2MSFTNGP14.phx.gbl...
> XML Bulkload is generally faster and more scaling for large amounts of
data.
> However, it it less flexible, since it needs an annotated schema, whereas
> OpenXML's shredding is more flexible with XPath, but since it loads all
the
> data apriori, is not as fast and scalable...
> Best regards
> Michael
> "clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
> news:%23aGRb9R9EHA.3708@.TK2MSFTNGP14.phx.gbl...
>

Bulk Load vs Shredding

Do both methodologies achieve the same goal; migrating data from a given XML
element to its corresponding column in a relational table?
If that is correct, I would appreciate your comments asserting why one
methodology should be used rather than the other.
<%= Clinton Gallagher
METROmilwaukee "Regional Information Services"
NET csgallagher AT metromilwaukee.com
URL http://clintongallagher.metromilwaukee.com/
XML Bulkload is generally faster and more scaling for large amounts of data.
However, it it less flexible, since it needs an annotated schema, whereas
OpenXML's shredding is more flexible with XPath, but since it loads all the
data apriori, is not as fast and scalable...
Best regards
Michael
"clintonG" <csgallagher@.REMOVETHISTEXTmetromilwaukee.com> wrote in message
news:%23aGRb9R9EHA.3708@.TK2MSFTNGP14.phx.gbl...
> Do both methodologies achieve the same goal; migrating data from a given
> XML
> element to its corresponding column in a relational table?
> If that is correct, I would appreciate your comments asserting why one
> methodology should be used rather than the other.
>
> --
> <%= Clinton Gallagher
> METROmilwaukee "Regional Information Services"
> NET csgallagher AT metromilwaukee.com
> URL http://clintongallagher.metromilwaukee.com/
>
|||Thank you. More reading has given me more information. No surprise there
huh? ;-)
I'm trying to kill several birds with as few stones as possible; using the
XmlTextWriter to write a file on the disk, get the data from that file and
map its data to table(s) in SQL Server and then based on a choice selected
in the form used to construct the XML apply a given XSLT to HTML or display
the raw XML in the browser.
<%= Clinton Gallagher
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:e0g$XZh9EHA.3708@.TK2MSFTNGP14.phx.gbl...
> XML Bulkload is generally faster and more scaling for large amounts of
data.
> However, it it less flexible, since it needs an annotated schema, whereas
> OpenXML's shredding is more flexible with XPath, but since it loads all
the
> data apriori, is not as fast and scalable...
> Best regards
> Michael
> "clintonG" <csgallagher@.REMOVETHISTEXTmetromilwaukee.com> wrote in message
> news:%23aGRb9R9EHA.3708@.TK2MSFTNGP14.phx.gbl...
>

Bulk Insertion of XML or Relational Data into SQL 2005

I have looked everywhere for a definitive answer to this question but I have
yet to see one. If you have XML data or Relational Data in Pure Text
Delimited Files, what is the best way to automate the process of inserting
that data into MS SQL 2005? Is the best setup to create an SPROC that
performs a BULK INSERT fired off by a trigger to look for the presence of a
file containing the XML/Relational data or is there a better way to perform
this action that DB developers embrace?
Thanks,
--
David Bowles"David Bowles" <DavidBowles@.discussions.microsoft.com> wrote in message
news:A60F8A0A-08BE-4646-B4C3-C5C8EAB42C60@.microsoft.com...
>I have looked everywhere for a definitive answer to this question but I
>have
> yet to see one. If you have XML data or Relational Data in Pure Text
> Delimited Files, what is the best way to automate the process of inserting
> that data into MS SQL 2005? Is the best setup to create an SPROC that
> performs a BULK INSERT fired off by a trigger to look for the presence of
> a
> file containing the XML/Relational data or is there a better way to
> perform
> this action that DB developers embrace?
> Thanks,
> --
> David Bowles
>
Consider using Integration Services (assuming Standard or Enterprise Ed).
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||From what I've read on SSIS, it seems that it's more for utilization with
Business Intelligence and Data Mining. I have also read that when using SSI
S
the tables that data is applied to must be optimized for OLAP rather than
OLTP. Our IT group initially considered SSIS, but once we read up on SSIS
and found from several sources that it's not good for Transactional
Processing of Bulk Data, we moved on to looking for other solutions. From
your experience with SSIS, is this not the case?
--
David Bowles
"David Portas" wrote:

> "David Bowles" <DavidBowles@.discussions.microsoft.com> wrote in message
> news:A60F8A0A-08BE-4646-B4C3-C5C8EAB42C60@.microsoft.com...
> Consider using Integration Services (assuming Standard or Enterprise Ed).
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>|||"David Bowles" <DavidBowles@.discussions.microsoft.com> wrote in message
news:4F29AB9B-B487-4228-A178-AF4358918DE8@.microsoft.com...
> From what I've read on SSIS, it seems that it's more for utilization with
> Business Intelligence and Data Mining.
Not really. Data Warehousing requires moving lots of data around, and
that's what SSIS does.

>I have also read that when using SSIS
> the tables that data is applied to must be optimized for OLAP rather than
> OLTP. Our IT group initially considered SSIS, but once we read up on SSIS
> and found from several sources that it's not good for Transactional
> Processing of Bulk Data, we moved on to looking for other solutions. From
> your experience with SSIS, is this not the case?
> --
You might be confusing SSIS with SSAS. Anyway SSIS is a platform for
building, debugging, running and managing data movement processes (aka ETL).
It is a good fit for what you're trying to accomplish.
David|||David Bowles wrote:
> From what I've read on SSIS, it seems that it's more for utilization with
> Business Intelligence and Data Mining. I have also read that when using S
SIS
> the tables that data is applied to must be optimized for OLAP rather than
> OLTP. Our IT group initially considered SSIS, but once we read up on SSIS
> and found from several sources that it's not good for Transactional
> Processing of Bulk Data, we moved on to looking for other solutions. From
> your experience with SSIS, is this not the case?
> --
> David Bowles
>
SSIS is part of the BI toolset but it's definitely not just for OLAP.
If you are familiar with DTS you'll find SSIS a very pleasant surprise
in terms of the performance it can achieve. Some good examples can be
found at:
http://blogs.conchango.com/jamiethomson/
http://www.sqlis.com/
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Build SQL statement to show some tiers

in my relational sql db structure I have some items and its children, grandchildren items
to print these values do i need to build one sql statement for each tier?
into my sproc i tried something as following but it returns me only the last tier
SELECT * FROM A
INNER JOIN
B ON A.field1 = B.field2
INNER JOIN
C ON A.filed3 = C.field4
WHERE A.filed1 = @.myParam

thanks a lot

I'm not sure if you're looking to query an hierarchy but if so:
It sounds like you've got a table that's representing a hierarchial relationship, ie something like this:
create table myTable
(
id int,
parentID int null,
value varchar(30)
)
There are a bunch of good articles on sqlteam. Take a look at this one:
http://www.sqlteam.com/item.asp?ItemID=8866
Hope this helps,