Wrox Programmer Forums
Go Back   Wrox Programmer Forums > XML > XML
|
XML General XML discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the XML section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 5th, 2009, 12:59 PM
Registered User
 
Join Date: Jun 2009
Posts: 8
Thanks: 1
Thanked 0 Times in 0 Posts
Default Problem reading XML file from Northwind Database (SQL 2005)

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
 
Old June 5th, 2009, 01:06 PM
Friend of Wrox
 
Join Date: Nov 2007
Posts: 1,243
Thanks: 0
Thanked 245 Times in 244 Posts
Default

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.
__________________
Martin Honnen
Microsoft MVP (XML, Data Platform Development) 2005/04 - 2013/03
My blog
 
Old June 5th, 2009, 01:12 PM
Registered User
 
Join Date: Jun 2009
Posts: 8
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by Martin Honnen View Post
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
 
Old June 5th, 2009, 01:34 PM
Friend of Wrox
 
Join Date: Nov 2007
Posts: 1,243
Thanks: 0
Thanked 245 Times in 244 Posts
Default

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.
__________________
Martin Honnen
Microsoft MVP (XML, Data Platform Development) 2005/04 - 2013/03
My blog
The Following User Says Thank You to Martin Honnen For This Useful Post:
jujiro_eb (June 5th, 2009)
 
Old June 5th, 2009, 01:57 PM
Registered User
 
Join Date: Jun 2009
Posts: 8
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Exercises for PUBS and Northwind SQL Sevrer database JaideepC99 SQL Language 3 October 10th, 2011 04:17 AM
Help with PHP file reading XML file for output rydog65 Beginning PHP 0 March 26th, 2008 05:13 PM
problem in reading xml database balaguru XML 0 May 29th, 2007 02:41 AM
scheduled export to XML file from sql 2005 jjhm93 XML 0 November 28th, 2006 07:02 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.