>Your XML is not well-formed. Entity references need to be specified in
>place of illegal XML characters (e.g. "&" instead of "&"). Note that
[quoted text clipped - 17 lines]
></member>
></crew>
Sorry, the link of second one should be
http://www.oniva.com/upload/1356/x2.jpg
>but after using the COM object of SQLXML to load XML file into
>Database, the data will look like this:
>http://www.oniva.com/upload/1356/x1.jpg
worlman385@yahoo.com - 30 Mar 2008 23:36 GMT
Just solve the problem
use NVARCHAR instead of VARCHAR
NVARCHAR - support unicdoe
==============
CREATE TABLE ProgramListings.dbo.productionCrew
(
program NVARCHAR(20),
role NVARCHAR(20),
givenname NVARCHAR(20),
surname NVARCHAR(20),
PRIMARY KEY(program, role, surname)
)
> I think the input is UTF-8 data but the SQLXML interface convert UTF-8
> to ASCII so the data is messed up when loaded from XML to database.
Does your XML include a processing instruction to specify UTF-8 encoding?
For example:
<?xml version="1.0" encoding="UTF-8" ?>

Signature
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
> Thanks your help Dan!
>
[quoted text clipped - 33 lines]
>></member>
>></crew>
worlman385@yahoo.com - 30 Mar 2008 23:25 GMT
Yes, I put the string like
<?xml version="1.0" encoding="UTF-8" ?>
also tried other encoding string but no luck.
I tried to put the parameter in the connection string:
sendStringParametersAsUnicode=true
but doesn't help
=======================
hr = pIXMLBulkLoad->put_ConnectionString(L"provider=SQLOLEDB;data
source=(local)\\SQLEXPRESS;database=ProgramListings;integrated
security=SSPI;sendStringParametersAsUnicode=true");
}
>> I think the input is UTF-8 data but the SQLXML interface convert UTF-8
>> to ASCII so the data is messed up when loaded from XML to database.
[quoted text clipped - 3 lines]
>
><?xml version="1.0" encoding="UTF-8" ?>
worlman385@yahoo.com - 30 Mar 2008 23:36 GMT
Just solve the problem
use NVARCHAR instead of VARCHAR
NVARCHAR - support unicdoe
==============
CREATE TABLE ProgramListings.dbo.productionCrew
(
program NVARCHAR(20),
role NVARCHAR(20),
givenname NVARCHAR(20),
surname NVARCHAR(20),
PRIMARY KEY(program, role, surname)
)
Dan Guzman - 31 Mar 2008 12:42 GMT
> Just solve the problem
>
> use NVARCHAR instead of VARCHAR
I'm glad you were able to figure this out. I had assumed that your database
default collation was appropriate for the characters being stored.

Signature
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
> Just solve the problem
>
[quoted text clipped - 12 lines]
> PRIMARY KEY(program, role, surname)
> )
> Thanks your help Dan!
>
[quoted text clipped - 9 lines]
> I think the input is UTF-8 data but the SQLXML interface convert UTF-8
> to ASCII so the data is messed up when loaded from XML to database.
Your original post was encoded in iso-2022-cn, which may explain why I
and Dan so very funny characters.
Since your target columns are varchar, I need to ask: what is the collation
of these columns? If that collation does not include é in its ANSI set,
you cannot get the name right. Then again, then you should get "e". What
you got appears to be UTF-8 interpreted as ANSI.
I don't have any experience of XML bulkload, so I don't know what is
going on. You could try to add
<?xml version="1.0" encoding="utf-8" ?>
first in the file, even though this should not be needed since UTF-8
is the default for XML.

Signature
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
worlman385@yahoo.com - 30 Mar 2008 23:36 GMT
Just solve the problem
use NVARCHAR instead of VARCHAR
NVARCHAR - support unicdoe
==============
CREATE TABLE ProgramListings.dbo.productionCrew
(
program NVARCHAR(20),
role NVARCHAR(20),
givenname NVARCHAR(20),
surname NVARCHAR(20),
PRIMARY KEY(program, role, surname)
)
>> I think the input is UTF-8 data but the SQLXML interface convert UTF-8
>> to ASCII so the data is messed up when loaded from XML to database.
[quoted text clipped - 14 lines]
>first in the file, even though this should not be needed since UTF-8
>is the default for XML.