FAQ: How do I import the XML file with the Cyrillic alphabets into a table using SQL XML 4.0?

  • Article

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.