Re: SQLXMLBulkLoad Question (Please help)

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Thanks for the response!

Are you confirming that this is a bug in XmlBulkload?

We are using XmlBulkload because we distribute large subsets of data from a
corporate database to multiple remote client databases. There is one xml
file generated for each table (130+ tables) and these xml files are zipped
and sent to remote client sites via dial-up modem.

We send database changes as XML files generated via dataset WriteXml which
produces the correct values for nulls, empty strings and spaces.

If you do a ReadXml into a dataset using the same XML file as input, the
dataset also contains correct values for nulls, empty strings and spaces. In
addition, if you process XML in TSQL using sp_xml_preparedocument and OPENXML
these values are preserved correctly. I do expect XmlBulkload to be
consistent with these other types of XML processing in handling nulls, empty
strings and spaces. If your export process exports data a certain way then
your import process should process data by the same rules or you get corrupt
data.

Please note that our production xml file frequently contains more than 1
million rows per table, hence the need for fast processing by SqlXmlBulkLoad
at the remote client sites.

To create a CDATA section for every character type column on every table
seems like a lot of daily processing overhead for each column of each table
for 1000 target clients.


"Bertan ARI [MSFT]" wrote:

> For Bulkload, the only way to preserve Empty String and whitespaces in an
> element content is using CDATA. Otherwise whitespaces and empy content will
> be ignored by Bulkload.
>
> Creating a CDATA section is trivial. If you are using XmlTextWriter from
> ..Net, just use StartCDATA function. If you are using Native MsXml, use the
> "startCDATA" funtion on ISAX:LexicalHandler.
>
> I am also curious why you are using XmlBulkload to migrate data from one
> database to another one while there are other dedicated tools for this like
> DTS.
>
> Thanks.
>
>
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
> "LeeH" <LeeH@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:781C7712-2DFE-4378-8D59-9168BD19E34F@xxxxxxxxxxxxxxxx
> > lhayes@xxxxxxxxxxxxxxx
> >
> > "MSSQLServerDeveloper" wrote:
> >
> > > Lee - Whats an email address for you?
> > >
> > > "LeeH" wrote:
> > >
> > > > Thanks for the input!
> > > > Again I don't think defaults are an issue. I want to preserve Nulls,
> Empty
> > > > strings AND Blank Spaces. I'm moving data from a table in one
> database to a
> > > > table in another database and expect the data in the destination table
> to be
> > > > exactly as the source table.
> > > >
> > > > "Dennis Redfield" wrote:
> > > >
> > > > > 0) CDATA is certainly an option which does work. I think we should
> raise
> > > > > the issue of how to get WriteXML to do this (and the place to post
> that
> > > > > question is into one of the DOTNET newsgroups)
> > > > > 1) Set your defaults in the database not in the XSD file, the matrix
> of how
> > > > > SQL Server handles nulls and defaults is complex and deserves
> attension to
> > > > > BOL to get it right.
> > > > >
> > > > > dlr
> > > > >
> > > > > "LvBohemian" <LvBohemian@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > > > > news:4CE33848-EE1A-4040-BD00-E01E0CBF7E66@xxxxxxxxxxxxxxxx
> > > > > > <sometag>
> > > > > > <![CDATA[This section is not type checked and may solve your
> > > > > problem]]>
> > > > > > </sometag>
> > > > >
> > > > >
> > > > >
>
>
>
.

Relevant Pages

  • Re: SQLXMLBulkLoad Question (Please help)
    ... This is a known issue with Bulkload and CDATA or attribute centric are ... > corporate database to multiple remote client databases. ... > file generated for each table and these xml files are zipped ... > dataset also contains correct values for nulls, empty strings and spaces. ...
    (microsoft.public.sqlserver.xml)
  • Re: general archetecture questions.
    ... > first app i'm writing will be using XML as a database of sorts, using> dataset and dataview and wrapped up in a set of classes. ... in the past i used> ".getinstance" in java to get a single instance of a class contained in a application variable to ensure i dont have 3-4> different instances hitting the database and worse now writing 3-4 different> xml files. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: xml, sql mobile and server 2005
    ... I have a Windows Mobile application that uses a database. ... this works great and I get some nice xml files on mp mobile device. ... Depending on whether schema info is included on the wm5 side, ... CONSTRAINT PRIMARY KEY CLUSTERED ...
    (microsoft.public.sqlserver.xml)
  • Re: Windows forms application and database back-up
    ... But setting it back to the database needs all kind of creation, ... What if you created a function that created a "Select * from Table" for all tables and wrote that to multiple XML files. ... attached to the SQL Server instance), ... The other option would be to use SQL Server Express' USER INSTANCE, to which, the user do not have to have local admin rigth to attach database to SQL Server Express instance. ...
    (microsoft.public.dotnet.general)
  • Re: Database programminf question...
    ... Does writing the data to the XML files on the laptop retain the ... files allowing them to be read back in without loosing any database ... the laptop SQL instance in a way that would allow using the DataSet/ ...
    (microsoft.public.dotnet.languages.csharp)