XML Bulk import problem

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Microsoft (neil_owens_at__NO_SPAM_hotmail.com)
Date: 08/05/04 

Date: Thu, 5 Aug 2004 19:00:36 +0100

I tried the samples in the SQL XML SP2 download and they worked just fine.
Now I'm getting the following error in the error file when trying this on
'my' data:-

<?xml version="1.0"?>
<Error>
<Record>
<HResult>0x80040E14</HResult>
<SQLState>42000</SQLState>
<NativeError></NativeError>
<ErrorState>1</ErrorState>
<Severity>16</Severity>
<Source>Microsoft OLE DB Provider for SQL Server</Source>
<Description><![CDATA[Bulk Insert failed. Unexpected NULL value in data file
row 1, column 1. Destination column (ServerID) is defined NOT NULL.]]>
</Description>
</Record>
</Error>

My XML data file is:

<MONITORREPORT>
<REPORT>
<SERVERID>115</SERVERID>
<REPORTDATE>27-Jul-04 23:59:25</REPORTDATE>
<COUNTERID>2</COUNTERID>
<DATA>0</DATA>
<COUNTERID>3</COUNTERID>
<DATA>59</DATA>
</REPORT>
</MONITORREPORT>

the XSD file is:-

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:dt="urn:schemas-microsoft-com:datatypes"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
 <xsd:element name="REPORT" sql:relation="PerfMonData">
  <xsd:complexType>
    <xsd:attribute name="SERVERID" sql:field="ServerID" type="xsd:integer"/>
    <xsd:attribute name="REPORTDATE" sql:field="ReportDate"
type="xsd:dateTime"/>
    <xsd:attribute name="COUNTERID" sql:field="MonitorObjID"
type="xsd:integer"/>
    <xsd:attribute name="DATA" sql:field="Data" type="xsd:string"/>
  </xsd:complexType>
 </xsd:element>
</xsd:schema>

If I allow NULLs in the table, I get NULL's inserted into the SQL table but
not the data, so I think the parser isn't extracting the data. I'm using
VB6SP5 and SQLXMLSP2

The VB code is:-

Dim oBulkLoad As New SQLXMLBulkLoad3
oBulkLoad.ConnectionString = Connection
oBulkLoad.Transaction = True
oBulkLoad.ErrorLogFile = "d:\temp\bulkerrorlog.xml"
oBulkLoad.Execute "d:\monitor.xsd", "d:\monitor.xml"
Set oBulkLoad = Nothing

Any help greatly appreciated as the full data file is nearly 300MB and I so
want this to work.

TIA

Neil

Relevant Pages

  • Re: XML Bulk import problem
    ... There are some problems with the schema: you are using elements in the data, ... >I tried the samples in the SQL XML SP2 download and they worked just fine. ... > Dim oBulkLoad As New SQLXMLBulkLoad3 ... > Any help greatly appreciated as the full data file is nearly 300MB and I ...
    (microsoft.public.sqlserver.xml)
  • Re: Issues with CHANGE_TRACKING AUTO after initial index population
    ... There's a problem with 64 bit SQL 2005 server. ... Director of Text Mining and Database Strategy ... started the FT initial index population when our site activity is low. ... High activity on the data file. ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Is there a maximum size to a Python program?
    ... Thanks John, I understand where you are coming from and will try and digest ... problem with a generating a single SQL statement to cover all requests. ... The idea is that instead of reading a data file and generating SQL per ... and I am new to Python. ...
    (comp.lang.python)
  • Re: Can a query find info X numbr of rows from target info?
    ... Yes, when I import the data file, I am letting Access create an index. ... giving me the desired explicit sort order. ... an exact comparison between my SQL & your advice. ... > the 'dumpend' records. ...
    (microsoft.public.access.queries)
  • Re: System table 13 error - trying to upgrade 7 to 2K
    ... All of the system tables live in the First data file (called the Primary ... Wayne Snyder, MCDBA, SQL Server MVP ... I support the Professional Association of SQL Server and it's community of SQL Server professionals. ... > I did a simple backup from sql 7 and tested the restore> back to 7, then created a blank db in my sql 2000 system,> then tried to restore the MEX database. ...
    (microsoft.public.sqlserver.server)