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="tempuri.org/">
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="w3/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-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-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<NewDataSet xmlns="">
<ad_mstr diffgr:id="ad_mstr4073" msdata:rowOrder="4072">
<ad_addr>SOCIOS3</ad_addr>
<ad_name>COMPANY X</ad_name>
<ad_line1 />
<ad_line2 />
<ad_city />
<ad_state />
<ad_zip />
...
<ad_domain>VIM</ad_domain>
<oid_ad_mstr>0</oid_ad_mstr>
</ad_mstr>
...
<ad_mstr diffgr:id="ad_mstr4074" msdata:rowOrder="4073">
<ad_addr>SOCIOS4</ad_addr>
<ad_name>COMPANY Y</ad_name>
<ad_line1 />
...
<oid_ad_mstr>0</oid_ad_mstr>
</ad_mstr>
</NewDataSet>
</diffgr:diffgram>
</DataSet>
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 }
Line30
Line31 objDS.WriteXml(sw, System.Data.XmlWriteMode.WriteSchema);
Line32 string sqlText = buildBulkUpdateSql(sb.ToString(), objDS.Tables[tablename]);
Line33
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='''' xmlns:xs=''w3/2001/XMLSchema'' xmlns:msdata=''urn:schemas-microsoft-com:xml-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'' />
...
</NewDataSet>'
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'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.
BR,
