Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Problems with OPENXML

Thread Tools

Problems with OPENXML

xavirm xavirm is offline
Junior Member
Join Date: May 2009
Posts: 2
Iíve been working on a procedure in order to insert data from a Web Service to a SQL Server using a Bulk Insert.

Something like this:

Line1 DataSet dsAd_Mstr = objWebServices.Obtener_Ad_Mstr();
Line2 objConnectionC.Open();
Line3 Global.BulkTableInsert(dsAd_Mstr, objConnectionC, "Ad_Mstr");

In the first line Iím extracting the information from a Web Service. This will return something like this (take a look at the encoding, is it ok ?):

<?xml version="1.0" encoding="utf-8"?>
<DataSet xmlns="">
<xs:schema id="NewDataSet" xmlns="" xmlnss="w3/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-comml-msdata">
<xs:element name="NewDataSet" msdata:IsDataSet="true">
<xs:complexType> <xs:choice maxOccurs="unbounded">
<xs:element name="ad_mstr"> <xs:complexType> <xs:sequence>
<xs:element name="ad_addr" type="xs:string" minOccurs="0" />
<xs:element name="ad_name" type="xs:string" minOccurs="0" />
<xs:element name="ad_line1" type="xs:string" minOccurs="0" />
<xs:element name="ad_domain" type="xs:string" MinOccurs="0" />
<xs:element name="oid_ad_mstr" type="xs:decimal" minOccurs="0" />
</xs:sequence> </xs:complexType> </xs:element>
</xs:choice> </xs:complexType> </xs:element> </xs:schema>
<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-comml-msdata" xmlns:diffgr="urn:schemas-microsoft-comml-diffgram-v1">
<NewDataSet xmlns="">
<ad_mstr diffgr:id="ad_mstr4073" msdata:rowOrder="4072">
<ad_name>COMPANY X</ad_name>
<ad_line1 />
<ad_line2 />
<ad_city />
<ad_state />
<ad_zip />
<ad_mstr diffgr:id="ad_mstr4074" msdata:rowOrder="4073">
<ad_name>COMPANY Y</ad_name>
<ad_line1 />
I think there is no need to explain line number 2 (opening connection).

The line number 3 is a calling to the following function:
Line21 public static void BulkTableInsert(DataSet objDS, SqlConnection objCon, string tablename)
Line22 {
Line23 //Change the column mapping first.
Line24 System.Text.StringBuilder sb = new System.Text.StringBuilder( 1000);
Line25 System.IO.StringWriter sw = new System.IO.StringWriter(sb);
Line26 foreach( DataColumn col in objDS.Tables[tablename].Columns)
Line27 {
Line28 col.ColumnMapping = System.Data.MappingType.Attribute;
Line29 }
Line31 objDS.WriteXml(sw, System.Data.XmlWriteMode.WriteSchema);
Line32 string sqlText = buildBulkUpdateSql(sb.ToString(), objDS.Tables[tablename]);
Line34 sqlText = sqlText.Replace("''true''", "''1''");
Line35 sqlText = sqlText.Replace("''false''", "''0''");
Line36 sqlText = sqlText.Replace("Š", "a");
Line37 sqlText = sqlText.Replace("ť", "e");
Line38 sqlText = sqlText.Replace("Ū", "i");
Line39 sqlText = sqlText.Replace("ů", "o");
Line40 sqlText = sqlText.Replace("ķ", "u");
Line41 sqlText = sqlText.Replace("—", "N");
Line42 sqlText = sqlText.Replace("Ů", "n");
Line43 execSql(objCon, sqlText);
Line44 }
(I implemented by myself lines 34 to 42, are they ok ? If I configure in a different way the web service could I avoid this ?)

The line 32 is calling the following function:
Line45 static string buildBulkUpdateSql( string dataXml, DataTable table)
Line46 {
Line47 System.Text.StringBuilder sb = new System.Text.StringBuilder();
Line48 dataXml = dataXml.Replace(Environment.NewLine, "");
Line49 dataXml = dataXml.Replace("\"", "''");
Line50 //init the xml doc
Line51 sb.Append(" SET NOCOUNT ON");
Line52 sb.Append(" DECLARE @hDoc INT");
Line53 //sb.AppendFormat(" EXEC sp_xml_preparedocument @hDoc OUTPUT, '<?xml version=''1.0'' encoding=''iso-8859-1''?> {0}'", dataXml);
Line54 sb.AppendFormat(" EXEC sp_xml_preparedocument @hDoc OUTPUT, '{0}'", dataXml);
Line55 //This code deletes old data based on PK.
Line56 sb.AppendFormat(" DELETE {0} FROM {0} INNER JOIN ", table.TableName);
Line57 sb.AppendFormat(" (SELECT * FROM OPENXML (@hdoc, '/NewDataSet/{0}', 1)",
Line58 table.TableName);
Line59 sb.AppendFormat(" WITH {0}) xmltable ON 1 = 1", table.TableName);
Line60 foreach( DataColumn col in table.PrimaryKey)
Line61 {
Line62 sb.AppendFormat(" AND {0}.{1} = xmltable.{1}", table.TableName,
Line63 col.ColumnName);
Line64 }
Line65 //This code inserts new data.
Line66 sb.AppendFormat(" INSERT INTO {0} SELECT *", table.TableName);
Line67 sb.AppendFormat(" FROM OPENXML (@hdoc, '/NewDataSet/{0}', 1) WITH {0}",
Line68 table.TableName);
Line69 //clear the xml doc
Line70 sb.Append(" EXEC sp_xml_removedocument @hDoc");
Line71 return sb.ToString();
Line72 }

I think that you can imagine what does the function called in line 43 (Executes the dynamic query).

Iím taking these 3 last function from the article "A generic bulk insert using DataSets and OpenXML" from CodeProject.

So far the whole procedure looks great. But when I execute the calling to the line 3, an exception throws stating just an SQL Exception.

The code generated (sqlText) by the BulkTableInsert looks like this (nevermind about the linebreaks, they're there because of the cut-copy operation, they are not for real):

SET NOCOUNT ON DECLARE @hDoc INT EXEC sp_xml_preparedocument @hDoc OUTPUT,
'<NewDataSet> <xs:schema id=''NewDataSet'' xmlns='''' xmlnss=''w3/2001/XMLSchema'' xmlns:msdata=''urn:schemas-microsoft-comml-msdata''> <xs:element name=''NewDataSet'' msdata:IsDataSet=''1''> <xs:complexType> <xs:choice maxOccurs=''unbounded''> <xs:element name=''ad_mstr''> <xs:complexType> <xs:attribute name=''ad_addr'' type=''xs:string'' /> <xs:attribute name=''ad_name'' type=''xs:string'' /> <xs:attribute name=''ad_line1'' type=''xs:string'' />
<xs:attribute name=''oid_ad_mstr'' type=''xs:decimal'' /> </xs:complexType> </xs:element> </xs:choice> </xs:complexType> </xs:element> </xs:schema>
<ad_mstr ad_addr=''SOCIOS3'' ad_name=''COMPANY X'' ad_line1='''' ad_line2='''' ad_city='''' ad_state='''' ad_z
ip='''' ad_type=''customer'' ad_attn='''' ad_phone='''' ad_ext='''' ad_ref='''' ad_sort=''COMPANY X'' ad_country=''MEXICO'' ad_attn2='''' ad_phone2='''' ad_ext2='''' ad_fax='''' ad_fax2='''' ad_line3='''' ad_user1='''' ad_user2='''' ad_lang='''' ad_pst_id='''' ad_date=''02/03/1993'' ad_county='''' ad_temp=''0'' ad_bk_acct1='''' ad_bk_acct2='''' ad_format=''0'' ad_vat_reg='''' ad_coc_reg='''' ad_gst_id='''' ad_tax_type='''' ad_taxc=''1'' ad_taxable=''1'' ad_tax_in=''0'' ad_conrep='''' ad_edi_tpid='''' ad_edi_ctrl='';;;;'' ad_timezone='''' ad_userid='''' ad_edi_id='''' ad_barlbl_prt='''' ad_barlbl_val='''' ad_calendar='''' ad_edi_std='''' ad_edi_level='''' ad__qad01='''' ad__qad02='''' ad__qad03='''' ad__qad04='''' ad__qad05='''' ad__chr01='''' ad__chr02='''' ad__chr03='''' ad__chr04='''' ad__chr05='''' ad_tp_loc_code='''' ad_ctry=''MEX'' ad_tax_zone=''MEXICO'' ad_tax_usage=''BUSES'' ad_misc1_id='''' ad_misc2_id='''' ad_misc3_id='''' ad_wk_offset=''0'' ad_inv_mthd='''' ad_sch_mthd='''' ad_po_mt
hd='''' ad_asn_data='''' ad_intr_division='''' ad_tax_report=''0'' ad_name_control='''' ad_last_file=''0'' ad_domain=''VIM'' oid_ad_mstr=''0'' />
DELETE ad_mstr FROM ad_mstr INNER JOIN (SELECT * FROM OPENXML (@hdoc, '/NewDataSet/ad_mstr', 1) WITH ad_mstr) xmltable ON 1 = 1 INSERT INTO ad_mstr SELECT * FROM OPENXML (@hdoc, '/NewDataSet/ad_mstr', 1) WITH ad_mstr EXEC sp_xml_removedocument @hDoc

If I take the sqlText (line 43) value (the whole XML code whose fragment I pasted above) and test it in an SQL Server Query Analyzer, first I have change all the apostrophes inside the text values (change ARTHURíS for ARTHUR&apos;S, is this correct ?), eliminated the linebreaks, then I have an error stating:

Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Line 1 XML parsing error: An invalid character was found in text content.

Now I have some "special" (and hidden) characters. I can remove the whole text in the value field and everything works fine, so that's what I'm guessing there are some hidden chars.

How can I do to remove them ?
Should I prepare the info prior to send it to the web service ?
Should I configure the web service in order to fix this ?
Should I chance the encoding ot he collation ?

The server (and the table) where Iím extracting the info is the same that the server (and the table) where Iím trying to insert the info.
I have changed the links because of this site requirements.
If you need extra info, please, let me know.
Thanks a lot.
Reply With Quote

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Petition against Office OpenXML (OOXML) joe_90 NZ Computing 0 10-27-2007 06:52 AM
Converting HTML to XHTML (JTidy,OpenXML,Xerces) Java 9 03-24-2006 01:35 PM
Problems compiling simple C++ code (also problems with std::string) Susan Baker C++ 2 06-26-2005 01:43 AM
Syntax for OpenXML in ASP.NET =?Utf-8?B?TWlja2U=?= ASP .Net 0 09-29-2004 01:37 PM
Re: sound problems and modem problems Harold Potter Computer Support 5 12-04-2003 04:12 PM