SQLXML BulkLoader not throwing any error but no data is inserted

Question

I'm trying to use SQLXMLBulkLoader4 from C# code into an SQL 2008 DB. But for some reason it doesn't insert any rows at all despite not throwing any error. I've made use of the bulkloads own ErrorLog file, (to check for any errors that might not cause it to crash), but no error is reported.

I have a XML file that is downloaded from a supplier (basically a list of products), I wrote an XSD to match the fields to our DB. There is nothing else writing to those specific tables, and nothing else using those files.

My BulkLoad code looks as follows (I X'ed out the actual connection string values):

public void Bulkload(string schemaFile, string xmlFile, string source)
    {
        SQLXMLBULKLOADLib.SQLXMLBulkLoad4 bulkload = new SQLXMLBULKLOADLib.SQLXMLBulkLoad4();

        try
        {
            bulkload.ConnectionString = "Provider=sqloledb;server=X;database=X;uid=X;pwd=X";
            bulkload.ErrorLogFile = k_ArticleInfoDirectory + source + "BulkLoadError.log";
            bulkload.KeepIdentity = false;
            bulkload.Execute(schemaFile, xmlFile);
        }
        catch (Exception e)
        {
            Console.WriteLine("Fel i BL: " + e);
            throw;
        }
        finally
        {
            bulkload = null;
        }

    }

The XML looks like this (stripped down, everything below is just a couple more product-fields and then more products):

<?xml version="1.0" encoding="utf-8"?>
<GetProductsResult xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Status xmlns="http://schemas.servicestack.net/types">
    <Code>0</Code>
    <Message>Ok</Message>
  </Status>
  <NumberOfProducts xmlns="http://schemas.servicestack.net/types">9826</NumberOfProducts>
  <Products xmlns="http://schemas.servicestack.net/types">
    <Product>
      <ProductID>1000002</ProductID>
      <CreatedDate>2011-11-24 15:54</CreatedDate>
      <UpdatedDate>2011-11-24 15:54</UpdatedDate>
      <Title>Vi tolererar inga förlorare - klanen Kennedy</Title>
      <Publisher>Piratförlaget</Publisher>
      ... And some more fields per Product

The XSD I wrote looks like this (again shortened):

<?xml version="1.0" encoding="iso-8859-1"?>
<xs:schema targetNamespace="http://schemas.servicestack.net/types" xmlns:xs="http://www.w3.org/2001/XMLSchema" attributeFormDefault="qualified" elementFormDefault="qualified" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

<xs:annotation>
    <xs:appinfo>

        <sql:relationship name="Status"
        parent="tblElibProduct"
        parent-key="id"
        child="tblElibStatus"
        child-key="product_id" />
                    ... + Several other sql:relationships
            </xs:appinfo>
</xs:annotation>
    <xs:element name="GetProductsResult" sql:is-constant="1">
    <xs:complexType>
        <xs:sequence>
            <xs:element name="Status" sql:is-constant="1">
                <xs:complexType>
                    <xs:sequence>
                        <xs:element name="Code" type="xs:integer" minOccurs="0" />
                        <xs:element name="Message" type="xs:string" minOccurs="0" />
                    </xs:sequence>
                </xs:complexType>
            </xs:element>
            <xs:element name="NumberOfProducts" type="xs:string" sql:is-constant="1"/>
            <xs:element name="Products" sql:is-constant="1">
                <xs:complexType>
                    <xs:sequence>
                        <xs:element minOccurs="0" maxOccurs="unbounded" name="Product" sql:relation="tblElibProduct">
                            <xs:complexType>
                                <xs:sequence>
                                    <xs:element minOccurs="1" maxOccurs="1" name="CreatedDate" type="xs:date" sql:field="created_date" />
                                    <xs:element minOccurs="1" maxOccurs="1" name="UpdatedDate" type="xs:date" sql:field="updated_date" />
                                    <xs:element minOccurs="1" maxOccurs="1" name="Title" type="xs:string" sql:field="title" />

I've stared myself blind at the namespaces, but they look correct to me. (Read about a lot of similar errors where different namespaces was the cause). The code is running from my computer, both my computer and the one with the DB have access to the network folder with the XML and XSD files.

I've tried deliberately changing some field-names in both files, and the BulkLoader flares up throwing an error on the field I just changed.

I've compared both my BulkLoader code and the XSD to examples I've found on the net, and I can't find any difference that would account for this behaviour.
It's probably something easy I'm overlooking, but I'm just not seeing it atm.

Any help at all pointing me in the right direction is deeply welcomed.

Thank you in advance!

(P.S. Sorry if the post is off in some way, it's my first time posting here, I did do my research however and I did try to follow the guidelines on how to post =) )

score 1 · Accepted Answer · answered Oct 10 '12 at 15:41

Following up on your suspicion that there might be a namespaces issue ...

The namespaces do match (between element instance and element declaration) for the elements that are qualified in the instance.

But they don't all match in the other direction; the schema declares an element whose expanded name is {http://schemas.servicestack.net/types}GetProductsResult, but no such element appears in the input instance (there is a GetProductsResult, but it's not namespace-qualified).

You might want to see what happens if you change the XML so that the first start-tag reads

<GetProductsResult 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
  xmlns:xsd="http://www.w3.org/2001/XMLSchema"
  xmlns="http://schemas.servicestack.net/types" >

That was the ticket! G'dang namespaces ruining my day... Thank you for the quick reply! =) — MrWizard, Oct 11 '12 at 05:57

score 1 · Answer 2 · answered Oct 10 '12 at 17:03

First rule to follow should always be: validate your XML against your XSD. In your case, it sounds more like you have to change your XSD rather than your XML simply because you seem to try to fit your XSD to match your XML - you said downloaded from a supplier. Your solution should rather work with the supplier's file, since if you wish to reload their file later, you don't want to keep changing it to fit what seems to be a faulty XSD.

You actually seem to need at least two XSD files: one defines a schema without a target namespace, and it imports another schema which targets http://schemas.servicestack.net/types. There are quite a number of options to allow you to generate an XSD out of an XML, and to validate an XML against an XSD.

That makes a lot of sense actually. As it is now this is more of a fix to make it work while the supplier works on providing us with an XSD to validate against. It's not how I'd prefer to work, but what to do when we do want to sell their products asap... — MrWizard, Oct 11 '12 at 05:55

SQLXML BulkLoader not throwing any error but no data is inserted

2 Answers2