Encoding problem in SQLXML - bulkload
in [Using MS SQL Server]
|
From: worlman385 on 30 Mar 2008 05:42 I got an XML Data in UTF-8 encoding like this Dah$)A(&li but when I run the schema to insert data into SQL Server 2005 Express edition, the data will become like this Dah$)A!'�0�7li How can I solve the encoding problem? XML data: ======================================= <crew program='SH008774030000'> <member> <role>Director</role> <givenname>Dah$)A(&li</givenname> <surname>Hall</surname> </member> <member> <role>Writer</role> <givenname>Dah$)A(&li</givenname> <surname>Hall</surname> </member> </crew> XML schema: ======================================= <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" xmlns:sql="urn:schemas-microsoft-com:mapping-schema"> <xsd:annotation> <xsd:appinfo> <sql:relationship name="OrderOD" parent="productionID" parent-key="program" child="productionCrew" child-key="program"/> <sql:relationship name="ODProduct" parent="productionCrew" parent-key="role givenname surname" child="crew" child-key="role givenname surname"/> </xsd:appinfo> </xsd:annotation> <xsd:element name="crew" sql:relation="productionID"> <xsd:complexType> <xsd:sequence> <xsd:element name="member" sql:relation="crew" sql:relationship="OrderOD ODProduct"> <xsd:complexType> <xsd:sequence> <xsd:element name="role" type="xsd:string"/> <xsd:element name="givenname" type="xsd:string"/> <xsd:element name="surname" type="xsd:string"/> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:sequence> <xsd:attribute name="program" type="xsd:string"/> </xsd:complexType> </xsd:element> </xsd:schema> Database table: ======================================= CREATE TABLE ProgramListings.dbo.productionCrew ( program VARCHAR(20), role VARCHAR(20), givenname VARCHAR(20), surname VARCHAR(20), PRIMARY KEY(program, role, surname) ) CREATE TABLE ProgramListings.dbo.crew ( role VARCHAR(20), givenname VARCHAR(20), surname VARCHAR(20), PRIMARY KEY(role, surname) )
From: Dan Guzman on 30 Mar 2008 10:37 >I got an XML Data in UTF-8 encoding like this > > Dah$)A(&li > > but when I run the schema to insert data into SQL Server 2005 Express > edition, the data will become like this > > Dah$)A!'�0"7li > > How can I solve the encoding problem? Your XML is not well-formed. Entity references need to be specified in place of illegal XML characters (e.g. "&" instead of "&"). Note that this is not specific to SQLXML but part of the basic XML standards. See http://www.w3.org/TR/REC-xml/. A CDATA section is commonly used in XML in order to eliminate the need to escape illegal characters. In a CDATA section, only the end tag ("]]>"). For example: <crew program='SH008774030000'> <member> <role>Director</role> <givenname><![CDATA[Dah$)A(&li]]></givenname> <surname>Hall</surname> </member> <member> <role>Writer</role> <givenname><![CDATA[Dah$)A(&li]]></givenname> <surname>Hall</surname> </member> </crew> -- Hope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ <worlman385(a)yahoo.com> wrote in message news:23ouu39b3arbpskfmgdtfof9b81ilskjg3(a)4ax.com... >I got an XML Data in UTF-8 encoding like this > > Dah$)A(&li > > but when I run the schema to insert data into SQL Server 2005 Express > edition, the data will become like this > > Dah$)A!'�0"7li > > How can I solve the encoding problem? > > XML data: > ======================================= > > <crew program='SH008774030000'> > <member> > <role>Director</role> > <givenname>Dah$)A(&li</givenname> > <surname>Hall</surname> > </member> > <member> > <role>Writer</role> > <givenname>Dah$)A(&li</givenname> > <surname>Hall</surname> > </member> > </crew> > > > XML schema: > ======================================= > > <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" > elementFormDefault="qualified" > xmlns:sql="urn:schemas-microsoft-com:mapping-schema"> > > <xsd:annotation> > <xsd:appinfo> > <sql:relationship name="OrderOD" parent="productionID" > parent-key="program" > child="productionCrew" child-key="program"/> > > <sql:relationship name="ODProduct" parent="productionCrew" > parent-key="role givenname surname" child="crew" > child-key="role givenname surname"/> > </xsd:appinfo> > </xsd:annotation> > > > <xsd:element name="crew" sql:relation="productionID"> > <xsd:complexType> > <xsd:sequence> > <xsd:element name="member" sql:relation="crew" > sql:relationship="OrderOD ODProduct"> > <xsd:complexType> > <xsd:sequence> > <xsd:element name="role" type="xsd:string"/> > <xsd:element name="givenname" type="xsd:string"/> > <xsd:element name="surname" type="xsd:string"/> > </xsd:sequence> > </xsd:complexType> > </xsd:element> > </xsd:sequence> > <xsd:attribute name="program" type="xsd:string"/> > </xsd:complexType> > </xsd:element> > > > </xsd:schema> > > Database table: > ======================================= > > CREATE TABLE ProgramListings.dbo.productionCrew > ( > program VARCHAR(20), > role VARCHAR(20), > givenname VARCHAR(20), > surname VARCHAR(20), > PRIMARY KEY(program, role, surname) > ) > > CREATE TABLE ProgramListings.dbo.crew > ( > role VARCHAR(20), > givenname VARCHAR(20), > surname VARCHAR(20), > PRIMARY KEY(role, surname) > > )
From: Dan Guzman on 30 Mar 2008 10:56 > In a CDATA section, only the end tag ("]]>"). This sentence should have been: In a CDATA section, only the end tag ("]]>") is recognized as markup. -- Hope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ "Dan Guzman" <guzmanda(a)nospam-online.sbcglobal.net> wrote in message news:6E2D5295-651D-4DE4-ACF6-149251E0433B(a)microsoft.com... > >I got an XML Data in UTF-8 encoding like this >> >> Dah$)A(&li >> >> but when I run the schema to insert data into SQL Server 2005 Express >> edition, the data will become like this >> >> Dah$)A!'�0"7li >> >> How can I solve the encoding problem? > > Your XML is not well-formed. Entity references need to be specified in > place of illegal XML characters (e.g. "&" instead of "&"). Note that > this is not specific to SQLXML but part of the basic XML standards. See > http://www.w3.org/TR/REC-xml/. > > A CDATA section is commonly used in XML in order to eliminate the need to > escape illegal characters. In a CDATA section, only the end tag ("]]>"). > For example: > > <crew program='SH008774030000'> > <member> > <role>Director</role> > <givenname><![CDATA[Dah$)A(&li]]></givenname> > <surname>Hall</surname> > </member> > <member> > <role>Writer</role> > <givenname><![CDATA[Dah$)A(&li]]></givenname> > <surname>Hall</surname> > </member> > </crew> > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > http://weblogs.sqlteam.com/dang/ > > <worlman385(a)yahoo.com> wrote in message > news:23ouu39b3arbpskfmgdtfof9b81ilskjg3(a)4ax.com... >>I got an XML Data in UTF-8 encoding like this >> >> Dah$)A(&li >> >> but when I run the schema to insert data into SQL Server 2005 Express >> edition, the data will become like this >> >> Dah$)A!'�0"7li >> >> How can I solve the encoding problem? >> >> XML data: >> ======================================= >> >> <crew program='SH008774030000'> >> <member> >> <role>Director</role> >> <givenname>Dah$)A(&li</givenname> >> <surname>Hall</surname> >> </member> >> <member> >> <role>Writer</role> >> <givenname>Dah$)A(&li</givenname> >> <surname>Hall</surname> >> </member> >> </crew> >> >> >> XML schema: >> ======================================= >> >> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" >> elementFormDefault="qualified" >> xmlns:sql="urn:schemas-microsoft-com:mapping-schema"> >> >> <xsd:annotation> >> <xsd:appinfo> >> <sql:relationship name="OrderOD" parent="productionID" >> parent-key="program" >> child="productionCrew" child-key="program"/> >> >> <sql:relationship name="ODProduct" parent="productionCrew" >> parent-key="role givenname surname" child="crew" >> child-key="role givenname surname"/> >> </xsd:appinfo> >> </xsd:annotation> >> >> >> <xsd:element name="crew" sql:relation="productionID"> >> <xsd:complexType> >> <xsd:sequence> >> <xsd:element name="member" sql:relation="crew" >> sql:relationship="OrderOD ODProduct"> >> <xsd:complexType> >> <xsd:sequence> >> <xsd:element name="role" type="xsd:string"/> >> <xsd:element name="givenname" type="xsd:string"/> >> <xsd:element name="surname" type="xsd:string"/> >> </xsd:sequence> >> </xsd:complexType> >> </xsd:element> >> </xsd:sequence> >> <xsd:attribute name="program" type="xsd:string"/> >> </xsd:complexType> >> </xsd:element> >> >> >> </xsd:schema> >> >> Database table: >> ======================================= >> >> CREATE TABLE ProgramListings.dbo.productionCrew >> ( >> program VARCHAR(20), >> role VARCHAR(20), >> givenname VARCHAR(20), >> surname VARCHAR(20), >> PRIMARY KEY(program, role, surname) >> ) >> >> CREATE TABLE ProgramListings.dbo.crew >> ( >> role VARCHAR(20), >> givenname VARCHAR(20), >> surname VARCHAR(20), >> PRIMARY KEY(role, surname) >> >> ) >
From: worlman385 on 30 Mar 2008 18:46 Thanks your help Dan! But no! the XML is well formed: http://www.oniva.com/upload/1356/x1.jpg but after using the COM object of SQLXML to load XML file into Database, the data will look like this: http://www.oniva.com/upload/1356/x1.jpg notice the givenname data is changed. 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. Since some data in XML is non-ASCII >Your XML is not well-formed. Entity references need to be specified in >place of illegal XML characters (e.g. "&" instead of "&"). Note that >this is not specific to SQLXML but part of the basic XML standards. See >http://www.w3.org/TR/REC-xml/. > >A CDATA section is commonly used in XML in order to eliminate the need to >escape illegal characters. In a CDATA section, only the end tag ("]]>"). >For example: > ><crew program='SH008774030000'> ><member> ><role>Director</role> ><givenname><![CDATA[Dah$)A(&li]]></givenname> ><surname>Hall</surname> ></member> ><member> ><role>Writer</role> ><givenname><![CDATA[Dah$)A(&li]]></givenname> ><surname>Hall</surname> ></member> ></crew>
From: worlman385 on 30 Mar 2008 18:57
Sorry, the link of second one should be http://www.oniva.com/upload/1356/x2.jpg > >but after using the COM object of SQLXML to load XML file into >Database, the data will look like this: >http://www.oniva.com/upload/1356/x1.jpg > |