Any suggestions on how to process very large files?

I am receiving large XML files from an external source. I need to inseert
all the <DATA> elements into a table on SQL Server. These files can be as
small as a couple k or as large as 3 megs. I would like to input all the data
using sprocs on the database server. Can I really really do that or should I
just write a client side app to read the data and insert into the table?
(An example is attached with just 3 data points. Most have tens of thousands
of data points.)
What is the most effiecent approach to handling large XML files? What
technology works the best? If there is information I should be reading on
this I do not seem to be able to locate it in MSDN.
Any help will be appreciated.
AlanS
<?xml version="1.0" encoding="utf-8" ?>
<ORGANIZATION xmlns:xsi="http://www.w3.org/2000/10/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="http://oasis.caiso.com/oasisv003.xsd">
CAISO
<REPORT_ITEM>
<HEADER>
<REPORT>AS_FINAL_MCP</REPORT>
<SYSTEM>OASIS</SYSTEM>
<TZ>PPT</TZ>
<MKT_TYPE>A</MKT_TYPE>
<UOM>US$/MW</UOM>
<INTERVAL>ENDING</INTERVAL>
<SEC_PER_INTERVAL>3600</SEC_PER_INTERVAL>
</HEADER>
<DATA>
<DATA_ITEM>GNSPIN_PRC</DATA_ITEM>
<RESOURCE_NAME>AZ2</RESOURCE_NAME>
<OPR_DATE>2004-10-29</OPR_DATE>
<INTERVAL_NUM>1</INTERVAL_NUM>
<VALUE>0.8</VALUE>
</DATA>
<DATA>
<DATA_ITEM>GNSPIN_PRC</DATA_ITEM>
<RESOURCE_NAME>AZ2</RESOURCE_NAME>
<OPR_DATE>2004-10-29</OPR_DATE>
<INTERVAL_NUM>2</INTERVAL_NUM>
<VALUE>0.8</VALUE>
</DATA>
<DATA>
<DATA_ITEM>GNSPIN_PRC</DATA_ITEM>
<RESOURCE_NAME>AZ2</RESOURCE_NAME>
<OPR_DATE>2004-10-29</OPR_DATE>
<INTERVAL_NUM>3</INTERVAL_NUM>
<VALUE>0.8</VALUE>
</DATA>
</REPORT_ITEM>
<DISCLAIMER_ITEM>
<DISCLAIMER>The contents of these pages are subject to change without
notice. Decisions based on information contained within the web site are the
visitor's sole responsibility.
</DISCLAIMER>
</DISCLAIMER_ITEM>
</ORGANIZATION>
If you have a dedicated machine with otherwise no load, you should be able
to use OpenXML.
If you have other loads on your machine or the data is often larger than a
couple of 100kB, you may want to consider the XML Bulkload object.
Best regards
Michael
"AlanS" <AlanS@.discussions.microsoft.com> wrote in message
news:BB511473-A60B-4E4F-B492-F050D8024B65@.microsoft.com...
>I am receiving large XML files from an external source. I need to inseert
> all the <DATA> elements into a table on SQL Server. These files can be as
> small as a couple k or as large as 3 megs. I would like to input all the
> data
> using sprocs on the database server. Can I really really do that or should
> I
> just write a client side app to read the data and insert into the table?
> (An example is attached with just 3 data points. Most have tens of
> thousands
> of data points.)
> What is the most effiecent approach to handling large XML files? What
> technology works the best? If there is information I should be reading on
> this I do not seem to be able to locate it in MSDN.
> Any help will be appreciated.
> AlanS
>
> <?xml version="1.0" encoding="utf-8" ?>
> <ORGANIZATION xmlns:xsi="http://www.w3.org/2000/10/XMLSchema-instance"
> xsi:noNamespaceSchemaLocation="http://oasis.caiso.com/oasisv003.xsd">
> CAISO
> <REPORT_ITEM>
> <HEADER>
> <REPORT>AS_FINAL_MCP</REPORT>
> <SYSTEM>OASIS</SYSTEM>
> <TZ>PPT</TZ>
> <MKT_TYPE>A</MKT_TYPE>
> <UOM>US$/MW</UOM>
> <INTERVAL>ENDING</INTERVAL>
> <SEC_PER_INTERVAL>3600</SEC_PER_INTERVAL>
> </HEADER>
> <DATA>
> <DATA_ITEM>GNSPIN_PRC</DATA_ITEM>
> <RESOURCE_NAME>AZ2</RESOURCE_NAME>
> <OPR_DATE>2004-10-29</OPR_DATE>
> <INTERVAL_NUM>1</INTERVAL_NUM>
> <VALUE>0.8</VALUE>
> </DATA>
> <DATA>
> <DATA_ITEM>GNSPIN_PRC</DATA_ITEM>
> <RESOURCE_NAME>AZ2</RESOURCE_NAME>
> <OPR_DATE>2004-10-29</OPR_DATE>
> <INTERVAL_NUM>2</INTERVAL_NUM>
> <VALUE>0.8</VALUE>
> </DATA>
> <DATA>
> <DATA_ITEM>GNSPIN_PRC</DATA_ITEM>
> <RESOURCE_NAME>AZ2</RESOURCE_NAME>
> <OPR_DATE>2004-10-29</OPR_DATE>
> <INTERVAL_NUM>3</INTERVAL_NUM>
> <VALUE>0.8</VALUE>
> </DATA>
> </REPORT_ITEM>
> <DISCLAIMER_ITEM>
> <DISCLAIMER>The contents of these pages are subject to change without
> notice. Decisions based on information contained within the web site are
> the
> visitor's sole responsibility.
> </DISCLAIMER>
> </DISCLAIMER_ITEM>
> </ORGANIZATION>
>
|||Hello, Michael!
You wrote on Fri, 29 Oct 2004 18:30:10 -0700:
MRM> If you have other loads on your machine or the data is often larger
MRM> than a couple of 100kB, you may want to consider the XML Bulkload
MRM> object.
Since the original authers xml format is very simple I would suggest to use
SqlXmlBulkLoad object and nothing else.
With best regards, Alex Shirshov.
|||Ok. I have tried to use SQLXmlBulkLoad. But, I am having a problem. First
point though. I am told that this is an invalid connection string.
//
// sqlConnection1
//
this.sqlConnection1.ConnectionString = "workstation id=DEVELOPER;packet
size=4096;integrated security=SSPI;data source=ALANS;persist security
info=False;initial catalog=Llama";
The connection string works. I use it earlier to connect to populate a grid.
Here is my using statement;
using SQLXMLBULKLOADLib;
Here is my code
(1) SQLXMLBulkLoad objBL = new SQLXMLBulkLoad();
(2) objBL.ConnectionString = this.sqlConnection1.ConnectionString;
(3) objBL.ErrorLogFile = @."C:\APSES\error.log";
(4)objBL.Execute(@."C:\APSES\XML TestsSampleSchema.xml", @."C:\APSES\XML
TestsSampleXMLData.xml");
(5)objBL = null;
Exception occurs at line (4). Invalid Connect String.
Here is the error log
<?xml version="1.0"?>
<Result State="FAILED">
<Error><HResult>0x80040E21I32</HResult>
<Description><![CDATA[Invalid connection string.]]></Description>
<Source>XML BulkLoad for SQL Server</Source>
<Type>FATAL</Type>
</Error>
</Result State>
What is incorrect and how do I correct it?