Problem reading XML file from Northwind Database (SQL 2005)

jujiro_eb · June 5th, 2009, 12:59 PM

Folks,

I loaded Northwind database to my sql server 2005. I am extracting a nested
xml using (BCP) the following query below. As you know that Northwind database
has a lot of European names with special characters. The data gets
extracted properly. However, when I try to view the file in IE (8.0), I get
an error, "An invalid character was found in text content".

Code:

 
SELECT Customer.*,
 (SELECT Orders.*
   FROM Orders
   WHERE Orders.CustomerID = Customer.CustomerID
   FOR XML AUTO, ELEMENTS, TYPE, ROOT ('orders')),
 (SELECT DISTINCT LastName, FirstName
   FROM Employees Employee
   JOIN Orders Orders ON Orders.EmployeeID = Employee.EmployeeID
   WHERE Customer.CustomerID = Orders.CustomerID
   FOR XML AUTO, ELEMENTS, TYPE, ROOT ('employees'))
   FROM Customers Customer
FOR XML AUTO, ELEMENTS, TYPE, ROOT ('customers')

I understand the cause of the error. IE is trying to view the file with
UTF-8 encoding. Obviously by adding the UTF-16 encoding statement to this
file is not going to work. The real solution is that the file must be
created with UTF-16 encoding.

How can I achieve this?

I would appreciate your help.

Regards.

Ash

Martin Honnen · June 5th, 2009, 01:06 PM

You have posted a T-SQL FOR XML statement and told us that IE 8 gives you an error loading an XML file. How exactly do you produce that file? When producing that file you need to ensure it has the right BOM and perhaps XML declaration.

jujiro_eb · June 5th, 2009, 01:12 PM

Quote:

Originally Posted by Martin Honnen

You have posted a T-SQL FOR XML statement and told us that IE 8 gives you an error loading an XML file. How exactly do you produce that file? When producing that file you need to ensure it has the right BOM and perhaps XML declaration.

Hello Martin,

I run the sql statement using bcp as following:

Code:

bcp "Query as listed in my original post" queryout c:\temp\test.xml -SSERVER -UUser -PPassword -c -r -t

The file gets created as c:\temp\test.xml and I open it in IE 8.0.

Regards.

AK

Martin Honnen · June 5th, 2009, 01:34 PM

The documentation of the bcp command line tool suggests you can have a -C[codepage] parameter so you could try

Code:

bcp "Query as listed in my original post" queryout c:\temp\test.xml -SSERVER -UUser -PPassword -c -r -t -C65001

to hope it creates an UTF-8 (65001 is the code page for UTF-8) encoded output file that way. That is completely untested however.
If that does not help and no one else does show up here with knowledge about bcp then I would suggest you try one of the Microsoft SQL server newsgroups or forums.

jujiro_eb · June 5th, 2009, 01:57 PM

Martin ,

You are right on money.

I never paid attention to the code page switch in BCP. Never really knowing the intrinsics of the code page, here is what I observed in the output XML file. For every special character, I get a two byte long characters. However, in the beowser, it looks like the original character. Rest of the characters are in single byte. As a result the file created with code page 65001 is only slightly larger than the file created without it.

I learnt something today. Now, we have to kill Kenny (South Park).

Thank you so much.

Ash