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.
