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?

Any suggesstions please

I am passing a XML string from client, In the back end I
am verifying the data with constraints of the columns.
Exec sp_xml_preparedocument @.hdoc OUTPUT,@.xml
select * into #tempApps
from openxml(@.hdoc, N'//application',2)
with AP_Applications --This is the physical table
EXEC sp_xml_removedocument @.hdoc
The system will create an ApplicationId at the time of
inserting a new record. Before inserting a record I am
checking the data.
When I run the above code I am getting the following
error.
Unexpected NULL value returned for
column '[OpenXML].AppId' from the OLE DB
provider 'OpenXML'. This column cannot be NULL.
OLE DB error trace [Non-interface error: Unexpected NULL
value returned for the column: ProviderName='OpenXML',
TableName='[OpenXML]', ColumnName='AppId'].
I will appreciate you can give some suggestions.
Thanks in Advance
You cannot use a table name with an identity column in the with clause. You
have to give the with clause (without the Identity column) explicit.
Best regards
Michael
<anonymous@.discussions.microsoft.com> wrote in message
news:784a01c43121$a8d09420$a501280a@.phx.gbl...
>I am passing a XML string from client, In the back end I
> am verifying the data with constraints of the columns.
> Exec sp_xml_preparedocument @.hdoc OUTPUT,@.xml
> select * into #tempApps
> from openxml(@.hdoc, N'//application',2)
> with AP_Applications --This is the physical table
>
>
> EXEC sp_xml_removedocument @.hdoc
>
> The system will create an ApplicationId at the time of
> inserting a new record. Before inserting a record I am
> checking the data.
> When I run the above code I am getting the following
> error.
> Unexpected NULL value returned for
> column '[OpenXML].AppId' from the OLE DB
> provider 'OpenXML'. This column cannot be NULL.
> OLE DB error trace [Non-interface error: Unexpected NULL
> value returned for the column: ProviderName='OpenXML',
> TableName='[OpenXML]', ColumnName='AppId'].
> I will appreciate you can give some suggestions.
>
> Thanks in Advance
>

any advantage using an xml string as parameter

Hi,
I just wanna know the pros and cons while using XML string as argument to the procedure. Which one will be better in terms of security and performance.
regards
Aneesh R.
That depends on what you want to do with the parameter. Could you please be a bit more specific?
Best regards
Michael
"Aneesh" <aneesh.r@.eostek.com> wrote in message news:%23TD84aoiEHA.1356@.TK2MSFTNGP09.phx.gbl...
Hi,
I just wanna know the pros and cons while using XML string as argument to the procedure. Which one will be better in terms of security and performance.
regards
Aneesh R.
|||Hi,
Actually the application is E-Com. Is it better to use XML parameters for Password verification, UserRegistration etc. I need to consider both Performance and security
regards
Aneesh R.
That depends on what you want to do with the parameter. Could you please be a bit more specific?
Best regards
Michael
"Aneesh" <aneesh.r@.eostek.com> wrote in message news:%23TD84aoiEHA.1356@.TK2MSFTNGP09.phx.gbl...
Hi,
I just wanna know the pros and cons while using XML string as argument to the procedure. Which one will be better in terms of security and performance.
regards
Aneesh R.
|||Some items are dependent on your perf and scaling requirements:
- Are you planning on using OpenXML to unshred it? You may find that large XML documents may be problematic from a scaling point of view (>100kB). Small ones should be fine.
- Are you planning on using a T-SQL statement instead to parse a CSV format? That may be less efficient, but performance tests should be done.
Some items are general security items:
- Do you expose the stored proc parameter to arbitrary users? In that case there are some DoS scenarios that you may need to prepare for by checking for them on the mid-tier. If you have full control over the XML format that you send, then there is no more or less security than on any other data value that you send.
HTH
Michael
"Aneesh" <aneesh.r@.eostek.com> wrote in message news:O0iDzmyiEHA.3564@.TK2MSFTNGP10.phx.gbl...
Hi,
Actually the application is E-Com. Is it better to use XML parameters for Password verification, UserRegistration etc. I need to consider both Performance and security
regards
Aneesh R.
That depends on what you want to do with the parameter. Could you please be a bit more specific?
Best regards
Michael
"Aneesh" <aneesh.r@.eostek.com> wrote in message news:%23TD84aoiEHA.1356@.TK2MSFTNGP09.phx.gbl...
Hi,
I just wanna know the pros and cons while using XML string as argument to the procedure. Which one will be better in terms of security and performance.
regards
Aneesh R.