I posted this last week and no one posted a reply. I'll try to re-word the
question so it's clearer as to what I'm asking.

I'm running SQLXMLBulkLoad from a vb.NET app supplying both and xml file and
a schema (xsd) file. The data is getting loaded into the table except for
columns that contain spaces or empty strings. These columns are being
populated with NULLS.

Is there anyway to instruct SQLXMLBulkLoad to load columns with empty
strings (or columns that contain only spaces) and retain those values instead
of populating with a Null?

The XML input was created from a dataset (WriteXml) and I'm trying to load
this data into another database and it needs to retain all values (i.e. empty
strings). I expected that column tags that are missing from the xml file
would be loaded as nulls but columns such as <tag1></tag2> would load as an
empty string.

I've searched for this problem extensively but can't find that anyone has
experienced this problem. However, it seems so basic (Null vs empty string)
I would think that if SQLXMLBulkLoad did not load data correctly that other
people would have reported this since Null and empty string handling are so
basic from a database perspective. Am I missing something here?

LeeH - Which version are you using 3.0 or 4.0? Do you have any examples?

"LeeH" wrote:

I'm running SQLXML 3.0 SP3.

"MSSQLServerDeveloper" wrote:

Yes, I have examples. Below is a sample .xsd and .xml listings.

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
xmlns:codegen="urn:schemas-microsoft-com:xml-msprop">
<xsd:element name="TestTableNew" sql:relation="TestTableNew" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="A" type="xsd:string" />
<xsd:element name="B" type="xsd:integer" />
<xsd:element name="C" type="xsd:integer" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
<TestTableNew>
<A>TestA</A>
<B>100</B>
<C>200</C>
</TestTableNew>
<TestTableNew>
<B>400</B>
<C>500</C>
</TestTableNew>
<TestTableNew>
<A></A>
<B>5</B>
<C>6</C>
</TestTableNew>
</NewDataSet>

"MSSQLServerDeveloper" wrote:

Let me check and get back with you ...

"LeeH" wrote:

The error you are getting indicates that you have a column in the table which
is not nullable.

Because SqlXml bulkload does not find any data for that column, it attempts
to insert a null. You can fix this by specifying a default value in the
mapping schema or in the database - or by making the column nullable.



"LeeH" wrote:

All columns allow nulls.
I'm not getting an error. What I'm seeing with the example data is that the
2nd row is correctly loading a null for columns "A" because the column tag
for column "A" is missing.

In the 3rd and last row column "A" is shown as <A></A>. This too is loading
column "A" as a null and it needs to load it as an empty string. This is the
problem.


"MSSQLServerDeveloper" wrote:

OK .. BTW I am using version 4.0 - Do you have a URL where I can download
version 3.0 to try this out?

"LeeH" wrote:

Are you talking about the SQLXML version?
No, I don't have a URL to post it to.
I wasn't aware that there is a 4.0. It appears from the MS website that 3.0
SP3 is the latest?

Thanks for sticking with me on this!


"MSSQLServerDeveloper" wrote:

absolutely. Love to help. Do you have the URL from MS website that says 3.0
is latest version? And hopefully a download?

"LeeH" wrote: