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.
No comments:
Post a Comment