FAQ: How do I import the XML file with the Cyrillic alphabets into a table using SQL XML 4.0?
Questions: I am trying to import the XML data into a database table in SQL Server 2008 using SQL XML4.0. When there are some Cyrillic alphabets in the XML file, it does not work. The error message is:
An invalid character was found in text content.
Is it possible to import the XML file with Cyrillic alphabets into the table using SQL XML 4.0?
Answer: MSXML has native support for the following encodings:
UTF-8
UTF-16
UCS-2
UCS-4
ISO-10646-UCS-2
UNICODE-1-1-UTF-8
UNICODE-2-0-UTF-16
UNICODE-2-0-UTF-8
It also recognizes other encodings, such as ISO-8859-1, WINDOWS-1252 and so on.
If there are some Cyrillic alphabets in the XML file, you need to specify a valid encoding for Cyrillic alphabets. You can specify the encoding as follows:
<?xml version="1.0" encoding=" WINDOWS -1252"?>
<ROOT>
<Customers>
<CustomerID>1112</CustomerID>
<CompanyName> æøå êèé</CompanyName>
<City>Los Angeles</City>
</Customers>
</ROOT>
Now, we can use SQLXML4.0 to import this XML file into the table, please refer to the following steps:
- 1. Create this table:
CREATE TABLE Cust
(
CustomerID INT PRIMARY KEY,
CompanyName VARCHAR(20),
City VARCHAR(20)
)
GO
- 2. Add the following XCD schema:
<xsd:schema xmlns:xsd="https://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="ROOT" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Customers" sql:relation="Cust" maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CustomerID" type="xsd:integer" />
<xsd:element name="CompanyName" type="xsd:string" />
<xsd:element name="City" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
- 3. According to the following link about XML Bulk Load Examples, execute the bulk loader script.
The result is:
References:
XML Bulk Load Examples
https://msdn.microsoft.com/en-us/library/ms171806.aspx
INFO: XML Encoding and DOM Interface Methods
https://support.microsoft.com/kb/275883
Applies to
Microsoft SQL Server 2005 all editions;
Microsoft SQL Server 2008 all editions;
Microsoft SQL Server 2008 R2 all editions.