hey guys, my xml bulk load creates no error message, but does not populate my tables.
I am very new to xml and sql server, so maybe this is a simple problem?
i have a root element i dont want to map, and then several tables of data in one document. I am unconcerned with any key constraints at this point.this board wont let me post all my data at once, so one at a time...
heres how i call the load
****************************
Function Main()
Set oXMLBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad.3.0")
oXMLBulkLoad.ConnectionString = "PROVIDER=SQLOLEDB;SERVER=localhost;" & _
"DATABASE=MyDB;INTEGRATED SECURITY=sspi;"
oXMLBulkLoad.Execute "C:\myschema", "C:\myfile"
oXMLBulkLoad.SchemaGen = True
Set oXMLBulkLoad = Nothing
Main = DTSTaskExecResult_success
End Function
***************************************|||ill try attaching my schemasql
Showing posts with label xml. Show all posts
Showing posts with label xml. Show all posts
Thursday, March 29, 2012
Bulk Load: Is there any substitute for the fixed="..." modifier
Hi to everybody!
I have to Bulk Load an XML file whose Schema Definition has
some fixed="..." modifiers. After noticing something wrong
with these elements, I've found in this newsgroup that
sqlxml doesn't support the fixed="..." modifier. However,
if I substitute the fixed="VALUE-1" modifier with a
default="VALUE-1" modifier I can't prevent Bulk Load to
accept values different from "VALUE-1".
Does anybody know how to code the fixed="..." modifiers in
the Schema Definition so that XML Bulk Load can accept them?
TIA
Greetings,
David GrantThe best way to acheive this sort of functionality is to validate the
source document before loading via SqlXmlBulkload.
SqlXml does a limited amount of validation of source Xml documents, but it
is not a full validation mechanism. For example, there are cases where the
bulkload component would load invalid source Xml.
Andrew Conrad
Microsoft Corporation|||Thank you for your quick response, Andrew.
Greetings,
David Grant
>--Original Message--
>The best way to acheive this sort of functionality is to
validate the
>source document before loading via SqlXmlBulkload.
>SqlXml does a limited amount of validation of source Xml
documents, but it
>is not a full validation mechanism. For example, there
are cases where the
>bulkload component would load invalid source Xml.
>Andrew Conrad
>Microsoft Corporation
>.
>
I have to Bulk Load an XML file whose Schema Definition has
some fixed="..." modifiers. After noticing something wrong
with these elements, I've found in this newsgroup that
sqlxml doesn't support the fixed="..." modifier. However,
if I substitute the fixed="VALUE-1" modifier with a
default="VALUE-1" modifier I can't prevent Bulk Load to
accept values different from "VALUE-1".
Does anybody know how to code the fixed="..." modifiers in
the Schema Definition so that XML Bulk Load can accept them?
TIA
Greetings,
David GrantThe best way to acheive this sort of functionality is to validate the
source document before loading via SqlXmlBulkload.
SqlXml does a limited amount of validation of source Xml documents, but it
is not a full validation mechanism. For example, there are cases where the
bulkload component would load invalid source Xml.
Andrew Conrad
Microsoft Corporation|||Thank you for your quick response, Andrew.
Greetings,
David Grant
>--Original Message--
>The best way to acheive this sort of functionality is to
validate the
>source document before loading via SqlXmlBulkload.
>SqlXml does a limited amount of validation of source Xml
documents, but it
>is not a full validation mechanism. For example, there
are cases where the
>bulkload component would load invalid source Xml.
>Andrew Conrad
>Microsoft Corporation
>.
>
Bulk Load: Is there any substitute for the fixed="..." modifier
Hi to everybody!
I have to Bulk Load an XML file whose Schema Definition has
some fixed="..." modifiers. After noticing something wrong
with these elements, I've found in this newsgroup that
sqlxml doesn't support the fixed="..." modifier. However,
if I substitute the fixed="VALUE-1" modifier with a
default="VALUE-1" modifier I can't prevent Bulk Load to
accept values different from "VALUE-1".
Does anybody know how to code the fixed="..." modifiers in
the Schema Definition so that XML Bulk Load can accept them?
TIA
Greetings,
David Grant
The best way to acheive this sort of functionality is to validate the
source document before loading via SqlXmlBulkload.
SqlXml does a limited amount of validation of source Xml documents, but it
is not a full validation mechanism. For example, there are cases where the
bulkload component would load invalid source Xml.
Andrew Conrad
Microsoft Corporation
|||Thank you for your quick response, Andrew.
Greetings,
David Grant
>--Original Message--
>The best way to acheive this sort of functionality is to
validate the
>source document before loading via SqlXmlBulkload.
>SqlXml does a limited amount of validation of source Xml
documents, but it
>is not a full validation mechanism. For example, there
are cases where the
>bulkload component would load invalid source Xml.
>Andrew Conrad
>Microsoft Corporation
>.
>
I have to Bulk Load an XML file whose Schema Definition has
some fixed="..." modifiers. After noticing something wrong
with these elements, I've found in this newsgroup that
sqlxml doesn't support the fixed="..." modifier. However,
if I substitute the fixed="VALUE-1" modifier with a
default="VALUE-1" modifier I can't prevent Bulk Load to
accept values different from "VALUE-1".
Does anybody know how to code the fixed="..." modifiers in
the Schema Definition so that XML Bulk Load can accept them?
TIA
Greetings,
David Grant
The best way to acheive this sort of functionality is to validate the
source document before loading via SqlXmlBulkload.
SqlXml does a limited amount of validation of source Xml documents, but it
is not a full validation mechanism. For example, there are cases where the
bulkload component would load invalid source Xml.
Andrew Conrad
Microsoft Corporation
|||Thank you for your quick response, Andrew.
Greetings,
David Grant
>--Original Message--
>The best way to acheive this sort of functionality is to
validate the
>source document before loading via SqlXmlBulkload.
>SqlXml does a limited amount of validation of source Xml
documents, but it
>is not a full validation mechanism. For example, there
are cases where the
>bulkload component would load invalid source Xml.
>Andrew Conrad
>Microsoft Corporation
>.
>
Bulk Load XML into SQL using Schema File ...
Hi ...
I am bulk loading XML data into SQL using
SQLXMLBulkLoad.SQLXMLBulkload.3.0 COM Object. To do this I
provide the xml data and a schema file (below).
When I use the attached schema file I get the following error:
No data was provided for column 'SensorId' on table 'MacgowanTestRWISRawSurface', and this column cannot contain NULL values.
I changed the table definition to allow nulls in the SensorId column
and the bulk load was successful. Notice on the records in the
MacgowanTestRWISRawSurface table … where are the records with the NULL
SensorId(s) in the XML data ? Is this is a side effect of my
self-join with the MacgowanTestRWISRawSurface table ?
Any comments?
Thanks,
Chris
[Alphanumericdata].[dbo].[MacgowanTestRWISRawAtmospheric]
RecordId DataSourceId
ProductInstanceId
SystemId RpuId SensorId ObsDateTime
-- --
-- --
1
OH
5abbbc86-fb2c-4703-9589-b55f763ee150
200 0
NULL NULL
2
OH
5abbbc86-fb2c-4703-9589-b55f763ee150
200 1
NULL NULL
3
OH
5abbbc86-fb2c-4703-9589-b55f763ee150
200 2
NULL NULL
4
OH
5abbbc86-fb2c-4703-9589-b55f763ee150
200 3
NULL NULL
[Alphanumericdata].[dbo].[MacgowanTestRWISRawSurface]
RecordId DataSourceId SystemId RpuId SensorId ObsDateTime
-- -- --
1
OH
200 0
0 2005-12-05 15:48:00.000
2
OH
200 0
1 2005-12-05 15:48:00.000
3
OH
200 0
NULL NULL
4
OH
200 1
0 2005-12-05 15:49:00.000
5
OH
200 1
NULL NULL
6
OH
200 2
0 2005-12-05 15:50:00.000
7
OH
200 2
1 2005-12-05 15:50:00.000
8
OH
200 2
NULL NULL
9
OH
200 3
0 2005-12-05 15:45:00.000
10
OH
200 3
1 2005-12-05 15:45:00.000
11
OH
200 3
NULL NULL
//////////////////////////////////////////////////////
// Schema File
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="MacgowanTest_Atmospheric_Surface"
parent="MacgowanTestRWISRawAtmospheric"
parent-key="SystemId RpuId"
child="MacgowanTestRWISRawSurface"
child-key="SystemId RpuId" />
</xsd:appinfo>
</xsd:annotation>
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="MacgowanTest_Surface_Surface"
parent="MacgowanTestRWISRawSurface"
parent-key="SystemId RpuId"
child="MacgowanTestRWISRawSurface"
child-key="SystemId RpuId" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="site" sql:relation="MacgowanTestRWISRawAtmospheric" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="sensors" sql:relation="MacgowanTestRWISRawSurface"
sql:relationship="MacgowanTest_Atmospheric_Surface">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="surface" sql:relation="MacgowanTestRWISRawSurface"
sql:relationship="MacgowanTest_Surface_Surface" >
<xsd:complexType>
<xsd:attribute name="id" type="xsd:string" sql:field="SensorId"/>
<xsd:attribute name="datetime" type="xsd:date"
sql:field="ObsDateTime" />
<xsd:attribute name="surfacecondition" type="xsd:string"
sql:field="SurfaceCondition"/>
<xsd:attribute name="surfacetemp" type="xsd:string"
sql:field="SurfaceTemperature"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="sysid" type="xsd:string" sql:field="SystemId"/>
<xsd:attribute name="rpuid" type="xsd:string" sql:field="RpuId"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>
//////////////////////////////////////////////////////
// Xml Data
<?xml version="1.0"?>
<odot_rwis_site_info>
<site id="200000" number="1" sysid="200" rpuid="0"
name="1-SR127 @. SR249" longitude="-84.554946" latitude="41.383527">
<atmospheric datetime="12/05/2005 03:48:00 PM"
airtemp="-490" dewpoint="-800" relativehumidity="73" windspeedavg="11"
windspeedgust="19" winddirectionavg="265" winddirectiongust="295"
pressure="65535" precipitationintensity="None" precipitationtype="None"
precipitationrate="0" precipitationaccumulation="-1" visibility="2000"
/>
<sensors>
<surface id="0" datetime="12/05/2005
03:48:00 PM" name="North Bound Driving Lane" surfacecondition="Dry"
surfacetemp="1900" freezingtemp="32767" chemicalfactor="255"
chemicalpercent="255" depth="32767" icepercent="255"
subsurfacetemp="450" waterlevel="0">
<traffic
datetime="12/05/2005 03:48:00 PM" occupancy="0" avgspeed="82"
volume="21" sftemp="1900" sfstate="255">
<normalbins>
<bin datetime="12/05/2005 03:48:00 PM" binnumber="0" bincount="7"
/>
<bin datetime="12/05/2005 03:48:00 PM" binnumber="1" bincount="0"
/>
</normalbins>
<longbins>
<bin datetime="12/05/2005 03:48:00 PM" binnumber="2" bincount="0"
/>
<bin datetime="12/05/2005 03:48:00 PM" binnumber="3" bincount="0"
/>
<bin datetime="12/05/2005 03:48:00 PM" binnumber="4" bincount="1"
/>
<bin datetime="12/05/2005 03:48:00 PM" binnumber="5" bincount="0"
/>
</longbins>
</traffic>
</surface>
<surface id="1" datetime="12/05/2005
03:48:00 PM" name="Bridge Deck Simulator" surfacecondition="Other"
surfacetemp="-60" freezingtemp="32767" chemicalfactor="255"
chemicalpercent="255" depth="32767" icepercent="255"
subsurfacetemp="-999999" waterlevel="0" />
</sensors>
</site>
<site id="200001" number="2" sysid="200" rpuid="1"
name="2-Hardin County Garage" longitude="-83.6148588"
latitude="40.6305358">
<atmospheric datetime="12/05/2005 03:49:00 PM"
airtemp="-590" dewpoint="-900" relativehumidity="75" windspeedavg="9"
windspeedgust="22" winddirectionavg="303" winddirectiongust="299"
pressure="65535" precipitationintensity="None" precipitationtype="None"
precipitationrate="0" precipitationaccumulation="-1" visibility="2000"
/>
<sensors>
<surface id="0" datetime="12/05/2005
03:49:00 PM" name="Bridge Deck Simulator" surfacecondition="Other"
surfacetemp="-410" freezingtemp="32767" chemicalfactor="255"
chemicalpercent="255" depth="32767" icepercent="255"
subsurfacetemp="300" waterlevel="0" />
</sensors>
</site>
<site id="200002" number="3" sysid="200" rpuid="2"
name="3-US24 @. Indiana Line" longitude="-84.769597"
latitude="41.17308">
<atmospheric datetime="12/05/2005 03:50:00 PM"
airtemp="-500" dewpoint="-900" relativehumidity="68" windspeedavg="9"
windspeedgust="19" winddirectionavg="279" winddirectiongust="313"
pressure="65535" precipitationintensity="Light"
precipitationtype="Snow" precipitationrate="115"
precipitationaccumulation="-1" visibility="2000" />
<sensors>
<surface id="0" datetime="12/05/2005
03:50:00 PM" name="North Bound Driving Lane" surfacecondition="Dry"
surfacetemp="-100" freezingtemp="32767" chemicalfactor="255"
chemicalpercent="255" depth="32767" icepercent="255"
subsurfacetemp="160" waterlevel="0">
<traffic
datetime="12/05/2005 03:50:00 PM" occupancy="1" avgspeed="89"
volume="16" sftemp="-100" sfstate="255">
<normalbins>
<bin datetime="12/05/2005 03:50:00 PM" binnumber="0" bincount="8"
/>
<bin datetime="12/05/2005 03:50:00 PM" binnumber="1" bincount="0"
/>
</normalbins>
<longbins>
<bin datetime="12/05/2005 03:50:00 PM" binnumber="2" bincount="0"
/>
<bin datetime="12/05/2005 03:50:00 PM" binnumber="3" bincount="0"
/>
<bin datetime="12/05/2005 03:50:00 PM" binnumber="4" bincount="1"
/>
<bin datetime="12/05/2005 03:50:00 PM" binnumber="5" bincount="7"
/>
</longbins>
</traffic>
</surface>
<surface id="1" datetime="12/05/2005
03:50:00 PM" name="Bridge Deck Simulator" surfacecondition="Other"
surfacetemp="-130" freezingtemp="32767" chemicalfactor="255"
chemicalpercent="255" depth="32767" icepercent="255"
subsurfacetemp="-999999" waterlevel="0" />
</sensors>
</site>
<site id="200003" number="4" sysid="200" rpuid="3"
name="4-US224 @. SR190" longitude="-84.272065" latitude="40.94717">
<atmospheric datetime="12/05/2005 03:45:00 PM"
airtemp="-670" dewpoint="-900" relativehumidity="82" windspeedavg="6"
windspeedgust="20" winddirectionavg="216" winddirectiongust="275"
pressure="65535" precipitationintensity="None" precipitationtype="None"
precipitationrate="0" precipitationaccumulation="-1" visibility="2000"
/>
<sensors>
<surface id="0" datetime="12/05/2005
03:45:00 PM" name="North Bound Driving Lane" surfacecondition="Dry"
surfacetemp="100" freezingtemp="32767" chemicalfactor="255"
chemicalpercent="255" depth="32767" icepercent="255"
subsurfacetemp="300" waterlevel="0">
<traffic
datetime="12/05/2005 03:45:00 PM" occupancy="0" avgspeed="108"
volume="11" sftemp="100" sfstate="255">
<normalbins>
<bin datetime="12/05/2005 03:45:00 PM" binnumber="0" bincount="10"
/>
<bin datetime="12/05/2005 03:45:00 PM" binnumber="1" bincount="0"
/>
</normalbins>
<longbins>
<bin datetime="12/05/2005 03:45:00 PM" binnumber="2" bincount="0"
/>
<bin datetime="12/05/2005 03:45:00 PM" binnumber="3" bincount="0"
/>
<bin datetime="12/05/2005 03:45:00 PM" binnumber="4" bincount="1"
/>
<bin datetime="12/05/2005 03:45:00 PM" binnumber="5" bincount="0"
/>
</longbins>
</traffic>
</surface>
<surface id="1" datetime="12/05/2005
03:45:00 PM" name="Bridge Deck Simulator" surfacecondition="Other"
surfacetemp="-310" freezingtemp="32767" chemicalfactor="255"
chemicalpercent="255" depth="32767" icepercent="255"
subsurfacetemp="-999999" waterlevel="0" />
</sensors>
</site>
</odot_rwis_site_info>
It appears that the SensorId's are NULL in the rows corresponding to the "site" elements. The way your mapping is written, each 'site' element gets its own row in MacgowanTestRWISRawSurface, and each surface element gets its own row in MacgowanTestRWISRawSurface. The NULLs show up because "site" doesnt have SensorID mapped anywhere.
Bulk Load XML into SQL using Schema File ...
Hi ...
I am bulk loading XML data into SQL using SQLXMLBulkLoad.SQLXMLBulkload.3.0 COM Object. To do this I provide the xml data and a schema file (below).
When I use the attached schema file I get the following error:
No data was provided for column 'SensorId' on table 'MacgowanTestRWISRawSurface', and this column cannot contain NULL values.
I changed the table definition to allow nulls in the SensorId column and the bulk load was successful. Notice on the records in the MacgowanTestRWISRawSurface table … where are the records with the NULL SensorId(s) in the XML data ? Is this is a side effect of my self-join with the MacgowanTestRWISRawSurface table ?
Any comments?
Thanks,
Chris
I am bulk loading XML data into SQL using SQLXMLBulkLoad.SQLXMLBulkload.3.0 COM Object. To do this I provide the xml data and a schema file (below).
When I use the attached schema file I get the following error:
No data was provided for column 'SensorId' on table 'MacgowanTestRWISRawSurface', and this column cannot contain NULL values.
I changed the table definition to allow nulls in the SensorId column and the bulk load was successful. Notice on the records in the MacgowanTestRWISRawSurface table … where are the records with the NULL SensorId(s) in the XML data ? Is this is a side effect of my self-join with the MacgowanTestRWISRawSurface table ?
Any comments?
Thanks,
Chris
[Alphanumericdata].[dbo].[MacgowanTestRWISRawAtmospheric]
RecordId DataSourceId ProductInstanceId SystemId RpuId SensorId ObsDateTime
-- -- -- --
1 OH 5abbbc86-fb2c-4703-9589-b55f763ee150 200 0 NULL NULL
2 OH 5abbbc86-fb2c-4703-9589-b55f763ee150 200 1 NULL NULL
3 OH 5abbbc86-fb2c-4703-9589-b55f763ee150 200 2 NULL NULL
4 OH 5abbbc86-fb2c-4703-9589-b55f763ee150 200 3 NULL NULL
[Alphanumericdata].[dbo].[MacgowanTestRWISRawSurface]
RecordId DataSourceId SystemId RpuId SensorId ObsDateTime
-- -- --
1 OH 200 0 0 2005-12-05 15:48:00.000
2 OH 200 0 1 2005-12-05 15:48:00.000
3 OH 200 0 NULL NULL
4 OH 200 1 0 2005-12-05 15:49:00.000
5 OH 200 1 NULL NULL
6 OH 200 2 0 2005-12-05 15:50:00.000
7 OH 200 2 1 2005-12-05 15:50:00.000
8 OH 200 2 NULL NULL
9 OH 200 3 0 2005-12-05 15:45:00.000
10 OH 200 3 1 2005-12-05 15:45:00.000
11 OH 200 3 NULL NULL
//////////////////////////////////////////////////////
// Schema File
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="MacgowanTest_Atmospheric_Surface"
parent="MacgowanTestRWISRawAtmospheric"
parent-key="SystemId RpuId"
child="MacgowanTestRWISRawSurface"
child-key="SystemId RpuId" />
</xsd:appinfo>
</xsd:annotation>
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="MacgowanTest_Surface_Surface"
parent="MacgowanTestRWISRawSurface"
parent-key="SystemId RpuId"
child="MacgowanTestRWISRawSurface"
child-key="SystemId RpuId" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="site" sql:relation="MacgowanTestRWISRawAtmospheric" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="sensors" sql:relation="MacgowanTestRWISRawSurface"
sql:relationship="MacgowanTest_Atmospheric_Surface">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="surface" sql:relation="MacgowanTestRWISRawSurface"
sql:relationship="MacgowanTest_Surface_Surface" >
<xsd:complexType>
<xsd:attribute name="id" type="xsd:string" sql:field="SensorId"/>
<xsd:attribute name="datetime" type="xsd:date" sql:field="ObsDateTime" />
<xsd:attribute name="surfacecondition" type="xsd:string" sql:field="SurfaceCondition"/>
<xsd:attribute name="surfacetemp" type="xsd:string" sql:field="SurfaceTemperature"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="sysid" type="xsd:string" sql:field="SystemId"/>
<xsd:attribute name="rpuid" type="xsd:string" sql:field="RpuId"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>
//////////////////////////////////////////////////////
// Xml Data
<?xml version="1.0"?>
<odot_rwis_site_info>
<site id="200000" number="1" sysid="200" rpuid="0" name="1-SR127 @. SR249" longitude="-84.554946" latitude="41.383527">
<atmospheric datetime="12/05/2005 03:48:00 PM" airtemp="-490" dewpoint="-800" relativehumidity="73" windspeedavg="11" windspeedgust="19" winddirectionavg="265" winddirectiongust="295" pressure="65535" precipitationintensity="None" precipitationtype="None" precipitationrate="0" precipitationaccumulation="-1" visibility="2000" />
<sensors>
<surface id="0" datetime="12/05/2005 03:48:00 PM" name="North Bound Driving Lane" surfacecondition="Dry" surfacetemp="1900" freezingtemp="32767" chemicalfactor="255" chemicalpercent="255" depth="32767" icepercent="255" subsurfacetemp="450" waterlevel="0">
<traffic datetime="12/05/2005 03:48:00 PM" occupancy="0" avgspeed="82" volume="21" sftemp="1900" sfstate="255">
<normalbins>
<bin datetime="12/05/2005 03:48:00 PM" binnumber="0" bincount="7" />
<bin datetime="12/05/2005 03:48:00 PM" binnumber="1" bincount="0" />
</normalbins>
<longbins>
<bin datetime="12/05/2005 03:48:00 PM" binnumber="2" bincount="0" />
<bin datetime="12/05/2005 03:48:00 PM" binnumber="3" bincount="0" />
<bin datetime="12/05/2005 03:48:00 PM" binnumber="4" bincount="1" />
<bin datetime="12/05/2005 03:48:00 PM" binnumber="5" bincount="0" />
</longbins>
</traffic>
</surface>
<surface id="1" datetime="12/05/2005 03:48:00 PM" name="Bridge Deck Simulator" surfacecondition="Other" surfacetemp="-60" freezingtemp="32767" chemicalfactor="255" chemicalpercent="255" depth="32767" icepercent="255" subsurfacetemp="-999999" waterlevel="0" />
</sensors>
</site>
<site id="200001" number="2" sysid="200" rpuid="1" name="2-Hardin County Garage" longitude="-83.6148588" latitude="40.6305358">
<atmospheric datetime="12/05/2005 03:49:00 PM" airtemp="-590" dewpoint="-900" relativehumidity="75" windspeedavg="9" windspeedgust="22" winddirectionavg="303" winddirectiongust="299" pressure="65535" precipitationintensity="None" precipitationtype="None" precipitationrate="0" precipitationaccumulation="-1" visibility="2000" />
<sensors>
<surface id="0" datetime="12/05/2005 03:49:00 PM" name="Bridge Deck Simulator" surfacecondition="Other" surfacetemp="-410" freezingtemp="32767" chemicalfactor="255" chemicalpercent="255" depth="32767" icepercent="255" subsurfacetemp="300" waterlevel="0" />
</sensors>
</site>
<site id="200002" number="3" sysid="200" rpuid="2" name="3-US24 @. Indiana Line" longitude="-84.769597" latitude="41.17308">
<atmospheric datetime="12/05/2005 03:50:00 PM" airtemp="-500" dewpoint="-900" relativehumidity="68" windspeedavg="9" windspeedgust="19" winddirectionavg="279" winddirectiongust="313" pressure="65535" precipitationintensity="Light" precipitationtype="Snow" precipitationrate="115" precipitationaccumulation="-1" visibility="2000" />
<sensors>
<surface id="0" datetime="12/05/2005 03:50:00 PM" name="North Bound Driving Lane" surfacecondition="Dry" surfacetemp="-100" freezingtemp="32767" chemicalfactor="255" chemicalpercent="255" depth="32767" icepercent="255" subsurfacetemp="160" waterlevel="0">
<traffic datetime="12/05/2005 03:50:00 PM" occupancy="1" avgspeed="89" volume="16" sftemp="-100" sfstate="255">
<normalbins>
<bin datetime="12/05/2005 03:50:00 PM" binnumber="0" bincount="8" />
<bin datetime="12/05/2005 03:50:00 PM" binnumber="1" bincount="0" />
</normalbins>
<longbins>
<bin datetime="12/05/2005 03:50:00 PM" binnumber="2" bincount="0" />
<bin datetime="12/05/2005 03:50:00 PM" binnumber="3" bincount="0" />
<bin datetime="12/05/2005 03:50:00 PM" binnumber="4" bincount="1" />
<bin datetime="12/05/2005 03:50:00 PM" binnumber="5" bincount="7" />
</longbins>
</traffic>
</surface>
<surface id="1" datetime="12/05/2005 03:50:00 PM" name="Bridge Deck Simulator" surfacecondition="Other" surfacetemp="-130" freezingtemp="32767" chemicalfactor="255" chemicalpercent="255" depth="32767" icepercent="255" subsurfacetemp="-999999" waterlevel="0" />
</sensors>
</site>
<site id="200003" number="4" sysid="200" rpuid="3" name="4-US224 @. SR190" longitude="-84.272065" latitude="40.94717">
<atmospheric datetime="12/05/2005 03:45:00 PM" airtemp="-670" dewpoint="-900" relativehumidity="82" windspeedavg="6" windspeedgust="20" winddirectionavg="216" winddirectiongust="275" pressure="65535" precipitationintensity="None" precipitationtype="None" precipitationrate="0" precipitationaccumulation="-1" visibility="2000" />
<sensors>
<surface id="0" datetime="12/05/2005 03:45:00 PM" name="North Bound Driving Lane" surfacecondition="Dry" surfacetemp="100" freezingtemp="32767" chemicalfactor="255" chemicalpercent="255" depth="32767" icepercent="255" subsurfacetemp="300" waterlevel="0">
<traffic datetime="12/05/2005 03:45:00 PM" occupancy="0" avgspeed="108" volume="11" sftemp="100" sfstate="255">
<normalbins>
<bin datetime="12/05/2005 03:45:00 PM" binnumber="0" bincount="10" />
<bin datetime="12/05/2005 03:45:00 PM" binnumber="1" bincount="0" />
</normalbins>
<longbins>
<bin datetime="12/05/2005 03:45:00 PM" binnumber="2" bincount="0" />
<bin datetime="12/05/2005 03:45:00 PM" binnumber="3" bincount="0" />
<bin datetime="12/05/2005 03:45:00 PM" binnumber="4" bincount="1" />
<bin datetime="12/05/2005 03:45:00 PM" binnumber="5" bincount="0" />
</longbins>
</traffic>
</surface>
<surface id="1" datetime="12/05/2005 03:45:00 PM" name="Bridge Deck Simulator" surfacecondition="Other" surfacetemp="-310" freezingtemp="32767" chemicalfactor="255" chemicalpercent="255" depth="32767" icepercent="255" subsurfacetemp="-999999" waterlevel="0" />
</sensors>
</site>
</odot_rwis_site_info>
It appears that the SensorId's are NULL in the rows corresponding to the "site" elements. The way your mapping is written, each 'site' element gets its own row in MacgowanTestRWISRawSurface, and each surface element gets its own row in MacgowanTestRWISRawSurface. The NULLs show up because "site" doesnt have SensorID mapped anywhere.
Bulk Load XML file to SQL Server (Express) Table
Hi All,
I have an asp.net 2.0 app that needs to bulk load data from an xml file into a Sql Server (Express) table. Is there an easy way to do this?
Thanks,
Claude.
Here is a working sample from Microsoft. Hope this helps.
http://support.microsoft.com/default.aspx/kb/316005/en-us
|||Thanks for your reply.
Are there any examples of doing this in asp.net 2.0 using version 2 of the .Net framework? I am currently writing my app in c#.
TIA,
Claude.
|||
The data is going into SQL Server so you have to use XML SQL Server will accept. The links below covers SQL Server 2005 XML. Hope this helps.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp
http://msdn2.microsoft.com/en-us/library/ms191184.aspx
sqlBulk load XML file (SQL2005)
I have saved a dataset as xml on the disk. I want to bulk load the xml file
into a table in the SQL server 2005. I have checked out openrowset (t-sql)
but it seems to me that it can only load data in one column of type xml of a
table. I
dont want to use sp_xml_preparedocument to create an in memory
representation of the xml file.
I know I can use SQLXML in my code but is'nt the functionallity also in the
new System.Data.SqlXml namespace? I have found very little info about this.
Any ideas? Other solutions?
Regards Andreas :-)
PS! I use VS.NET with ADO.NET 2.0, C# and SQL Server 2005 CTP in my project.
Hi,
You can use SQLXMLBulkload component through Interop.
Here is a reference you can use to work from :
http://msdn.microsoft.com/library/de...us/dnanchor/ht
ml/anch_SQLXML.asp
Thanks,
Monica Frintu
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
|||System.Data.Sqlxml will not ship with Visual Studio.Net 2005. So it is not
recommended to use that.
Use SQLXML 3.0 native components through interop instead.
thanks
Chandra
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"AWHK" <awhk@.newsgroup.nospam> wrote in message
news:eNvvyJQcFHA.2696@.TK2MSFTNGP09.phx.gbl...
>I have saved a dataset as xml on the disk. I want to bulk load the xml file
> into a table in the SQL server 2005. I have checked out openrowset (t-sql)
> but it seems to me that it can only load data in one column of type xml of
> a table. I
> dont want to use sp_xml_preparedocument to create an in memory
> representation of the xml file.
> I know I can use SQLXML in my code but is'nt the functionallity also in
> the
> new System.Data.SqlXml namespace? I have found very little info about
> this.
> Any ideas? Other solutions?
>
> Regards Andreas :-)
>
> PS! I use VS.NET with ADO.NET 2.0, C# and SQL Server 2005 CTP in my
> project.
>
>
|||Here is a whitepaper that explains using SqlXml3 Bulkload in .Net:
http://msdn.microsoft.com/library/de...exchsqlxml.asp
--
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"AWHK" <awhk@.newsgroup.nospam> wrote in message
news:eNvvyJQcFHA.2696@.TK2MSFTNGP09.phx.gbl...
>I have saved a dataset as xml on the disk. I want to bulk load the xml file
> into a table in the SQL server 2005. I have checked out openrowset (t-sql)
> but it seems to me that it can only load data in one column of type xml of
> a table. I
> dont want to use sp_xml_preparedocument to create an in memory
> representation of the xml file.
> I know I can use SQLXML in my code but is'nt the functionallity also in
> the
> new System.Data.SqlXml namespace? I have found very little info about
> this.
> Any ideas? Other solutions?
>
> Regards Andreas :-)
>
> PS! I use VS.NET with ADO.NET 2.0, C# and SQL Server 2005 CTP in my
> project.
>
>
into a table in the SQL server 2005. I have checked out openrowset (t-sql)
but it seems to me that it can only load data in one column of type xml of a
table. I
dont want to use sp_xml_preparedocument to create an in memory
representation of the xml file.
I know I can use SQLXML in my code but is'nt the functionallity also in the
new System.Data.SqlXml namespace? I have found very little info about this.
Any ideas? Other solutions?
Regards Andreas :-)
PS! I use VS.NET with ADO.NET 2.0, C# and SQL Server 2005 CTP in my project.
Hi,
You can use SQLXMLBulkload component through Interop.
Here is a reference you can use to work from :
http://msdn.microsoft.com/library/de...us/dnanchor/ht
ml/anch_SQLXML.asp
Thanks,
Monica Frintu
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
|||System.Data.Sqlxml will not ship with Visual Studio.Net 2005. So it is not
recommended to use that.
Use SQLXML 3.0 native components through interop instead.
thanks
Chandra
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"AWHK" <awhk@.newsgroup.nospam> wrote in message
news:eNvvyJQcFHA.2696@.TK2MSFTNGP09.phx.gbl...
>I have saved a dataset as xml on the disk. I want to bulk load the xml file
> into a table in the SQL server 2005. I have checked out openrowset (t-sql)
> but it seems to me that it can only load data in one column of type xml of
> a table. I
> dont want to use sp_xml_preparedocument to create an in memory
> representation of the xml file.
> I know I can use SQLXML in my code but is'nt the functionallity also in
> the
> new System.Data.SqlXml namespace? I have found very little info about
> this.
> Any ideas? Other solutions?
>
> Regards Andreas :-)
>
> PS! I use VS.NET with ADO.NET 2.0, C# and SQL Server 2005 CTP in my
> project.
>
>
|||Here is a whitepaper that explains using SqlXml3 Bulkload in .Net:
http://msdn.microsoft.com/library/de...exchsqlxml.asp
--
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"AWHK" <awhk@.newsgroup.nospam> wrote in message
news:eNvvyJQcFHA.2696@.TK2MSFTNGP09.phx.gbl...
>I have saved a dataset as xml on the disk. I want to bulk load the xml file
> into a table in the SQL server 2005. I have checked out openrowset (t-sql)
> but it seems to me that it can only load data in one column of type xml of
> a table. I
> dont want to use sp_xml_preparedocument to create an in memory
> representation of the xml file.
> I know I can use SQLXML in my code but is'nt the functionallity also in
> the
> new System.Data.SqlXml namespace? I have found very little info about
> this.
> Any ideas? Other solutions?
>
> Regards Andreas :-)
>
> PS! I use VS.NET with ADO.NET 2.0, C# and SQL Server 2005 CTP in my
> project.
>
>
Bulk Load with Parent / Child failing
I'm trying to bulk load an xml with parent child nodes.
I have setup an sql:relationship and I have also setup the two database
columns as Identity columns. However when I try to load the data, SQL is
telling me that the column can not be NULL ...
Any ideas please ?
Michael Tissington
http://www.oaklodge.com
http://www.sqlview.netHello Michael,
Thank you for using Microsoft MSDN newsgroup!
I understand you encountered the issue when importing xml with parent/child
nodes. To get a better idea of the exact issue, will you send the xml files
and script to create the tables in database to me so that I may reproduce
the issue on my side? You could send it to me at petery@.microsoft.com.
Also, you may want to refer to the following article for some know issue:
330582 PRB: "No Data Was Provided for Column" Error Message When You
Propagate Identity Column with SQLXMLBulkLoad
http://support.microsoft.com/defaul...kb;EN-US;330582
I look forward to your reply.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
I have setup an sql:relationship and I have also setup the two database
columns as Identity columns. However when I try to load the data, SQL is
telling me that the column can not be NULL ...
Any ideas please ?
Michael Tissington
http://www.oaklodge.com
http://www.sqlview.netHello Michael,
Thank you for using Microsoft MSDN newsgroup!
I understand you encountered the issue when importing xml with parent/child
nodes. To get a better idea of the exact issue, will you send the xml files
and script to create the tables in database to me so that I may reproduce
the issue on my side? You could send it to me at petery@.microsoft.com.
Also, you may want to refer to the following article for some know issue:
330582 PRB: "No Data Was Provided for Column" Error Message When You
Propagate Identity Column with SQLXMLBulkLoad
http://support.microsoft.com/defaul...kb;EN-US;330582
I look forward to your reply.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
Tuesday, March 27, 2012
Bulk Load Strange XML Format
I have an application that exports an XML file in a strange format. I would like to be able to upload this file into an SQL 2000 database using Bulk Load. I have been able to complete some of the various examples and have a handle on what I need to do in the Bulk Load.
The problem I have is understanding how to load the data (in the form the application exports) into a relational format. My data does not contain any indexes and has multiple tables that will need to be created. A snippet of the XML is copied below:
================================================== ==============================
<Round Cn="Amherst Golf Course" Cc="Amherst" Cs="NS" Cy="CA" St="-1030918208" Et="-1030902963" Sf="0" Pt="31" Gm="3" Op="0">
<Rp N="Steve Peddle" S="STEVE" Hv="25" Te="1">
<Sc h1="7" h2="6" h3="6" h4="3" h5="7" h6="6" h7="6" h8="5" h9="5" h10="5" h11="6" h12="5" h13="3" h14="6" h15="4" h16="6" h17="5" h18="6"/>
<Pt h1="1" h2="2" h3="2" h4="1" h5="2" h6="1" h7="2" h8="1" h9="2" h10="2" h11="2" h12="2" h13="1" h14="2" h15="2" h16="2" h17="2" h18="1"/>
</Rp>
<Rp N="Chris White" S="Chris" Hv="18" Te="1">
<Sc h1="7" h2="5" h3="5" h4="3" h5="8" h6="4" h7="5" h8="6" h9="4" h10="5" h11="6" h12="5" h13="4" h14="6" h15="4" h16="5" h17="5" h18="7"/>
<Pt h1="2" h2="2" h3="1" h4="1" h5="3" h6="1" h7="2" h8="3" h9="2" h10="2" h11="2" h12="2" h13="2" h14="2" h15="2" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Scott McKillop" S="Scott" Hv="14" Te="1">
<Sc h1="7" h2="5" h3="4" h4="3" h5="7" h6="5" h7="5" h8="6" h9="4" h10="5" h11="5" h12="5" h13="4" h14="4" h15="4" h16="4" h17="5" h18="7"/>
<Pt h1="1" h2="1" h3="2" h4="2" h5="3" h6="2" h7="1" h8="2" h9="1" h10="2" h11="2" h12="3" h13="2" h14="1" h15="3" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Steve Rayworth" S="SteveR" Hv="13" Te="1">
<Sc h1="6" h2="5" h3="5" h4="3" h5="5" h6="6" h7="3" h8="5" h9="3" h10="6" h11="4" h12="5" h13="3" h14="5" h15="3" h16="4" h17="4" h18="7"/>
<Pt h1="2" h2="2" h3="2" h4="2" h5="2" h6="2" h7="1" h8="2" h9="2" h10="3" h11="1" h12="2" h13="1" h14="2" h15="2" h16="1" h17="2" h18="2"/>
<Ev h1="0" h2="0" h3="0" h4="0" h5="0" h6="0" h7="0" h8="0" h9="4" h10="0" h11="0" h12="0" h13="0" h14="0" h15="0" h16="0" h17="0" h18="0"/>
</Rp>
<Games>
<Gm I="0" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="17" M="0" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="11" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="12" H2="5" H3="1" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
</Games>
</Round>
================================================== ================================================== =============================
The application is actually used to track golf rounds.
a.. Each round can have many players;
a.. Each Player wil have 1 Score (<sc>)
b.. Each Player will have 1 Putt (<Pt>)
c.. Each Player will have 1 Ev? (<Ev>)
b.. Each Round can have 1 Games
a.. Each Games can have multiple Gm's (<Gm>)
The problem with this structure is that there are no indexes in the file, and from what I understand about the Bulk Load and relational databases, is that it will be next to impossible to link the data properly after importing.
Is there any way that this can be imported into SQL 2000? Will I have to write an application using VB to manually create the indexes and then import? The solution that I come up with will have to be repeatable as the data will need to be updated with a new XML file often.
If the entire XML file will help with understanding the scope of the issue, I can attach it.
Thanks in advance
Do golfers have ID's or anything? If not, can you just add an ID column that will auto-generate and id for each row? You could then relate them using that.
Irwin Dolobowsky
Program Manager - SqlXml
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott McKillop" <scott_mckillop_NOSPAM_MAN@.adlt.com_REMOVE_CAPS> wrote in message news:ubT1rwsMEHA.3420@.TK2MSFTNGP11.phx.gbl...
I have an application that exports an XML file in a strange format. I would like to be able to upload this file into an SQL 2000 database using Bulk Load. I have been able to complete some of the various examples and have a handle on what I need to do in the Bulk Load.
The problem I have is understanding how to load the data (in the form the application exports) into a relational format. My data does not contain any indexes and has multiple tables that will need to be created. A snippet of the XML is copied below:
================================================== ==============================
<Round Cn="Amherst Golf Course" Cc="Amherst" Cs="NS" Cy="CA" St="-1030918208" Et="-1030902963" Sf="0" Pt="31" Gm="3" Op="0">
<Rp N="Steve Peddle" S="STEVE" Hv="25" Te="1">
<Sc h1="7" h2="6" h3="6" h4="3" h5="7" h6="6" h7="6" h8="5" h9="5" h10="5" h11="6" h12="5" h13="3" h14="6" h15="4" h16="6" h17="5" h18="6"/>
<Pt h1="1" h2="2" h3="2" h4="1" h5="2" h6="1" h7="2" h8="1" h9="2" h10="2" h11="2" h12="2" h13="1" h14="2" h15="2" h16="2" h17="2" h18="1"/>
</Rp>
<Rp N="Chris White" S="Chris" Hv="18" Te="1">
<Sc h1="7" h2="5" h3="5" h4="3" h5="8" h6="4" h7="5" h8="6" h9="4" h10="5" h11="6" h12="5" h13="4" h14="6" h15="4" h16="5" h17="5" h18="7"/>
<Pt h1="2" h2="2" h3="1" h4="1" h5="3" h6="1" h7="2" h8="3" h9="2" h10="2" h11="2" h12="2" h13="2" h14="2" h15="2" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Scott McKillop" S="Scott" Hv="14" Te="1">
<Sc h1="7" h2="5" h3="4" h4="3" h5="7" h6="5" h7="5" h8="6" h9="4" h10="5" h11="5" h12="5" h13="4" h14="4" h15="4" h16="4" h17="5" h18="7"/>
<Pt h1="1" h2="1" h3="2" h4="2" h5="3" h6="2" h7="1" h8="2" h9="1" h10="2" h11="2" h12="3" h13="2" h14="1" h15="3" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Steve Rayworth" S="SteveR" Hv="13" Te="1">
<Sc h1="6" h2="5" h3="5" h4="3" h5="5" h6="6" h7="3" h8="5" h9="3" h10="6" h11="4" h12="5" h13="3" h14="5" h15="3" h16="4" h17="4" h18="7"/>
<Pt h1="2" h2="2" h3="2" h4="2" h5="2" h6="2" h7="1" h8="2" h9="2" h10="3" h11="1" h12="2" h13="1" h14="2" h15="2" h16="1" h17="2" h18="2"/>
<Ev h1="0" h2="0" h3="0" h4="0" h5="0" h6="0" h7="0" h8="0" h9="4" h10="0" h11="0" h12="0" h13="0" h14="0" h15="0" h16="0" h17="0" h18="0"/>
</Rp>
<Games>
<Gm I="0" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="17" M="0" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="11" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="12" H2="5" H3="1" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
</Games>
</Round>
================================================== ================================================== =============================
The application is actually used to track golf rounds.
a.. Each round can have many players;
a.. Each Player wil have 1 Score (<sc>)
b.. Each Player will have 1 Putt (<Pt>)
c.. Each Player will have 1 Ev? (<Ev>)
b.. Each Round can have 1 Games
a.. Each Games can have multiple Gm's (<Gm>)
The problem with this structure is that there are no indexes in the file, and from what I understand about the Bulk Load and relational databases, is that it will be next to impossible to link the data properly after importing.
Is there any way that this can be imported into SQL 2000? Will I have to write an application using VB to manually create the indexes and then import? The solution that I come up with will have to be repeatable as the data will need to be updated with a new XML file often.
If the entire XML file will help with understanding the scope of the issue, I can attach it.
Thanks in advance
|||Thanks for your response! I think I may have stumbled upon something that will help... I was finding mixed documents on the internet regarding the Bulk Load functionality... But I think the trick is to use Version 3.0SP2 with identities to get this to work...
I'm going to start with some easier samples and hopefully fluke my way through it. If I have a more precise and meaningful question in my experiments, I'll holler!
Thanks Again!
"Irwin Dolobowsky [MS]" <irwind@.mail.microsoft.com> wrote in message news:%23BIJuAwMEHA.3208@.TK2MSFTNGP10.phx.gbl...
Do golfers have ID's or anything? If not, can you just add an ID column that will auto-generate and id for each row? You could then relate them using that.
Irwin Dolobowsky
Program Manager - SqlXml
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott McKillop" <scott_mckillop_NOSPAM_MAN@.adlt.com_REMOVE_CAPS> wrote in message news:ubT1rwsMEHA.3420@.TK2MSFTNGP11.phx.gbl...
I have an application that exports an XML file in a strange format. I would like to be able to upload this file into an SQL 2000 database using Bulk Load. I have been able to complete some of the various examples and have a handle on what I need to do in the Bulk Load.
The problem I have is understanding how to load the data (in the form the application exports) into a relational format. My data does not contain any indexes and has multiple tables that will need to be created. A snippet of the XML is copied below:
================================================== ==============================
<Round Cn="Amherst Golf Course" Cc="Amherst" Cs="NS" Cy="CA" St="-1030918208" Et="-1030902963" Sf="0" Pt="31" Gm="3" Op="0">
<Rp N="Steve Peddle" S="STEVE" Hv="25" Te="1">
<Sc h1="7" h2="6" h3="6" h4="3" h5="7" h6="6" h7="6" h8="5" h9="5" h10="5" h11="6" h12="5" h13="3" h14="6" h15="4" h16="6" h17="5" h18="6"/>
<Pt h1="1" h2="2" h3="2" h4="1" h5="2" h6="1" h7="2" h8="1" h9="2" h10="2" h11="2" h12="2" h13="1" h14="2" h15="2" h16="2" h17="2" h18="1"/>
</Rp>
<Rp N="Chris White" S="Chris" Hv="18" Te="1">
<Sc h1="7" h2="5" h3="5" h4="3" h5="8" h6="4" h7="5" h8="6" h9="4" h10="5" h11="6" h12="5" h13="4" h14="6" h15="4" h16="5" h17="5" h18="7"/>
<Pt h1="2" h2="2" h3="1" h4="1" h5="3" h6="1" h7="2" h8="3" h9="2" h10="2" h11="2" h12="2" h13="2" h14="2" h15="2" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Scott McKillop" S="Scott" Hv="14" Te="1">
<Sc h1="7" h2="5" h3="4" h4="3" h5="7" h6="5" h7="5" h8="6" h9="4" h10="5" h11="5" h12="5" h13="4" h14="4" h15="4" h16="4" h17="5" h18="7"/>
<Pt h1="1" h2="1" h3="2" h4="2" h5="3" h6="2" h7="1" h8="2" h9="1" h10="2" h11="2" h12="3" h13="2" h14="1" h15="3" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Steve Rayworth" S="SteveR" Hv="13" Te="1">
<Sc h1="6" h2="5" h3="5" h4="3" h5="5" h6="6" h7="3" h8="5" h9="3" h10="6" h11="4" h12="5" h13="3" h14="5" h15="3" h16="4" h17="4" h18="7"/>
<Pt h1="2" h2="2" h3="2" h4="2" h5="2" h6="2" h7="1" h8="2" h9="2" h10="3" h11="1" h12="2" h13="1" h14="2" h15="2" h16="1" h17="2" h18="2"/>
<Ev h1="0" h2="0" h3="0" h4="0" h5="0" h6="0" h7="0" h8="0" h9="4" h10="0" h11="0" h12="0" h13="0" h14="0" h15="0" h16="0" h17="0" h18="0"/>
</Rp>
<Games>
<Gm I="0" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="17" M="0" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="11" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="12" H2="5" H3="1" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
</Games>
</Round>
================================================== ================================================== =============================
The application is actually used to track golf rounds.
a.. Each round can have many players;
a.. Each Player wil have 1 Score (<sc>)
b.. Each Player will have 1 Putt (<Pt>)
c.. Each Player will have 1 Ev? (<Ev>)
b.. Each Round can have 1 Games
a.. Each Games can have multiple Gm's (<Gm>)
The problem with this structure is that there are no indexes in the file, and from what I understand about the Bulk Load and relational databases, is that it will be next to impossible to link the data properly after importing.
Is there any way that this can be imported into SQL 2000? Will I have to write an application using VB to manually create the indexes and then import? The solution that I come up with will have to be repeatable as the data will need to be updated with a new XML file often.
If the entire XML file will help with understanding the scope of the issue, I can attach it.
Thanks in advance
|||That's where I was going. Glad you found it.
Irwin
Irwin Dolobowsky
Program Manager - SqlXml
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott McKillop" <smckillop@.NOSPAM.ns.sympatico.ca.REMOVECAPS> wrote in message news:%23PKm2RwMEHA.1348@.TK2MSFTNGP12.phx.gbl...
Thanks for your response! I think I may have stumbled upon something that will help... I was finding mixed documents on the internet regarding the Bulk Load functionality... But I think the trick is to use Version 3.0SP2 with identities to get this to work...
I'm going to start with some easier samples and hopefully fluke my way through it. If I have a more precise and meaningful question in my experiments, I'll holler!
Thanks Again!
"Irwin Dolobowsky [MS]" <irwind@.mail.microsoft.com> wrote in message news:%23BIJuAwMEHA.3208@.TK2MSFTNGP10.phx.gbl...
Do golfers have ID's or anything? If not, can you just add an ID column that will auto-generate and id for each row? You could then relate them using that.
Irwin Dolobowsky
Program Manager - SqlXml
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott McKillop" <scott_mckillop_NOSPAM_MAN@.adlt.com_REMOVE_CAPS> wrote in message news:ubT1rwsMEHA.3420@.TK2MSFTNGP11.phx.gbl...
I have an application that exports an XML file in a strange format. I would like to be able to upload this file into an SQL 2000 database using Bulk Load. I have been able to complete some of the various examples and have a handle on what I need to do in the Bulk Load.
The problem I have is understanding how to load the data (in the form the application exports) into a relational format. My data does not contain any indexes and has multiple tables that will need to be created. A snippet of the XML is copied below:
================================================== ==============================
<Round Cn="Amherst Golf Course" Cc="Amherst" Cs="NS" Cy="CA" St="-1030918208" Et="-1030902963" Sf="0" Pt="31" Gm="3" Op="0">
<Rp N="Steve Peddle" S="STEVE" Hv="25" Te="1">
<Sc h1="7" h2="6" h3="6" h4="3" h5="7" h6="6" h7="6" h8="5" h9="5" h10="5" h11="6" h12="5" h13="3" h14="6" h15="4" h16="6" h17="5" h18="6"/>
<Pt h1="1" h2="2" h3="2" h4="1" h5="2" h6="1" h7="2" h8="1" h9="2" h10="2" h11="2" h12="2" h13="1" h14="2" h15="2" h16="2" h17="2" h18="1"/>
</Rp>
<Rp N="Chris White" S="Chris" Hv="18" Te="1">
<Sc h1="7" h2="5" h3="5" h4="3" h5="8" h6="4" h7="5" h8="6" h9="4" h10="5" h11="6" h12="5" h13="4" h14="6" h15="4" h16="5" h17="5" h18="7"/>
<Pt h1="2" h2="2" h3="1" h4="1" h5="3" h6="1" h7="2" h8="3" h9="2" h10="2" h11="2" h12="2" h13="2" h14="2" h15="2" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Scott McKillop" S="Scott" Hv="14" Te="1">
<Sc h1="7" h2="5" h3="4" h4="3" h5="7" h6="5" h7="5" h8="6" h9="4" h10="5" h11="5" h12="5" h13="4" h14="4" h15="4" h16="4" h17="5" h18="7"/>
<Pt h1="1" h2="1" h3="2" h4="2" h5="3" h6="2" h7="1" h8="2" h9="1" h10="2" h11="2" h12="3" h13="2" h14="1" h15="3" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Steve Rayworth" S="SteveR" Hv="13" Te="1">
<Sc h1="6" h2="5" h3="5" h4="3" h5="5" h6="6" h7="3" h8="5" h9="3" h10="6" h11="4" h12="5" h13="3" h14="5" h15="3" h16="4" h17="4" h18="7"/>
<Pt h1="2" h2="2" h3="2" h4="2" h5="2" h6="2" h7="1" h8="2" h9="2" h10="3" h11="1" h12="2" h13="1" h14="2" h15="2" h16="1" h17="2" h18="2"/>
<Ev h1="0" h2="0" h3="0" h4="0" h5="0" h6="0" h7="0" h8="0" h9="4" h10="0" h11="0" h12="0" h13="0" h14="0" h15="0" h16="0" h17="0" h18="0"/>
</Rp>
<Games>
<Gm I="0" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="17" M="0" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="11" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="12" H2="5" H3="1" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
</Games>
</Round>
================================================== ================================================== =============================
The application is actually used to track golf rounds.
a.. Each round can have many players;
a.. Each Player wil have 1 Score (<sc>)
b.. Each Player will have 1 Putt (<Pt>)
c.. Each Player will have 1 Ev? (<Ev>)
b.. Each Round can have 1 Games
a.. Each Games can have multiple Gm's (<Gm>)
The problem with this structure is that there are no indexes in the file, and from what I understand about the Bulk Load and relational databases, is that it will be next to impossible to link the data properly after importing.
Is there any way that this can be imported into SQL 2000? Will I have to write an application using VB to manually create the indexes and then import? The solution that I come up with will have to be repeatable as the data will need to be updated with a new XML file often.
If the entire XML file will help with understanding the scope of the issue, I can attach it.
Thanks in advance
The problem I have is understanding how to load the data (in the form the application exports) into a relational format. My data does not contain any indexes and has multiple tables that will need to be created. A snippet of the XML is copied below:
================================================== ==============================
<Round Cn="Amherst Golf Course" Cc="Amherst" Cs="NS" Cy="CA" St="-1030918208" Et="-1030902963" Sf="0" Pt="31" Gm="3" Op="0">
<Rp N="Steve Peddle" S="STEVE" Hv="25" Te="1">
<Sc h1="7" h2="6" h3="6" h4="3" h5="7" h6="6" h7="6" h8="5" h9="5" h10="5" h11="6" h12="5" h13="3" h14="6" h15="4" h16="6" h17="5" h18="6"/>
<Pt h1="1" h2="2" h3="2" h4="1" h5="2" h6="1" h7="2" h8="1" h9="2" h10="2" h11="2" h12="2" h13="1" h14="2" h15="2" h16="2" h17="2" h18="1"/>
</Rp>
<Rp N="Chris White" S="Chris" Hv="18" Te="1">
<Sc h1="7" h2="5" h3="5" h4="3" h5="8" h6="4" h7="5" h8="6" h9="4" h10="5" h11="6" h12="5" h13="4" h14="6" h15="4" h16="5" h17="5" h18="7"/>
<Pt h1="2" h2="2" h3="1" h4="1" h5="3" h6="1" h7="2" h8="3" h9="2" h10="2" h11="2" h12="2" h13="2" h14="2" h15="2" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Scott McKillop" S="Scott" Hv="14" Te="1">
<Sc h1="7" h2="5" h3="4" h4="3" h5="7" h6="5" h7="5" h8="6" h9="4" h10="5" h11="5" h12="5" h13="4" h14="4" h15="4" h16="4" h17="5" h18="7"/>
<Pt h1="1" h2="1" h3="2" h4="2" h5="3" h6="2" h7="1" h8="2" h9="1" h10="2" h11="2" h12="3" h13="2" h14="1" h15="3" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Steve Rayworth" S="SteveR" Hv="13" Te="1">
<Sc h1="6" h2="5" h3="5" h4="3" h5="5" h6="6" h7="3" h8="5" h9="3" h10="6" h11="4" h12="5" h13="3" h14="5" h15="3" h16="4" h17="4" h18="7"/>
<Pt h1="2" h2="2" h3="2" h4="2" h5="2" h6="2" h7="1" h8="2" h9="2" h10="3" h11="1" h12="2" h13="1" h14="2" h15="2" h16="1" h17="2" h18="2"/>
<Ev h1="0" h2="0" h3="0" h4="0" h5="0" h6="0" h7="0" h8="0" h9="4" h10="0" h11="0" h12="0" h13="0" h14="0" h15="0" h16="0" h17="0" h18="0"/>
</Rp>
<Games>
<Gm I="0" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="17" M="0" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="11" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="12" H2="5" H3="1" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
</Games>
</Round>
================================================== ================================================== =============================
The application is actually used to track golf rounds.
a.. Each round can have many players;
a.. Each Player wil have 1 Score (<sc>)
b.. Each Player will have 1 Putt (<Pt>)
c.. Each Player will have 1 Ev? (<Ev>)
b.. Each Round can have 1 Games
a.. Each Games can have multiple Gm's (<Gm>)
The problem with this structure is that there are no indexes in the file, and from what I understand about the Bulk Load and relational databases, is that it will be next to impossible to link the data properly after importing.
Is there any way that this can be imported into SQL 2000? Will I have to write an application using VB to manually create the indexes and then import? The solution that I come up with will have to be repeatable as the data will need to be updated with a new XML file often.
If the entire XML file will help with understanding the scope of the issue, I can attach it.
Thanks in advance
Do golfers have ID's or anything? If not, can you just add an ID column that will auto-generate and id for each row? You could then relate them using that.
Irwin Dolobowsky
Program Manager - SqlXml
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott McKillop" <scott_mckillop_NOSPAM_MAN@.adlt.com_REMOVE_CAPS> wrote in message news:ubT1rwsMEHA.3420@.TK2MSFTNGP11.phx.gbl...
I have an application that exports an XML file in a strange format. I would like to be able to upload this file into an SQL 2000 database using Bulk Load. I have been able to complete some of the various examples and have a handle on what I need to do in the Bulk Load.
The problem I have is understanding how to load the data (in the form the application exports) into a relational format. My data does not contain any indexes and has multiple tables that will need to be created. A snippet of the XML is copied below:
================================================== ==============================
<Round Cn="Amherst Golf Course" Cc="Amherst" Cs="NS" Cy="CA" St="-1030918208" Et="-1030902963" Sf="0" Pt="31" Gm="3" Op="0">
<Rp N="Steve Peddle" S="STEVE" Hv="25" Te="1">
<Sc h1="7" h2="6" h3="6" h4="3" h5="7" h6="6" h7="6" h8="5" h9="5" h10="5" h11="6" h12="5" h13="3" h14="6" h15="4" h16="6" h17="5" h18="6"/>
<Pt h1="1" h2="2" h3="2" h4="1" h5="2" h6="1" h7="2" h8="1" h9="2" h10="2" h11="2" h12="2" h13="1" h14="2" h15="2" h16="2" h17="2" h18="1"/>
</Rp>
<Rp N="Chris White" S="Chris" Hv="18" Te="1">
<Sc h1="7" h2="5" h3="5" h4="3" h5="8" h6="4" h7="5" h8="6" h9="4" h10="5" h11="6" h12="5" h13="4" h14="6" h15="4" h16="5" h17="5" h18="7"/>
<Pt h1="2" h2="2" h3="1" h4="1" h5="3" h6="1" h7="2" h8="3" h9="2" h10="2" h11="2" h12="2" h13="2" h14="2" h15="2" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Scott McKillop" S="Scott" Hv="14" Te="1">
<Sc h1="7" h2="5" h3="4" h4="3" h5="7" h6="5" h7="5" h8="6" h9="4" h10="5" h11="5" h12="5" h13="4" h14="4" h15="4" h16="4" h17="5" h18="7"/>
<Pt h1="1" h2="1" h3="2" h4="2" h5="3" h6="2" h7="1" h8="2" h9="1" h10="2" h11="2" h12="3" h13="2" h14="1" h15="3" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Steve Rayworth" S="SteveR" Hv="13" Te="1">
<Sc h1="6" h2="5" h3="5" h4="3" h5="5" h6="6" h7="3" h8="5" h9="3" h10="6" h11="4" h12="5" h13="3" h14="5" h15="3" h16="4" h17="4" h18="7"/>
<Pt h1="2" h2="2" h3="2" h4="2" h5="2" h6="2" h7="1" h8="2" h9="2" h10="3" h11="1" h12="2" h13="1" h14="2" h15="2" h16="1" h17="2" h18="2"/>
<Ev h1="0" h2="0" h3="0" h4="0" h5="0" h6="0" h7="0" h8="0" h9="4" h10="0" h11="0" h12="0" h13="0" h14="0" h15="0" h16="0" h17="0" h18="0"/>
</Rp>
<Games>
<Gm I="0" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="17" M="0" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="11" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="12" H2="5" H3="1" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
</Games>
</Round>
================================================== ================================================== =============================
The application is actually used to track golf rounds.
a.. Each round can have many players;
a.. Each Player wil have 1 Score (<sc>)
b.. Each Player will have 1 Putt (<Pt>)
c.. Each Player will have 1 Ev? (<Ev>)
b.. Each Round can have 1 Games
a.. Each Games can have multiple Gm's (<Gm>)
The problem with this structure is that there are no indexes in the file, and from what I understand about the Bulk Load and relational databases, is that it will be next to impossible to link the data properly after importing.
Is there any way that this can be imported into SQL 2000? Will I have to write an application using VB to manually create the indexes and then import? The solution that I come up with will have to be repeatable as the data will need to be updated with a new XML file often.
If the entire XML file will help with understanding the scope of the issue, I can attach it.
Thanks in advance
|||Thanks for your response! I think I may have stumbled upon something that will help... I was finding mixed documents on the internet regarding the Bulk Load functionality... But I think the trick is to use Version 3.0SP2 with identities to get this to work...
I'm going to start with some easier samples and hopefully fluke my way through it. If I have a more precise and meaningful question in my experiments, I'll holler!
Thanks Again!
"Irwin Dolobowsky [MS]" <irwind@.mail.microsoft.com> wrote in message news:%23BIJuAwMEHA.3208@.TK2MSFTNGP10.phx.gbl...
Do golfers have ID's or anything? If not, can you just add an ID column that will auto-generate and id for each row? You could then relate them using that.
Irwin Dolobowsky
Program Manager - SqlXml
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott McKillop" <scott_mckillop_NOSPAM_MAN@.adlt.com_REMOVE_CAPS> wrote in message news:ubT1rwsMEHA.3420@.TK2MSFTNGP11.phx.gbl...
I have an application that exports an XML file in a strange format. I would like to be able to upload this file into an SQL 2000 database using Bulk Load. I have been able to complete some of the various examples and have a handle on what I need to do in the Bulk Load.
The problem I have is understanding how to load the data (in the form the application exports) into a relational format. My data does not contain any indexes and has multiple tables that will need to be created. A snippet of the XML is copied below:
================================================== ==============================
<Round Cn="Amherst Golf Course" Cc="Amherst" Cs="NS" Cy="CA" St="-1030918208" Et="-1030902963" Sf="0" Pt="31" Gm="3" Op="0">
<Rp N="Steve Peddle" S="STEVE" Hv="25" Te="1">
<Sc h1="7" h2="6" h3="6" h4="3" h5="7" h6="6" h7="6" h8="5" h9="5" h10="5" h11="6" h12="5" h13="3" h14="6" h15="4" h16="6" h17="5" h18="6"/>
<Pt h1="1" h2="2" h3="2" h4="1" h5="2" h6="1" h7="2" h8="1" h9="2" h10="2" h11="2" h12="2" h13="1" h14="2" h15="2" h16="2" h17="2" h18="1"/>
</Rp>
<Rp N="Chris White" S="Chris" Hv="18" Te="1">
<Sc h1="7" h2="5" h3="5" h4="3" h5="8" h6="4" h7="5" h8="6" h9="4" h10="5" h11="6" h12="5" h13="4" h14="6" h15="4" h16="5" h17="5" h18="7"/>
<Pt h1="2" h2="2" h3="1" h4="1" h5="3" h6="1" h7="2" h8="3" h9="2" h10="2" h11="2" h12="2" h13="2" h14="2" h15="2" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Scott McKillop" S="Scott" Hv="14" Te="1">
<Sc h1="7" h2="5" h3="4" h4="3" h5="7" h6="5" h7="5" h8="6" h9="4" h10="5" h11="5" h12="5" h13="4" h14="4" h15="4" h16="4" h17="5" h18="7"/>
<Pt h1="1" h2="1" h3="2" h4="2" h5="3" h6="2" h7="1" h8="2" h9="1" h10="2" h11="2" h12="3" h13="2" h14="1" h15="3" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Steve Rayworth" S="SteveR" Hv="13" Te="1">
<Sc h1="6" h2="5" h3="5" h4="3" h5="5" h6="6" h7="3" h8="5" h9="3" h10="6" h11="4" h12="5" h13="3" h14="5" h15="3" h16="4" h17="4" h18="7"/>
<Pt h1="2" h2="2" h3="2" h4="2" h5="2" h6="2" h7="1" h8="2" h9="2" h10="3" h11="1" h12="2" h13="1" h14="2" h15="2" h16="1" h17="2" h18="2"/>
<Ev h1="0" h2="0" h3="0" h4="0" h5="0" h6="0" h7="0" h8="0" h9="4" h10="0" h11="0" h12="0" h13="0" h14="0" h15="0" h16="0" h17="0" h18="0"/>
</Rp>
<Games>
<Gm I="0" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="17" M="0" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="11" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="12" H2="5" H3="1" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
</Games>
</Round>
================================================== ================================================== =============================
The application is actually used to track golf rounds.
a.. Each round can have many players;
a.. Each Player wil have 1 Score (<sc>)
b.. Each Player will have 1 Putt (<Pt>)
c.. Each Player will have 1 Ev? (<Ev>)
b.. Each Round can have 1 Games
a.. Each Games can have multiple Gm's (<Gm>)
The problem with this structure is that there are no indexes in the file, and from what I understand about the Bulk Load and relational databases, is that it will be next to impossible to link the data properly after importing.
Is there any way that this can be imported into SQL 2000? Will I have to write an application using VB to manually create the indexes and then import? The solution that I come up with will have to be repeatable as the data will need to be updated with a new XML file often.
If the entire XML file will help with understanding the scope of the issue, I can attach it.
Thanks in advance
|||That's where I was going. Glad you found it.
Irwin
Irwin Dolobowsky
Program Manager - SqlXml
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott McKillop" <smckillop@.NOSPAM.ns.sympatico.ca.REMOVECAPS> wrote in message news:%23PKm2RwMEHA.1348@.TK2MSFTNGP12.phx.gbl...
Thanks for your response! I think I may have stumbled upon something that will help... I was finding mixed documents on the internet regarding the Bulk Load functionality... But I think the trick is to use Version 3.0SP2 with identities to get this to work...
I'm going to start with some easier samples and hopefully fluke my way through it. If I have a more precise and meaningful question in my experiments, I'll holler!
Thanks Again!
"Irwin Dolobowsky [MS]" <irwind@.mail.microsoft.com> wrote in message news:%23BIJuAwMEHA.3208@.TK2MSFTNGP10.phx.gbl...
Do golfers have ID's or anything? If not, can you just add an ID column that will auto-generate and id for each row? You could then relate them using that.
Irwin Dolobowsky
Program Manager - SqlXml
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott McKillop" <scott_mckillop_NOSPAM_MAN@.adlt.com_REMOVE_CAPS> wrote in message news:ubT1rwsMEHA.3420@.TK2MSFTNGP11.phx.gbl...
I have an application that exports an XML file in a strange format. I would like to be able to upload this file into an SQL 2000 database using Bulk Load. I have been able to complete some of the various examples and have a handle on what I need to do in the Bulk Load.
The problem I have is understanding how to load the data (in the form the application exports) into a relational format. My data does not contain any indexes and has multiple tables that will need to be created. A snippet of the XML is copied below:
================================================== ==============================
<Round Cn="Amherst Golf Course" Cc="Amherst" Cs="NS" Cy="CA" St="-1030918208" Et="-1030902963" Sf="0" Pt="31" Gm="3" Op="0">
<Rp N="Steve Peddle" S="STEVE" Hv="25" Te="1">
<Sc h1="7" h2="6" h3="6" h4="3" h5="7" h6="6" h7="6" h8="5" h9="5" h10="5" h11="6" h12="5" h13="3" h14="6" h15="4" h16="6" h17="5" h18="6"/>
<Pt h1="1" h2="2" h3="2" h4="1" h5="2" h6="1" h7="2" h8="1" h9="2" h10="2" h11="2" h12="2" h13="1" h14="2" h15="2" h16="2" h17="2" h18="1"/>
</Rp>
<Rp N="Chris White" S="Chris" Hv="18" Te="1">
<Sc h1="7" h2="5" h3="5" h4="3" h5="8" h6="4" h7="5" h8="6" h9="4" h10="5" h11="6" h12="5" h13="4" h14="6" h15="4" h16="5" h17="5" h18="7"/>
<Pt h1="2" h2="2" h3="1" h4="1" h5="3" h6="1" h7="2" h8="3" h9="2" h10="2" h11="2" h12="2" h13="2" h14="2" h15="2" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Scott McKillop" S="Scott" Hv="14" Te="1">
<Sc h1="7" h2="5" h3="4" h4="3" h5="7" h6="5" h7="5" h8="6" h9="4" h10="5" h11="5" h12="5" h13="4" h14="4" h15="4" h16="4" h17="5" h18="7"/>
<Pt h1="1" h2="1" h3="2" h4="2" h5="3" h6="2" h7="1" h8="2" h9="1" h10="2" h11="2" h12="3" h13="2" h14="1" h15="3" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Steve Rayworth" S="SteveR" Hv="13" Te="1">
<Sc h1="6" h2="5" h3="5" h4="3" h5="5" h6="6" h7="3" h8="5" h9="3" h10="6" h11="4" h12="5" h13="3" h14="5" h15="3" h16="4" h17="4" h18="7"/>
<Pt h1="2" h2="2" h3="2" h4="2" h5="2" h6="2" h7="1" h8="2" h9="2" h10="3" h11="1" h12="2" h13="1" h14="2" h15="2" h16="1" h17="2" h18="2"/>
<Ev h1="0" h2="0" h3="0" h4="0" h5="0" h6="0" h7="0" h8="0" h9="4" h10="0" h11="0" h12="0" h13="0" h14="0" h15="0" h16="0" h17="0" h18="0"/>
</Rp>
<Games>
<Gm I="0" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="17" M="0" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="11" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="12" H2="5" H3="1" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
</Games>
</Round>
================================================== ================================================== =============================
The application is actually used to track golf rounds.
a.. Each round can have many players;
a.. Each Player wil have 1 Score (<sc>)
b.. Each Player will have 1 Putt (<Pt>)
c.. Each Player will have 1 Ev? (<Ev>)
b.. Each Round can have 1 Games
a.. Each Games can have multiple Gm's (<Gm>)
The problem with this structure is that there are no indexes in the file, and from what I understand about the Bulk Load and relational databases, is that it will be next to impossible to link the data properly after importing.
Is there any way that this can be imported into SQL 2000? Will I have to write an application using VB to manually create the indexes and then import? The solution that I come up with will have to be repeatable as the data will need to be updated with a new XML file often.
If the entire XML file will help with understanding the scope of the issue, I can attach it.
Thanks in advance
Bulk Load schema problems
Hi, I'm from Brazil, so sorry bad english
I have a xml document like this:
<competition name="Formula one" year="2003"> <!-- this is the root element
-->
<race number="1" local="Brazil">
<pilots number_pilots="10">
<pilot name="schummacker" pos="1" team="ferrari"/>
<pilot name="barriquelo" pos="2" team="ferrari"/>
No help for me?
"Luciano Pagliarini" wrote:
> Hi, I'm from Brazil, so sorry bad english
> I have a xml document like this:
> <competition name="Formula one" year="2003"> <!-- this is the root element
> -->
> <race number="1" local="Brazil">
> <pilots number_pilots="10">
> <pilot name="schummacker" pos="1" team="ferrari"/>
> <pilot name="barriquelo" pos="2" team="ferrari"/>
> .
> .
> .
> </pilots>
> <teams number_teams="5">
> <team name="ferrari" points="18"/>
> <team name="willians" points="9"/>
> .
> .
> .
> </teams>
> <totals finished_pilots="8" out_pilots="2" accidents="1"/>
> </race>
> </competition>
>
> ps.: I will have one file for each race. I need import this to sql server
> using xml bulk load and defining xsd schema. I need fast import.
> the schema that i made, actualy works to import, but have some problems
>
> 1) I have a table named "competitions" mapped to the element "competition"
> of the xml documents. Always i run XMLBulkLoad.execute one record to this
> element is placed in the table. If i have 500 documents to be read, 500
> records with the same content will be placed in this table. (I make a
> workarround make one file with a union of the small files, puting the element
> just one time. this is a workarround that consume time, and the time to load
> for me is important)
> 2) If a have a table named "pilots_results" with this layout:
> "year" , "race_id" , "pilot_name" , "position"
> how can I construct a schema that import JUST this data to ONE table, and
> nothing more. (the error is like the element "race" have no item associated
> with it, or some like this. sory I dont remenber)
> 3) Now, if i have a table named "races" with this layout:
> "year" , "race_id" , "finished_Pilots" , "out_pilots" , "accidenst"
> how can I construct schema to import this way?
> note that the node "totals" is a subelement of the element "race", but is
> totaly associated, because is totals for the especific race!
> acctualy a have to create a table named "races_totals" just to put this
> data, mapping the element "totals" to this table. note that for each
> "race_id" in that "year" I will have just one "totals" element.
> 4) and, finally, the performanse...
> for 500 xml documents about 50kb each one, the time to import is ~4minutes
> or more.
> the tables are off indexses, no transaction is used, no users access the
> database, just the normal windows services running,
>
> anybody can help? this is very urgent for me, I have a delivery stated
> period for this system and can't see the light in the end of tunel.
> thanks for all
> Luciano Pagliarini
|||Hi Luciano,
For
1) I don't see any other way, may be you can run bulkload.execute in a loop
over all your data files
2) you can define schema that has a relationship between competition and
race and a relationship between race and pilot_results
that way you can propagate ID from competition to race to pilot_results
or
you just have year and raceid repeated for every pilot_results
<pilot name="schummacker" pos="1" team="ferrari" year="2003" raceid="1"/>
3) similar to (2)
4) I do not understand your question here. If you are looking for ways to
increase perf, try not using a transaction, keepidentity=<default value> and
no schemagen
HTH,
-Chandra
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Luciano Pagliarini" <LucianoPagliarini@.discussions.microsoft.com> wrote in
message news:3DCA9725-CA12-4997-99AB-E1334D692424@.microsoft.com...[vbcol=seagreen]
> No help for me?
> "Luciano Pagliarini" wrote:
I have a xml document like this:
<competition name="Formula one" year="2003"> <!-- this is the root element
-->
<race number="1" local="Brazil">
<pilots number_pilots="10">
<pilot name="schummacker" pos="1" team="ferrari"/>
<pilot name="barriquelo" pos="2" team="ferrari"/>
No help for me?
"Luciano Pagliarini" wrote:
> Hi, I'm from Brazil, so sorry bad english
> I have a xml document like this:
> <competition name="Formula one" year="2003"> <!-- this is the root element
> -->
> <race number="1" local="Brazil">
> <pilots number_pilots="10">
> <pilot name="schummacker" pos="1" team="ferrari"/>
> <pilot name="barriquelo" pos="2" team="ferrari"/>
> .
> .
> .
> </pilots>
> <teams number_teams="5">
> <team name="ferrari" points="18"/>
> <team name="willians" points="9"/>
> .
> .
> .
> </teams>
> <totals finished_pilots="8" out_pilots="2" accidents="1"/>
> </race>
> </competition>
>
> ps.: I will have one file for each race. I need import this to sql server
> using xml bulk load and defining xsd schema. I need fast import.
> the schema that i made, actualy works to import, but have some problems
>
> 1) I have a table named "competitions" mapped to the element "competition"
> of the xml documents. Always i run XMLBulkLoad.execute one record to this
> element is placed in the table. If i have 500 documents to be read, 500
> records with the same content will be placed in this table. (I make a
> workarround make one file with a union of the small files, puting the element
> just one time. this is a workarround that consume time, and the time to load
> for me is important)
> 2) If a have a table named "pilots_results" with this layout:
> "year" , "race_id" , "pilot_name" , "position"
> how can I construct a schema that import JUST this data to ONE table, and
> nothing more. (the error is like the element "race" have no item associated
> with it, or some like this. sory I dont remenber)
> 3) Now, if i have a table named "races" with this layout:
> "year" , "race_id" , "finished_Pilots" , "out_pilots" , "accidenst"
> how can I construct schema to import this way?
> note that the node "totals" is a subelement of the element "race", but is
> totaly associated, because is totals for the especific race!
> acctualy a have to create a table named "races_totals" just to put this
> data, mapping the element "totals" to this table. note that for each
> "race_id" in that "year" I will have just one "totals" element.
> 4) and, finally, the performanse...
> for 500 xml documents about 50kb each one, the time to import is ~4minutes
> or more.
> the tables are off indexses, no transaction is used, no users access the
> database, just the normal windows services running,
>
> anybody can help? this is very urgent for me, I have a delivery stated
> period for this system and can't see the light in the end of tunel.
> thanks for all
> Luciano Pagliarini
|||Hi Luciano,
For
1) I don't see any other way, may be you can run bulkload.execute in a loop
over all your data files
2) you can define schema that has a relationship between competition and
race and a relationship between race and pilot_results
that way you can propagate ID from competition to race to pilot_results
or
you just have year and raceid repeated for every pilot_results
<pilot name="schummacker" pos="1" team="ferrari" year="2003" raceid="1"/>
3) similar to (2)
4) I do not understand your question here. If you are looking for ways to
increase perf, try not using a transaction, keepidentity=<default value> and
no schemagen
HTH,
-Chandra
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Luciano Pagliarini" <LucianoPagliarini@.discussions.microsoft.com> wrote in
message news:3DCA9725-CA12-4997-99AB-E1334D692424@.microsoft.com...[vbcol=seagreen]
> No help for me?
> "Luciano Pagliarini" wrote:
Bulk load 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 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 Load - In a Panic Need Help... PLEASE!
Please help I am new to XML and Schema will someone look this over and tell
me what I am doing wrong?
Ok, I receive inventory updates once a week from my vendor in the following
format: (Obviously trimmed down)
<?xml version="1.0" encoding="UTF-8"?>
<AvailableBatch>
<Date>08/14/2005</Date>
<Available>
<Sku>40</Sku>
<Part>10 </Part>
<Location>20</Location>
<Qty>0</Qty>
<Time>07:20:09</Time>
<Detail>
<Desc>TIGHTS, MENS</Desc>
<Price>14.90</Price>
</Detail>
</Available>
<Available>
<Sku>REPEAT OVER 10000 TIMES</Sku>
<Part>1REPEAT OVER 10000 TIMES</Part>
<Location>REPEAT OVER 10000 TIMES</Location>
<Qty>REPEAT OVER 10000 TIMES</Qty>
<Time>REPEAT OVER 10000 TIMES</Time>
<Detail>
<Desc>REPEAT OVER 10000 TIMES</Desc>
<Price>REPEAT OVER 10000 TIMES</Price>
</Detail>
</Available>
</AvailableBatch>
tonight I picked through tutorials to try to setup my schema file... I know
this is wrong... Im just at a loss as to why? Now please keep in mind that
the above xml file only has the date once and then repeats the "<Available>"
section thousands of times:
Schema File:
<?xml version="1.0" encoding="utf-8" ?>
<xs:schema id="XMLSchema"
targetNamespace="http://tempuri.org/XMLSchema.xsd"
elementFormDefault="qualified"
xmlns="http://tempuri.org/XMLSchema.xsd"
xmlns:mstns="http://tempuri.org/XMLSchema.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="AvailableBatch">
<xs:complexType>
<xs:sequence>
<xs:element name ="Date">
<xs:complexType>
<xs:sequence id="Available">
<xs:element name ="Sku" type ="xs:string" maxOccurs
="unbounded"/>
<xs:element name ="Part" type ="xs:string" maxOccurs
="unbounded"/>
<xs:element name ="Location" type ="xs:string" maxOccurs
="unbounded"/>
<xs:element name ="Qty" type ="xs:string" maxOccurs
="unbounded"/>
<xs:element name ="Time" type ="xs:string" maxOccurs
="unbounded"/>
<xs:sequence id ="Detail">
<xs:element name ="Description" type ="xs:string" maxOccurs
="unbounded"/>
<xs:element name ="Price" type ="xs:string" maxOccurs
="unbounded"/>
</xs:sequence>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
I have a tuesday deadline to get this data into my data base... can someone
please give me a hand?
I really appreciate whoever can take their time to help me... You have no
idea how much I appreciate it!
Aaron
I did find a schema generator: Would this be correct or is this wrong?
<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
elementFormDefault="qualified">
<xs:element name="AvailableBatch">
<xs:complexType>
<xs:sequence>
<xs:element ref="Date"/>
<xs:element maxOccurs="unbounded" ref="Available"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Date" type="xs:string"/>
<xs:element name="Available">
<xs:complexType>
<xs:sequence>
<xs:element ref="Sku"/>
<xs:element ref="Part"/>
<xs:element ref="Location"/>
<xs:element ref="Qty"/>
<xs:element ref="Time"/>
<xs:element ref="Detail"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Sku" type="xs:integer"/>
<xs:element name="Part" type="xs:string"/>
<xs:element name="Location" type="xs:integer"/>
<xs:element name="Qty" type="xs:integer"/>
<xs:element name="Time" type="xs:NMTOKEN"/>
<xs:element name="Detail">
<xs:complexType>
<xs:sequence>
<xs:element ref="Desc"/>
<xs:element minOccurs="0" ref="Weight"/>
<xs:element minOccurs="0" ref="Price"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Desc" type="xs:string"/>
<xs:element name="Weight" type="xs:decimal"/>
<xs:element name="Price" type="xs:decimal"/>
</xs:schema>
Aaron C wrote:
>Please help I am new to XML and Schema will someone look this over and tell
>me what I am doing wrong?
>Ok, I receive inventory updates once a week from my vendor in the following
>format: (Obviously trimmed down)
><?xml version="1.0" encoding="UTF-8"?>
><AvailableBatch>
> <Date>08/14/2005</Date>
> <Available>
> <Sku>40</Sku>
> <Part>10 </Part>
> <Location>20</Location>
> <Qty>0</Qty>
> <Time>07:20:09</Time>
> <Detail>
> <Desc>TIGHTS, MENS</Desc>
> <Price>14.90</Price>
> </Detail>
> </Available>
><Available>
> <Sku>REPEAT OVER 10000 TIMES</Sku>
> <Part>1REPEAT OVER 10000 TIMES</Part>
> <Location>REPEAT OVER 10000 TIMES</Location>
> <Qty>REPEAT OVER 10000 TIMES</Qty>
> <Time>REPEAT OVER 10000 TIMES</Time>
> <Detail>
> <Desc>REPEAT OVER 10000 TIMES</Desc>
> <Price>REPEAT OVER 10000 TIMES</Price>
> </Detail>
> </Available>
></AvailableBatch>
>tonight I picked through tutorials to try to setup my schema file... I know
>this is wrong... Im just at a loss as to why? Now please keep in mind that
>the above xml file only has the date once and then repeats the "<Available>"
>section thousands of times:
>Schema File:
><?xml version="1.0" encoding="utf-8" ?>
><xs:schema id="XMLSchema"
> targetNamespace="http://tempuri.org/XMLSchema.xsd"
> elementFormDefault="qualified"
> xmlns="http://tempuri.org/XMLSchema.xsd"
> xmlns:mstns="http://tempuri.org/XMLSchema.xsd"
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:element name="AvailableBatch">
> <xs:complexType>
> <xs:sequence>
> <xs:element name ="Date">
> <xs:complexType>
> <xs:sequence id="Available">
> <xs:element name ="Sku" type ="xs:string" maxOccurs
>="unbounded"/>
> <xs:element name ="Part" type ="xs:string" maxOccurs
>="unbounded"/>
> <xs:element name ="Location" type ="xs:string" maxOccurs
>="unbounded"/>
> <xs:element name ="Qty" type ="xs:string" maxOccurs
>="unbounded"/>
> <xs:element name ="Time" type ="xs:string" maxOccurs
>="unbounded"/>
> <xs:sequence id ="Detail">
> <xs:element name ="Description" type ="xs:string" maxOccurs
>="unbounded"/>
> <xs:element name ="Price" type ="xs:string" maxOccurs
>="unbounded"/>
> </xs:sequence>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
></xs:schema>
>I have a tuesday deadline to get this data into my data base... can someone
>please give me a hand?
>I really appreciate whoever can take their time to help me... You have no
>idea how much I appreciate it!
>Aaron
|||Now that I have my schema and my xml file: Where do I go from here to
bulkload my Sql Dataase?
In over my head, lol
Aaron
Aaron C wrote:[vbcol=seagreen]
>I did find a schema generator: Would this be correct or is this wrong?
><?xml version="1.0" encoding="UTF-8"?>
><xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
>elementFormDefault="qualified">
> <xs:element name="AvailableBatch">
> <xs:complexType>
> <xs:sequence>
> <xs:element ref="Date"/>
> <xs:element maxOccurs="unbounded" ref="Available"/>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> <xs:element name="Date" type="xs:string"/>
> <xs:element name="Available">
> <xs:complexType>
> <xs:sequence>
> <xs:element ref="Sku"/>
> <xs:element ref="Part"/>
> <xs:element ref="Location"/>
> <xs:element ref="Qty"/>
> <xs:element ref="Time"/>
> <xs:element ref="Detail"/>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> <xs:element name="Sku" type="xs:integer"/>
> <xs:element name="Part" type="xs:string"/>
> <xs:element name="Location" type="xs:integer"/>
> <xs:element name="Qty" type="xs:integer"/>
> <xs:element name="Time" type="xs:NMTOKEN"/>
> <xs:element name="Detail">
> <xs:complexType>
> <xs:sequence>
> <xs:element ref="Desc"/>
> <xs:element minOccurs="0" ref="Weight"/>
> <xs:element minOccurs="0" ref="Price"/>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> <xs:element name="Desc" type="xs:string"/>
> <xs:element name="Weight" type="xs:decimal"/>
> <xs:element name="Price" type="xs:decimal"/>
></xs:schema>
>[quoted text clipped - 80 lines]
Message posted via http://www.droptable.com
|||You have to specify the annotations to describe the mapping between your Xml
and relatinal structures.
Check out this link:
http://msdn.microsoft.com/library/de...tions_0gqb.asp
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aaron C via droptable.com" <forum@.droptable.com> wrote in message
news:52DF026D67AC8@.droptable.com...
> Now that I have my schema and my xml file: Where do I go from here to
> bulkload my Sql Dataase?
> In over my head, lol
> Aaron
>
> Aaron C wrote:
>
> --
> Message posted via http://www.droptable.com
me what I am doing wrong?
Ok, I receive inventory updates once a week from my vendor in the following
format: (Obviously trimmed down)
<?xml version="1.0" encoding="UTF-8"?>
<AvailableBatch>
<Date>08/14/2005</Date>
<Available>
<Sku>40</Sku>
<Part>10 </Part>
<Location>20</Location>
<Qty>0</Qty>
<Time>07:20:09</Time>
<Detail>
<Desc>TIGHTS, MENS</Desc>
<Price>14.90</Price>
</Detail>
</Available>
<Available>
<Sku>REPEAT OVER 10000 TIMES</Sku>
<Part>1REPEAT OVER 10000 TIMES</Part>
<Location>REPEAT OVER 10000 TIMES</Location>
<Qty>REPEAT OVER 10000 TIMES</Qty>
<Time>REPEAT OVER 10000 TIMES</Time>
<Detail>
<Desc>REPEAT OVER 10000 TIMES</Desc>
<Price>REPEAT OVER 10000 TIMES</Price>
</Detail>
</Available>
</AvailableBatch>
tonight I picked through tutorials to try to setup my schema file... I know
this is wrong... Im just at a loss as to why? Now please keep in mind that
the above xml file only has the date once and then repeats the "<Available>"
section thousands of times:
Schema File:
<?xml version="1.0" encoding="utf-8" ?>
<xs:schema id="XMLSchema"
targetNamespace="http://tempuri.org/XMLSchema.xsd"
elementFormDefault="qualified"
xmlns="http://tempuri.org/XMLSchema.xsd"
xmlns:mstns="http://tempuri.org/XMLSchema.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="AvailableBatch">
<xs:complexType>
<xs:sequence>
<xs:element name ="Date">
<xs:complexType>
<xs:sequence id="Available">
<xs:element name ="Sku" type ="xs:string" maxOccurs
="unbounded"/>
<xs:element name ="Part" type ="xs:string" maxOccurs
="unbounded"/>
<xs:element name ="Location" type ="xs:string" maxOccurs
="unbounded"/>
<xs:element name ="Qty" type ="xs:string" maxOccurs
="unbounded"/>
<xs:element name ="Time" type ="xs:string" maxOccurs
="unbounded"/>
<xs:sequence id ="Detail">
<xs:element name ="Description" type ="xs:string" maxOccurs
="unbounded"/>
<xs:element name ="Price" type ="xs:string" maxOccurs
="unbounded"/>
</xs:sequence>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
I have a tuesday deadline to get this data into my data base... can someone
please give me a hand?
I really appreciate whoever can take their time to help me... You have no
idea how much I appreciate it!
Aaron
I did find a schema generator: Would this be correct or is this wrong?
<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
elementFormDefault="qualified">
<xs:element name="AvailableBatch">
<xs:complexType>
<xs:sequence>
<xs:element ref="Date"/>
<xs:element maxOccurs="unbounded" ref="Available"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Date" type="xs:string"/>
<xs:element name="Available">
<xs:complexType>
<xs:sequence>
<xs:element ref="Sku"/>
<xs:element ref="Part"/>
<xs:element ref="Location"/>
<xs:element ref="Qty"/>
<xs:element ref="Time"/>
<xs:element ref="Detail"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Sku" type="xs:integer"/>
<xs:element name="Part" type="xs:string"/>
<xs:element name="Location" type="xs:integer"/>
<xs:element name="Qty" type="xs:integer"/>
<xs:element name="Time" type="xs:NMTOKEN"/>
<xs:element name="Detail">
<xs:complexType>
<xs:sequence>
<xs:element ref="Desc"/>
<xs:element minOccurs="0" ref="Weight"/>
<xs:element minOccurs="0" ref="Price"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Desc" type="xs:string"/>
<xs:element name="Weight" type="xs:decimal"/>
<xs:element name="Price" type="xs:decimal"/>
</xs:schema>
Aaron C wrote:
>Please help I am new to XML and Schema will someone look this over and tell
>me what I am doing wrong?
>Ok, I receive inventory updates once a week from my vendor in the following
>format: (Obviously trimmed down)
><?xml version="1.0" encoding="UTF-8"?>
><AvailableBatch>
> <Date>08/14/2005</Date>
> <Available>
> <Sku>40</Sku>
> <Part>10 </Part>
> <Location>20</Location>
> <Qty>0</Qty>
> <Time>07:20:09</Time>
> <Detail>
> <Desc>TIGHTS, MENS</Desc>
> <Price>14.90</Price>
> </Detail>
> </Available>
><Available>
> <Sku>REPEAT OVER 10000 TIMES</Sku>
> <Part>1REPEAT OVER 10000 TIMES</Part>
> <Location>REPEAT OVER 10000 TIMES</Location>
> <Qty>REPEAT OVER 10000 TIMES</Qty>
> <Time>REPEAT OVER 10000 TIMES</Time>
> <Detail>
> <Desc>REPEAT OVER 10000 TIMES</Desc>
> <Price>REPEAT OVER 10000 TIMES</Price>
> </Detail>
> </Available>
></AvailableBatch>
>tonight I picked through tutorials to try to setup my schema file... I know
>this is wrong... Im just at a loss as to why? Now please keep in mind that
>the above xml file only has the date once and then repeats the "<Available>"
>section thousands of times:
>Schema File:
><?xml version="1.0" encoding="utf-8" ?>
><xs:schema id="XMLSchema"
> targetNamespace="http://tempuri.org/XMLSchema.xsd"
> elementFormDefault="qualified"
> xmlns="http://tempuri.org/XMLSchema.xsd"
> xmlns:mstns="http://tempuri.org/XMLSchema.xsd"
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:element name="AvailableBatch">
> <xs:complexType>
> <xs:sequence>
> <xs:element name ="Date">
> <xs:complexType>
> <xs:sequence id="Available">
> <xs:element name ="Sku" type ="xs:string" maxOccurs
>="unbounded"/>
> <xs:element name ="Part" type ="xs:string" maxOccurs
>="unbounded"/>
> <xs:element name ="Location" type ="xs:string" maxOccurs
>="unbounded"/>
> <xs:element name ="Qty" type ="xs:string" maxOccurs
>="unbounded"/>
> <xs:element name ="Time" type ="xs:string" maxOccurs
>="unbounded"/>
> <xs:sequence id ="Detail">
> <xs:element name ="Description" type ="xs:string" maxOccurs
>="unbounded"/>
> <xs:element name ="Price" type ="xs:string" maxOccurs
>="unbounded"/>
> </xs:sequence>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
></xs:schema>
>I have a tuesday deadline to get this data into my data base... can someone
>please give me a hand?
>I really appreciate whoever can take their time to help me... You have no
>idea how much I appreciate it!
>Aaron
|||Now that I have my schema and my xml file: Where do I go from here to
bulkload my Sql Dataase?
In over my head, lol
Aaron
Aaron C wrote:[vbcol=seagreen]
>I did find a schema generator: Would this be correct or is this wrong?
><?xml version="1.0" encoding="UTF-8"?>
><xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
>elementFormDefault="qualified">
> <xs:element name="AvailableBatch">
> <xs:complexType>
> <xs:sequence>
> <xs:element ref="Date"/>
> <xs:element maxOccurs="unbounded" ref="Available"/>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> <xs:element name="Date" type="xs:string"/>
> <xs:element name="Available">
> <xs:complexType>
> <xs:sequence>
> <xs:element ref="Sku"/>
> <xs:element ref="Part"/>
> <xs:element ref="Location"/>
> <xs:element ref="Qty"/>
> <xs:element ref="Time"/>
> <xs:element ref="Detail"/>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> <xs:element name="Sku" type="xs:integer"/>
> <xs:element name="Part" type="xs:string"/>
> <xs:element name="Location" type="xs:integer"/>
> <xs:element name="Qty" type="xs:integer"/>
> <xs:element name="Time" type="xs:NMTOKEN"/>
> <xs:element name="Detail">
> <xs:complexType>
> <xs:sequence>
> <xs:element ref="Desc"/>
> <xs:element minOccurs="0" ref="Weight"/>
> <xs:element minOccurs="0" ref="Price"/>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> <xs:element name="Desc" type="xs:string"/>
> <xs:element name="Weight" type="xs:decimal"/>
> <xs:element name="Price" type="xs:decimal"/>
></xs:schema>
>[quoted text clipped - 80 lines]
Message posted via http://www.droptable.com
|||You have to specify the annotations to describe the mapping between your Xml
and relatinal structures.
Check out this link:
http://msdn.microsoft.com/library/de...tions_0gqb.asp
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aaron C via droptable.com" <forum@.droptable.com> wrote in message
news:52DF026D67AC8@.droptable.com...
> Now that I have my schema and my xml file: Where do I go from here to
> bulkload my Sql Dataase?
> In over my head, lol
> Aaron
>
> Aaron C wrote:
>
> --
> Message posted via http://www.droptable.com
Bulk Load - In a Panic Need Help... PLEASE!
Please help I am new to XML and Schema will someone look this over and tell
me what I am doing wrong'
Ok, I receive inventory updates once a w
from my vendor in the following
format: (Obviously trimmed down)
<?xml version="1.0" encoding="UTF-8"?>
<AvailableBatch>
<Date>08/14/2005</Date>
<Available>
<Sku>40</Sku>
<Part>10 </Part>
<Location>20</Location>
<Qty>0</Qty>
<Time>07:20:09</Time>
<Detail>
<Desc>TIGHTS, MENS</Desc>
<Price>14.90</Price>
</Detail>
</Available>
<Available>
<Sku>REPEAT OVER 10000 TIMES</Sku>
<Part>1REPEAT OVER 10000 TIMES</Part>
<Location>REPEAT OVER 10000 TIMES</Location>
<Qty>REPEAT OVER 10000 TIMES</Qty>
<Time>REPEAT OVER 10000 TIMES</Time>
<Detail>
<Desc>REPEAT OVER 10000 TIMES</Desc>
<Price>REPEAT OVER 10000 TIMES</Price>
</Detail>
</Available>
</AvailableBatch>
tonight I picked through tutorials to try to setup my schema file... I know
this is wrong... Im just at a loss as to why? Now please keep in mind that
the above xml file only has the date once and then repeats the "<Available>"
section thousands of times:
Schema File:
<?xml version="1.0" encoding="utf-8" ?>
<xs:schema id="XMLSchema"
targetNamespace="http://tempuri.org/XMLSchema.xsd"
elementFormDefault="qualified"
xmlns="http://tempuri.org/XMLSchema.xsd"
xmlns:mstns="http://tempuri.org/XMLSchema.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="AvailableBatch">
<xs:complexType>
<xs:sequence>
<xs:element name ="Date">
<xs:complexType>
<xs:sequence id="Available">
<xs:element name ="Sku" type ="xs:string" maxOccurs
="unbounded"/>
<xs:element name ="Part" type ="xs:string" maxOccurs
="unbounded"/>
<xs:element name ="Location" type ="xs:string" maxOccurs
="unbounded"/>
<xs:element name ="Qty" type ="xs:string" maxOccurs
="unbounded"/>
<xs:element name ="Time" type ="xs:string" maxOccurs
="unbounded"/>
<xs:sequence id ="Detail">
<xs:element name ="Description" type ="xs:string" maxOccurs
="unbounded"/>
<xs:element name ="Price" type ="xs:string" maxOccurs
="unbounded"/>
</xs:sequence>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
I have a tuesday deadline to get this data into my data base... can someone
please give me a hand?
I really appreciate whoever can take their time to help me... You have no
idea how much I appreciate it!
AaronI did find a schema generator: Would this be correct or is this wrong?
<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
elementFormDefault="qualified">
<xs:element name="AvailableBatch">
<xs:complexType>
<xs:sequence>
<xs:element ref="Date"/>
<xs:element maxOccurs="unbounded" ref="Available"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Date" type="xs:string"/>
<xs:element name="Available">
<xs:complexType>
<xs:sequence>
<xs:element ref="Sku"/>
<xs:element ref="Part"/>
<xs:element ref="Location"/>
<xs:element ref="Qty"/>
<xs:element ref="Time"/>
<xs:element ref="Detail"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Sku" type="xs:integer"/>
<xs:element name="Part" type="xs:string"/>
<xs:element name="Location" type="xs:integer"/>
<xs:element name="Qty" type="xs:integer"/>
<xs:element name="Time" type="xs:NMTOKEN"/>
<xs:element name="Detail">
<xs:complexType>
<xs:sequence>
<xs:element ref="Desc"/>
<xs:element minOccurs="0" ref="Weight"/>
<xs:element minOccurs="0" ref="Price"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Desc" type="xs:string"/>
<xs:element name="Weight" type="xs:decimal"/>
<xs:element name="Price" type="xs:decimal"/>
</xs:schema>
Aaron C wrote:
>Please help I am new to XML and Schema will someone look this over and tell
>me what I am doing wrong'
>Ok, I receive inventory updates once a w from my vendor in the following
>format: (Obviously trimmed down)
><?xml version="1.0" encoding="UTF-8"?>
><AvailableBatch>
> <Date>08/14/2005</Date>
> <Available>
> <Sku>40</Sku>
> <Part>10 </Part>
> <Location>20</Location>
> <Qty>0</Qty>
> <Time>07:20:09</Time>
> <Detail>
> <Desc>TIGHTS, MENS</Desc>
> <Price>14.90</Price>
> </Detail>
> </Available>
><Available>
> <Sku>REPEAT OVER 10000 TIMES</Sku>
> <Part>1REPEAT OVER 10000 TIMES</Part>
> <Location>REPEAT OVER 10000 TIMES</Location>
> <Qty>REPEAT OVER 10000 TIMES</Qty>
> <Time>REPEAT OVER 10000 TIMES</Time>
> <Detail>
> <Desc>REPEAT OVER 10000 TIMES</Desc>
> <Price>REPEAT OVER 10000 TIMES</Price>
> </Detail>
> </Available>
></AvailableBatch>
>tonight I picked through tutorials to try to setup my schema file... I kno
w
>this is wrong... Im just at a loss as to why? Now please keep in mind tha
t
>the above xml file only has the date once and then repeats the "<Available>
"
>section thousands of times:
>Schema File:
><?xml version="1.0" encoding="utf-8" ?>
><xs:schema id="XMLSchema"
> targetNamespace="http://tempuri.org/XMLSchema.xsd"
> elementFormDefault="qualified"
> xmlns="http://tempuri.org/XMLSchema.xsd"
> xmlns:mstns="http://tempuri.org/XMLSchema.xsd"
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:element name="AvailableBatch">
> <xs:complexType>
> <xs:sequence>
> <xs:element name ="Date">
> <xs:complexType>
> <xs:sequence id="Available">
> <xs:element name ="Sku" type ="xs:string" maxOccurs
>="unbounded"/>
> <xs:element name ="Part" type ="xs:string" maxOccurs
>="unbounded"/>
> <xs:element name ="Location" type ="xs:string" maxOccurs
>="unbounded"/>
> <xs:element name ="Qty" type ="xs:string" maxOccurs
>="unbounded"/>
> <xs:element name ="Time" type ="xs:string" maxOccurs
>="unbounded"/>
> <xs:sequence id ="Detail">
> <xs:element name ="Description" type ="xs:string" maxOccurs
>="unbounded"/>
> <xs:element name ="Price" type ="xs:string" maxOccurs
>="unbounded"/>
> </xs:sequence>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
></xs:schema>
>I have a tuesday deadline to get this data into my data base... can someon
e
>please give me a hand?
>I really appreciate whoever can take their time to help me... You have no
>idea how much I appreciate it!
>Aaron|||Now that I have my schema and my xml file: Where do I go from here to
bulkload my Sql Dataase?
In over my head, lol
Aaron
Aaron C wrote:
>I did find a schema generator: Would this be correct or is this wrong?
><?xml version="1.0" encoding="UTF-8"?>
><xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
>elementFormDefault="qualified">
> <xs:element name="AvailableBatch">
> <xs:complexType>
> <xs:sequence>
> <xs:element ref="Date"/>
> <xs:element maxOccurs="unbounded" ref="Available"/>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> <xs:element name="Date" type="xs:string"/>
> <xs:element name="Available">
> <xs:complexType>
> <xs:sequence>
> <xs:element ref="Sku"/>
> <xs:element ref="Part"/>
> <xs:element ref="Location"/>
> <xs:element ref="Qty"/>
> <xs:element ref="Time"/>
> <xs:element ref="Detail"/>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> <xs:element name="Sku" type="xs:integer"/>
> <xs:element name="Part" type="xs:string"/>
> <xs:element name="Location" type="xs:integer"/>
> <xs:element name="Qty" type="xs:integer"/>
> <xs:element name="Time" type="xs:NMTOKEN"/>
> <xs:element name="Detail">
> <xs:complexType>
> <xs:sequence>
> <xs:element ref="Desc"/>
> <xs:element minOccurs="0" ref="Weight"/>
> <xs:element minOccurs="0" ref="Price"/>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> <xs:element name="Desc" type="xs:string"/>
> <xs:element name="Weight" type="xs:decimal"/>
> <xs:element name="Price" type="xs:decimal"/>
></xs:schema>
>
>[quoted text clipped - 80 lines]
Message posted via http://www.webservertalk.com|||You have to specify the annotations to describe the mapping between your Xml
and relatinal structures.
Check out this link:
http://msdn.microsoft.com/library/d...ations_0gqb.asp
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aaron C via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:52DF026D67AC8@.webservertalk.com...
> Now that I have my schema and my xml file: Where do I go from here to
> bulkload my Sql Dataase?
> In over my head, lol
> Aaron
>
> Aaron C wrote:
>
> --
> Message posted via http://www.webservertalk.com
me what I am doing wrong'
Ok, I receive inventory updates once a w
from my vendor in the followingformat: (Obviously trimmed down)
<?xml version="1.0" encoding="UTF-8"?>
<AvailableBatch>
<Date>08/14/2005</Date>
<Available>
<Sku>40</Sku>
<Part>10 </Part>
<Location>20</Location>
<Qty>0</Qty>
<Time>07:20:09</Time>
<Detail>
<Desc>TIGHTS, MENS</Desc>
<Price>14.90</Price>
</Detail>
</Available>
<Available>
<Sku>REPEAT OVER 10000 TIMES</Sku>
<Part>1REPEAT OVER 10000 TIMES</Part>
<Location>REPEAT OVER 10000 TIMES</Location>
<Qty>REPEAT OVER 10000 TIMES</Qty>
<Time>REPEAT OVER 10000 TIMES</Time>
<Detail>
<Desc>REPEAT OVER 10000 TIMES</Desc>
<Price>REPEAT OVER 10000 TIMES</Price>
</Detail>
</Available>
</AvailableBatch>
tonight I picked through tutorials to try to setup my schema file... I know
this is wrong... Im just at a loss as to why? Now please keep in mind that
the above xml file only has the date once and then repeats the "<Available>"
section thousands of times:
Schema File:
<?xml version="1.0" encoding="utf-8" ?>
<xs:schema id="XMLSchema"
targetNamespace="http://tempuri.org/XMLSchema.xsd"
elementFormDefault="qualified"
xmlns="http://tempuri.org/XMLSchema.xsd"
xmlns:mstns="http://tempuri.org/XMLSchema.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="AvailableBatch">
<xs:complexType>
<xs:sequence>
<xs:element name ="Date">
<xs:complexType>
<xs:sequence id="Available">
<xs:element name ="Sku" type ="xs:string" maxOccurs
="unbounded"/>
<xs:element name ="Part" type ="xs:string" maxOccurs
="unbounded"/>
<xs:element name ="Location" type ="xs:string" maxOccurs
="unbounded"/>
<xs:element name ="Qty" type ="xs:string" maxOccurs
="unbounded"/>
<xs:element name ="Time" type ="xs:string" maxOccurs
="unbounded"/>
<xs:sequence id ="Detail">
<xs:element name ="Description" type ="xs:string" maxOccurs
="unbounded"/>
<xs:element name ="Price" type ="xs:string" maxOccurs
="unbounded"/>
</xs:sequence>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
I have a tuesday deadline to get this data into my data base... can someone
please give me a hand?
I really appreciate whoever can take their time to help me... You have no
idea how much I appreciate it!
AaronI did find a schema generator: Would this be correct or is this wrong?
<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
elementFormDefault="qualified">
<xs:element name="AvailableBatch">
<xs:complexType>
<xs:sequence>
<xs:element ref="Date"/>
<xs:element maxOccurs="unbounded" ref="Available"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Date" type="xs:string"/>
<xs:element name="Available">
<xs:complexType>
<xs:sequence>
<xs:element ref="Sku"/>
<xs:element ref="Part"/>
<xs:element ref="Location"/>
<xs:element ref="Qty"/>
<xs:element ref="Time"/>
<xs:element ref="Detail"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Sku" type="xs:integer"/>
<xs:element name="Part" type="xs:string"/>
<xs:element name="Location" type="xs:integer"/>
<xs:element name="Qty" type="xs:integer"/>
<xs:element name="Time" type="xs:NMTOKEN"/>
<xs:element name="Detail">
<xs:complexType>
<xs:sequence>
<xs:element ref="Desc"/>
<xs:element minOccurs="0" ref="Weight"/>
<xs:element minOccurs="0" ref="Price"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Desc" type="xs:string"/>
<xs:element name="Weight" type="xs:decimal"/>
<xs:element name="Price" type="xs:decimal"/>
</xs:schema>
Aaron C wrote:
>Please help I am new to XML and Schema will someone look this over and tell
>me what I am doing wrong'
>Ok, I receive inventory updates once a w
from my vendor in the following>format: (Obviously trimmed down)
><?xml version="1.0" encoding="UTF-8"?>
><AvailableBatch>
> <Date>08/14/2005</Date>
> <Available>
> <Sku>40</Sku>
> <Part>10 </Part>
> <Location>20</Location>
> <Qty>0</Qty>
> <Time>07:20:09</Time>
> <Detail>
> <Desc>TIGHTS, MENS</Desc>
> <Price>14.90</Price>
> </Detail>
> </Available>
><Available>
> <Sku>REPEAT OVER 10000 TIMES</Sku>
> <Part>1REPEAT OVER 10000 TIMES</Part>
> <Location>REPEAT OVER 10000 TIMES</Location>
> <Qty>REPEAT OVER 10000 TIMES</Qty>
> <Time>REPEAT OVER 10000 TIMES</Time>
> <Detail>
> <Desc>REPEAT OVER 10000 TIMES</Desc>
> <Price>REPEAT OVER 10000 TIMES</Price>
> </Detail>
> </Available>
></AvailableBatch>
>tonight I picked through tutorials to try to setup my schema file... I kno
w
>this is wrong... Im just at a loss as to why? Now please keep in mind tha
t
>the above xml file only has the date once and then repeats the "<Available>
"
>section thousands of times:
>Schema File:
><?xml version="1.0" encoding="utf-8" ?>
><xs:schema id="XMLSchema"
> targetNamespace="http://tempuri.org/XMLSchema.xsd"
> elementFormDefault="qualified"
> xmlns="http://tempuri.org/XMLSchema.xsd"
> xmlns:mstns="http://tempuri.org/XMLSchema.xsd"
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:element name="AvailableBatch">
> <xs:complexType>
> <xs:sequence>
> <xs:element name ="Date">
> <xs:complexType>
> <xs:sequence id="Available">
> <xs:element name ="Sku" type ="xs:string" maxOccurs
>="unbounded"/>
> <xs:element name ="Part" type ="xs:string" maxOccurs
>="unbounded"/>
> <xs:element name ="Location" type ="xs:string" maxOccurs
>="unbounded"/>
> <xs:element name ="Qty" type ="xs:string" maxOccurs
>="unbounded"/>
> <xs:element name ="Time" type ="xs:string" maxOccurs
>="unbounded"/>
> <xs:sequence id ="Detail">
> <xs:element name ="Description" type ="xs:string" maxOccurs
>="unbounded"/>
> <xs:element name ="Price" type ="xs:string" maxOccurs
>="unbounded"/>
> </xs:sequence>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
></xs:schema>
>I have a tuesday deadline to get this data into my data base... can someon
e
>please give me a hand?
>I really appreciate whoever can take their time to help me... You have no
>idea how much I appreciate it!
>Aaron|||Now that I have my schema and my xml file: Where do I go from here to
bulkload my Sql Dataase?
In over my head, lol
Aaron
Aaron C wrote:
>I did find a schema generator: Would this be correct or is this wrong?
><?xml version="1.0" encoding="UTF-8"?>
><xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
>elementFormDefault="qualified">
> <xs:element name="AvailableBatch">
> <xs:complexType>
> <xs:sequence>
> <xs:element ref="Date"/>
> <xs:element maxOccurs="unbounded" ref="Available"/>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> <xs:element name="Date" type="xs:string"/>
> <xs:element name="Available">
> <xs:complexType>
> <xs:sequence>
> <xs:element ref="Sku"/>
> <xs:element ref="Part"/>
> <xs:element ref="Location"/>
> <xs:element ref="Qty"/>
> <xs:element ref="Time"/>
> <xs:element ref="Detail"/>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> <xs:element name="Sku" type="xs:integer"/>
> <xs:element name="Part" type="xs:string"/>
> <xs:element name="Location" type="xs:integer"/>
> <xs:element name="Qty" type="xs:integer"/>
> <xs:element name="Time" type="xs:NMTOKEN"/>
> <xs:element name="Detail">
> <xs:complexType>
> <xs:sequence>
> <xs:element ref="Desc"/>
> <xs:element minOccurs="0" ref="Weight"/>
> <xs:element minOccurs="0" ref="Price"/>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> <xs:element name="Desc" type="xs:string"/>
> <xs:element name="Weight" type="xs:decimal"/>
> <xs:element name="Price" type="xs:decimal"/>
></xs:schema>
>
>[quoted text clipped - 80 lines]
Message posted via http://www.webservertalk.com|||You have to specify the annotations to describe the mapping between your Xml
and relatinal structures.
Check out this link:
http://msdn.microsoft.com/library/d...ations_0gqb.asp
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aaron C via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:52DF026D67AC8@.webservertalk.com...
> Now that I have my schema and my xml file: Where do I go from here to
> bulkload my Sql Dataase?
> In over my head, lol
> Aaron
>
> Aaron C wrote:
>
> --
> Message posted via http://www.webservertalk.com
Bulk Insertion of XML or Relational Data into SQL 2005
I have looked everywhere for a definitive answer to this question but I have
yet to see one. If you have XML data or Relational Data in Pure Text
Delimited Files, what is the best way to automate the process of inserting
that data into MS SQL 2005? Is the best setup to create an SPROC that
performs a BULK INSERT fired off by a trigger to look for the presence of a
file containing the XML/Relational data or is there a better way to perform
this action that DB developers embrace?
Thanks,
--
David Bowles"David Bowles" <DavidBowles@.discussions.microsoft.com> wrote in message
news:A60F8A0A-08BE-4646-B4C3-C5C8EAB42C60@.microsoft.com...
>I have looked everywhere for a definitive answer to this question but I
>have
> yet to see one. If you have XML data or Relational Data in Pure Text
> Delimited Files, what is the best way to automate the process of inserting
> that data into MS SQL 2005? Is the best setup to create an SPROC that
> performs a BULK INSERT fired off by a trigger to look for the presence of
> a
> file containing the XML/Relational data or is there a better way to
> perform
> this action that DB developers embrace?
> Thanks,
> --
> David Bowles
>
Consider using Integration Services (assuming Standard or Enterprise Ed).
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||From what I've read on SSIS, it seems that it's more for utilization with
Business Intelligence and Data Mining. I have also read that when using SSI
S
the tables that data is applied to must be optimized for OLAP rather than
OLTP. Our IT group initially considered SSIS, but once we read up on SSIS
and found from several sources that it's not good for Transactional
Processing of Bulk Data, we moved on to looking for other solutions. From
your experience with SSIS, is this not the case?
--
David Bowles
"David Portas" wrote:
> "David Bowles" <DavidBowles@.discussions.microsoft.com> wrote in message
> news:A60F8A0A-08BE-4646-B4C3-C5C8EAB42C60@.microsoft.com...
> Consider using Integration Services (assuming Standard or Enterprise Ed).
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>|||"David Bowles" <DavidBowles@.discussions.microsoft.com> wrote in message
news:4F29AB9B-B487-4228-A178-AF4358918DE8@.microsoft.com...
> From what I've read on SSIS, it seems that it's more for utilization with
> Business Intelligence and Data Mining.
Not really. Data Warehousing requires moving lots of data around, and
that's what SSIS does.
>I have also read that when using SSIS
> the tables that data is applied to must be optimized for OLAP rather than
> OLTP. Our IT group initially considered SSIS, but once we read up on SSIS
> and found from several sources that it's not good for Transactional
> Processing of Bulk Data, we moved on to looking for other solutions. From
> your experience with SSIS, is this not the case?
> --
You might be confusing SSIS with SSAS. Anyway SSIS is a platform for
building, debugging, running and managing data movement processes (aka ETL).
It is a good fit for what you're trying to accomplish.
David|||David Bowles wrote:
> From what I've read on SSIS, it seems that it's more for utilization with
> Business Intelligence and Data Mining. I have also read that when using S
SIS
> the tables that data is applied to must be optimized for OLAP rather than
> OLTP. Our IT group initially considered SSIS, but once we read up on SSIS
> and found from several sources that it's not good for Transactional
> Processing of Bulk Data, we moved on to looking for other solutions. From
> your experience with SSIS, is this not the case?
> --
> David Bowles
>
SSIS is part of the BI toolset but it's definitely not just for OLAP.
If you are familiar with DTS you'll find SSIS a very pleasant surprise
in terms of the performance it can achieve. Some good examples can be
found at:
http://blogs.conchango.com/jamiethomson/
http://www.sqlis.com/
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
yet to see one. If you have XML data or Relational Data in Pure Text
Delimited Files, what is the best way to automate the process of inserting
that data into MS SQL 2005? Is the best setup to create an SPROC that
performs a BULK INSERT fired off by a trigger to look for the presence of a
file containing the XML/Relational data or is there a better way to perform
this action that DB developers embrace?
Thanks,
--
David Bowles"David Bowles" <DavidBowles@.discussions.microsoft.com> wrote in message
news:A60F8A0A-08BE-4646-B4C3-C5C8EAB42C60@.microsoft.com...
>I have looked everywhere for a definitive answer to this question but I
>have
> yet to see one. If you have XML data or Relational Data in Pure Text
> Delimited Files, what is the best way to automate the process of inserting
> that data into MS SQL 2005? Is the best setup to create an SPROC that
> performs a BULK INSERT fired off by a trigger to look for the presence of
> a
> file containing the XML/Relational data or is there a better way to
> perform
> this action that DB developers embrace?
> Thanks,
> --
> David Bowles
>
Consider using Integration Services (assuming Standard or Enterprise Ed).
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||From what I've read on SSIS, it seems that it's more for utilization with
Business Intelligence and Data Mining. I have also read that when using SSI
S
the tables that data is applied to must be optimized for OLAP rather than
OLTP. Our IT group initially considered SSIS, but once we read up on SSIS
and found from several sources that it's not good for Transactional
Processing of Bulk Data, we moved on to looking for other solutions. From
your experience with SSIS, is this not the case?
--
David Bowles
"David Portas" wrote:
> "David Bowles" <DavidBowles@.discussions.microsoft.com> wrote in message
> news:A60F8A0A-08BE-4646-B4C3-C5C8EAB42C60@.microsoft.com...
> Consider using Integration Services (assuming Standard or Enterprise Ed).
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>|||"David Bowles" <DavidBowles@.discussions.microsoft.com> wrote in message
news:4F29AB9B-B487-4228-A178-AF4358918DE8@.microsoft.com...
> From what I've read on SSIS, it seems that it's more for utilization with
> Business Intelligence and Data Mining.
Not really. Data Warehousing requires moving lots of data around, and
that's what SSIS does.
>I have also read that when using SSIS
> the tables that data is applied to must be optimized for OLAP rather than
> OLTP. Our IT group initially considered SSIS, but once we read up on SSIS
> and found from several sources that it's not good for Transactional
> Processing of Bulk Data, we moved on to looking for other solutions. From
> your experience with SSIS, is this not the case?
> --
You might be confusing SSIS with SSAS. Anyway SSIS is a platform for
building, debugging, running and managing data movement processes (aka ETL).
It is a good fit for what you're trying to accomplish.
David|||David Bowles wrote:
> From what I've read on SSIS, it seems that it's more for utilization with
> Business Intelligence and Data Mining. I have also read that when using S
SIS
> the tables that data is applied to must be optimized for OLAP rather than
> OLTP. Our IT group initially considered SSIS, but once we read up on SSIS
> and found from several sources that it's not good for Transactional
> Processing of Bulk Data, we moved on to looking for other solutions. From
> your experience with SSIS, is this not the case?
> --
> David Bowles
>
SSIS is part of the BI toolset but it's definitely not just for OLAP.
If you are familiar with DTS you'll find SSIS a very pleasant surprise
in terms of the performance it can achieve. Some good examples can be
found at:
http://blogs.conchango.com/jamiethomson/
http://www.sqlis.com/
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
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.
Sunday, March 25, 2012
Bulk Insert XML?
Is there a way to import a large XML file into SQL Server 2000/2005 using
T-SQL?
Looking for some utility like Bulk Insert/bcp...
- RSQLXML 3 SP3.
Comes with a great utility called SQLXML Bulk Load. Should do exactly what
you're after.
HTH. Ryan
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:53153384-7C83-40A5-9885-7DF2833DB41C@.microsoft.com...
> Is there a way to import a large XML file into SQL Server 2000/2005 using
> T-SQL?
> Looking for some utility like Bulk Insert/bcp...
> - R|||In SQL 2005 the OPENROWSET function has been improved:
http://msdn2.microsoft.com/en-us/library/ms190312(SQL.90).aspx
Look at SINGLE_BLOB, SINGLE_CLOB and SINGLE_NLOB.
ML
http://milambda.blogspot.com/|||Use OPENXML. thats the best way for insert of XML through T-SQL.
Checkout in BOL for more info. Hope this helps.
--
"Rakesh" wrote:
> Is there a way to import a large XML file into SQL Server 2000/2005 using
> T-SQL?
> Looking for some utility like Bulk Insert/bcp...
> - R|||I have a couple of examples:
http://www.sqlservercentral.com/columnists/sholliday/
http://spaces.msn.com/sholliday/ 9/22/2005
..
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:76E7E737-C5F9-435A-9CBA-298429652D4C@.microsoft.com...
> Use OPENXML. thats the best way for insert of XML through T-SQL.
> Checkout in BOL for more info. Hope this helps.
> --
>
>
> "Rakesh" wrote:
>
using
T-SQL?
Looking for some utility like Bulk Insert/bcp...
- RSQLXML 3 SP3.
Comes with a great utility called SQLXML Bulk Load. Should do exactly what
you're after.
HTH. Ryan
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:53153384-7C83-40A5-9885-7DF2833DB41C@.microsoft.com...
> Is there a way to import a large XML file into SQL Server 2000/2005 using
> T-SQL?
> Looking for some utility like Bulk Insert/bcp...
> - R|||In SQL 2005 the OPENROWSET function has been improved:
http://msdn2.microsoft.com/en-us/library/ms190312(SQL.90).aspx
Look at SINGLE_BLOB, SINGLE_CLOB and SINGLE_NLOB.
ML
http://milambda.blogspot.com/|||Use OPENXML. thats the best way for insert of XML through T-SQL.
Checkout in BOL for more info. Hope this helps.
--
"Rakesh" wrote:
> Is there a way to import a large XML file into SQL Server 2000/2005 using
> T-SQL?
> Looking for some utility like Bulk Insert/bcp...
> - R|||I have a couple of examples:
http://www.sqlservercentral.com/columnists/sholliday/
http://spaces.msn.com/sholliday/ 9/22/2005
..
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:76E7E737-C5F9-435A-9CBA-298429652D4C@.microsoft.com...
> Use OPENXML. thats the best way for insert of XML through T-SQL.
> Checkout in BOL for more info. Hope this helps.
> --
>
>
> "Rakesh" wrote:
>
using
Bulk Insert XML with IDENTITY Column
Hi ...
I have a program that will insert xml data into a table. When I add an IDENTITY column to the table then I get the following error:
... [Cannot insert the value NULL into column 'RecordId', table 'Alphanumericdata.dbo.MacgowanTestCust'; column does not allow nulls. INSERT fails.]
Reading another article here I have added the KeepIdentity(true) to my pISQLXMLBulkLoad object.
Below is the table, xml, xsd and code ...
Any comments are appreciated.
Thanks,
Chris
I have a program that will insert xml data into a table. When I add an IDENTITY column to the table then I get the following error:
... [Cannot insert the value NULL into column 'RecordId', table 'Alphanumericdata.dbo.MacgowanTestCust'; column does not allow nulls. INSERT fails.]
Reading another article here I have added the KeepIdentity(true) to my pISQLXMLBulkLoad object.
Below is the table, xml, xsd and code ...
Any comments are appreciated.
Thanks,
Chris
///////////////////////////////////////////////////
// The code
char progID[] = "SQLXMLBulkLoad.SQLXMLBulkload.3.0";
CLSID clsid;
wchar_t wide[80];
mbstowcs(wide, progID, 80);
CLSIDFromProgID(wide, &clsid);
ISQLXMLBulkLoad* pISQLXMLBulkLoad = NULL;
if(SUCCEEDED(CoCreateInstance(clsid, NULL, CLSCTX_ALL, IID_ISQLXMLBulkLoad, (void**)&pISQLXMLBulkLoad)))
{
hResult = pISQLXMLBulkLoad->put_ConnectionString(bstrConnect);
hResult = pISQLXMLBulkLoad->put_ErrorLogFile(bstrXmlErrorLogFile);
hResult = pISQLXMLBulkLoad->put_KeepIdentity((bool)TRUE);
hResult = pISQLXMLBulkLoad->Execute(bstrXmlSchemaFile, vXmlDataFile);
}
///////////////////////////////////////////////////
// xml data
<ROOT>
<Customers>
<CustomerID>1111</CustomerID>
<CompanyName>Sean Chai</CompanyName>
<City>NY</City>
</Customers>
<Customers>
<CustomerID>1112</CustomerID>
<CompanyName>Tom Johnston</CompanyName>
<City>LA</City>
</Customers>
<Customers>
<CustomerID>1113</CustomerID>
<CompanyName>Institute of Art</CompanyName>
</Customers>
</ROOT>
///////////////////////////////////////////////////
// xsc schema file
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Customers" sql:relation="MacgowanTestCust" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CustomerID" type="xsd:integer" sql:field="CustomerID" />
<xsd:element name="CompanyName" type="xsd:string" sql:field="CompanyName" />
<xsd:element name="City" type="xsd:string" sql:field="City" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
///////////////////////////////////////////////////
// table
CREATE TABLE [MacgowanTestCust] (
[RecordId] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerID] [int] NOT NULL ,
[DataSourceId] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MacgowanTestCust_DataSourceId] DEFAULT ('OH'),
[CompanyName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
PRIMARY KEY CLUSTERED
(
[RecordId]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Hi ...
To allow SQL Server to set the IDENTITY column the KeepIdentity atribute needs to be set to false (KeepIdentity((bool)FALSE).
Thanks,
Chris
///////////////////////////////////////////////////
// The code
char progID[] = "SQLXMLBulkLoad.SQLXMLBulkload.3.0";
CLSID clsid;
wchar_t wide[80];
mbstowcs(wide, progID, 80);
CLSIDFromProgID(wide, &clsid);
ISQLXMLBulkLoad* pISQLXMLBulkLoad = NULL;
if(SUCCEEDED(CoCreateInstance(clsid, NULL, CLSCTX_ALL, IID_ISQLXMLBulkLoad, (void**)&pISQLXMLBulkLoad)))
{
hResult = pISQLXMLBulkLoad->put_ConnectionString(bstrConnect);
hResult = pISQLXMLBulkLoad->put_ErrorLogFile(bstrXmlErrorLogFile);
hResult = pISQLXMLBulkLoad->put_KeepIdentity((bool)FALSE);
hResult = pISQLXMLBulkLoad->Execute(bstrXmlSchemaFile, vXmlDataFile);
}
Subscribe to:
Posts (Atom)