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 file. Show all posts
Showing posts with label file. 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: 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.
>
>
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 multiple rows
Hi
I'm a total newbie in this area and would appreciate some help regarding
sqlxml bulk load. I have a xml file like below:
<voyage>
<portcalls>
<portcall>
<port_code>bjasta</port_code>
</portcall>
<portcall>
<port_code>ovik</port_code>
</portcall>
</portcalls>
</voyage>
My xsd-schema for this file is like this:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="voyage" sql:relation="portcode" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcalls" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcall" minOccurs="0" maxOccurs="unbounded"
sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="0" maxOccurs="unbounded" name="port_code"
type="xsd:string" />
</xsd:sequence>
</xsd:complexType> </xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
When executing SQLXML Bulk Load-object the following error occurs: "Data
mapping to column 'port_code' was already found in the data. Make sure that
no two schema definitions map to the same column."
I want the parser to bulk load new rows into my table "portcode" for every
port_code-element found in the xml-file, but this code only works when havin
g
only one port-code-element in the xml-file.
What is the issue here? Is there a way to get this scenario to work?
Thanks in advance for your help. My deadline is closing in on me :-(
Regards
Daniel Nhttp://msdn.microsoft.com/library/d... />
sqlxml.asp|||Hi,
You need to slightly modify the schema to look like this :
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="voyage" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcalls" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcall" minOccurs="0" maxOccurs="unbounded"
sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="0" sql:relation="portcode"
maxOccurs="unbounded" name="port_code"
type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
I hope this will solve your problem.
Best Regards,
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" .
I'm a total newbie in this area and would appreciate some help regarding
sqlxml bulk load. I have a xml file like below:
<voyage>
<portcalls>
<portcall>
<port_code>bjasta</port_code>
</portcall>
<portcall>
<port_code>ovik</port_code>
</portcall>
</portcalls>
</voyage>
My xsd-schema for this file is like this:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="voyage" sql:relation="portcode" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcalls" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcall" minOccurs="0" maxOccurs="unbounded"
sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="0" maxOccurs="unbounded" name="port_code"
type="xsd:string" />
</xsd:sequence>
</xsd:complexType> </xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
When executing SQLXML Bulk Load-object the following error occurs: "Data
mapping to column 'port_code' was already found in the data. Make sure that
no two schema definitions map to the same column."
I want the parser to bulk load new rows into my table "portcode" for every
port_code-element found in the xml-file, but this code only works when havin
g
only one port-code-element in the xml-file.
What is the issue here? Is there a way to get this scenario to work?
Thanks in advance for your help. My deadline is closing in on me :-(
Regards
Daniel Nhttp://msdn.microsoft.com/library/d... />
sqlxml.asp|||Hi,
You need to slightly modify the schema to look like this :
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="voyage" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcalls" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcall" minOccurs="0" maxOccurs="unbounded"
sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="0" sql:relation="portcode"
maxOccurs="unbounded" name="port_code"
type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
I hope this will solve your problem.
Best Regards,
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" .
Bulk load multiple rows
Hi
I'm a total newbie in this area and would appreciate some help regarding
sqlxml bulk load. I have a xml file like below:
<voyage>
<portcalls>
<portcall>
<port_code>bjasta</port_code>
</portcall>
<portcall>
<port_code>ovik</port_code>
</portcall>
</portcalls>
</voyage>
My xsd-schema for this file is like this:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="voyage" sql:relation="portcode" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcalls" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcall" minOccurs="0" maxOccurs="unbounded"
sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="0" maxOccurs="unbounded" name="port_code"
type="xsd:string" />
</xsd:sequence>
</xsd:complexType></xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
When executing SQLXML Bulk Load-object the following error occurs: "Data
mapping to column 'port_code' was already found in the data. Make sure that
no two schema definitions map to the same column."
I want the parser to bulk load new rows into my table "portcode" for every
port_code-element found in the xml-file, but this code only works when having
only one port-code-element in the xml-file.
What is the issue here? Is there a way to get this scenario to work?
Thanks in advance for your help. My deadline is closing in on me :-(
Regards
Daniel N
http://msdn.microsoft.com/library/de...exchsqlxml.asp
|||Hi,
You need to slightly modify the schema to look like this :
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="voyage" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcalls" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcall" minOccurs="0" maxOccurs="unbounded"
sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="0" sql:relation="portcode"
maxOccurs="unbounded" name="port_code"
type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
I hope this will solve your problem.
Best Regards,
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" .
I'm a total newbie in this area and would appreciate some help regarding
sqlxml bulk load. I have a xml file like below:
<voyage>
<portcalls>
<portcall>
<port_code>bjasta</port_code>
</portcall>
<portcall>
<port_code>ovik</port_code>
</portcall>
</portcalls>
</voyage>
My xsd-schema for this file is like this:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="voyage" sql:relation="portcode" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcalls" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcall" minOccurs="0" maxOccurs="unbounded"
sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="0" maxOccurs="unbounded" name="port_code"
type="xsd:string" />
</xsd:sequence>
</xsd:complexType></xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
When executing SQLXML Bulk Load-object the following error occurs: "Data
mapping to column 'port_code' was already found in the data. Make sure that
no two schema definitions map to the same column."
I want the parser to bulk load new rows into my table "portcode" for every
port_code-element found in the xml-file, but this code only works when having
only one port-code-element in the xml-file.
What is the issue here? Is there a way to get this scenario to work?
Thanks in advance for your help. My deadline is closing in on me :-(
Regards
Daniel N
http://msdn.microsoft.com/library/de...exchsqlxml.asp
|||Hi,
You need to slightly modify the schema to look like this :
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="voyage" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcalls" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcall" minOccurs="0" maxOccurs="unbounded"
sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="0" sql:relation="portcode"
maxOccurs="unbounded" name="port_code"
type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
I hope this will solve your problem.
Best Regards,
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" .
Bulk load from Access to SQL 2003
I am trying to configure a bulk table upload from a 2003 Access table to a matching table in SQL with SSIS. I can configure the source file but am unable to configure the destination. When I configure the SQL source and use SQL Native Client I get an error message of:
The selected connection manager uses an earlier version of a SQL server provider. Bulk insert requires a connection that uses a SQL server 2005 provider.
When I go through the new connection setup I don't see any available provider named like that. I believe the SQL server I am loading to is a 2003 version.
try using the ole db destination. it uses bulk loading by design.|||Thanks, that seemed to work to pull the table up. Pushing takes forever.
Bulk Load and SQL function defaults
Is it possible to include a field default in the schema file that represent
a database function?
For example, all of our SQL tables includes the following fields...
CreateDate datetime NOT NULL DEFAULT (getdate())
CreateUser char (255) NOT NULL DEFAULT (suser_sname())
When creating our schema we tried the following:
<xsd:element name="CreateDate" sql:datatype="DateTime" default="getdate()"
/>
<xsd:element name="CreateUser" sql:datatype="Char" default="suser_sname()"
/>
The "CreateDate" element fails with an "Invalid character value for cast
specification." error, while the second element will insert the string value
'suser_sname()' into the "CreateUser" field.
Thanks in advance
No, this is not possible. The default is an XML schema default clause and
cannot contain an T-SQL expression.
Instead, define a default on the relational table column to which you map
the element and make sure that there is no value added.
Best regards
Michael
"Cipher" <c@.c.com> wrote in message
news:OU8QUY0TEHA.3988@.tk2msftngp13.phx.gbl...
> Is it possible to include a field default in the schema file that
> represent
> a database function?
> For example, all of our SQL tables includes the following fields...
> CreateDate datetime NOT NULL DEFAULT (getdate())
> CreateUser char (255) NOT NULL DEFAULT (suser_sname())
> When creating our schema we tried the following:
> <xsd:element name="CreateDate" sql:datatype="DateTime" default="getdate()"
> />
> <xsd:element name="CreateUser" sql:datatype="Char" default="suser_sname()"
> />
> The "CreateDate" element fails with an "Invalid character value for cast
> specification." error, while the second element will insert the string
> value
> 'suser_sname()' into the "CreateUser" field.
>
> Thanks in advance
>
sql
a database function?
For example, all of our SQL tables includes the following fields...
CreateDate datetime NOT NULL DEFAULT (getdate())
CreateUser char (255) NOT NULL DEFAULT (suser_sname())
When creating our schema we tried the following:
<xsd:element name="CreateDate" sql:datatype="DateTime" default="getdate()"
/>
<xsd:element name="CreateUser" sql:datatype="Char" default="suser_sname()"
/>
The "CreateDate" element fails with an "Invalid character value for cast
specification." error, while the second element will insert the string value
'suser_sname()' into the "CreateUser" field.
Thanks in advance
No, this is not possible. The default is an XML schema default clause and
cannot contain an T-SQL expression.
Instead, define a default on the relational table column to which you map
the element and make sure that there is no value added.
Best regards
Michael
"Cipher" <c@.c.com> wrote in message
news:OU8QUY0TEHA.3988@.tk2msftngp13.phx.gbl...
> Is it possible to include a field default in the schema file that
> represent
> a database function?
> For example, all of our SQL tables includes the following fields...
> CreateDate datetime NOT NULL DEFAULT (getdate())
> CreateUser char (255) NOT NULL DEFAULT (suser_sname())
> When creating our schema we tried the following:
> <xsd:element name="CreateDate" sql:datatype="DateTime" default="getdate()"
> />
> <xsd:element name="CreateUser" sql:datatype="Char" default="suser_sname()"
> />
> The "CreateDate" element fails with an "Invalid character value for cast
> specification." error, while the second element will insert the string
> value
> 'suser_sname()' into the "CreateUser" field.
>
> Thanks in advance
>
sql
Bulk load and passing parameters in
I'm doing a Bulk Load of an XML file.
The file consists of a parent/child relationship joined by an interger
column in both tables.
The parent table may already have some values in it.
How can I pass in the starting value and also have it auto increment for
each element ?
Michael Tissington
http://www.oaklodge.com
http://www.sqlview.netHello Michael,
If I understand this correctly, you parent table has a column (int)
referenced by a clild table. The parent table have some rows. You want to
change the column to identiy, and pass a starting value to this column. If
I'm off-base, please let me know.
Based on my research, you could not pass starting value or change column
propperty via mapping file. You may want to use "alter table" to change the
column. You could change this in Enterprise Manager, or you need to write
a sql code to do this by creating a temp table, copy from original table
when SET IDENTITY_INSERT is set to on, and then rename the temp table to
the original one. You may want to use profiler to capture the trace when
changing this in Enterprise Manager
You may want to refer to the following article to for some related
information
Using XML Bulk Load with Identity Columns
http://www.sqlmag.com/Article/Artic...rver_40239.html
Hope this is helpful. Please post back if you have further questions.
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.
The file consists of a parent/child relationship joined by an interger
column in both tables.
The parent table may already have some values in it.
How can I pass in the starting value and also have it auto increment for
each element ?
Michael Tissington
http://www.oaklodge.com
http://www.sqlview.netHello Michael,
If I understand this correctly, you parent table has a column (int)
referenced by a clild table. The parent table have some rows. You want to
change the column to identiy, and pass a starting value to this column. If
I'm off-base, please let me know.
Based on my research, you could not pass starting value or change column
propperty via mapping file. You may want to use "alter table" to change the
column. You could change this in Enterprise Manager, or you need to write
a sql code to do this by creating a temp table, copy from original table
when SET IDENTITY_INSERT is set to on, and then rename the temp table to
the original one. You may want to use profiler to capture the trace when
changing this in Enterprise Manager
You may want to refer to the following article to for some related
information
Using XML Bulk Load with Identity Columns
http://www.sqlmag.com/Article/Artic...rver_40239.html
Hope this is helpful. Please post back if you have further questions.
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 insertion
Hi,
I am working on an application that is to read a large number of XML files, take out specific values from each file, and store these in a SQL server so that reports can be generated from these values. There are some 15-20,000 files for each month of the year. I am OK with parsing the files and getting the fields that I need but I don't want to insert one record at a time as I parse the files. I was told that I can create a .exe file that parses the xml files and stores the required values in a csv file and use these csv files to initiate a bulk insert, using Business Intelligence Studio. I have not been able to find any info or article on how to do this. Any help on how I can accomplish this, or alternate solutions is greatly appreciated.
You can make use of SQL BulkCopy feature of ADO.NET. Load your XML into a DataSet/DataTable and run SQL Bulk copy into your table. Very few lines of code.
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString))
{
foreach (string tableName in tableNames)
{
SqlBulkCopy(dataSet.Tables[tableName], tableName, bulkCopy);
}
}
private static void SqlBulkCopy(DataTable dataTable, string tableName, SqlBulkCopy bulkCopy)
{
bulkCopy.DestinationTableName = tableName;
bulkCopy.ColumnMappings.Clear();
foreach (DataColumn myCol in dataTable.Columns)
bulkCopy.ColumnMappings.Add(myCol.ColumnName, myCol.ColumnName);
bulkCopy.WriteToServer(dataTable);
}
Thanks for the reply Raghu. I don't need all the data elements in the XML files, only a selected few. This is an example XML file:
<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
xmlns:rs='urn:schemas-microsoft-com:rowset'
xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
<s:ElementType name='row' content='eltOnly' rs:updatable='true'>
<s:AttributeType name='c0' rs:name='AFR Finalization' rs:number='1' rs:write='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='18' rs:precision='0' rs:fixedlength='true' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='Processed' rs:number='2' rs:write='true'>
<s:datatype dt:type='int' dt:maxLength='4' rs:precision='0' rs:fixedlength='true' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='Rate' rs:number='3' rs:write='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='7' rs:precision='0' rs:fixedlength='true' rs:maybenull='false'/>
</s:AttributeType>
<s:extends type='rs:rowbase'/>
</s:ElementType>
</s:Schema>
<rs:data>
<rs:insert>
<z:row c0='CIF '/>
<z:row c0=' Input ' Processed='12028'/>
<z:row c0=' Finalized ' Processed='5444' Rate=' 45.26%'/>
<z:row c0='RTS '/>
<z:row c0=' Input ' Processed='9802'/>
<z:row c0=' Finalized ' Processed='5504' Rate=' 56.15%'/>
<z:row c0='Interception '/>
<z:row c0=' Input ' Processed='12639'/>
<z:row c0=' Finalized ' Processed='8220' Rate=' 65.04%'/>
</rs:insert>
</rs:data>
</xml>
I only need CIF/Input and RTS/INPUT from this particular file. There will be one of this file for each day of the month and I will have to process a month's worth of xml files. There are tens of different xml files but they all follow the same schema.
Thanks.
bulk inserting uniqueidentifier column
Hi at all,
I'm trying to bulk insert a uniqueidentifier column from unicode file.
In my file I have guid generated from c# application and they are
formatted in this way (separated by "|") :
Latin1_General_CI_AS
Latin1_General_CI_AS
SQLUNIQUEID is what you would use in a binary file. It's not applicable
here.
I'm sorry, cut and pasting sample text file I've removed the correct
syntax :\
In fact the last line has the terminator you specified. :)
>
SQLUNIQUEID is what you would use in a binary file. It's not applicable
here.
I'm migrating this bulk insert frm sql server 2000... in 2005 this
doesn't work. Is it caused by more strictly rules in 2005 engine?
Is sql converting my "char" guid in binary?
I'm trying to bulk insert a uniqueidentifier column from unicode file.
In my file I have guid generated from c# application and they are
formatted in this way (separated by "|") :
guid | field1 | field2
fc0c0c42-438e-4897-96db-8b0489e873ef|field1|field2
In my destination table I have three column:
id (uniqueidentifier)
field1 (nvarchar)
field2 (nvarchar)
I use in bulk insert a format file like this :
9.0
3
1SQLNCHAR00"|\0"1IDLatin1_General_CI_AS
2SQLNCHAR00"|\0"2Field1Latin1_General_CI_AS
3SQLNCHAR00"|\0"3Field2Latin1_General_CI_AS
and I use this script
BULK INSERT [dbo].[KWTA2] FROM 'd:\WTA2.txt'
WITH (FORMATFILE = 'd:\wta2Format.FMT')
It doesn't work, it prints out
Msg 8152, Level 16, State 13, Line 2
String or binary data would be truncated.
I've also tried to specify in FMT file SQLUNIQUEID instead of SQLNCHAR
and it works perfectly but it imports another data. For example the
guid fc0c0c42-438e-4897-96db-8b0489e873ef became
00350031-0039-0033-3100-300030003000
Please can you help me?
Why sql converts alphanumerical GUID into only numbers ID?
How can I bulk insert GUID? (I didn't find anything googling around :
\ )
Thanks!
Bob(bob.speaking@.gmail.com) writes:
Quote:
Originally Posted by
I'm trying to bulk insert a uniqueidentifier column from unicode file.
In my file I have guid generated from c# application and they are
formatted in this way (separated by "|") :
>
guid | field1 | field2
fc0c0c42-438e-4897-96db-8b0489e873ef|field1|field2
>
In my destination table I have three column:
id (uniqueidentifier)
field1 (nvarchar)
field2 (nvarchar)
>
I use in bulk insert a format file like this :
>
9.0
3
1 SQLNCHAR 0 0 "|\0" 1 ID Latin1_General_CI_AS
2 SQLNCHAR 0 0 "|\0" 2 Field1
Latin1_General_CI_AS
Quote:
Originally Posted by
3 SQLNCHAR 0 0 "|\0" 3 Field2
Latin1_General_CI_AS
Does the file really consist of one single line?
Assuming that you have one record per line in the file, the terminator
for field 3 should be \r\0\n\0. What happens now is that Field2 in the
first record extends into the GUID in the second record, and then it
goes downhill from there.
Quote:
Originally Posted by
I've also tried to specify in FMT file SQLUNIQUEID instead of SQLNCHAR
and it works perfectly but it imports another data. For example the
guid fc0c0c42-438e-4897-96db-8b0489e873ef became
00350031-0039-0033-3100-300030003000
SQLUNIQUEID is what you would use in a binary file. It's not applicable
here.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>
Quote:
Originally Posted by
Does the file really consist of one single line?
>
Assuming that you have one record per line in the file, the terminator
for field 3 should be \r\0\n\0. What happens now is that Field2 in the
first record extends into the GUID in the second record, and then it
goes downhill from there.
I'm sorry, cut and pasting sample text file I've removed the correct
syntax :\
In fact the last line has the terminator you specified. :)
Quote:
Originally Posted by
Quote:
Originally Posted by
I've also tried to specify in FMT file SQLUNIQUEID instead of SQLNCHAR
and it works perfectly but it imports another data. For example the
guid fc0c0c42-438e-4897-96db-8b0489e873ef became
00350031-0039-0033-3100-300030003000
>
SQLUNIQUEID is what you would use in a binary file. It's not applicable
here.
I'm migrating this bulk insert frm sql server 2000... in 2005 this
doesn't work. Is it caused by more strictly rules in 2005 engine?
Is sql converting my "char" guid in binary?
Thanks for the prompt reply :)
bobsql
BULK INSERTing UNICODE data with format files
Sorry but could you please show me how I convert (specifically the
separators) this bcp format file to unicode so that it works (with a unicod
e
file). I have tried various methods without success.
8.0
2
1 SQLNCHAR 2 100 "," 1 RecordId
Latin1_General_CI_AS
2 SQLNCHAR 4 0 "\r\n" 2 TheText
Latin1_General_CI_AS
Thanks
Peter Doyle
"Erland Sommarskog" wrote:
> Nitin M (nitin@.nowhere.com) writes:
> Ah! Glad to hear that you where able to find it out yourself.
>
> The obvious idea would be to save the format file as Unicode, but that
> does not work; you only get a message about unknown version. I tried in
> SQL 2005 as well, but SQL 2005 appears to think that a Unicode file must
> be an XML format file.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>Peter Doyle (PeterDoyle@.discussions.microsoft.com) writes:
> Sorry but could you please show me how I convert (specifically the
> separators) this bcp format file to unicode so that it works (with a
> unicode file). I have tried various methods without success.
> 8.0
> 2
> 1 SQLNCHAR 2 100 "," 1 RecordId
> Latin1_General_CI_AS
> 2 SQLNCHAR 4 0 "\r\n" 2 TheText
> Latin1_General_CI_AS
The above won't work for several reasons. You have specified a
prefix length, but prefix lengths is only for binary formats.
The problem with impoting Unicode files, is that the format file itself
must be a 8-bit file. At least I seem to recall that BCP freaked out
when I tried with a Unicode file. (Hm, did I try BULK INSERT as well+)
Anyway, with the format indicated by your format file, you don't any
format file at all. For a table like:
CREATE TABLE test (a nvarchar(100) NOT NULL,
b nvarchar(100) NOT NULL)
And this data (saved as Unicode):
Detta r lite data,Och detta r mer data.
Sedan kommer det n mer data hr,Och nu kommer det n mer
Trams!, Det r vad det r!
This command line will do:
bcp tempdb..test in slask.bcp -w -t, -T
-w specifies that the file a Unicode file.
But in case that your real case calls for a format file, here is one
that works with the above table and data:
8.0
2
1 SQLNCHAR 0 0 ",\0" 1 a ""
2 SQLNCHAR 0 0 "\r\0\n\0" 2 b ""
The tricky part is the \0 that must come aftet the character it belongs
to, due to endianness.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
separators) this bcp format file to unicode so that it works (with a unicod
e
file). I have tried various methods without success.
8.0
2
1 SQLNCHAR 2 100 "," 1 RecordId
Latin1_General_CI_AS
2 SQLNCHAR 4 0 "\r\n" 2 TheText
Latin1_General_CI_AS
Thanks
Peter Doyle
"Erland Sommarskog" wrote:
> Nitin M (nitin@.nowhere.com) writes:
> Ah! Glad to hear that you where able to find it out yourself.
>
> The obvious idea would be to save the format file as Unicode, but that
> does not work; you only get a message about unknown version. I tried in
> SQL 2005 as well, but SQL 2005 appears to think that a Unicode file must
> be an XML format file.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>Peter Doyle (PeterDoyle@.discussions.microsoft.com) writes:
> Sorry but could you please show me how I convert (specifically the
> separators) this bcp format file to unicode so that it works (with a
> unicode file). I have tried various methods without success.
> 8.0
> 2
> 1 SQLNCHAR 2 100 "," 1 RecordId
> Latin1_General_CI_AS
> 2 SQLNCHAR 4 0 "\r\n" 2 TheText
> Latin1_General_CI_AS
The above won't work for several reasons. You have specified a
prefix length, but prefix lengths is only for binary formats.
The problem with impoting Unicode files, is that the format file itself
must be a 8-bit file. At least I seem to recall that BCP freaked out
when I tried with a Unicode file. (Hm, did I try BULK INSERT as well+)
Anyway, with the format indicated by your format file, you don't any
format file at all. For a table like:
CREATE TABLE test (a nvarchar(100) NOT NULL,
b nvarchar(100) NOT NULL)
And this data (saved as Unicode):
Detta r lite data,Och detta r mer data.
Sedan kommer det n mer data hr,Och nu kommer det n mer
Trams!, Det r vad det r!
This command line will do:
bcp tempdb..test in slask.bcp -w -t, -T
-w specifies that the file a Unicode file.
But in case that your real case calls for a format file, here is one
that works with the above table and data:
8.0
2
1 SQLNCHAR 0 0 ",\0" 1 a ""
2 SQLNCHAR 0 0 "\r\0\n\0" 2 b ""
The tricky part is the \0 that must come aftet the character it belongs
to, due to endianness.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
BULK INSERTing UNICODE data with format files
Hi,
I am trying to bulk insert data with UNICODE characters into a table using a
format file. I am using SQL Server 2000 with all the latest SPs.
When I try to bulk insert the data I get the following error.
"Bulk Insert: Unexpected end-of-file (EOF) encountered in data file."
What am I doing incorrectly?
Please help me out with this. What is the correct way to do this. My data
file will have UNICODE characters (for nchar, nvarchar sql types) and
would also have data for other types like (int, datetime etc). And I want to
use a format file.
Thanks in anticipation,
Nitin M
I have created the data file in the following way.
---
StreamWriter DataWriter = new
StreamWriter("data.txt",false,System.Text.Encoding.Unicode);
DataWriter.WriteLine("1/1/2005@.@.aa@.@.aaaa@.@.23@.@.-1.9879@.@.");
DataWriter.Close();
----
--
This is the definition of my table.
---
CREATE TABLE [dbo].[AllTypes] (
[mydate] [datetime] NULL ,
[mychar] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[myvarchar] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[myint] [int] NULL ,
[myreal] [real] NULL
) ON [PRIMARY]
---
The bulk insert query that I use
---
bulk insert alltypes from 'data.txt' with
(
datafiletype='widechar',
formatfile = 'bcp.format.txt',
tablock
)
---
My format file
---
8.0
5
1 SQLNCHAR 0 0 "@.@." 1 mydate ""
2 SQLNCHAR 0 0 "@.@." 2 mychar SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 0 0 "@.@." 3 myvarchar SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 0 0 "@.@." 4 myint ""
5 SQLNCHAR 0 0 "@.@.\r\n" 5 myreal ""
---Nitin M (nitin@.nowhere.com) writes:
> I am trying to bulk insert data with UNICODE characters into a table
> using a format file. I am using SQL Server 2000 with all the latest
> SPs.
> When I try to bulk insert the data I get the following error.
> "Bulk Insert: Unexpected end-of-file (EOF) encountered in data file."
> What am I doing incorrectly?
> Please help me out with this. What is the correct way to do this. My
> data file will have UNICODE characters (for nchar, nvarchar sql types)
> and would also have data for other types like (int, datetime etc). And I
> want to use a format file.
I only got half-way of solving this puzzle. You need to specify the
separators as Unicode as well. I tried this:
8.0
5
1 SQLNCHAR 0 0 "\0@.\0@." 1 mydate ""
2 SQLNCHAR 0 0 "\0@.\0@." 2 mychar SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 0 0 "\0@.\0@." 3 myvarchar SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 0 0 "\0@.\0@." 4 myint ""
5 SQLNCHAR 0 0 "\0@.\0@.\0\r\0\n" 5 myreal ""
This got me past the EOF error, but instead I got conversion errors for
the numeric values. You could make all columns characters columns, so
you see what BCP actually finds, and then maybe modify the separators
from this.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,
Thanks a lot for looking into this.
Even I got around the problem by specifying separators as Unicode. The
reason you are getting conversion errors is due to the byte ordering of the
separators. Try the other byte order. It works for me. I am not getting any
conversion errors.
Is there no other cleaner way around this?
Thanks,
Nitin
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns973F74A723AEFYazorman@.127.0.0.1...
> Nitin M (nitin@.nowhere.com) writes:
>
> I only got half-way of solving this puzzle. You need to specify the
> separators as Unicode as well. I tried this:
> 8.0
> 5
> 1 SQLNCHAR 0 0 "\0@.\0@." 1 mydate ""
> 2 SQLNCHAR 0 0 "\0@.\0@." 2 mychar SQL_Latin1_General_CP1_CI_AS
> 3 SQLNCHAR 0 0 "\0@.\0@." 3 myvarchar SQL_Latin1_General_CP1_CI_AS
> 4 SQLNCHAR 0 0 "\0@.\0@." 4 myint ""
> 5 SQLNCHAR 0 0 "\0@.\0@.\0\r\0\n" 5 myreal ""
> This got me past the EOF error, but instead I got conversion errors for
> the numeric values. You could make all columns characters columns, so
> you see what BCP actually finds, and then maybe modify the separators
> from this.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Nitin M (nitin@.nowhere.com) writes:
> Thanks a lot for looking into this.
> Even I got around the problem by specifying separators as Unicode. The
> reason you are getting conversion errors is due to the byte ordering of
> the separators. Try the other byte order. It works for me. I am not
> getting any conversion errors.
Ah! Glad to hear that you where able to find it out yourself.
> Is there no other cleaner way around this?
The obvious idea would be to save the format file as Unicode, but that
does not work; you only get a message about unknown version. I tried in
SQL 2005 as well, but SQL 2005 appears to think that a Unicode file must
be an XML format file.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
I am trying to bulk insert data with UNICODE characters into a table using a
format file. I am using SQL Server 2000 with all the latest SPs.
When I try to bulk insert the data I get the following error.
"Bulk Insert: Unexpected end-of-file (EOF) encountered in data file."
What am I doing incorrectly?
Please help me out with this. What is the correct way to do this. My data
file will have UNICODE characters (for nchar, nvarchar sql types) and
would also have data for other types like (int, datetime etc). And I want to
use a format file.
Thanks in anticipation,
Nitin M
I have created the data file in the following way.
---
StreamWriter DataWriter = new
StreamWriter("data.txt",false,System.Text.Encoding.Unicode);
DataWriter.WriteLine("1/1/2005@.@.aa@.@.aaaa@.@.23@.@.-1.9879@.@.");
DataWriter.Close();
----
--
This is the definition of my table.
---
CREATE TABLE [dbo].[AllTypes] (
[mydate] [datetime] NULL ,
[mychar] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[myvarchar] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[myint] [int] NULL ,
[myreal] [real] NULL
) ON [PRIMARY]
---
The bulk insert query that I use
---
bulk insert alltypes from 'data.txt' with
(
datafiletype='widechar',
formatfile = 'bcp.format.txt',
tablock
)
---
My format file
---
8.0
5
1 SQLNCHAR 0 0 "@.@." 1 mydate ""
2 SQLNCHAR 0 0 "@.@." 2 mychar SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 0 0 "@.@." 3 myvarchar SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 0 0 "@.@." 4 myint ""
5 SQLNCHAR 0 0 "@.@.\r\n" 5 myreal ""
---Nitin M (nitin@.nowhere.com) writes:
> I am trying to bulk insert data with UNICODE characters into a table
> using a format file. I am using SQL Server 2000 with all the latest
> SPs.
> When I try to bulk insert the data I get the following error.
> "Bulk Insert: Unexpected end-of-file (EOF) encountered in data file."
> What am I doing incorrectly?
> Please help me out with this. What is the correct way to do this. My
> data file will have UNICODE characters (for nchar, nvarchar sql types)
> and would also have data for other types like (int, datetime etc). And I
> want to use a format file.
I only got half-way of solving this puzzle. You need to specify the
separators as Unicode as well. I tried this:
8.0
5
1 SQLNCHAR 0 0 "\0@.\0@." 1 mydate ""
2 SQLNCHAR 0 0 "\0@.\0@." 2 mychar SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 0 0 "\0@.\0@." 3 myvarchar SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 0 0 "\0@.\0@." 4 myint ""
5 SQLNCHAR 0 0 "\0@.\0@.\0\r\0\n" 5 myreal ""
This got me past the EOF error, but instead I got conversion errors for
the numeric values. You could make all columns characters columns, so
you see what BCP actually finds, and then maybe modify the separators
from this.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,
Thanks a lot for looking into this.
Even I got around the problem by specifying separators as Unicode. The
reason you are getting conversion errors is due to the byte ordering of the
separators. Try the other byte order. It works for me. I am not getting any
conversion errors.
Is there no other cleaner way around this?
Thanks,
Nitin
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns973F74A723AEFYazorman@.127.0.0.1...
> Nitin M (nitin@.nowhere.com) writes:
>
> I only got half-way of solving this puzzle. You need to specify the
> separators as Unicode as well. I tried this:
> 8.0
> 5
> 1 SQLNCHAR 0 0 "\0@.\0@." 1 mydate ""
> 2 SQLNCHAR 0 0 "\0@.\0@." 2 mychar SQL_Latin1_General_CP1_CI_AS
> 3 SQLNCHAR 0 0 "\0@.\0@." 3 myvarchar SQL_Latin1_General_CP1_CI_AS
> 4 SQLNCHAR 0 0 "\0@.\0@." 4 myint ""
> 5 SQLNCHAR 0 0 "\0@.\0@.\0\r\0\n" 5 myreal ""
> This got me past the EOF error, but instead I got conversion errors for
> the numeric values. You could make all columns characters columns, so
> you see what BCP actually finds, and then maybe modify the separators
> from this.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Nitin M (nitin@.nowhere.com) writes:
> Thanks a lot for looking into this.
> Even I got around the problem by specifying separators as Unicode. The
> reason you are getting conversion errors is due to the byte ordering of
> the separators. Try the other byte order. It works for me. I am not
> getting any conversion errors.
Ah! Glad to hear that you where able to find it out yourself.
> Is there no other cleaner way around this?
The obvious idea would be to save the format file as Unicode, but that
does not work; you only get a message about unknown version. I tried in
SQL 2005 as well, but SQL 2005 appears to think that a Unicode file must
be an XML format file.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Subscribe to:
Posts (Atom)