XML data insert into table

The problem is that the <DataContent> portion of the XML contains HTML.  HTML contains characters that are illegal in well formed XML.

Options I can think of:

1)  Change the data type of the column to a varchar or text or other non-xml type.

2)  Separate the HTML and XML. Create a separate varchar column to save the <DataContent> HTML in, and save the XML in your XML column without the <DataContent>.  You can merge them later during data retrieval.

Hi Eric, Thanks for your response.

1. Change the data type of the column to a varchar or text or other non-xml type.
- will it be easy to handle the data if the column has sent non-xml type?

2. Separate the HTML and XML.
-  how can i separate the html and xml and can merge later? any example?

Thank you

public void LoadXml(string xpath) {
            try{
            XmlDocument xdoc = new XmlDocument();
            xdoc.PreserveWhitespace = false;
            xdoc.Load(xpath);
            string myXmlData = xdoc.OuterXml;
            SqlConnection conn = new SqlConnection("Data Source=test\\SQLEXPRESS;Initial Catalog=newsml;Integrated Security=True");
            conn.Open();

            string strSql = "insert into XmlNews_New(sysdate,news) values('2010/11/20',Convert(xml,'" +  myXmlData + "',2))";
            SqlCommand comm = new SqlCommand(strSql,conn);
            comm.ExecuteNonQuery();
            conn.Close();
            }
            catch(Exception ex){
            }

Select allOpen in new window

If you intend to use xml functions in the database on that xml column, then you should probably keep the xml intact, and separate the html into a separate column or table.

If you are not using xml functions in the database and are only storing the data, then you can store it in a varchar(max) column.

HI Erick
As you suggested the separation xml and html data works very much fine on my contest.
BTW can you help me how can I separate the xml and html data on my following code.

1.
XML column
from:
<?xml version="1.0" encoding="utf-8"?>
.......
<Format FormalName="XHTML"/>
(before <DataContent>)

2. nvarchar(max) column
from:
<DataContent>
........
</DataContent>

Thanks alot.

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE NewsML SYSTEM "http://www.iptc.org/std/NewsML/1.2/specification/NewsML_1.2.dtd">
<NewsML Version="1.2">
  <Catalog Href="http://www-test.com.catalog.xml" />
  <NewsEnvelope>
    <TransmissionId>1328088_</TransmissionId>
    <DateAndTime>20100930T110626+0200</DateAndTime>
    <NewsProduct FormalName="Regulatory Information Service"/>
  </NewsEnvelope>
  <NewsItem>
    <Identification>
      <NewsIdentifier>
        <ProviderId>test.com</ProviderId>
        <DateId>20100930</DateId>
        <NewsItemId>TEST1328088</NewsItemId>
        <RevisionId PreviousRevision="0" Update="N">1</RevisionId>
        <PublicIdentifier>urn:newsml:TEST.com:20100930:XX1328088:1</PublicIdentifier>
      </NewsIdentifier>
    </Identification>
    <NewsManagement>
      <NewsItemType FormalName="News"/>
      <FirstCreated>20100930T110626+0200</FirstCreated>
      <ThisRevisionCreated>20100930T110626+0200</ThisRevisionCreated>
      <Status FormalName="Usable"/>
      <Urgency FormalName="4"/>
      <Property FormalName="sst.3rdPartyStyleGuideVersion" Value="2.0" />
    <Property FormalName="category" Value="N" />
    <Property FormalName="ern" Value="N.A." />
    <Property FormalName="distributor" Value="SSS" />
    </NewsManagement>
    <NewsComponent xml:lang="en" Essential="no" EquivalentsList="no" Duid="NC00001">
      <TopicSet FormalName="Companies">
        <Topic Duid="T000001">
          <TopicType FormalName="Company"/>
          <FormalName Scheme="CompanyLongName"><![CDATA[Test Client]]></FormalName>
          <FormalName Scheme="CompanyShortName"><![CDATA[]]></FormalName>
      <FormalName Scheme="Country"><![CDATA[U.S.A.]]></FormalName>
      <FormalName Scheme="City"><![CDATA[Paris]]></FormalName>
          <FormalName Scheme="TIDM"></FormalName>
      <FormalName Scheme="USTIC"></FormalName>
          <FormalName Scheme="ISIN"></FormalName>
      <FormalName Scheme="ISIC"></FormalName>
          <FormalName Scheme="cRIC"></FormalName>
      <FormalName Scheme="CompanyUrl"></FormalName>
      <FormalName Scheme="GermanWkn"></FormalName>
      <FormalName Scheme="Sedol"></FormalName>
        </Topic>    
      </TopicSet>  
      <Role FormalName="Main"/>
      <NewsLines>
        <HeadLine><![CDATA[TEST RELEASE]]></HeadLine>
        <DateLine>London, September, 30, 2010</DateLine>
      </NewsLines>
      <AdministrativeMetadata>
        <Creator>     
          <Party FormalName="Test Client"/>  
        </Creator>
        <Source>
          <Party FormalName="Test Client"/>
        </Source>
      </AdministrativeMetadata>

      <RightsMetadata/>

      <DescriptiveMetadata>
        <Language FormalName="en"/>
        <TopicOccurrence Topic="#T000001"/>

        <TopicOccurrence Topic="#T00003" HowPresent="FSACategories"/>
        <TopicOccurrence Topic="#T00004" HowPresent="MediumImportance"/>
    
              <SubjectCode Duid="SC#1" HowPresent="Related" >
        <Subject Duid="S#1" HowPresent="Related" FormalName="Economy, Business And Finance" />
              <SubjectMatter Duid="S#1_SM#1" HowPresent="Related" FormalName="Company Information" />
                  <SubjectDetail Duid="S#1_SM#1_SD#1" HowPresent="Related" FormalName="Contract" />
                  <SubjectDetail Duid="S#1_SM#1_SD#2" HowPresent="Related" FormalName="Earnings" />
                    </SubjectCode>
        
    <TopicOccurrence Topic="#ICB_IN" HowPresent="ICBClasification"/>
    <TopicOccurrence Topic="#ICB_SU" HowPresent="ICBClasification"/>
    <TopicOccurrence Topic="#ICB_SE" HowPresent="ICBClasification"/>
      </DescriptiveMetadata>
    
      <ContentItem Duid="CI00001">
        <MediaType FormalName="text"/>
        <Format FormalName="XHTML"/>
<DataContent>
<html xmlns="http://www.w3.org/1999/xhtml" xmlns:mce="mce"><head><style>* { font-family: Arial, Verdana, Helvetica; font-size: 13px;}
td { padding: 3px; }
}</style><title>TEST RELEASE</title></head><body class="TEST">   <p align="center" class="TEST" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 10pt; margin-left: 0cm"><i class="TEST"><u class="TEST">You can disregard this test release. </u></i></p> <p class="TEST" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 36pt; text-indent: -18pt">· TEST TEXT TEST TEXT TEST TEXT TEST TEXT TEST TEXT TEST TEXT TEST TEXT TEST TEXT TEST TEXT TEST TEXT </p> <p class="TEST" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 36pt"> </p> <p class="TEST" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 36pt; text-indent: -18pt">· TEST TEXT TEST TEXT TEST TEXT TEST TEXT TEST TEXT TEST TEXT TEST TEXT </p> <p class="TEST" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 36pt"> </p> <p class="TEST" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 10pt; margin-left: 36pt; text-indent: -18pt">· TEST TEXT TEST TEXT TEST TEXT TEST TEXT TEST TEXT TEST TEXT TEST TEXT TEST TEXT TEST TEXT </p> <p align="justify" class="TEST" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 10pt; margin-left: 0cm"><a class="TEST" href="http://www.test.com/" target="_blank">Test</a> text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test.</p> <p align="justify" class="TEST" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 10pt; margin-left: 0cm">Test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text test text. <a class="TEST" href="http://www.1.com/" target="_blank">www.1.com</a> <a class="TEST" href="mailto:test@1.com" target="_blank">test@1.com</a> </p> <p class="TEST" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 10pt; margin-left: 0cm">Quelques caractères spéciaux €, à, é, è, ç, ï, ë, í, ñ, %, &amp;, §, #,;, «, »</p> <p class="TEST" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 10pt; margin-left: 0cm">TABLEAU 1</p>                                                                     </body></html>
</DataContent>
      </ContentItem>
    </NewsComponent>
  </NewsItem>
</NewsML>

Select allOpen in new window

Hi Eric,
Thanks for the above code. it works awesome.
I have following questions.

1. I have found sometime the xml data also has illegal characters so I need to make it make it non xml column. so what i have thought is better to put the all the xml intact into nvarchar(max) column, what do you suggest?

2. After I stored xml data on non-xml column table, how can i query the xml data
eg. <ProviderId>test.com</ProviderId>
<HeadLine><![CDATA[TEST RELEASE]]></HeadLine>

Thanks alot

Once you have stored the XML as nvarchar, it cannot be queried using SQL XQuery calls directly from the table.
The only way to use the XQuery methods would be to first convert the nvarchar to XML in SQL, which you cannot do since wou will have the same problem again with the invalid characters.

It seems like the XMLDocument from the UI is not as sensitive to the formatting so you could do the parsing there.  For example pull down the data from the database and load it into an xmlDocument, then manipulate the data.  The downside is you can only do this one row at a time so you will have to store information in other columns that allow you to find the rows you need.

What you decide to do will depend on how the data is used.  If you need to search the table for a certain value or date in the XML column, then it is best to save the data as XML, and separate all the data nodes which might contain invalid characters into separate columns.  If you do not need to search the XML column in SQL, then you could store it as nvarchar with other identifying columns to aid in locating your records.

Finally, if you decide to store the data as nvarchar and need to extract certain node values then you could use string functions to pull out the data.  Again this method requires that you can locate the one row of data in the table that you need to query.

Another option would be to remove or change the invalid characters.
Can you give an example of the XML you need to save with the bad data you are having problems with?

HI Erick,

Thanks for your response.

I have added the example file below. what I want to do is, we receive daily news on the following format, I want to load the file into database and query the xml data to display for website.
I need data like:
Date
Category
Companyurl
Headline
Body

And here is the illegal character on xml file like:
<FormalName Scheme="City">
 <![CDATA[ Montréal, Québec
  ]]>
  </FormalName>

(it is different kind of European characters which is not accepting on xml data type on sql)

Many thanks

<?xml version="1.0" encoding="utf-8" ?> 
  <!DOCTYPE NewsML (View Source for full doctype...)> 
  <NewsML Version="1.2">
  <Catalog Href="http://about.news.com/newsml/vocabulary/catalog-news-3rdParty-master_catalog.xml" /> 
 <NewsEnvelope>
  <TransmissionId>1464248_</TransmissionId> 
  <DateAndTime>20101119T160335+0100</DateAndTime> 
  <NewsProduct FormalName="Regulatory Information Service" /> 
  </NewsEnvelope>
 <NewsItem>
 <Identification>
 <NewsIdentifier>
  <ProviderId>testnews.com</ProviderId> 
  <DateId>20101119</DateId> 
  <NewsItemId>test1464248</NewsItemId> 
  <RevisionId PreviousRevision="0" Update="N">1</RevisionId> 
  <PublicIdentifier>urn:newsml:testnews.com:20101119:test1464248:1</PublicIdentifier> 
  </NewsIdentifier>
  </Identification>
 <NewsManagement>
  <NewsItemType FormalName="News" /> 
  <FirstCreated>20101119T160335+0100</FirstCreated> 
  <ThisRevisionCreated>20101119T160335+0100</ThisRevisionCreated> 
  <Status FormalName="Usable" /> 
  <Urgency FormalName="4" /> 
  <Property FormalName="news.3rdPartyStyleGuideVersion" Value="2.0" /> 
  <Property FormalName="category" Value="N" /> 
  <Property FormalName="ern" Value="N.A." /> 
  <Property FormalName="distributor" Value="test news ONE" /> 
  </NewsManagement>
 <NewsComponent xml:lang="en" Essential="no" EquivalentsList="no" Duid="NC00001">
 <TopicSet FormalName="Companies">
 <Topic Duid="T000001">
  <TopicType FormalName="Company" /> 
 <FormalName Scheme="CompanyLongName">
 <![CDATA[  Transportation
  ]]> 
  </FormalName>
 <FormalName Scheme="CompanyShortName">
 <![CDATA[ 
  ]]> 
  </FormalName>
 <FormalName Scheme="Country">
 <![CDATA[ U.S.A.
  ]]> 
  </FormalName>
 <FormalName Scheme="City">
 <![CDATA[ Montréal, Québec
  ]]> 
  </FormalName>
  <FormalName Scheme="TIDM" /> 
  <FormalName Scheme="USTIC" /> 
  <FormalName Scheme="ISIN" /> 
  <FormalName Scheme="ISIC" /> 
  <FormalName Scheme="cRIC" /> 
  <FormalName Scheme="CompanyUrl">http://www..com</FormalName> 
  <FormalName Scheme="GermanWkn" /> 
  <FormalName Scheme="Sedol" /> 
  </Topic>
  </TopicSet>
 <TopicSet FormalName="FSACategories">
 <Topic Duid="T00003">
  <TopicType FormalName="FSACategory" /> 
  <FormalName Scheme="FSACategory" /> 
  </Topic>
  </TopicSet>
 <TopicSet FormalName="MediumImportance">
 <Topic Duid="T00004">
  <TopicType FormalName="Geography" Scheme="RTT" /> 
  <FormalName Scheme="N2000">us</FormalName> 
  </Topic>
  </TopicSet>
 <TopicSet FormalName="ICBClasification">
 <Topic Duid="ICB_IN">
  <TopicType FormalName="ICBIndustry" /> 
  <FormalName Scheme="ICBIndustry">Industrials</FormalName> 
  </Topic>
 <Topic Duid="ICB_SU">
  <TopicType FormalName="ICBSupersector" /> 
  <FormalName Scheme="ICBSupersector">Industrial Goods & Services</FormalName> 
  </Topic>
 <Topic Duid="ICB_SE">
  <TopicType FormalName="ICBSector" /> 
  <FormalName Scheme="ICBSector">Industrial Engineering</FormalName> 
  </Topic>
  </TopicSet>
  <TopicSet FormalName="CustomTags" /> 
  <TopicSet FormalName="ErnTagging" /> 
  <Role FormalName="Main" /> 
 <NewsLines>
 <HeadLine>
 <![CDATA[ headline News Trains Soon Operating
  ]]> 
  </HeadLine>
  <DateLine>London, November, 19, 2010</DateLine> 
  </NewsLines>
 <AdministrativeMetadata>
 <Creator>
  <Party FormalName="Transportation" /> 
  </Creator>
 <Source>
  <Party FormalName="Transportation" /> 
  </Source>
  </AdministrativeMetadata>
  <RightsMetadata /> 
 <DescriptiveMetadata>
  <Language FormalName="en" /> 
  <TopicOccurrence Topic="#T000001" /> 
  <TopicOccurrence Topic="#T00003" HowPresent="FSACategories" /> 
  <TopicOccurrence Topic="#T00004" HowPresent="MediumImportance" /> 
 <SubjectCode Duid="SC#1" HowPresent="Related">
  <Subject Duid="S#1" HowPresent="Related" FormalName="Economy, Business And Finance" /> 
  <SubjectMatter Duid="S#1_SM#1" HowPresent="Related" FormalName="Company Information" /> 
  </SubjectCode>
  <TopicOccurrence Topic="#ICB_IN" HowPresent="ICBClasification" /> 
  <TopicOccurrence Topic="#ICB_SU" HowPresent="ICBClasification" /> 
  <TopicOccurrence Topic="#ICB_SE" HowPresent="ICBClasification" /> 
  </DescriptiveMetadata>
 <ContentItem Duid="CI00001">
  <MediaType FormalName="text" /> 
  <Format FormalName="XHTML" /> 
 <DataContent>
 <html xmlns="http://www.w3.org/1999/xhtml" xmlns:mce="mce">
 <head>
  <style>* { font-family: Arial, Verdana, Helvetica; font-size: 13px;} td { padding: 3px; } }</style> 
  <title>headline News Trains Soon Operating</title> 
  </head>
 <body class="news">
 <p class="news" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm">
  <strong class="news">Deutsche Bahn orders a further 51 trains of the flexible vehicle system for service on the "Mitteldeutsche S-Bahn" railway network (Central German suburban network)</strong> 
  </p>
  <p class="news" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm"> </p> 
  <p class="news" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm">BERLIN, GERMANY--(November 19, 2010) - asfjalsf askf asfk asfsa f asdfdsdka fasfj askfjs afa.</p> 
  <p class="news" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm"> </p> 
  <p class="news" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm">36 new three-car and 15 four-car TALENT 2 suburban trains will enter service beginning December 2013 in and around Leipzig on the "Mitteldeutsche S-Bahn" network.</p> 
  <p class="news" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm"> </p> 
  <p class="news" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm">The trains are comfortably-equipped with air-conditioning, sliding steps at each entry and spacious multi-purpose areas with wheelchair, pram and bicycle access. Seating is generously spaced, not just for first class passengers, and therefore meets regional as well as suburban travel needs. For families with children there are specially-designed seating areas. The trains are also equipped with toilets for disabled passengers. The passenger compartments have integrated monitors that display current travel information. Passenger safety is enhanced through video monitoring and implementation of the latest crash requirements.</p> 
  <p class="news" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm"> </p> 
  <p class="news" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm">Paintwork specially designed for the "Mitteldeutsche S-Bahn" network gives these TALENT 2 trains a completely different appearance to others.</p> 
  <p class="news" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm"> </p> 
  <p class="news" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm">The new order brings the total number of TALENT 2 trains ordered by Deutsche Bahn to 287. At the end of August, DB AG ordered twenty-three five-car trains for service on the Warnow network in Mecklenburg-Vorpommern, and a total of thirty-seven (34 four-car and three two-car) trains for the Werdenfels railway in Bavaria.</p> 
  <p class="news" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm"> </p> 
  <p class="news" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm">A major feature of the recently developed TALENT 2 Electrical Multiple Unit platform (the 442 series) is its modular concept which includes a high degree of standardisation. The trains can be configured with almost unlimited flexibility and are both cost effective and cost transparent at the same time. The innovative, modular construction principle allows for countless variants on the same train type. The two- to six-car trains can be equipped with a wide variety of technical modules, depending on whether they are to be used as commuter or regional express trains.</p> 
  <p class="news" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm"> </p> 
  <p class="news" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm">A scalable traction output provided by the MITRAC propulsion and control system makes these trains particularly energy efficient and easily adaptable to the frequent acceleration and braking requirements in the commuter sector, as well as to the demands of the regional rail network.</p> 
  <p class="news" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm"> </p> 
- <p class="news" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm">
  <strong class="news">About Transportation</strong> 
  </p>
  <p class="news" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm"> </p> 
  <p class="news" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm">Transportation, a global leader in rail technology, offers the broadest portfolio in the rail industry and delivers innovative products and services that set new standards in sustainable mobility. ECO4 technologies - built on the four cornerstones of energy, efficiency, economy and ecology - conserve energy, protect the environment and help to improve total train performance. Transportation is headquartered in Berlin, Germany and has a presence in over 60 countries. It has an installed base of over 100,000 vehicles worldwide.</p> 
  <p class="news" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm"> </p> 
 <p class="news" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm">
  <strong class="news">About</strong> 
  </p>
  <p class="news" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm"> </p> 
 <p class="news" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm">
  A world-leading manufacturer of innovative transportation solutions, from commercial aircraft and business jets to rail transportation equipment, systems and services, Inc. is a global corporation headquartered in Canada. Its revenues for the fiscal year ended Jan. 31, 2010, were $19.4 billion US, and its shares are traded on the Toronto Stock Exchange (BBD). is listed as an index component to the Dow Jones Sustainability World and North America indexes. News and information are available at 
  <a class="news" href="http://www..com/" target="_blank">www..com</a> 
  </p>
  <p class="news" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm"> </p> 
<p class="news" style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom:0pt;">
  , TALENT, MITRAC and ECO4 are trademarks of Inc. or its subsidiaries. 
  <br class="news" /> 
  <a class="news" href="http://www.theclimateisrightfortrains.com/" target="_blank">www.theclimateisrightfortrains.com</a> 
  </p>
  <p class="news" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm"> </p> 
 <p class="news" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm">
  <strong class="news">Contacts:</strong> 
  <br class="news" /> 
  North America: +1 450 441 3007 begin_of_the_skype_highlighting              +1 450 441 3007      end_of_the_skype_highlighting 
  <br class="news" /> 
  Canada: Marc-Andre Lefebvre 
  <br class="news" /> 
  <a class="news" href="mailto:marc-andre.lefebvre@ca.transport..com" target="_blank">marc-andre.lefebvre@ca.transport..com</a> 
  <br class="news" /> 
  USA: Maryanne Roberts 
  <br class="news" /> 
  <a class="news" href="mailto:maryanne.roberts@us.transport..com" target="_blank">maryanne.roberts@us.transport..com</a> 
  <br class="news" /> 
  <br class="news" /> 
  Germany, Austria, Switzerland, Central 
  <br class="news" /> 
  and Eastern Europe: +49 30 98607 1134 begin_of_the_skype_highlighting              +49 30 98607 1134      end_of_the_skype_highlighting 
  <br class="news" /> 
  Heiner Spannuth, 
  <a class="news" href="mailto:heiner.spannuth@de.transport..com" target="_blank">heiner.spannuth@de.transport..com</a> 
  <br class="news" /> 
  <br class="news" /> 
  Nordic Countries: +46 10 852 5062 begin_of_the_skype_highlighting              +46 10 852 5062      end_of_the_skype_highlighting 
  <br class="news" /> 
  Jonny Hedberg, 
  <a class="news" href="mailto:jonny.hedberg@se.transport..com" target="_blank">jonny.hedberg@se.transport..com</a> 
  <br class="news" /> 
  <br class="news" /> 
  Russia: +7 495 775 1830 begin_of_the_skype_highlighting              +7 495 775 1830      end_of_the_skype_highlighting 
  <br class="news" /> 
  Alexander Bocharov 
  <br class="news" /> 
  <a class="news" href="mailto:alexander.bocharov@ru.transport..com" target="_blank">alexander.bocharov@ru.transport..com</a> 
  <br class="news" /> 
  <br class="news" /> 
  UK, Ireland, Australia, New Zealand, other countries: 
  <br class="news" /> 
  +44 1332 266470 begin_of_the_skype_highlighting              +44 1332 266470      end_of_the_skype_highlighting 
  <br class="news" /> 
  Neil Harvey, 
  <a class="news" href="mailto:neil.harvey@uk.transport..com" target="_blank">neil.harvey@uk.transport..com</a> 
  <br class="news" /> 
  <br class="news" /> 
  Benelux: +49 30 98607 1141 begin_of_the_skype_highlighting              +49 30 98607 1141      end_of_the_skype_highlighting 
  <br class="news" /> 
  Janet Olthof, 
  <a class="news" href="mailto:janet.olthof@de.transport..com" target="_blank">janet.olthof@de.transport..com</a> 
  <br class="news" /> 
  <br class="news" /> 
  France: +33 6 07 78 95 38 begin_of_the_skype_highlighting              +33 6 07 78 95 38      end_of_the_skype_highlighting 
  <br class="news" /> 
  Anne Froger, 
  <a class="news" href="mailto:anne.froger@fr.transport..com" target="_blank">anne.froger@fr.transport..com</a> 
  <br class="news" /> 
  <br class="news" /> 
  South Europe, Brazil and India: +35 1 919 693 728 begin_of_the_skype_highlighting              +35 1 919 693 728      end_of_the_skype_highlighting 
  <br class="news" /> 
  Luis Ramos, 
  <a class="news" href="mailto:luis.ramos@pt.transport..com" target="_blank">luis.ramos@pt.transport..com</a> 
  </p>
 <div id="div_with_clipping_code_id" name="div_with_clipping_code_name">
  <p>test#1464248</p> 
  <br /> 
  </div>
  <div id="div_with_attachments_id" name="div_with_attachments_name" /> 
 <div id="div_with_disclaimer_id" name="div_with_disclaimer_name">
 <p>
  This announcement is distributed by test news on behalf of test news clients. 
  <br /> 
  <br /> 
  The owner of this announcement warrants that: 
  <br /> 
  (i) the releases contained herein are protected by copyright and other applicable laws; and 
  <br /> 
  (ii) they are solely responsible for the content, accuracy and originality of the 
  <br /> 
  information contained therein. 
  <br /> 
  <br /> 
  Source: Transportation via test news ONE 
  </p>
  <br /> 
  </div>
  </body>
  </html>
  </DataContent>
  </ContentItem>
  </NewsComponent>
  </NewsItem>
  </NewsML>

Select allOpen in new window

The only problem I found here is this:

<FormalName Scheme="ICBSupersector">Industrial Goods & Services</FormalName>

XML doesn't like the '&'

It can be corrected using CDATA to tell the parser to treat the content as text:
<FormalName Scheme="ICBSupersector"><![CDATA[Industrial Goods & Services]]></FormalName>

But I am not sure if you have any control over that.

HI Eric,
Thanks for your response. Sorry I posted the code from browser so the character "&amp;" was displayed "&"  that you thought error but the the illegal character was like this character "é"
<FormalName Scheme="City">Montréal, Québec</FormalName>

The xml file is encoded in utf-8 and the character é takes 2 byte while ASCII character takes 1 byte so it was error occured while parsing.
when I use encoding "iso-8859-1" or "Windows-1252" then sql insert is fine.

Anyway Thanks a lot for your support.
I am going to post more questions, hope to discuss with you again.

Thanks

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE NewsML SYSTEM "http://www.iptc.org/std/NewsML/1.2/specification/NewsML_1.2.dtd">
<NewsML Version="1.2">
  <Catalog Href="http://about.zzz.com/newsml/vocabulary/catalog-zzz-3rdParty-master_catalog.xml" />
  <NewsEnvelope>
    <TransmissionId>1464248_</TransmissionId>
    <DateAndTime>20101119T160335+0100</DateAndTime>
    <NewsProduct FormalName="Regulatory Information Service"/>
  </NewsEnvelope>
  <NewsItem>
    <Identification>
      <NewsIdentifier>
        <ProviderId>xyzzzz.com</ProviderId>
        <DateId>20101119</DateId>
        <NewsItemId>abc1464248</NewsItemId>
        <RevisionId PreviousRevision="0" Update="N">1</RevisionId>
        <PublicIdentifier>urn:newsml:xyzzzz.com:20101119:abc1464248:1</PublicIdentifier>
      </NewsIdentifier>
    </Identification>
    <NewsManagement>
      <NewsItemType FormalName="News"/>
      <FirstCreated>20101119T160335+0100</FirstCreated>
      <ThisRevisionCreated>20101119T160335+0100</ThisRevisionCreated>
      <Status FormalName="Usable"/>
      <Urgency FormalName="4"/>
      <Property FormalName="zzz.3rdPartyStyleGuideVersion" Value="2.0" />
    <Property FormalName="category" Value="N" />
    <Property FormalName="ern" Value="N.A." />
    <Property FormalName="distributor" Value="xyz zzz ONE" />
    </NewsManagement>
    <NewsComponent xml:lang="en" Essential="no" EquivalentsList="no" Duid="NC00001">
      <TopicSet FormalName="Companies">
        <Topic Duid="T000001">
          <TopicType FormalName="Company"/>
          <FormalName Scheme="CompanyLongName"><![CDATA[xxx Transportation]]></FormalName>
          <FormalName Scheme="CompanyShortName"><![CDATA[]]></FormalName>
      <FormalName Scheme="Country"><![CDATA[U.S.A.]]></FormalName>
      <FormalName Scheme="City"><![CDATA[Montréal, Québec]]></FormalName>
          <FormalName Scheme="TIDM"></FormalName>
      <FormalName Scheme="USTIC"></FormalName>
          <FormalName Scheme="ISIN"></FormalName>
      <FormalName Scheme="ISIC"></FormalName>
          <FormalName Scheme="cRIC"></FormalName>
      <FormalName Scheme="CompanyUrl">http://www.xxx.com</FormalName>
      <FormalName Scheme="GermanWkn"></FormalName>
      <FormalName Scheme="Sedol"></FormalName>
        </Topic>
      </TopicSet>
      <TopicSet FormalName="FSACategories">
        <Topic Duid="T00003">
          <TopicType FormalName="FSACategory"/>
          <FormalName Scheme="FSACategory"></FormalName>
        </Topic>
      </TopicSet>
      <TopicSet FormalName="MediumImportance">
        <Topic Duid="T00004">
          <TopicType FormalName="Geography" Scheme="RTT" />
          <FormalName Scheme="N2000">us</FormalName>
        </Topic>
      </TopicSet>
    
    <TopicSet FormalName="ICBClasification">
        <Topic Duid="ICB_IN">
          <TopicType FormalName="ICBIndustry"/>
          <FormalName Scheme="ICBIndustry">Industrials</FormalName>
        </Topic>
    <Topic Duid="ICB_SU">
          <TopicType FormalName="ICBSupersector"/>
          <FormalName Scheme="ICBSupersector">Industrial Goods &amp; Services</FormalName>
        </Topic>
    <Topic Duid="ICB_SE">
          <TopicType FormalName="ICBSector"/>
          <FormalName Scheme="ICBSector">Industrial Engineering</FormalName>
        </Topic>
      </TopicSet>
        
        <TopicSet FormalName="CustomTags">    
    
      </TopicSet>
            
        <TopicSet FormalName="ErnTagging">    
        </TopicSet>        
      
      <Role FormalName="Main"/>
      <NewsLines>
        <HeadLine><![CDATA[xxx TALENT 2 Trains Soon Operating in the Leipzig Area ]]></HeadLine>
        <DateLine>London, November, 19, 2010</DateLine>
      </NewsLines>

      <AdministrativeMetadata>
        <Creator>     
          <Party FormalName="xxx Transportation"/>  
        </Creator>
        <Source>
          <Party FormalName="xxx Transportation"/>
        </Source>
      </AdministrativeMetadata>

      <RightsMetadata/>

      <DescriptiveMetadata>
        <Language FormalName="en"/>
        <TopicOccurrence Topic="#T000001"/>

        <TopicOccurrence Topic="#T00003" HowPresent="FSACategories"/>
        <TopicOccurrence Topic="#T00004" HowPresent="MediumImportance"/>
    
              <SubjectCode Duid="SC#1" HowPresent="Related" >
        <Subject Duid="S#1" HowPresent="Related" FormalName="Economy, Business And Finance" />
              <SubjectMatter Duid="S#1_SM#1" HowPresent="Related" FormalName="Company Information" />
                    </SubjectCode>
        
    <TopicOccurrence Topic="#ICB_IN" HowPresent="ICBClasification"/>
    <TopicOccurrence Topic="#ICB_SU" HowPresent="ICBClasification"/>
    <TopicOccurrence Topic="#ICB_SE" HowPresent="ICBClasification"/>
      </DescriptiveMetadata>
    
      <ContentItem Duid="CI00001">
        <MediaType FormalName="text"/>
        <Format FormalName="XHTML"/>
<DataContent>
<html xmlns="http://www.w3.org/1999/xhtml" xmlns:mce="mce"><head><style>* { font-family: Arial, Verdana, Helvetica; font-size: 13px;}
td { padding: 3px; }
}</style><title>xxx TALENT 2 Trains Soon Operating in the Leipzig Area </title></head><body class="test">   <p class="test" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm"><strong class="test">Deutsche Bahn orders a further 51 trains of the flexible vehicle system for service on the &quot;Mitteldeutsche S-Bahn&quot; railway network (Central German suburban network) </strong></p> <p class="test" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm"> </p> <p class="test" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm">BERLIN, GERMANY--(Marketwire - November 19, 2010) - xxx Transportation will supply another 51 xxx TALENT 2 trains to Deutsche Bahn AG (DB AG). The order value amounts to approximately 200 million euro ($271 million US). </p> <p class="test" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm"> </p> <p class="test" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm">36 new three-car and 15 four-car TALENT 2 suburban trains will enter service beginning December 2013 in and around Leipzig on the &quot;Mitteldeutsche S-Bahn&quot; network. </p> <p class="test" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm"> </p> <p class="test" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm">The trains are comfortably-equipped with air-conditioning, sliding steps at each entry and spacious multi-purpose areas with wheelchair, pram and bicycle access. Seating is generously spaced, not just for first class passengers, and therefore meets regional as well as suburban travel needs. For families with children there are specially-designed seating areas. The trains are also equipped with toilets for disabled passengers. The passenger compartments have integrated monitors that display current travel information. Passenger safety is enhanced through video monitoring and implementation of the latest crash requirements. </p> <p class="test" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm"> </p> <p class="test" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm">Paintwork specially designed for the &quot;Mitteldeutsche S-Bahn&quot; network gives these TALENT 2 trains a completely different appearance to others. </p> <p class="test" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm"> </p> <p class="test" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm">The new order brings the total number of TALENT 2 trains ordered by Deutsche Bahn to 287. At the end of August, DB AG ordered twenty-three five-car trains for service on the Warnow network in Mecklenburg-Vorpommern, and a total of thirty-seven (34 four-car and three two-car) trains for the Werdenfels railway in Bavaria. </p> <p class="test" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm"> </p> <p class="test" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm">A major feature of the recently developed TALENT 2 Electrical Multiple Unit platform (the 442 series) is its modular concept which includes a high degree of standardisation. The trains can be configured with almost unlimited flexibility and are both cost effective and cost transparent at the same time. The innovative, modular construction principle allows for countless variants on the same train type. The two- to six-car trains can be equipped with a wide variety of technical modules, depending on whether they are to be used as commuter or regional express trains. </p> <p class="test" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm"> </p> <p class="test" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm">A scalable traction output provided by the xxx MITRAC propulsion and control system makes these trains particularly energy efficient and easily adaptable to the frequent acceleration and braking requirements in the commuter sector, as well as to the demands of the regional rail network. </p> <p class="test" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm"> </p> <p class="test" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm"><strong class="test">About xxx Transportation </strong></p> <p class="test" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm"> </p> <p class="test" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm">xxx Transportation, a global leader in rail technology, offers the broadest portfolio in the rail industry and delivers innovative products and services that set new standards in sustainable mobility. xxx ECO4 technologies - built on the four cornerstones of energy, efficiency, economy and ecology - conserve energy, protect the environment and help to improve total train performance. xxx Transportation is headquartered in Berlin, Germany and has a presence in over 60 countries. It has an installed base of over 100,000 vehicles worldwide. </p> <p class="test" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm"> </p> <p class="test" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm"><strong class="test">About xxx </strong></p> <p class="test" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm"> </p> <p class="test" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm">A world-leading manufacturer of innovative transportation solutions, from commercial aircraft and business jets to rail transportation equipment, systems and services, xxx Inc. is a global corporation headquartered in Canada. Its revenues for the fiscal year ended Jan. 31, 2010, were $19.4 billion US, and its shares are traded on the Toronto Stock Exchange (BBD). xxx is listed as an index component to the Dow Jones Sustainability World and North America indexes. News and information are available at <a class="test" href="http://www.xxx.com/" target="_blank">www.xxx.com</a> </p> <p class="test" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm"> </p> <p class="test" style="margin-top: 0cm; margin-right: 0cm;  margin-left: 0cm; margin-bottom:0pt;">xxx, TALENT, MITRAC and ECO4 are trademarks of xxx Inc. or its subsidiaries. <br class="test" /><a class="test" href="http://www.theclimateisrightfortrains.com/" target="_blank">www.theclimateisrightfortrains.com</a> </p>  <p class="test" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm"> </p> <p class="test" style="margin-top: 0cm; margin-right: 0cm; margin-bottom: 0pt; margin-left: 0cm"><strong class="test">Contacts:</strong><br class="test" />North America: +1 450 441 3007<br class="test" />Canada: Marc-Andre Lefebvre<br class="test" /><a class="test" href="mailto:marc-andre.lefebvre@ca.transport.xxx.com" target="_blank">marc-andre.lefebvre@ca.transport.xxx.com</a><br class="test" />USA: Maryanne Roberts<br class="test" /><a class="test" href="mailto:maryanne.roberts@us.transport.xxx.com" target="_blank">maryanne.roberts@us.transport.xxx.com</a><br class="test" /><br class="test" />Germany, Austria, Switzerland, Central<br class="test" />and Eastern Europe: +49 30 98607 1134<br class="test" />Heiner Spannuth, <a class="test" href="mailto:heiner.spannuth@de.transport.xxx.com" target="_blank">heiner.spannuth@de.transport.xxx.com</a><br class="test" /><br class="test" />Nordic Countries: +46 10 852 5062<br class="test" />Jonny Hedberg, <a class="test" href="mailto:jonny.hedberg@se.transport.xxx.com" target="_blank">jonny.hedberg@se.transport.xxx.com</a><br class="test" /><br class="test" />Russia: +7 495 775 1830<br class="test" />Alexander Bocharov<br class="test" /><a class="test" href="mailto:alexander.bocharov@ru.transport.xxx.com" target="_blank">alexander.bocharov@ru.transport.xxx.com</a><br class="test" /><br class="test" />UK, Ireland, Australia, New Zealand, other countries:<br class="test" />+44 1332 266470<br class="test" />Neil Harvey, <a class="test" href="mailto:neil.harvey@uk.transport.xxx.com" target="_blank">neil.harvey@uk.transport.xxx.com</a><br class="test" /><br class="test" />Benelux: +49 30 98607 1141<br class="test" />Janet Olthof, <a class="test" href="mailto:janet.olthof@de.transport.xxx.com" target="_blank">janet.olthof@de.transport.xxx.com</a><br class="test" /><br class="test" />France: +33 6 07 78 95 38<br class="test" />Anne Froger, <a class="test" href="mailto:anne.froger@fr.transport.xxx.com" target="_blank">anne.froger@fr.transport.xxx.com</a><br class="test" /><br class="test" />South Europe, Brazil and India: +35 1 919 693 728<br class="test" />Luis Ramos, <a class="test" href="mailto:luis.ramos@pt.transport.xxx.com" target="_blank">luis.ramos@pt.transport.xxx.com</a></p><div id="div_with_clipping_code_id" name="div_with_clipping_code_name"><p>abc#1464248</p><br /></div><div id="div_with_attachments_id" name="div_with_attachments_name"></div><div id="div_with_disclaimer_id" name="div_with_disclaimer_name"><p>This announcement is distributed by xyz zzz on behalf of xyz zzz clients.<br /><br />The owner of this announcement warrants that:<br />(i) the releases contained herein are protected by copyright and other applicable laws; and<br />(ii) they are solely responsible for the content, accuracy and originality of the<br />information contained therein.<br /><br />Source: xxx Transportation via xyz zzz ONE</p><br /></div></body></html>
</DataContent>
      </ContentItem>
    </NewsComponent>
  </NewsItem>
</NewsML>

Select allOpen in new window

Excellent