i am using this code to upload file to a TempTable in the SQL SERVER :
"for %%f in ( F:\Temp\*.txt) do bcp ...."
the thing is that this code load all the file that in a given folder to the
table.
is it possible to some how (using a switch or anything else) that only 10
files each time will be uploaded?
thnaks in advance
peleg
On Tue, 19 Jun 2007 01:09:00 -0700, pelegk1
<pelegk1@.discussions.microsoft.com> wrote:
>i am using this code to upload file to a TempTable in the SQL SERVER :
>"for %%f in ( F:\Temp\*.txt) do bcp ...."
>the thing is that this code load all the file that in a given folder to the
>table.
>is it possible to some how (using a switch or anything else) that only 10
>files each time will be uploaded?
>thnaks in advance
>peleg
The FOR loop used in a bat file or at a command prompt has no
provision for working in batches of 10. Even if it somehow was able
to stop at ten, how would it know to start at number 11 the next time?
The simplest alternative I can think of is to write a program that
copies ten files at a time to a sub-folder, invokes the command as
above against the sub-folder, and then deletes the files from the
sub-folder.
Roy Harvey
Beacon Falls, CT
|||One method to call a separate command file that exits once the limit
reached. For example:
REM first command file
@.SET /a FileCount = 0
@.for %%f in ( * ) do CALL test1.cmd "%%f"
REM second command file
@.SET /a FileCount = %FileCount% + 1
@.IF %FileCount% GTR 10 GOTO Done
bcp ... "%1" in ...
:Done
However, I agree with Roy that you are better off with something more robust
than a command file. Perhaps a VBScript will suffice.
Hope this helps.
Dan Guzman
SQL Server MVP
"pelegk1" <pelegk1@.discussions.microsoft.com> wrote in message
news:2AFC6622-4C89-47F1-805C-33EDEE96BEA1@.microsoft.com...
>i am using this code to upload file to a TempTable in the SQL SERVER :
> "for %%f in ( F:\Temp\*.txt) do bcp ...."
> the thing is that this code load all the file that in a given folder to
> the
> table.
> is it possible to some how (using a switch or anything else) that only 10
> files each time will be uploaded?
> thnaks in advance
> peleg
>
sql
Showing posts with label load. Show all posts
Showing posts with label load. Show all posts
Thursday, March 29, 2012
bulk upload of files to table
i am using this code to upload file to a TempTable in the SQL SERVER :
"for %%f in ( F:\Temp\*.txt) do bcp ...."
the thing is that this code load all the file that in a given folder to the
table.
is it possible to some how (using a switch or anything else) that only 10
files each time will be uploaded?
thnaks in advance
pelegOn Tue, 19 Jun 2007 01:09:00 -0700, pelegk1
<pelegk1@.discussions.microsoft.com> wrote:
>i am using this code to upload file to a TempTable in the SQL SERVER :
>"for %%f in ( F:\Temp\*.txt) do bcp ...."
>the thing is that this code load all the file that in a given folder to the
>table.
>is it possible to some how (using a switch or anything else) that only 10
>files each time will be uploaded?
>thnaks in advance
>peleg
The FOR loop used in a bat file or at a command prompt has no
provision for working in batches of 10. Even if it somehow was able
to stop at ten, how would it know to start at number 11 the next time?
The simplest alternative I can think of is to write a program that
copies ten files at a time to a sub-folder, invokes the command as
above against the sub-folder, and then deletes the files from the
sub-folder.
Roy Harvey
Beacon Falls, CT|||One method to call a separate command file that exits once the limit
reached. For example:
REM first command file
@.SET /a FileCount = 0
@.for %%f in ( * ) do CALL test1.cmd "%%f"
REM second command file
@.SET /a FileCount = %FileCount% + 1
@.IF %FileCount% GTR 10 GOTO Done
bcp ... "%1" in ...
:Done
However, I agree with Roy that you are better off with something more robust
than a command file. Perhaps a VBScript will suffice.
Hope this helps.
Dan Guzman
SQL Server MVP
"pelegk1" <pelegk1@.discussions.microsoft.com> wrote in message
news:2AFC6622-4C89-47F1-805C-33EDEE96BEA1@.microsoft.com...
>i am using this code to upload file to a TempTable in the SQL SERVER :
> "for %%f in ( F:\Temp\*.txt) do bcp ...."
> the thing is that this code load all the file that in a given folder to
> the
> table.
> is it possible to some how (using a switch or anything else) that only 10
> files each time will be uploaded?
> thnaks in advance
> peleg
>
"for %%f in ( F:\Temp\*.txt) do bcp ...."
the thing is that this code load all the file that in a given folder to the
table.
is it possible to some how (using a switch or anything else) that only 10
files each time will be uploaded?
thnaks in advance
pelegOn Tue, 19 Jun 2007 01:09:00 -0700, pelegk1
<pelegk1@.discussions.microsoft.com> wrote:
>i am using this code to upload file to a TempTable in the SQL SERVER :
>"for %%f in ( F:\Temp\*.txt) do bcp ...."
>the thing is that this code load all the file that in a given folder to the
>table.
>is it possible to some how (using a switch or anything else) that only 10
>files each time will be uploaded?
>thnaks in advance
>peleg
The FOR loop used in a bat file or at a command prompt has no
provision for working in batches of 10. Even if it somehow was able
to stop at ten, how would it know to start at number 11 the next time?
The simplest alternative I can think of is to write a program that
copies ten files at a time to a sub-folder, invokes the command as
above against the sub-folder, and then deletes the files from the
sub-folder.
Roy Harvey
Beacon Falls, CT|||One method to call a separate command file that exits once the limit
reached. For example:
REM first command file
@.SET /a FileCount = 0
@.for %%f in ( * ) do CALL test1.cmd "%%f"
REM second command file
@.SET /a FileCount = %FileCount% + 1
@.IF %FileCount% GTR 10 GOTO Done
bcp ... "%1" in ...
:Done
However, I agree with Roy that you are better off with something more robust
than a command file. Perhaps a VBScript will suffice.
Hope this helps.
Dan Guzman
SQL Server MVP
"pelegk1" <pelegk1@.discussions.microsoft.com> wrote in message
news:2AFC6622-4C89-47F1-805C-33EDEE96BEA1@.microsoft.com...
>i am using this code to upload file to a TempTable in the SQL SERVER :
> "for %%f in ( F:\Temp\*.txt) do bcp ...."
> the thing is that this code load all the file that in a given folder to
> the
> table.
> is it possible to some how (using a switch or anything else) that only 10
> files each time will be uploaded?
> thnaks in advance
> peleg
>
bulk upload of files to table
i am using this code to upload file to a TempTable in the SQL SERVER :
"for %%f in ( F:\Temp\*.txt) do bcp ...."
the thing is that this code load all the file that in a given folder to the
table.
is it possible to some how (using a switch or anything else) that only 10
files each time will be uploaded?
thnaks in advance
pelegOn Tue, 19 Jun 2007 01:09:00 -0700, pelegk1
<pelegk1@.discussions.microsoft.com> wrote:
>i am using this code to upload file to a TempTable in the SQL SERVER :
>"for %%f in ( F:\Temp\*.txt) do bcp ...."
>the thing is that this code load all the file that in a given folder to the
>table.
>is it possible to some how (using a switch or anything else) that only 10
>files each time will be uploaded?
>thnaks in advance
>peleg
The FOR loop used in a bat file or at a command prompt has no
provision for working in batches of 10. Even if it somehow was able
to stop at ten, how would it know to start at number 11 the next time?
The simplest alternative I can think of is to write a program that
copies ten files at a time to a sub-folder, invokes the command as
above against the sub-folder, and then deletes the files from the
sub-folder.
Roy Harvey
Beacon Falls, CT|||One method to call a separate command file that exits once the limit
reached. For example:
REM first command file
@.SET /a FileCount = 0
@.for %%f in ( * ) do CALL test1.cmd "%%f"
REM second command file
@.SET /a FileCount = %FileCount% + 1
@.IF %FileCount% GTR 10 GOTO Done
bcp ... "%1" in ...
:Done
However, I agree with Roy that you are better off with something more robust
than a command file. Perhaps a VBScript will suffice.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"pelegk1" <pelegk1@.discussions.microsoft.com> wrote in message
news:2AFC6622-4C89-47F1-805C-33EDEE96BEA1@.microsoft.com...
>i am using this code to upload file to a TempTable in the SQL SERVER :
> "for %%f in ( F:\Temp\*.txt) do bcp ...."
> the thing is that this code load all the file that in a given folder to
> the
> table.
> is it possible to some how (using a switch or anything else) that only 10
> files each time will be uploaded?
> thnaks in advance
> peleg
>
"for %%f in ( F:\Temp\*.txt) do bcp ...."
the thing is that this code load all the file that in a given folder to the
table.
is it possible to some how (using a switch or anything else) that only 10
files each time will be uploaded?
thnaks in advance
pelegOn Tue, 19 Jun 2007 01:09:00 -0700, pelegk1
<pelegk1@.discussions.microsoft.com> wrote:
>i am using this code to upload file to a TempTable in the SQL SERVER :
>"for %%f in ( F:\Temp\*.txt) do bcp ...."
>the thing is that this code load all the file that in a given folder to the
>table.
>is it possible to some how (using a switch or anything else) that only 10
>files each time will be uploaded?
>thnaks in advance
>peleg
The FOR loop used in a bat file or at a command prompt has no
provision for working in batches of 10. Even if it somehow was able
to stop at ten, how would it know to start at number 11 the next time?
The simplest alternative I can think of is to write a program that
copies ten files at a time to a sub-folder, invokes the command as
above against the sub-folder, and then deletes the files from the
sub-folder.
Roy Harvey
Beacon Falls, CT|||One method to call a separate command file that exits once the limit
reached. For example:
REM first command file
@.SET /a FileCount = 0
@.for %%f in ( * ) do CALL test1.cmd "%%f"
REM second command file
@.SET /a FileCount = %FileCount% + 1
@.IF %FileCount% GTR 10 GOTO Done
bcp ... "%1" in ...
:Done
However, I agree with Roy that you are better off with something more robust
than a command file. Perhaps a VBScript will suffice.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"pelegk1" <pelegk1@.discussions.microsoft.com> wrote in message
news:2AFC6622-4C89-47F1-805C-33EDEE96BEA1@.microsoft.com...
>i am using this code to upload file to a TempTable in the SQL SERVER :
> "for %%f in ( F:\Temp\*.txt) do bcp ...."
> the thing is that this code load all the file that in a given folder to
> the
> table.
> is it possible to some how (using a switch or anything else) that only 10
> files each time will be uploaded?
> thnaks in advance
> peleg
>
bulk load: no error message, but not loading
this board wont let me post all my data at once, so one at a time...
heres how i call the load
****************************
Function Main()
Set oXMLBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad.3.0")
oXMLBulkLoad.ConnectionString = "PROVIDER=SQLOLEDB;SERVER=localhost;" &
_
"DATABASE=MyDB;INTEGRATED SECURITY=sspi;"
oXMLBulkLoad.Execute "C:\myschema", "C:\myfile"
oXMLBulkLoad.SchemaGen = True
Set oXMLBulkLoad = Nothing
Main = DTSTaskExecResult_success
End Function
***************************************
salival
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1331465.htmlill try attaching my schema
+---+
| Attachment filename: q.txt |
|Download attachment: http://www.mcse.ms/attachment.php?postid=3575113 |
+---+
salival
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1331465.html
heres how i call the load
****************************
Function Main()
Set oXMLBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad.3.0")
oXMLBulkLoad.ConnectionString = "PROVIDER=SQLOLEDB;SERVER=localhost;" &
_
"DATABASE=MyDB;INTEGRATED SECURITY=sspi;"
oXMLBulkLoad.Execute "C:\myschema", "C:\myfile"
oXMLBulkLoad.SchemaGen = True
Set oXMLBulkLoad = Nothing
Main = DTSTaskExecResult_success
End Function
***************************************
salival
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1331465.htmlill try attaching my schema
+---+
| Attachment filename: q.txt |
|Download attachment: http://www.mcse.ms/attachment.php?postid=3575113 |
+---+
salival
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1331465.html
bulk load: no error message, but not loading
hey guys, my xml bulk load creates no error message, but does not populate my tables.
I am very new to xml and sql server, so maybe this is a simple problem?
i have a root element i dont want to map, and then several tables of data in one document. I am unconcerned with any key constraints at this point.this board wont let me post all my data at once, so one at a time...
heres how i call the load
****************************
Function Main()
Set oXMLBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad.3.0")
oXMLBulkLoad.ConnectionString = "PROVIDER=SQLOLEDB;SERVER=localhost;" & _
"DATABASE=MyDB;INTEGRATED SECURITY=sspi;"
oXMLBulkLoad.Execute "C:\myschema", "C:\myfile"
oXMLBulkLoad.SchemaGen = True
Set oXMLBulkLoad = Nothing
Main = DTSTaskExecResult_success
End Function
***************************************|||ill try attaching my schemasql
I am very new to xml and sql server, so maybe this is a simple problem?
i have a root element i dont want to map, and then several tables of data in one document. I am unconcerned with any key constraints at this point.this board wont let me post all my data at once, so one at a time...
heres how i call the load
****************************
Function Main()
Set oXMLBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad.3.0")
oXMLBulkLoad.ConnectionString = "PROVIDER=SQLOLEDB;SERVER=localhost;" & _
"DATABASE=MyDB;INTEGRATED SECURITY=sspi;"
oXMLBulkLoad.Execute "C:\myschema", "C:\myfile"
oXMLBulkLoad.SchemaGen = True
Set oXMLBulkLoad = Nothing
Main = DTSTaskExecResult_success
End Function
***************************************|||ill try attaching my schemasql
Bulk Load: Is there any substitute for the fixed="..." modifier
Hi to everybody!
I have to Bulk Load an XML file whose Schema Definition has
some fixed="..." modifiers. After noticing something wrong
with these elements, I've found in this newsgroup that
sqlxml doesn't support the fixed="..." modifier. However,
if I substitute the fixed="VALUE-1" modifier with a
default="VALUE-1" modifier I can't prevent Bulk Load to
accept values different from "VALUE-1".
Does anybody know how to code the fixed="..." modifiers in
the Schema Definition so that XML Bulk Load can accept them?
TIA
Greetings,
David GrantThe best way to acheive this sort of functionality is to validate the
source document before loading via SqlXmlBulkload.
SqlXml does a limited amount of validation of source Xml documents, but it
is not a full validation mechanism. For example, there are cases where the
bulkload component would load invalid source Xml.
Andrew Conrad
Microsoft Corporation|||Thank you for your quick response, Andrew.
Greetings,
David Grant
>--Original Message--
>The best way to acheive this sort of functionality is to
validate the
>source document before loading via SqlXmlBulkload.
>SqlXml does a limited amount of validation of source Xml
documents, but it
>is not a full validation mechanism. For example, there
are cases where the
>bulkload component would load invalid source Xml.
>Andrew Conrad
>Microsoft Corporation
>.
>
I have to Bulk Load an XML file whose Schema Definition has
some fixed="..." modifiers. After noticing something wrong
with these elements, I've found in this newsgroup that
sqlxml doesn't support the fixed="..." modifier. However,
if I substitute the fixed="VALUE-1" modifier with a
default="VALUE-1" modifier I can't prevent Bulk Load to
accept values different from "VALUE-1".
Does anybody know how to code the fixed="..." modifiers in
the Schema Definition so that XML Bulk Load can accept them?
TIA
Greetings,
David GrantThe best way to acheive this sort of functionality is to validate the
source document before loading via SqlXmlBulkload.
SqlXml does a limited amount of validation of source Xml documents, but it
is not a full validation mechanism. For example, there are cases where the
bulkload component would load invalid source Xml.
Andrew Conrad
Microsoft Corporation|||Thank you for your quick response, Andrew.
Greetings,
David Grant
>--Original Message--
>The best way to acheive this sort of functionality is to
validate the
>source document before loading via SqlXmlBulkload.
>SqlXml does a limited amount of validation of source Xml
documents, but it
>is not a full validation mechanism. For example, there
are cases where the
>bulkload component would load invalid source Xml.
>Andrew Conrad
>Microsoft Corporation
>.
>
Bulk Load: Is there any substitute for the fixed="..." modifier
Hi to everybody!
I have to Bulk Load an XML file whose Schema Definition has
some fixed="..." modifiers. After noticing something wrong
with these elements, I've found in this newsgroup that
sqlxml doesn't support the fixed="..." modifier. However,
if I substitute the fixed="VALUE-1" modifier with a
default="VALUE-1" modifier I can't prevent Bulk Load to
accept values different from "VALUE-1".
Does anybody know how to code the fixed="..." modifiers in
the Schema Definition so that XML Bulk Load can accept them?
TIA
Greetings,
David Grant
The best way to acheive this sort of functionality is to validate the
source document before loading via SqlXmlBulkload.
SqlXml does a limited amount of validation of source Xml documents, but it
is not a full validation mechanism. For example, there are cases where the
bulkload component would load invalid source Xml.
Andrew Conrad
Microsoft Corporation
|||Thank you for your quick response, Andrew.
Greetings,
David Grant
>--Original Message--
>The best way to acheive this sort of functionality is to
validate the
>source document before loading via SqlXmlBulkload.
>SqlXml does a limited amount of validation of source Xml
documents, but it
>is not a full validation mechanism. For example, there
are cases where the
>bulkload component would load invalid source Xml.
>Andrew Conrad
>Microsoft Corporation
>.
>
I have to Bulk Load an XML file whose Schema Definition has
some fixed="..." modifiers. After noticing something wrong
with these elements, I've found in this newsgroup that
sqlxml doesn't support the fixed="..." modifier. However,
if I substitute the fixed="VALUE-1" modifier with a
default="VALUE-1" modifier I can't prevent Bulk Load to
accept values different from "VALUE-1".
Does anybody know how to code the fixed="..." modifiers in
the Schema Definition so that XML Bulk Load can accept them?
TIA
Greetings,
David Grant
The best way to acheive this sort of functionality is to validate the
source document before loading via SqlXmlBulkload.
SqlXml does a limited amount of validation of source Xml documents, but it
is not a full validation mechanism. For example, there are cases where the
bulkload component would load invalid source Xml.
Andrew Conrad
Microsoft Corporation
|||Thank you for your quick response, Andrew.
Greetings,
David Grant
>--Original Message--
>The best way to acheive this sort of functionality is to
validate the
>source document before loading via SqlXmlBulkload.
>SqlXml does a limited amount of validation of source Xml
documents, but it
>is not a full validation mechanism. For example, there
are cases where the
>bulkload component would load invalid source Xml.
>Andrew Conrad
>Microsoft Corporation
>.
>
Bulk Load XML into SQL using Schema File ...
Hi ...
I am bulk loading XML data into SQL using
SQLXMLBulkLoad.SQLXMLBulkload.3.0 COM Object. To do this I
provide the xml data and a schema file (below).
When I use the attached schema file I get the following error:
No data was provided for column 'SensorId' on table 'MacgowanTestRWISRawSurface', and this column cannot contain NULL values.
I changed the table definition to allow nulls in the SensorId column
and the bulk load was successful. Notice on the records in the
MacgowanTestRWISRawSurface table … where are the records with the NULL
SensorId(s) in the XML data ? Is this is a side effect of my
self-join with the MacgowanTestRWISRawSurface table ?
Any comments?
Thanks,
Chris
[Alphanumericdata].[dbo].[MacgowanTestRWISRawAtmospheric]
RecordId DataSourceId
ProductInstanceId
SystemId RpuId SensorId ObsDateTime
-- --
-- --
1
OH
5abbbc86-fb2c-4703-9589-b55f763ee150
200 0
NULL NULL
2
OH
5abbbc86-fb2c-4703-9589-b55f763ee150
200 1
NULL NULL
3
OH
5abbbc86-fb2c-4703-9589-b55f763ee150
200 2
NULL NULL
4
OH
5abbbc86-fb2c-4703-9589-b55f763ee150
200 3
NULL NULL
[Alphanumericdata].[dbo].[MacgowanTestRWISRawSurface]
RecordId DataSourceId SystemId RpuId SensorId ObsDateTime
-- -- --
1
OH
200 0
0 2005-12-05 15:48:00.000
2
OH
200 0
1 2005-12-05 15:48:00.000
3
OH
200 0
NULL NULL
4
OH
200 1
0 2005-12-05 15:49:00.000
5
OH
200 1
NULL NULL
6
OH
200 2
0 2005-12-05 15:50:00.000
7
OH
200 2
1 2005-12-05 15:50:00.000
8
OH
200 2
NULL NULL
9
OH
200 3
0 2005-12-05 15:45:00.000
10
OH
200 3
1 2005-12-05 15:45:00.000
11
OH
200 3
NULL NULL
//////////////////////////////////////////////////////
// Schema File
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="MacgowanTest_Atmospheric_Surface"
parent="MacgowanTestRWISRawAtmospheric"
parent-key="SystemId RpuId"
child="MacgowanTestRWISRawSurface"
child-key="SystemId RpuId" />
</xsd:appinfo>
</xsd:annotation>
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="MacgowanTest_Surface_Surface"
parent="MacgowanTestRWISRawSurface"
parent-key="SystemId RpuId"
child="MacgowanTestRWISRawSurface"
child-key="SystemId RpuId" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="site" sql:relation="MacgowanTestRWISRawAtmospheric" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="sensors" sql:relation="MacgowanTestRWISRawSurface"
sql:relationship="MacgowanTest_Atmospheric_Surface">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="surface" sql:relation="MacgowanTestRWISRawSurface"
sql:relationship="MacgowanTest_Surface_Surface" >
<xsd:complexType>
<xsd:attribute name="id" type="xsd:string" sql:field="SensorId"/>
<xsd:attribute name="datetime" type="xsd:date"
sql:field="ObsDateTime" />
<xsd:attribute name="surfacecondition" type="xsd:string"
sql:field="SurfaceCondition"/>
<xsd:attribute name="surfacetemp" type="xsd:string"
sql:field="SurfaceTemperature"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="sysid" type="xsd:string" sql:field="SystemId"/>
<xsd:attribute name="rpuid" type="xsd:string" sql:field="RpuId"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>
//////////////////////////////////////////////////////
// Xml Data
<?xml version="1.0"?>
<odot_rwis_site_info>
<site id="200000" number="1" sysid="200" rpuid="0"
name="1-SR127 @. SR249" longitude="-84.554946" latitude="41.383527">
<atmospheric datetime="12/05/2005 03:48:00 PM"
airtemp="-490" dewpoint="-800" relativehumidity="73" windspeedavg="11"
windspeedgust="19" winddirectionavg="265" winddirectiongust="295"
pressure="65535" precipitationintensity="None" precipitationtype="None"
precipitationrate="0" precipitationaccumulation="-1" visibility="2000"
/>
<sensors>
<surface id="0" datetime="12/05/2005
03:48:00 PM" name="North Bound Driving Lane" surfacecondition="Dry"
surfacetemp="1900" freezingtemp="32767" chemicalfactor="255"
chemicalpercent="255" depth="32767" icepercent="255"
subsurfacetemp="450" waterlevel="0">
<traffic
datetime="12/05/2005 03:48:00 PM" occupancy="0" avgspeed="82"
volume="21" sftemp="1900" sfstate="255">
<normalbins>
<bin datetime="12/05/2005 03:48:00 PM" binnumber="0" bincount="7"
/>
<bin datetime="12/05/2005 03:48:00 PM" binnumber="1" bincount="0"
/>
</normalbins>
<longbins>
<bin datetime="12/05/2005 03:48:00 PM" binnumber="2" bincount="0"
/>
<bin datetime="12/05/2005 03:48:00 PM" binnumber="3" bincount="0"
/>
<bin datetime="12/05/2005 03:48:00 PM" binnumber="4" bincount="1"
/>
<bin datetime="12/05/2005 03:48:00 PM" binnumber="5" bincount="0"
/>
</longbins>
</traffic>
</surface>
<surface id="1" datetime="12/05/2005
03:48:00 PM" name="Bridge Deck Simulator" surfacecondition="Other"
surfacetemp="-60" freezingtemp="32767" chemicalfactor="255"
chemicalpercent="255" depth="32767" icepercent="255"
subsurfacetemp="-999999" waterlevel="0" />
</sensors>
</site>
<site id="200001" number="2" sysid="200" rpuid="1"
name="2-Hardin County Garage" longitude="-83.6148588"
latitude="40.6305358">
<atmospheric datetime="12/05/2005 03:49:00 PM"
airtemp="-590" dewpoint="-900" relativehumidity="75" windspeedavg="9"
windspeedgust="22" winddirectionavg="303" winddirectiongust="299"
pressure="65535" precipitationintensity="None" precipitationtype="None"
precipitationrate="0" precipitationaccumulation="-1" visibility="2000"
/>
<sensors>
<surface id="0" datetime="12/05/2005
03:49:00 PM" name="Bridge Deck Simulator" surfacecondition="Other"
surfacetemp="-410" freezingtemp="32767" chemicalfactor="255"
chemicalpercent="255" depth="32767" icepercent="255"
subsurfacetemp="300" waterlevel="0" />
</sensors>
</site>
<site id="200002" number="3" sysid="200" rpuid="2"
name="3-US24 @. Indiana Line" longitude="-84.769597"
latitude="41.17308">
<atmospheric datetime="12/05/2005 03:50:00 PM"
airtemp="-500" dewpoint="-900" relativehumidity="68" windspeedavg="9"
windspeedgust="19" winddirectionavg="279" winddirectiongust="313"
pressure="65535" precipitationintensity="Light"
precipitationtype="Snow" precipitationrate="115"
precipitationaccumulation="-1" visibility="2000" />
<sensors>
<surface id="0" datetime="12/05/2005
03:50:00 PM" name="North Bound Driving Lane" surfacecondition="Dry"
surfacetemp="-100" freezingtemp="32767" chemicalfactor="255"
chemicalpercent="255" depth="32767" icepercent="255"
subsurfacetemp="160" waterlevel="0">
<traffic
datetime="12/05/2005 03:50:00 PM" occupancy="1" avgspeed="89"
volume="16" sftemp="-100" sfstate="255">
<normalbins>
<bin datetime="12/05/2005 03:50:00 PM" binnumber="0" bincount="8"
/>
<bin datetime="12/05/2005 03:50:00 PM" binnumber="1" bincount="0"
/>
</normalbins>
<longbins>
<bin datetime="12/05/2005 03:50:00 PM" binnumber="2" bincount="0"
/>
<bin datetime="12/05/2005 03:50:00 PM" binnumber="3" bincount="0"
/>
<bin datetime="12/05/2005 03:50:00 PM" binnumber="4" bincount="1"
/>
<bin datetime="12/05/2005 03:50:00 PM" binnumber="5" bincount="7"
/>
</longbins>
</traffic>
</surface>
<surface id="1" datetime="12/05/2005
03:50:00 PM" name="Bridge Deck Simulator" surfacecondition="Other"
surfacetemp="-130" freezingtemp="32767" chemicalfactor="255"
chemicalpercent="255" depth="32767" icepercent="255"
subsurfacetemp="-999999" waterlevel="0" />
</sensors>
</site>
<site id="200003" number="4" sysid="200" rpuid="3"
name="4-US224 @. SR190" longitude="-84.272065" latitude="40.94717">
<atmospheric datetime="12/05/2005 03:45:00 PM"
airtemp="-670" dewpoint="-900" relativehumidity="82" windspeedavg="6"
windspeedgust="20" winddirectionavg="216" winddirectiongust="275"
pressure="65535" precipitationintensity="None" precipitationtype="None"
precipitationrate="0" precipitationaccumulation="-1" visibility="2000"
/>
<sensors>
<surface id="0" datetime="12/05/2005
03:45:00 PM" name="North Bound Driving Lane" surfacecondition="Dry"
surfacetemp="100" freezingtemp="32767" chemicalfactor="255"
chemicalpercent="255" depth="32767" icepercent="255"
subsurfacetemp="300" waterlevel="0">
<traffic
datetime="12/05/2005 03:45:00 PM" occupancy="0" avgspeed="108"
volume="11" sftemp="100" sfstate="255">
<normalbins>
<bin datetime="12/05/2005 03:45:00 PM" binnumber="0" bincount="10"
/>
<bin datetime="12/05/2005 03:45:00 PM" binnumber="1" bincount="0"
/>
</normalbins>
<longbins>
<bin datetime="12/05/2005 03:45:00 PM" binnumber="2" bincount="0"
/>
<bin datetime="12/05/2005 03:45:00 PM" binnumber="3" bincount="0"
/>
<bin datetime="12/05/2005 03:45:00 PM" binnumber="4" bincount="1"
/>
<bin datetime="12/05/2005 03:45:00 PM" binnumber="5" bincount="0"
/>
</longbins>
</traffic>
</surface>
<surface id="1" datetime="12/05/2005
03:45:00 PM" name="Bridge Deck Simulator" surfacecondition="Other"
surfacetemp="-310" freezingtemp="32767" chemicalfactor="255"
chemicalpercent="255" depth="32767" icepercent="255"
subsurfacetemp="-999999" waterlevel="0" />
</sensors>
</site>
</odot_rwis_site_info>
It appears that the SensorId's are NULL in the rows corresponding to the "site" elements. The way your mapping is written, each 'site' element gets its own row in MacgowanTestRWISRawSurface, and each surface element gets its own row in MacgowanTestRWISRawSurface. The NULLs show up because "site" doesnt have SensorID mapped anywhere.
Bulk Load XML into SQL using Schema File ...
Hi ...
I am bulk loading XML data into SQL using SQLXMLBulkLoad.SQLXMLBulkload.3.0 COM Object. To do this I provide the xml data and a schema file (below).
When I use the attached schema file I get the following error:
No data was provided for column 'SensorId' on table 'MacgowanTestRWISRawSurface', and this column cannot contain NULL values.
I changed the table definition to allow nulls in the SensorId column and the bulk load was successful. Notice on the records in the MacgowanTestRWISRawSurface table … where are the records with the NULL SensorId(s) in the XML data ? Is this is a side effect of my self-join with the MacgowanTestRWISRawSurface table ?
Any comments?
Thanks,
Chris
I am bulk loading XML data into SQL using SQLXMLBulkLoad.SQLXMLBulkload.3.0 COM Object. To do this I provide the xml data and a schema file (below).
When I use the attached schema file I get the following error:
No data was provided for column 'SensorId' on table 'MacgowanTestRWISRawSurface', and this column cannot contain NULL values.
I changed the table definition to allow nulls in the SensorId column and the bulk load was successful. Notice on the records in the MacgowanTestRWISRawSurface table … where are the records with the NULL SensorId(s) in the XML data ? Is this is a side effect of my self-join with the MacgowanTestRWISRawSurface table ?
Any comments?
Thanks,
Chris
[Alphanumericdata].[dbo].[MacgowanTestRWISRawAtmospheric]
RecordId DataSourceId ProductInstanceId SystemId RpuId SensorId ObsDateTime
-- -- -- --
1 OH 5abbbc86-fb2c-4703-9589-b55f763ee150 200 0 NULL NULL
2 OH 5abbbc86-fb2c-4703-9589-b55f763ee150 200 1 NULL NULL
3 OH 5abbbc86-fb2c-4703-9589-b55f763ee150 200 2 NULL NULL
4 OH 5abbbc86-fb2c-4703-9589-b55f763ee150 200 3 NULL NULL
[Alphanumericdata].[dbo].[MacgowanTestRWISRawSurface]
RecordId DataSourceId SystemId RpuId SensorId ObsDateTime
-- -- --
1 OH 200 0 0 2005-12-05 15:48:00.000
2 OH 200 0 1 2005-12-05 15:48:00.000
3 OH 200 0 NULL NULL
4 OH 200 1 0 2005-12-05 15:49:00.000
5 OH 200 1 NULL NULL
6 OH 200 2 0 2005-12-05 15:50:00.000
7 OH 200 2 1 2005-12-05 15:50:00.000
8 OH 200 2 NULL NULL
9 OH 200 3 0 2005-12-05 15:45:00.000
10 OH 200 3 1 2005-12-05 15:45:00.000
11 OH 200 3 NULL NULL
//////////////////////////////////////////////////////
// Schema File
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="MacgowanTest_Atmospheric_Surface"
parent="MacgowanTestRWISRawAtmospheric"
parent-key="SystemId RpuId"
child="MacgowanTestRWISRawSurface"
child-key="SystemId RpuId" />
</xsd:appinfo>
</xsd:annotation>
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="MacgowanTest_Surface_Surface"
parent="MacgowanTestRWISRawSurface"
parent-key="SystemId RpuId"
child="MacgowanTestRWISRawSurface"
child-key="SystemId RpuId" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="site" sql:relation="MacgowanTestRWISRawAtmospheric" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="sensors" sql:relation="MacgowanTestRWISRawSurface"
sql:relationship="MacgowanTest_Atmospheric_Surface">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="surface" sql:relation="MacgowanTestRWISRawSurface"
sql:relationship="MacgowanTest_Surface_Surface" >
<xsd:complexType>
<xsd:attribute name="id" type="xsd:string" sql:field="SensorId"/>
<xsd:attribute name="datetime" type="xsd:date" sql:field="ObsDateTime" />
<xsd:attribute name="surfacecondition" type="xsd:string" sql:field="SurfaceCondition"/>
<xsd:attribute name="surfacetemp" type="xsd:string" sql:field="SurfaceTemperature"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="sysid" type="xsd:string" sql:field="SystemId"/>
<xsd:attribute name="rpuid" type="xsd:string" sql:field="RpuId"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>
//////////////////////////////////////////////////////
// Xml Data
<?xml version="1.0"?>
<odot_rwis_site_info>
<site id="200000" number="1" sysid="200" rpuid="0" name="1-SR127 @. SR249" longitude="-84.554946" latitude="41.383527">
<atmospheric datetime="12/05/2005 03:48:00 PM" airtemp="-490" dewpoint="-800" relativehumidity="73" windspeedavg="11" windspeedgust="19" winddirectionavg="265" winddirectiongust="295" pressure="65535" precipitationintensity="None" precipitationtype="None" precipitationrate="0" precipitationaccumulation="-1" visibility="2000" />
<sensors>
<surface id="0" datetime="12/05/2005 03:48:00 PM" name="North Bound Driving Lane" surfacecondition="Dry" surfacetemp="1900" freezingtemp="32767" chemicalfactor="255" chemicalpercent="255" depth="32767" icepercent="255" subsurfacetemp="450" waterlevel="0">
<traffic datetime="12/05/2005 03:48:00 PM" occupancy="0" avgspeed="82" volume="21" sftemp="1900" sfstate="255">
<normalbins>
<bin datetime="12/05/2005 03:48:00 PM" binnumber="0" bincount="7" />
<bin datetime="12/05/2005 03:48:00 PM" binnumber="1" bincount="0" />
</normalbins>
<longbins>
<bin datetime="12/05/2005 03:48:00 PM" binnumber="2" bincount="0" />
<bin datetime="12/05/2005 03:48:00 PM" binnumber="3" bincount="0" />
<bin datetime="12/05/2005 03:48:00 PM" binnumber="4" bincount="1" />
<bin datetime="12/05/2005 03:48:00 PM" binnumber="5" bincount="0" />
</longbins>
</traffic>
</surface>
<surface id="1" datetime="12/05/2005 03:48:00 PM" name="Bridge Deck Simulator" surfacecondition="Other" surfacetemp="-60" freezingtemp="32767" chemicalfactor="255" chemicalpercent="255" depth="32767" icepercent="255" subsurfacetemp="-999999" waterlevel="0" />
</sensors>
</site>
<site id="200001" number="2" sysid="200" rpuid="1" name="2-Hardin County Garage" longitude="-83.6148588" latitude="40.6305358">
<atmospheric datetime="12/05/2005 03:49:00 PM" airtemp="-590" dewpoint="-900" relativehumidity="75" windspeedavg="9" windspeedgust="22" winddirectionavg="303" winddirectiongust="299" pressure="65535" precipitationintensity="None" precipitationtype="None" precipitationrate="0" precipitationaccumulation="-1" visibility="2000" />
<sensors>
<surface id="0" datetime="12/05/2005 03:49:00 PM" name="Bridge Deck Simulator" surfacecondition="Other" surfacetemp="-410" freezingtemp="32767" chemicalfactor="255" chemicalpercent="255" depth="32767" icepercent="255" subsurfacetemp="300" waterlevel="0" />
</sensors>
</site>
<site id="200002" number="3" sysid="200" rpuid="2" name="3-US24 @. Indiana Line" longitude="-84.769597" latitude="41.17308">
<atmospheric datetime="12/05/2005 03:50:00 PM" airtemp="-500" dewpoint="-900" relativehumidity="68" windspeedavg="9" windspeedgust="19" winddirectionavg="279" winddirectiongust="313" pressure="65535" precipitationintensity="Light" precipitationtype="Snow" precipitationrate="115" precipitationaccumulation="-1" visibility="2000" />
<sensors>
<surface id="0" datetime="12/05/2005 03:50:00 PM" name="North Bound Driving Lane" surfacecondition="Dry" surfacetemp="-100" freezingtemp="32767" chemicalfactor="255" chemicalpercent="255" depth="32767" icepercent="255" subsurfacetemp="160" waterlevel="0">
<traffic datetime="12/05/2005 03:50:00 PM" occupancy="1" avgspeed="89" volume="16" sftemp="-100" sfstate="255">
<normalbins>
<bin datetime="12/05/2005 03:50:00 PM" binnumber="0" bincount="8" />
<bin datetime="12/05/2005 03:50:00 PM" binnumber="1" bincount="0" />
</normalbins>
<longbins>
<bin datetime="12/05/2005 03:50:00 PM" binnumber="2" bincount="0" />
<bin datetime="12/05/2005 03:50:00 PM" binnumber="3" bincount="0" />
<bin datetime="12/05/2005 03:50:00 PM" binnumber="4" bincount="1" />
<bin datetime="12/05/2005 03:50:00 PM" binnumber="5" bincount="7" />
</longbins>
</traffic>
</surface>
<surface id="1" datetime="12/05/2005 03:50:00 PM" name="Bridge Deck Simulator" surfacecondition="Other" surfacetemp="-130" freezingtemp="32767" chemicalfactor="255" chemicalpercent="255" depth="32767" icepercent="255" subsurfacetemp="-999999" waterlevel="0" />
</sensors>
</site>
<site id="200003" number="4" sysid="200" rpuid="3" name="4-US224 @. SR190" longitude="-84.272065" latitude="40.94717">
<atmospheric datetime="12/05/2005 03:45:00 PM" airtemp="-670" dewpoint="-900" relativehumidity="82" windspeedavg="6" windspeedgust="20" winddirectionavg="216" winddirectiongust="275" pressure="65535" precipitationintensity="None" precipitationtype="None" precipitationrate="0" precipitationaccumulation="-1" visibility="2000" />
<sensors>
<surface id="0" datetime="12/05/2005 03:45:00 PM" name="North Bound Driving Lane" surfacecondition="Dry" surfacetemp="100" freezingtemp="32767" chemicalfactor="255" chemicalpercent="255" depth="32767" icepercent="255" subsurfacetemp="300" waterlevel="0">
<traffic datetime="12/05/2005 03:45:00 PM" occupancy="0" avgspeed="108" volume="11" sftemp="100" sfstate="255">
<normalbins>
<bin datetime="12/05/2005 03:45:00 PM" binnumber="0" bincount="10" />
<bin datetime="12/05/2005 03:45:00 PM" binnumber="1" bincount="0" />
</normalbins>
<longbins>
<bin datetime="12/05/2005 03:45:00 PM" binnumber="2" bincount="0" />
<bin datetime="12/05/2005 03:45:00 PM" binnumber="3" bincount="0" />
<bin datetime="12/05/2005 03:45:00 PM" binnumber="4" bincount="1" />
<bin datetime="12/05/2005 03:45:00 PM" binnumber="5" bincount="0" />
</longbins>
</traffic>
</surface>
<surface id="1" datetime="12/05/2005 03:45:00 PM" name="Bridge Deck Simulator" surfacecondition="Other" surfacetemp="-310" freezingtemp="32767" chemicalfactor="255" chemicalpercent="255" depth="32767" icepercent="255" subsurfacetemp="-999999" waterlevel="0" />
</sensors>
</site>
</odot_rwis_site_info>
It appears that the SensorId's are NULL in the rows corresponding to the "site" elements. The way your mapping is written, each 'site' element gets its own row in MacgowanTestRWISRawSurface, and each surface element gets its own row in MacgowanTestRWISRawSurface. The NULLs show up because "site" doesnt have SensorID mapped anywhere.
Bulk Load XML file to SQL Server (Express) Table
Hi All,
I have an asp.net 2.0 app that needs to bulk load data from an xml file into a Sql Server (Express) table. Is there an easy way to do this?
Thanks,
Claude.
Here is a working sample from Microsoft. Hope this helps.
http://support.microsoft.com/default.aspx/kb/316005/en-us
|||Thanks for your reply.
Are there any examples of doing this in asp.net 2.0 using version 2 of the .Net framework? I am currently writing my app in c#.
TIA,
Claude.
|||
The data is going into SQL Server so you have to use XML SQL Server will accept. The links below covers SQL Server 2005 XML. Hope this helps.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp
http://msdn2.microsoft.com/en-us/library/ms191184.aspx
sqlBulk load XML file (SQL2005)
I have saved a dataset as xml on the disk. I want to bulk load the xml file
into a table in the SQL server 2005. I have checked out openrowset (t-sql)
but it seems to me that it can only load data in one column of type xml of a
table. I
dont want to use sp_xml_preparedocument to create an in memory
representation of the xml file.
I know I can use SQLXML in my code but is'nt the functionallity also in the
new System.Data.SqlXml namespace? I have found very little info about this.
Any ideas? Other solutions?
Regards Andreas :-)
PS! I use VS.NET with ADO.NET 2.0, C# and SQL Server 2005 CTP in my project.
Hi,
You can use SQLXMLBulkload component through Interop.
Here is a reference you can use to work from :
http://msdn.microsoft.com/library/de...us/dnanchor/ht
ml/anch_SQLXML.asp
Thanks,
Monica Frintu
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
|||System.Data.Sqlxml will not ship with Visual Studio.Net 2005. So it is not
recommended to use that.
Use SQLXML 3.0 native components through interop instead.
thanks
Chandra
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"AWHK" <awhk@.newsgroup.nospam> wrote in message
news:eNvvyJQcFHA.2696@.TK2MSFTNGP09.phx.gbl...
>I have saved a dataset as xml on the disk. I want to bulk load the xml file
> into a table in the SQL server 2005. I have checked out openrowset (t-sql)
> but it seems to me that it can only load data in one column of type xml of
> a table. I
> dont want to use sp_xml_preparedocument to create an in memory
> representation of the xml file.
> I know I can use SQLXML in my code but is'nt the functionallity also in
> the
> new System.Data.SqlXml namespace? I have found very little info about
> this.
> Any ideas? Other solutions?
>
> Regards Andreas :-)
>
> PS! I use VS.NET with ADO.NET 2.0, C# and SQL Server 2005 CTP in my
> project.
>
>
|||Here is a whitepaper that explains using SqlXml3 Bulkload in .Net:
http://msdn.microsoft.com/library/de...exchsqlxml.asp
--
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"AWHK" <awhk@.newsgroup.nospam> wrote in message
news:eNvvyJQcFHA.2696@.TK2MSFTNGP09.phx.gbl...
>I have saved a dataset as xml on the disk. I want to bulk load the xml file
> into a table in the SQL server 2005. I have checked out openrowset (t-sql)
> but it seems to me that it can only load data in one column of type xml of
> a table. I
> dont want to use sp_xml_preparedocument to create an in memory
> representation of the xml file.
> I know I can use SQLXML in my code but is'nt the functionallity also in
> the
> new System.Data.SqlXml namespace? I have found very little info about
> this.
> Any ideas? Other solutions?
>
> Regards Andreas :-)
>
> PS! I use VS.NET with ADO.NET 2.0, C# and SQL Server 2005 CTP in my
> project.
>
>
into a table in the SQL server 2005. I have checked out openrowset (t-sql)
but it seems to me that it can only load data in one column of type xml of a
table. I
dont want to use sp_xml_preparedocument to create an in memory
representation of the xml file.
I know I can use SQLXML in my code but is'nt the functionallity also in the
new System.Data.SqlXml namespace? I have found very little info about this.
Any ideas? Other solutions?
Regards Andreas :-)
PS! I use VS.NET with ADO.NET 2.0, C# and SQL Server 2005 CTP in my project.
Hi,
You can use SQLXMLBulkload component through Interop.
Here is a reference you can use to work from :
http://msdn.microsoft.com/library/de...us/dnanchor/ht
ml/anch_SQLXML.asp
Thanks,
Monica Frintu
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
|||System.Data.Sqlxml will not ship with Visual Studio.Net 2005. So it is not
recommended to use that.
Use SQLXML 3.0 native components through interop instead.
thanks
Chandra
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"AWHK" <awhk@.newsgroup.nospam> wrote in message
news:eNvvyJQcFHA.2696@.TK2MSFTNGP09.phx.gbl...
>I have saved a dataset as xml on the disk. I want to bulk load the xml file
> into a table in the SQL server 2005. I have checked out openrowset (t-sql)
> but it seems to me that it can only load data in one column of type xml of
> a table. I
> dont want to use sp_xml_preparedocument to create an in memory
> representation of the xml file.
> I know I can use SQLXML in my code but is'nt the functionallity also in
> the
> new System.Data.SqlXml namespace? I have found very little info about
> this.
> Any ideas? Other solutions?
>
> Regards Andreas :-)
>
> PS! I use VS.NET with ADO.NET 2.0, C# and SQL Server 2005 CTP in my
> project.
>
>
|||Here is a whitepaper that explains using SqlXml3 Bulkload in .Net:
http://msdn.microsoft.com/library/de...exchsqlxml.asp
--
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"AWHK" <awhk@.newsgroup.nospam> wrote in message
news:eNvvyJQcFHA.2696@.TK2MSFTNGP09.phx.gbl...
>I have saved a dataset as xml on the disk. I want to bulk load the xml file
> into a table in the SQL server 2005. I have checked out openrowset (t-sql)
> but it seems to me that it can only load data in one column of type xml of
> a table. I
> dont want to use sp_xml_preparedocument to create an in memory
> representation of the xml file.
> I know I can use SQLXML in my code but is'nt the functionallity also in
> the
> new System.Data.SqlXml namespace? I have found very little info about
> this.
> Any ideas? Other solutions?
>
> Regards Andreas :-)
>
> PS! I use VS.NET with ADO.NET 2.0, C# and SQL Server 2005 CTP in my
> project.
>
>
Bulk Load with Parent / Child failing
I'm trying to bulk load an xml with parent child nodes.
I have setup an sql:relationship and I have also setup the two database
columns as Identity columns. However when I try to load the data, SQL is
telling me that the column can not be NULL ...
Any ideas please ?
Michael Tissington
http://www.oaklodge.com
http://www.sqlview.netHello Michael,
Thank you for using Microsoft MSDN newsgroup!
I understand you encountered the issue when importing xml with parent/child
nodes. To get a better idea of the exact issue, will you send the xml files
and script to create the tables in database to me so that I may reproduce
the issue on my side? You could send it to me at petery@.microsoft.com.
Also, you may want to refer to the following article for some know issue:
330582 PRB: "No Data Was Provided for Column" Error Message When You
Propagate Identity Column with SQLXMLBulkLoad
http://support.microsoft.com/defaul...kb;EN-US;330582
I look forward to your reply.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
I have setup an sql:relationship and I have also setup the two database
columns as Identity columns. However when I try to load the data, SQL is
telling me that the column can not be NULL ...
Any ideas please ?
Michael Tissington
http://www.oaklodge.com
http://www.sqlview.netHello Michael,
Thank you for using Microsoft MSDN newsgroup!
I understand you encountered the issue when importing xml with parent/child
nodes. To get a better idea of the exact issue, will you send the xml files
and script to create the tables in database to me so that I may reproduce
the issue on my side? You could send it to me at petery@.microsoft.com.
Also, you may want to refer to the following article for some know issue:
330582 PRB: "No Data Was Provided for Column" Error Message When You
Propagate Identity Column with SQLXMLBulkLoad
http://support.microsoft.com/defaul...kb;EN-US;330582
I look forward to your reply.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
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...
>
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...
>
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...
>
Tuesday, March 27, 2012
Bulk Load Text File Using Transact-SQL
I am a newbie (not a developer) trying to load reference tables with SQL script from text files. So far, I know I must use format files but the examples I get are not clear. I cannot find a simple syntax for the format file or the bulk load script. Please help!
Does the following topics help? BOL (SQL Server 2000 or 2005) has lot of examples including format files, statements to use etc.
http://msdn2.microsoft.com/en-us/library/ms189989.aspx
http://msdn2.microsoft.com/en-us/library/aa337544.aspx
http://msdn2.microsoft.com/en-us/library/ms175915.aspx
http://msdn2.microsoft.com/en-us/library/ms178129.aspx
http://msdn2.microsoft.com/en-us/library/ms189848.aspx
http://msdn2.microsoft.com/en-us/library/ms190625.aspx
sqlBulk Load Strange XML Format
I have an application that exports an XML file in a strange format. I would like to be able to upload this file into an SQL 2000 database using Bulk Load. I have been able to complete some of the various examples and have a handle on what I need to do in the Bulk Load.
The problem I have is understanding how to load the data (in the form the application exports) into a relational format. My data does not contain any indexes and has multiple tables that will need to be created. A snippet of the XML is copied below:
================================================== ==============================
<Round Cn="Amherst Golf Course" Cc="Amherst" Cs="NS" Cy="CA" St="-1030918208" Et="-1030902963" Sf="0" Pt="31" Gm="3" Op="0">
<Rp N="Steve Peddle" S="STEVE" Hv="25" Te="1">
<Sc h1="7" h2="6" h3="6" h4="3" h5="7" h6="6" h7="6" h8="5" h9="5" h10="5" h11="6" h12="5" h13="3" h14="6" h15="4" h16="6" h17="5" h18="6"/>
<Pt h1="1" h2="2" h3="2" h4="1" h5="2" h6="1" h7="2" h8="1" h9="2" h10="2" h11="2" h12="2" h13="1" h14="2" h15="2" h16="2" h17="2" h18="1"/>
</Rp>
<Rp N="Chris White" S="Chris" Hv="18" Te="1">
<Sc h1="7" h2="5" h3="5" h4="3" h5="8" h6="4" h7="5" h8="6" h9="4" h10="5" h11="6" h12="5" h13="4" h14="6" h15="4" h16="5" h17="5" h18="7"/>
<Pt h1="2" h2="2" h3="1" h4="1" h5="3" h6="1" h7="2" h8="3" h9="2" h10="2" h11="2" h12="2" h13="2" h14="2" h15="2" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Scott McKillop" S="Scott" Hv="14" Te="1">
<Sc h1="7" h2="5" h3="4" h4="3" h5="7" h6="5" h7="5" h8="6" h9="4" h10="5" h11="5" h12="5" h13="4" h14="4" h15="4" h16="4" h17="5" h18="7"/>
<Pt h1="1" h2="1" h3="2" h4="2" h5="3" h6="2" h7="1" h8="2" h9="1" h10="2" h11="2" h12="3" h13="2" h14="1" h15="3" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Steve Rayworth" S="SteveR" Hv="13" Te="1">
<Sc h1="6" h2="5" h3="5" h4="3" h5="5" h6="6" h7="3" h8="5" h9="3" h10="6" h11="4" h12="5" h13="3" h14="5" h15="3" h16="4" h17="4" h18="7"/>
<Pt h1="2" h2="2" h3="2" h4="2" h5="2" h6="2" h7="1" h8="2" h9="2" h10="3" h11="1" h12="2" h13="1" h14="2" h15="2" h16="1" h17="2" h18="2"/>
<Ev h1="0" h2="0" h3="0" h4="0" h5="0" h6="0" h7="0" h8="0" h9="4" h10="0" h11="0" h12="0" h13="0" h14="0" h15="0" h16="0" h17="0" h18="0"/>
</Rp>
<Games>
<Gm I="0" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="17" M="0" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="11" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="12" H2="5" H3="1" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
</Games>
</Round>
================================================== ================================================== =============================
The application is actually used to track golf rounds.
a.. Each round can have many players;
a.. Each Player wil have 1 Score (<sc>)
b.. Each Player will have 1 Putt (<Pt>)
c.. Each Player will have 1 Ev? (<Ev>)
b.. Each Round can have 1 Games
a.. Each Games can have multiple Gm's (<Gm>)
The problem with this structure is that there are no indexes in the file, and from what I understand about the Bulk Load and relational databases, is that it will be next to impossible to link the data properly after importing.
Is there any way that this can be imported into SQL 2000? Will I have to write an application using VB to manually create the indexes and then import? The solution that I come up with will have to be repeatable as the data will need to be updated with a new XML file often.
If the entire XML file will help with understanding the scope of the issue, I can attach it.
Thanks in advance
Do golfers have ID's or anything? If not, can you just add an ID column that will auto-generate and id for each row? You could then relate them using that.
Irwin Dolobowsky
Program Manager - SqlXml
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott McKillop" <scott_mckillop_NOSPAM_MAN@.adlt.com_REMOVE_CAPS> wrote in message news:ubT1rwsMEHA.3420@.TK2MSFTNGP11.phx.gbl...
I have an application that exports an XML file in a strange format. I would like to be able to upload this file into an SQL 2000 database using Bulk Load. I have been able to complete some of the various examples and have a handle on what I need to do in the Bulk Load.
The problem I have is understanding how to load the data (in the form the application exports) into a relational format. My data does not contain any indexes and has multiple tables that will need to be created. A snippet of the XML is copied below:
================================================== ==============================
<Round Cn="Amherst Golf Course" Cc="Amherst" Cs="NS" Cy="CA" St="-1030918208" Et="-1030902963" Sf="0" Pt="31" Gm="3" Op="0">
<Rp N="Steve Peddle" S="STEVE" Hv="25" Te="1">
<Sc h1="7" h2="6" h3="6" h4="3" h5="7" h6="6" h7="6" h8="5" h9="5" h10="5" h11="6" h12="5" h13="3" h14="6" h15="4" h16="6" h17="5" h18="6"/>
<Pt h1="1" h2="2" h3="2" h4="1" h5="2" h6="1" h7="2" h8="1" h9="2" h10="2" h11="2" h12="2" h13="1" h14="2" h15="2" h16="2" h17="2" h18="1"/>
</Rp>
<Rp N="Chris White" S="Chris" Hv="18" Te="1">
<Sc h1="7" h2="5" h3="5" h4="3" h5="8" h6="4" h7="5" h8="6" h9="4" h10="5" h11="6" h12="5" h13="4" h14="6" h15="4" h16="5" h17="5" h18="7"/>
<Pt h1="2" h2="2" h3="1" h4="1" h5="3" h6="1" h7="2" h8="3" h9="2" h10="2" h11="2" h12="2" h13="2" h14="2" h15="2" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Scott McKillop" S="Scott" Hv="14" Te="1">
<Sc h1="7" h2="5" h3="4" h4="3" h5="7" h6="5" h7="5" h8="6" h9="4" h10="5" h11="5" h12="5" h13="4" h14="4" h15="4" h16="4" h17="5" h18="7"/>
<Pt h1="1" h2="1" h3="2" h4="2" h5="3" h6="2" h7="1" h8="2" h9="1" h10="2" h11="2" h12="3" h13="2" h14="1" h15="3" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Steve Rayworth" S="SteveR" Hv="13" Te="1">
<Sc h1="6" h2="5" h3="5" h4="3" h5="5" h6="6" h7="3" h8="5" h9="3" h10="6" h11="4" h12="5" h13="3" h14="5" h15="3" h16="4" h17="4" h18="7"/>
<Pt h1="2" h2="2" h3="2" h4="2" h5="2" h6="2" h7="1" h8="2" h9="2" h10="3" h11="1" h12="2" h13="1" h14="2" h15="2" h16="1" h17="2" h18="2"/>
<Ev h1="0" h2="0" h3="0" h4="0" h5="0" h6="0" h7="0" h8="0" h9="4" h10="0" h11="0" h12="0" h13="0" h14="0" h15="0" h16="0" h17="0" h18="0"/>
</Rp>
<Games>
<Gm I="0" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="17" M="0" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="11" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="12" H2="5" H3="1" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
</Games>
</Round>
================================================== ================================================== =============================
The application is actually used to track golf rounds.
a.. Each round can have many players;
a.. Each Player wil have 1 Score (<sc>)
b.. Each Player will have 1 Putt (<Pt>)
c.. Each Player will have 1 Ev? (<Ev>)
b.. Each Round can have 1 Games
a.. Each Games can have multiple Gm's (<Gm>)
The problem with this structure is that there are no indexes in the file, and from what I understand about the Bulk Load and relational databases, is that it will be next to impossible to link the data properly after importing.
Is there any way that this can be imported into SQL 2000? Will I have to write an application using VB to manually create the indexes and then import? The solution that I come up with will have to be repeatable as the data will need to be updated with a new XML file often.
If the entire XML file will help with understanding the scope of the issue, I can attach it.
Thanks in advance
|||Thanks for your response! I think I may have stumbled upon something that will help... I was finding mixed documents on the internet regarding the Bulk Load functionality... But I think the trick is to use Version 3.0SP2 with identities to get this to work...
I'm going to start with some easier samples and hopefully fluke my way through it. If I have a more precise and meaningful question in my experiments, I'll holler!
Thanks Again!
"Irwin Dolobowsky [MS]" <irwind@.mail.microsoft.com> wrote in message news:%23BIJuAwMEHA.3208@.TK2MSFTNGP10.phx.gbl...
Do golfers have ID's or anything? If not, can you just add an ID column that will auto-generate and id for each row? You could then relate them using that.
Irwin Dolobowsky
Program Manager - SqlXml
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott McKillop" <scott_mckillop_NOSPAM_MAN@.adlt.com_REMOVE_CAPS> wrote in message news:ubT1rwsMEHA.3420@.TK2MSFTNGP11.phx.gbl...
I have an application that exports an XML file in a strange format. I would like to be able to upload this file into an SQL 2000 database using Bulk Load. I have been able to complete some of the various examples and have a handle on what I need to do in the Bulk Load.
The problem I have is understanding how to load the data (in the form the application exports) into a relational format. My data does not contain any indexes and has multiple tables that will need to be created. A snippet of the XML is copied below:
================================================== ==============================
<Round Cn="Amherst Golf Course" Cc="Amherst" Cs="NS" Cy="CA" St="-1030918208" Et="-1030902963" Sf="0" Pt="31" Gm="3" Op="0">
<Rp N="Steve Peddle" S="STEVE" Hv="25" Te="1">
<Sc h1="7" h2="6" h3="6" h4="3" h5="7" h6="6" h7="6" h8="5" h9="5" h10="5" h11="6" h12="5" h13="3" h14="6" h15="4" h16="6" h17="5" h18="6"/>
<Pt h1="1" h2="2" h3="2" h4="1" h5="2" h6="1" h7="2" h8="1" h9="2" h10="2" h11="2" h12="2" h13="1" h14="2" h15="2" h16="2" h17="2" h18="1"/>
</Rp>
<Rp N="Chris White" S="Chris" Hv="18" Te="1">
<Sc h1="7" h2="5" h3="5" h4="3" h5="8" h6="4" h7="5" h8="6" h9="4" h10="5" h11="6" h12="5" h13="4" h14="6" h15="4" h16="5" h17="5" h18="7"/>
<Pt h1="2" h2="2" h3="1" h4="1" h5="3" h6="1" h7="2" h8="3" h9="2" h10="2" h11="2" h12="2" h13="2" h14="2" h15="2" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Scott McKillop" S="Scott" Hv="14" Te="1">
<Sc h1="7" h2="5" h3="4" h4="3" h5="7" h6="5" h7="5" h8="6" h9="4" h10="5" h11="5" h12="5" h13="4" h14="4" h15="4" h16="4" h17="5" h18="7"/>
<Pt h1="1" h2="1" h3="2" h4="2" h5="3" h6="2" h7="1" h8="2" h9="1" h10="2" h11="2" h12="3" h13="2" h14="1" h15="3" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Steve Rayworth" S="SteveR" Hv="13" Te="1">
<Sc h1="6" h2="5" h3="5" h4="3" h5="5" h6="6" h7="3" h8="5" h9="3" h10="6" h11="4" h12="5" h13="3" h14="5" h15="3" h16="4" h17="4" h18="7"/>
<Pt h1="2" h2="2" h3="2" h4="2" h5="2" h6="2" h7="1" h8="2" h9="2" h10="3" h11="1" h12="2" h13="1" h14="2" h15="2" h16="1" h17="2" h18="2"/>
<Ev h1="0" h2="0" h3="0" h4="0" h5="0" h6="0" h7="0" h8="0" h9="4" h10="0" h11="0" h12="0" h13="0" h14="0" h15="0" h16="0" h17="0" h18="0"/>
</Rp>
<Games>
<Gm I="0" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="17" M="0" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="11" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="12" H2="5" H3="1" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
</Games>
</Round>
================================================== ================================================== =============================
The application is actually used to track golf rounds.
a.. Each round can have many players;
a.. Each Player wil have 1 Score (<sc>)
b.. Each Player will have 1 Putt (<Pt>)
c.. Each Player will have 1 Ev? (<Ev>)
b.. Each Round can have 1 Games
a.. Each Games can have multiple Gm's (<Gm>)
The problem with this structure is that there are no indexes in the file, and from what I understand about the Bulk Load and relational databases, is that it will be next to impossible to link the data properly after importing.
Is there any way that this can be imported into SQL 2000? Will I have to write an application using VB to manually create the indexes and then import? The solution that I come up with will have to be repeatable as the data will need to be updated with a new XML file often.
If the entire XML file will help with understanding the scope of the issue, I can attach it.
Thanks in advance
|||That's where I was going. Glad you found it.
Irwin
Irwin Dolobowsky
Program Manager - SqlXml
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott McKillop" <smckillop@.NOSPAM.ns.sympatico.ca.REMOVECAPS> wrote in message news:%23PKm2RwMEHA.1348@.TK2MSFTNGP12.phx.gbl...
Thanks for your response! I think I may have stumbled upon something that will help... I was finding mixed documents on the internet regarding the Bulk Load functionality... But I think the trick is to use Version 3.0SP2 with identities to get this to work...
I'm going to start with some easier samples and hopefully fluke my way through it. If I have a more precise and meaningful question in my experiments, I'll holler!
Thanks Again!
"Irwin Dolobowsky [MS]" <irwind@.mail.microsoft.com> wrote in message news:%23BIJuAwMEHA.3208@.TK2MSFTNGP10.phx.gbl...
Do golfers have ID's or anything? If not, can you just add an ID column that will auto-generate and id for each row? You could then relate them using that.
Irwin Dolobowsky
Program Manager - SqlXml
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott McKillop" <scott_mckillop_NOSPAM_MAN@.adlt.com_REMOVE_CAPS> wrote in message news:ubT1rwsMEHA.3420@.TK2MSFTNGP11.phx.gbl...
I have an application that exports an XML file in a strange format. I would like to be able to upload this file into an SQL 2000 database using Bulk Load. I have been able to complete some of the various examples and have a handle on what I need to do in the Bulk Load.
The problem I have is understanding how to load the data (in the form the application exports) into a relational format. My data does not contain any indexes and has multiple tables that will need to be created. A snippet of the XML is copied below:
================================================== ==============================
<Round Cn="Amherst Golf Course" Cc="Amherst" Cs="NS" Cy="CA" St="-1030918208" Et="-1030902963" Sf="0" Pt="31" Gm="3" Op="0">
<Rp N="Steve Peddle" S="STEVE" Hv="25" Te="1">
<Sc h1="7" h2="6" h3="6" h4="3" h5="7" h6="6" h7="6" h8="5" h9="5" h10="5" h11="6" h12="5" h13="3" h14="6" h15="4" h16="6" h17="5" h18="6"/>
<Pt h1="1" h2="2" h3="2" h4="1" h5="2" h6="1" h7="2" h8="1" h9="2" h10="2" h11="2" h12="2" h13="1" h14="2" h15="2" h16="2" h17="2" h18="1"/>
</Rp>
<Rp N="Chris White" S="Chris" Hv="18" Te="1">
<Sc h1="7" h2="5" h3="5" h4="3" h5="8" h6="4" h7="5" h8="6" h9="4" h10="5" h11="6" h12="5" h13="4" h14="6" h15="4" h16="5" h17="5" h18="7"/>
<Pt h1="2" h2="2" h3="1" h4="1" h5="3" h6="1" h7="2" h8="3" h9="2" h10="2" h11="2" h12="2" h13="2" h14="2" h15="2" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Scott McKillop" S="Scott" Hv="14" Te="1">
<Sc h1="7" h2="5" h3="4" h4="3" h5="7" h6="5" h7="5" h8="6" h9="4" h10="5" h11="5" h12="5" h13="4" h14="4" h15="4" h16="4" h17="5" h18="7"/>
<Pt h1="1" h2="1" h3="2" h4="2" h5="3" h6="2" h7="1" h8="2" h9="1" h10="2" h11="2" h12="3" h13="2" h14="1" h15="3" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Steve Rayworth" S="SteveR" Hv="13" Te="1">
<Sc h1="6" h2="5" h3="5" h4="3" h5="5" h6="6" h7="3" h8="5" h9="3" h10="6" h11="4" h12="5" h13="3" h14="5" h15="3" h16="4" h17="4" h18="7"/>
<Pt h1="2" h2="2" h3="2" h4="2" h5="2" h6="2" h7="1" h8="2" h9="2" h10="3" h11="1" h12="2" h13="1" h14="2" h15="2" h16="1" h17="2" h18="2"/>
<Ev h1="0" h2="0" h3="0" h4="0" h5="0" h6="0" h7="0" h8="0" h9="4" h10="0" h11="0" h12="0" h13="0" h14="0" h15="0" h16="0" h17="0" h18="0"/>
</Rp>
<Games>
<Gm I="0" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="17" M="0" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="11" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="12" H2="5" H3="1" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
</Games>
</Round>
================================================== ================================================== =============================
The application is actually used to track golf rounds.
a.. Each round can have many players;
a.. Each Player wil have 1 Score (<sc>)
b.. Each Player will have 1 Putt (<Pt>)
c.. Each Player will have 1 Ev? (<Ev>)
b.. Each Round can have 1 Games
a.. Each Games can have multiple Gm's (<Gm>)
The problem with this structure is that there are no indexes in the file, and from what I understand about the Bulk Load and relational databases, is that it will be next to impossible to link the data properly after importing.
Is there any way that this can be imported into SQL 2000? Will I have to write an application using VB to manually create the indexes and then import? The solution that I come up with will have to be repeatable as the data will need to be updated with a new XML file often.
If the entire XML file will help with understanding the scope of the issue, I can attach it.
Thanks in advance
The problem I have is understanding how to load the data (in the form the application exports) into a relational format. My data does not contain any indexes and has multiple tables that will need to be created. A snippet of the XML is copied below:
================================================== ==============================
<Round Cn="Amherst Golf Course" Cc="Amherst" Cs="NS" Cy="CA" St="-1030918208" Et="-1030902963" Sf="0" Pt="31" Gm="3" Op="0">
<Rp N="Steve Peddle" S="STEVE" Hv="25" Te="1">
<Sc h1="7" h2="6" h3="6" h4="3" h5="7" h6="6" h7="6" h8="5" h9="5" h10="5" h11="6" h12="5" h13="3" h14="6" h15="4" h16="6" h17="5" h18="6"/>
<Pt h1="1" h2="2" h3="2" h4="1" h5="2" h6="1" h7="2" h8="1" h9="2" h10="2" h11="2" h12="2" h13="1" h14="2" h15="2" h16="2" h17="2" h18="1"/>
</Rp>
<Rp N="Chris White" S="Chris" Hv="18" Te="1">
<Sc h1="7" h2="5" h3="5" h4="3" h5="8" h6="4" h7="5" h8="6" h9="4" h10="5" h11="6" h12="5" h13="4" h14="6" h15="4" h16="5" h17="5" h18="7"/>
<Pt h1="2" h2="2" h3="1" h4="1" h5="3" h6="1" h7="2" h8="3" h9="2" h10="2" h11="2" h12="2" h13="2" h14="2" h15="2" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Scott McKillop" S="Scott" Hv="14" Te="1">
<Sc h1="7" h2="5" h3="4" h4="3" h5="7" h6="5" h7="5" h8="6" h9="4" h10="5" h11="5" h12="5" h13="4" h14="4" h15="4" h16="4" h17="5" h18="7"/>
<Pt h1="1" h2="1" h3="2" h4="2" h5="3" h6="2" h7="1" h8="2" h9="1" h10="2" h11="2" h12="3" h13="2" h14="1" h15="3" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Steve Rayworth" S="SteveR" Hv="13" Te="1">
<Sc h1="6" h2="5" h3="5" h4="3" h5="5" h6="6" h7="3" h8="5" h9="3" h10="6" h11="4" h12="5" h13="3" h14="5" h15="3" h16="4" h17="4" h18="7"/>
<Pt h1="2" h2="2" h3="2" h4="2" h5="2" h6="2" h7="1" h8="2" h9="2" h10="3" h11="1" h12="2" h13="1" h14="2" h15="2" h16="1" h17="2" h18="2"/>
<Ev h1="0" h2="0" h3="0" h4="0" h5="0" h6="0" h7="0" h8="0" h9="4" h10="0" h11="0" h12="0" h13="0" h14="0" h15="0" h16="0" h17="0" h18="0"/>
</Rp>
<Games>
<Gm I="0" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="17" M="0" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="11" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="12" H2="5" H3="1" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
</Games>
</Round>
================================================== ================================================== =============================
The application is actually used to track golf rounds.
a.. Each round can have many players;
a.. Each Player wil have 1 Score (<sc>)
b.. Each Player will have 1 Putt (<Pt>)
c.. Each Player will have 1 Ev? (<Ev>)
b.. Each Round can have 1 Games
a.. Each Games can have multiple Gm's (<Gm>)
The problem with this structure is that there are no indexes in the file, and from what I understand about the Bulk Load and relational databases, is that it will be next to impossible to link the data properly after importing.
Is there any way that this can be imported into SQL 2000? Will I have to write an application using VB to manually create the indexes and then import? The solution that I come up with will have to be repeatable as the data will need to be updated with a new XML file often.
If the entire XML file will help with understanding the scope of the issue, I can attach it.
Thanks in advance
Do golfers have ID's or anything? If not, can you just add an ID column that will auto-generate and id for each row? You could then relate them using that.
Irwin Dolobowsky
Program Manager - SqlXml
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott McKillop" <scott_mckillop_NOSPAM_MAN@.adlt.com_REMOVE_CAPS> wrote in message news:ubT1rwsMEHA.3420@.TK2MSFTNGP11.phx.gbl...
I have an application that exports an XML file in a strange format. I would like to be able to upload this file into an SQL 2000 database using Bulk Load. I have been able to complete some of the various examples and have a handle on what I need to do in the Bulk Load.
The problem I have is understanding how to load the data (in the form the application exports) into a relational format. My data does not contain any indexes and has multiple tables that will need to be created. A snippet of the XML is copied below:
================================================== ==============================
<Round Cn="Amherst Golf Course" Cc="Amherst" Cs="NS" Cy="CA" St="-1030918208" Et="-1030902963" Sf="0" Pt="31" Gm="3" Op="0">
<Rp N="Steve Peddle" S="STEVE" Hv="25" Te="1">
<Sc h1="7" h2="6" h3="6" h4="3" h5="7" h6="6" h7="6" h8="5" h9="5" h10="5" h11="6" h12="5" h13="3" h14="6" h15="4" h16="6" h17="5" h18="6"/>
<Pt h1="1" h2="2" h3="2" h4="1" h5="2" h6="1" h7="2" h8="1" h9="2" h10="2" h11="2" h12="2" h13="1" h14="2" h15="2" h16="2" h17="2" h18="1"/>
</Rp>
<Rp N="Chris White" S="Chris" Hv="18" Te="1">
<Sc h1="7" h2="5" h3="5" h4="3" h5="8" h6="4" h7="5" h8="6" h9="4" h10="5" h11="6" h12="5" h13="4" h14="6" h15="4" h16="5" h17="5" h18="7"/>
<Pt h1="2" h2="2" h3="1" h4="1" h5="3" h6="1" h7="2" h8="3" h9="2" h10="2" h11="2" h12="2" h13="2" h14="2" h15="2" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Scott McKillop" S="Scott" Hv="14" Te="1">
<Sc h1="7" h2="5" h3="4" h4="3" h5="7" h6="5" h7="5" h8="6" h9="4" h10="5" h11="5" h12="5" h13="4" h14="4" h15="4" h16="4" h17="5" h18="7"/>
<Pt h1="1" h2="1" h3="2" h4="2" h5="3" h6="2" h7="1" h8="2" h9="1" h10="2" h11="2" h12="3" h13="2" h14="1" h15="3" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Steve Rayworth" S="SteveR" Hv="13" Te="1">
<Sc h1="6" h2="5" h3="5" h4="3" h5="5" h6="6" h7="3" h8="5" h9="3" h10="6" h11="4" h12="5" h13="3" h14="5" h15="3" h16="4" h17="4" h18="7"/>
<Pt h1="2" h2="2" h3="2" h4="2" h5="2" h6="2" h7="1" h8="2" h9="2" h10="3" h11="1" h12="2" h13="1" h14="2" h15="2" h16="1" h17="2" h18="2"/>
<Ev h1="0" h2="0" h3="0" h4="0" h5="0" h6="0" h7="0" h8="0" h9="4" h10="0" h11="0" h12="0" h13="0" h14="0" h15="0" h16="0" h17="0" h18="0"/>
</Rp>
<Games>
<Gm I="0" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="17" M="0" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="11" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="12" H2="5" H3="1" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
</Games>
</Round>
================================================== ================================================== =============================
The application is actually used to track golf rounds.
a.. Each round can have many players;
a.. Each Player wil have 1 Score (<sc>)
b.. Each Player will have 1 Putt (<Pt>)
c.. Each Player will have 1 Ev? (<Ev>)
b.. Each Round can have 1 Games
a.. Each Games can have multiple Gm's (<Gm>)
The problem with this structure is that there are no indexes in the file, and from what I understand about the Bulk Load and relational databases, is that it will be next to impossible to link the data properly after importing.
Is there any way that this can be imported into SQL 2000? Will I have to write an application using VB to manually create the indexes and then import? The solution that I come up with will have to be repeatable as the data will need to be updated with a new XML file often.
If the entire XML file will help with understanding the scope of the issue, I can attach it.
Thanks in advance
|||Thanks for your response! I think I may have stumbled upon something that will help... I was finding mixed documents on the internet regarding the Bulk Load functionality... But I think the trick is to use Version 3.0SP2 with identities to get this to work...
I'm going to start with some easier samples and hopefully fluke my way through it. If I have a more precise and meaningful question in my experiments, I'll holler!
Thanks Again!
"Irwin Dolobowsky [MS]" <irwind@.mail.microsoft.com> wrote in message news:%23BIJuAwMEHA.3208@.TK2MSFTNGP10.phx.gbl...
Do golfers have ID's or anything? If not, can you just add an ID column that will auto-generate and id for each row? You could then relate them using that.
Irwin Dolobowsky
Program Manager - SqlXml
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott McKillop" <scott_mckillop_NOSPAM_MAN@.adlt.com_REMOVE_CAPS> wrote in message news:ubT1rwsMEHA.3420@.TK2MSFTNGP11.phx.gbl...
I have an application that exports an XML file in a strange format. I would like to be able to upload this file into an SQL 2000 database using Bulk Load. I have been able to complete some of the various examples and have a handle on what I need to do in the Bulk Load.
The problem I have is understanding how to load the data (in the form the application exports) into a relational format. My data does not contain any indexes and has multiple tables that will need to be created. A snippet of the XML is copied below:
================================================== ==============================
<Round Cn="Amherst Golf Course" Cc="Amherst" Cs="NS" Cy="CA" St="-1030918208" Et="-1030902963" Sf="0" Pt="31" Gm="3" Op="0">
<Rp N="Steve Peddle" S="STEVE" Hv="25" Te="1">
<Sc h1="7" h2="6" h3="6" h4="3" h5="7" h6="6" h7="6" h8="5" h9="5" h10="5" h11="6" h12="5" h13="3" h14="6" h15="4" h16="6" h17="5" h18="6"/>
<Pt h1="1" h2="2" h3="2" h4="1" h5="2" h6="1" h7="2" h8="1" h9="2" h10="2" h11="2" h12="2" h13="1" h14="2" h15="2" h16="2" h17="2" h18="1"/>
</Rp>
<Rp N="Chris White" S="Chris" Hv="18" Te="1">
<Sc h1="7" h2="5" h3="5" h4="3" h5="8" h6="4" h7="5" h8="6" h9="4" h10="5" h11="6" h12="5" h13="4" h14="6" h15="4" h16="5" h17="5" h18="7"/>
<Pt h1="2" h2="2" h3="1" h4="1" h5="3" h6="1" h7="2" h8="3" h9="2" h10="2" h11="2" h12="2" h13="2" h14="2" h15="2" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Scott McKillop" S="Scott" Hv="14" Te="1">
<Sc h1="7" h2="5" h3="4" h4="3" h5="7" h6="5" h7="5" h8="6" h9="4" h10="5" h11="5" h12="5" h13="4" h14="4" h15="4" h16="4" h17="5" h18="7"/>
<Pt h1="1" h2="1" h3="2" h4="2" h5="3" h6="2" h7="1" h8="2" h9="1" h10="2" h11="2" h12="3" h13="2" h14="1" h15="3" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Steve Rayworth" S="SteveR" Hv="13" Te="1">
<Sc h1="6" h2="5" h3="5" h4="3" h5="5" h6="6" h7="3" h8="5" h9="3" h10="6" h11="4" h12="5" h13="3" h14="5" h15="3" h16="4" h17="4" h18="7"/>
<Pt h1="2" h2="2" h3="2" h4="2" h5="2" h6="2" h7="1" h8="2" h9="2" h10="3" h11="1" h12="2" h13="1" h14="2" h15="2" h16="1" h17="2" h18="2"/>
<Ev h1="0" h2="0" h3="0" h4="0" h5="0" h6="0" h7="0" h8="0" h9="4" h10="0" h11="0" h12="0" h13="0" h14="0" h15="0" h16="0" h17="0" h18="0"/>
</Rp>
<Games>
<Gm I="0" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="17" M="0" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="11" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="12" H2="5" H3="1" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
</Games>
</Round>
================================================== ================================================== =============================
The application is actually used to track golf rounds.
a.. Each round can have many players;
a.. Each Player wil have 1 Score (<sc>)
b.. Each Player will have 1 Putt (<Pt>)
c.. Each Player will have 1 Ev? (<Ev>)
b.. Each Round can have 1 Games
a.. Each Games can have multiple Gm's (<Gm>)
The problem with this structure is that there are no indexes in the file, and from what I understand about the Bulk Load and relational databases, is that it will be next to impossible to link the data properly after importing.
Is there any way that this can be imported into SQL 2000? Will I have to write an application using VB to manually create the indexes and then import? The solution that I come up with will have to be repeatable as the data will need to be updated with a new XML file often.
If the entire XML file will help with understanding the scope of the issue, I can attach it.
Thanks in advance
|||That's where I was going. Glad you found it.
Irwin
Irwin Dolobowsky
Program Manager - SqlXml
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott McKillop" <smckillop@.NOSPAM.ns.sympatico.ca.REMOVECAPS> wrote in message news:%23PKm2RwMEHA.1348@.TK2MSFTNGP12.phx.gbl...
Thanks for your response! I think I may have stumbled upon something that will help... I was finding mixed documents on the internet regarding the Bulk Load functionality... But I think the trick is to use Version 3.0SP2 with identities to get this to work...
I'm going to start with some easier samples and hopefully fluke my way through it. If I have a more precise and meaningful question in my experiments, I'll holler!
Thanks Again!
"Irwin Dolobowsky [MS]" <irwind@.mail.microsoft.com> wrote in message news:%23BIJuAwMEHA.3208@.TK2MSFTNGP10.phx.gbl...
Do golfers have ID's or anything? If not, can you just add an ID column that will auto-generate and id for each row? You could then relate them using that.
Irwin Dolobowsky
Program Manager - SqlXml
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott McKillop" <scott_mckillop_NOSPAM_MAN@.adlt.com_REMOVE_CAPS> wrote in message news:ubT1rwsMEHA.3420@.TK2MSFTNGP11.phx.gbl...
I have an application that exports an XML file in a strange format. I would like to be able to upload this file into an SQL 2000 database using Bulk Load. I have been able to complete some of the various examples and have a handle on what I need to do in the Bulk Load.
The problem I have is understanding how to load the data (in the form the application exports) into a relational format. My data does not contain any indexes and has multiple tables that will need to be created. A snippet of the XML is copied below:
================================================== ==============================
<Round Cn="Amherst Golf Course" Cc="Amherst" Cs="NS" Cy="CA" St="-1030918208" Et="-1030902963" Sf="0" Pt="31" Gm="3" Op="0">
<Rp N="Steve Peddle" S="STEVE" Hv="25" Te="1">
<Sc h1="7" h2="6" h3="6" h4="3" h5="7" h6="6" h7="6" h8="5" h9="5" h10="5" h11="6" h12="5" h13="3" h14="6" h15="4" h16="6" h17="5" h18="6"/>
<Pt h1="1" h2="2" h3="2" h4="1" h5="2" h6="1" h7="2" h8="1" h9="2" h10="2" h11="2" h12="2" h13="1" h14="2" h15="2" h16="2" h17="2" h18="1"/>
</Rp>
<Rp N="Chris White" S="Chris" Hv="18" Te="1">
<Sc h1="7" h2="5" h3="5" h4="3" h5="8" h6="4" h7="5" h8="6" h9="4" h10="5" h11="6" h12="5" h13="4" h14="6" h15="4" h16="5" h17="5" h18="7"/>
<Pt h1="2" h2="2" h3="1" h4="1" h5="3" h6="1" h7="2" h8="3" h9="2" h10="2" h11="2" h12="2" h13="2" h14="2" h15="2" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Scott McKillop" S="Scott" Hv="14" Te="1">
<Sc h1="7" h2="5" h3="4" h4="3" h5="7" h6="5" h7="5" h8="6" h9="4" h10="5" h11="5" h12="5" h13="4" h14="4" h15="4" h16="4" h17="5" h18="7"/>
<Pt h1="1" h2="1" h3="2" h4="2" h5="3" h6="2" h7="1" h8="2" h9="1" h10="2" h11="2" h12="3" h13="2" h14="1" h15="3" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Steve Rayworth" S="SteveR" Hv="13" Te="1">
<Sc h1="6" h2="5" h3="5" h4="3" h5="5" h6="6" h7="3" h8="5" h9="3" h10="6" h11="4" h12="5" h13="3" h14="5" h15="3" h16="4" h17="4" h18="7"/>
<Pt h1="2" h2="2" h3="2" h4="2" h5="2" h6="2" h7="1" h8="2" h9="2" h10="3" h11="1" h12="2" h13="1" h14="2" h15="2" h16="1" h17="2" h18="2"/>
<Ev h1="0" h2="0" h3="0" h4="0" h5="0" h6="0" h7="0" h8="0" h9="4" h10="0" h11="0" h12="0" h13="0" h14="0" h15="0" h16="0" h17="0" h18="0"/>
</Rp>
<Games>
<Gm I="0" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="17" M="0" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="11" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="12" H2="5" H3="1" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
</Games>
</Round>
================================================== ================================================== =============================
The application is actually used to track golf rounds.
a.. Each round can have many players;
a.. Each Player wil have 1 Score (<sc>)
b.. Each Player will have 1 Putt (<Pt>)
c.. Each Player will have 1 Ev? (<Ev>)
b.. Each Round can have 1 Games
a.. Each Games can have multiple Gm's (<Gm>)
The problem with this structure is that there are no indexes in the file, and from what I understand about the Bulk Load and relational databases, is that it will be next to impossible to link the data properly after importing.
Is there any way that this can be imported into SQL 2000? Will I have to write an application using VB to manually create the indexes and then import? The solution that I come up with will have to be repeatable as the data will need to be updated with a new XML file often.
If the entire XML file will help with understanding the scope of the issue, I can attach it.
Thanks in advance
Bulk Load schema problems
Hi, I'm from Brazil, so sorry bad english
I have a xml document like this:
<competition name="Formula one" year="2003"> <!-- this is the root element
-->
<race number="1" local="Brazil">
<pilots number_pilots="10">
<pilot name="schummacker" pos="1" team="ferrari"/>
<pilot name="barriquelo" pos="2" team="ferrari"/>
No help for me?
"Luciano Pagliarini" wrote:
> Hi, I'm from Brazil, so sorry bad english
> I have a xml document like this:
> <competition name="Formula one" year="2003"> <!-- this is the root element
> -->
> <race number="1" local="Brazil">
> <pilots number_pilots="10">
> <pilot name="schummacker" pos="1" team="ferrari"/>
> <pilot name="barriquelo" pos="2" team="ferrari"/>
> .
> .
> .
> </pilots>
> <teams number_teams="5">
> <team name="ferrari" points="18"/>
> <team name="willians" points="9"/>
> .
> .
> .
> </teams>
> <totals finished_pilots="8" out_pilots="2" accidents="1"/>
> </race>
> </competition>
>
> ps.: I will have one file for each race. I need import this to sql server
> using xml bulk load and defining xsd schema. I need fast import.
> the schema that i made, actualy works to import, but have some problems
>
> 1) I have a table named "competitions" mapped to the element "competition"
> of the xml documents. Always i run XMLBulkLoad.execute one record to this
> element is placed in the table. If i have 500 documents to be read, 500
> records with the same content will be placed in this table. (I make a
> workarround make one file with a union of the small files, puting the element
> just one time. this is a workarround that consume time, and the time to load
> for me is important)
> 2) If a have a table named "pilots_results" with this layout:
> "year" , "race_id" , "pilot_name" , "position"
> how can I construct a schema that import JUST this data to ONE table, and
> nothing more. (the error is like the element "race" have no item associated
> with it, or some like this. sory I dont remenber)
> 3) Now, if i have a table named "races" with this layout:
> "year" , "race_id" , "finished_Pilots" , "out_pilots" , "accidenst"
> how can I construct schema to import this way?
> note that the node "totals" is a subelement of the element "race", but is
> totaly associated, because is totals for the especific race!
> acctualy a have to create a table named "races_totals" just to put this
> data, mapping the element "totals" to this table. note that for each
> "race_id" in that "year" I will have just one "totals" element.
> 4) and, finally, the performanse...
> for 500 xml documents about 50kb each one, the time to import is ~4minutes
> or more.
> the tables are off indexses, no transaction is used, no users access the
> database, just the normal windows services running,
>
> anybody can help? this is very urgent for me, I have a delivery stated
> period for this system and can't see the light in the end of tunel.
> thanks for all
> Luciano Pagliarini
|||Hi Luciano,
For
1) I don't see any other way, may be you can run bulkload.execute in a loop
over all your data files
2) you can define schema that has a relationship between competition and
race and a relationship between race and pilot_results
that way you can propagate ID from competition to race to pilot_results
or
you just have year and raceid repeated for every pilot_results
<pilot name="schummacker" pos="1" team="ferrari" year="2003" raceid="1"/>
3) similar to (2)
4) I do not understand your question here. If you are looking for ways to
increase perf, try not using a transaction, keepidentity=<default value> and
no schemagen
HTH,
-Chandra
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Luciano Pagliarini" <LucianoPagliarini@.discussions.microsoft.com> wrote in
message news:3DCA9725-CA12-4997-99AB-E1334D692424@.microsoft.com...[vbcol=seagreen]
> No help for me?
> "Luciano Pagliarini" wrote:
I have a xml document like this:
<competition name="Formula one" year="2003"> <!-- this is the root element
-->
<race number="1" local="Brazil">
<pilots number_pilots="10">
<pilot name="schummacker" pos="1" team="ferrari"/>
<pilot name="barriquelo" pos="2" team="ferrari"/>
No help for me?
"Luciano Pagliarini" wrote:
> Hi, I'm from Brazil, so sorry bad english
> I have a xml document like this:
> <competition name="Formula one" year="2003"> <!-- this is the root element
> -->
> <race number="1" local="Brazil">
> <pilots number_pilots="10">
> <pilot name="schummacker" pos="1" team="ferrari"/>
> <pilot name="barriquelo" pos="2" team="ferrari"/>
> .
> .
> .
> </pilots>
> <teams number_teams="5">
> <team name="ferrari" points="18"/>
> <team name="willians" points="9"/>
> .
> .
> .
> </teams>
> <totals finished_pilots="8" out_pilots="2" accidents="1"/>
> </race>
> </competition>
>
> ps.: I will have one file for each race. I need import this to sql server
> using xml bulk load and defining xsd schema. I need fast import.
> the schema that i made, actualy works to import, but have some problems
>
> 1) I have a table named "competitions" mapped to the element "competition"
> of the xml documents. Always i run XMLBulkLoad.execute one record to this
> element is placed in the table. If i have 500 documents to be read, 500
> records with the same content will be placed in this table. (I make a
> workarround make one file with a union of the small files, puting the element
> just one time. this is a workarround that consume time, and the time to load
> for me is important)
> 2) If a have a table named "pilots_results" with this layout:
> "year" , "race_id" , "pilot_name" , "position"
> how can I construct a schema that import JUST this data to ONE table, and
> nothing more. (the error is like the element "race" have no item associated
> with it, or some like this. sory I dont remenber)
> 3) Now, if i have a table named "races" with this layout:
> "year" , "race_id" , "finished_Pilots" , "out_pilots" , "accidenst"
> how can I construct schema to import this way?
> note that the node "totals" is a subelement of the element "race", but is
> totaly associated, because is totals for the especific race!
> acctualy a have to create a table named "races_totals" just to put this
> data, mapping the element "totals" to this table. note that for each
> "race_id" in that "year" I will have just one "totals" element.
> 4) and, finally, the performanse...
> for 500 xml documents about 50kb each one, the time to import is ~4minutes
> or more.
> the tables are off indexses, no transaction is used, no users access the
> database, just the normal windows services running,
>
> anybody can help? this is very urgent for me, I have a delivery stated
> period for this system and can't see the light in the end of tunel.
> thanks for all
> Luciano Pagliarini
|||Hi Luciano,
For
1) I don't see any other way, may be you can run bulkload.execute in a loop
over all your data files
2) you can define schema that has a relationship between competition and
race and a relationship between race and pilot_results
that way you can propagate ID from competition to race to pilot_results
or
you just have year and raceid repeated for every pilot_results
<pilot name="schummacker" pos="1" team="ferrari" year="2003" raceid="1"/>
3) similar to (2)
4) I do not understand your question here. If you are looking for ways to
increase perf, try not using a transaction, keepidentity=<default value> and
no schemagen
HTH,
-Chandra
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Luciano Pagliarini" <LucianoPagliarini@.discussions.microsoft.com> wrote in
message news:3DCA9725-CA12-4997-99AB-E1334D692424@.microsoft.com...[vbcol=seagreen]
> No help for me?
> "Luciano Pagliarini" wrote:
Bulk Load problem - error connecting
I get the following error when trying to use the SQL Bulk Load object.
"Error connecting to the data source." The datasource is used right above this code to successfully read from the database. It is the last line that blows up.
Here is the problem code:
connStr = "provider=SQLNCLI;Data Source=myserver;Initial Catalog=mydb;Integrated Security=True"
Dim objBL As New SQLXMLBULKLOADLib.SQLXMLBulkLoad
objBL.ConnectionString = connStr
objBL.BulkLoad = True
objBL.XMLFragment = True
objBL.KeepIdentity = False
objBL.ErrorLogFile = "C:\BulkLoadErrors.xml"
objBL.Execute(SchemaFile, datafile)
(SchemaFile and datafile are strings containing the full file name and path)
Use the connection string as follows :
ConnStr = "provider=sqloledb;data source=myserver;database=mydb;integrated security=SSPI;"
It should work.
Thanks.
Naras.
|||Thank you! That did it. Annoying part is the connection string I was using was taken straight from the MSDN examples.bulk load option native? or formatfile?
I tested bulk load.
I had knowed native option is faster.
But test resulted that formatfile is faster than native option.
Test data is 120000 row, and text file size is 25M.
And I monitored log records, log bytes, elapsed time.
Result : log records, log bytes, elapsed time(ss).
native 7934, 468212, 12
fomatfile 5041, 297452, 9
Is this a special case because data size is much small?
Or is this truth?
Thank you!
>
> I tested bulk load.
> I had knowed native option is faster.
> But test resulted that formatfile is faster than native option.
> Test data is 120000 row, and text file size is 25M.
> And I monitored log records, log bytes, elapsed time.
> Result : log records, log bytes, elapsed time(ss).
> native 7934, 468212, 12
> fomatfile 5041, 297452, 9
> Is this a special case because data size is much small?
> Or is this truth?
> Thank you!
>
There are always exceptions to a rule. SQL Server documentation says that
native format is the fastest form of data transfer. There is also a table
in SQL Server books online that tells you what format to use on which
circumstances.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
I had knowed native option is faster.
But test resulted that formatfile is faster than native option.
Test data is 120000 row, and text file size is 25M.
And I monitored log records, log bytes, elapsed time.
Result : log records, log bytes, elapsed time(ss).
native 7934, 468212, 12
fomatfile 5041, 297452, 9
Is this a special case because data size is much small?
Or is this truth?
Thank you!
>
> I tested bulk load.
> I had knowed native option is faster.
> But test resulted that formatfile is faster than native option.
> Test data is 120000 row, and text file size is 25M.
> And I monitored log records, log bytes, elapsed time.
> Result : log records, log bytes, elapsed time(ss).
> native 7934, 468212, 12
> fomatfile 5041, 297452, 9
> Is this a special case because data size is much small?
> Or is this truth?
> Thank you!
>
There are always exceptions to a rule. SQL Server documentation says that
native format is the fastest form of data transfer. There is also a table
in SQL Server books online that tells you what format to use on which
circumstances.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
bulk load option native? or formatfile?
I tested bulk load.
I had knowed native option is faster.
But test resulted that formatfile is faster than native option.
Test data is 120000 row, and text file size is 25M.
And I monitored log records, log bytes, elapsed time.
Result : log records, log bytes, elapsed time(ss).
native 7934, 468212, 12
fomatfile 5041, 297452, 9
Is this a special case because data size is much small?
Or is this truth?
Thank you!>
> I tested bulk load.
> I had knowed native option is faster.
> But test resulted that formatfile is faster than native option.
> Test data is 120000 row, and text file size is 25M.
> And I monitored log records, log bytes, elapsed time.
> Result : log records, log bytes, elapsed time(ss).
> native 7934, 468212, 12
> fomatfile 5041, 297452, 9
> Is this a special case because data size is much small?
> Or is this truth?
> Thank you!
>
--
There are always exceptions to a rule. SQL Server documentation says that
native format is the fastest form of data transfer. There is also a table
in SQL Server books online that tells you what format to use on which
circumstances.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.sql
I had knowed native option is faster.
But test resulted that formatfile is faster than native option.
Test data is 120000 row, and text file size is 25M.
And I monitored log records, log bytes, elapsed time.
Result : log records, log bytes, elapsed time(ss).
native 7934, 468212, 12
fomatfile 5041, 297452, 9
Is this a special case because data size is much small?
Or is this truth?
Thank you!>
> I tested bulk load.
> I had knowed native option is faster.
> But test resulted that formatfile is faster than native option.
> Test data is 120000 row, and text file size is 25M.
> And I monitored log records, log bytes, elapsed time.
> Result : log records, log bytes, elapsed time(ss).
> native 7934, 468212, 12
> fomatfile 5041, 297452, 9
> Is this a special case because data size is much small?
> Or is this truth?
> Thank you!
>
--
There are always exceptions to a rule. SQL Server documentation says that
native format is the fastest form of data transfer. There is also a table
in SQL Server books online that tells you what format to use on which
circumstances.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.sql
Subscribe to:
Posts (Atom)