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.
>
>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 $)A(& 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.
>