|
Absoloute pap.
2 major issues, this will build the xml in utf-16 so if you have any funny characters in your data you need to set the encoding of the xmlWriter to UTF-8 or ASCII or whatever suits. Then you've got the issue of properly escaping some characters for both XML and SQL.
The result? An absoloute mess, and that's not even accounting for the fact that if your document is not trustworthy you're essentially chucking an untrusted string at sql server without parameters.
SqlBulkCopy does the job just fine. I hate misleading "expert" examples like this, for the sake of humanity stop it.
|
|
|
|
|
FROM MY POST:
I've tested the heck out of this using small and medium size tables and DataSets. There is tons of room for improvement and feature enhancement and I know this won't work in many professional environments, but it's a good start for me that I wish I had two days ago. Like I mentioned, I'm looking forward to hearing from the community about speeding up this snippet a bit. -end
You do know how to read, right? well, do you know how to read timestamps? Do you know what day today is?
this post is over FOUR YEARS OLD, retard. SqlBulkCopy was buggy and impossible to use at the time. In fact, I clearly mention that in later posts.
misleading 'expert' examples. clearly you're too stupid to understand timestamps. Or maybe too stupid to understand the exact answer to your specific question isn't actually googleable and you might just have to do some work yourself. maybe a little research. Don't worry, eventually someone will post their work and you can just copy it.
ug. I don't even know why I'm posting to this since it's been dead for four years, but man, you're dumb.
|
|
|
|
|
Burn! lol
Good response @poodull76!
|
|
|
|
|
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. The web method looks like this:
Line4 [WebMethod]
Line5 public DataSet Obtener_Ad_Mstr()
Line6 {
Line7 COffline oOffline = new COffline();
Line8 try
Line9 {
Line10 return oOffline.Obtener_Ad_Mstr();
Line11 }
Line12 catch (Exception ex)
Line13 {
Line14 throw(ex);
Line15 }
Line16 finally
Line17 {
Line18 oOffline = null;
Line19 }
Line20 }
This will return something like this:
<?xml version="1.0" encoding="utf-8"?>
<DataSet xmlns="http://tempuri.org/">
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/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_line2" type="xs:string" minOccurs="0" />
<xs:element name="ad_city" type="xs:string" minOccurs="0" />
<xs:element name="ad_state" type="xs:string" minOccurs="0" />
<xs:element name="ad_zip" type="xs:string" minOccurs="0" />
<xs:element name="ad_type" type="xs:string" minOccurs="0" />
<xs:element name="ad_attn" type="xs:string" minOccurs="0" />
<xs:element name="ad_phone" type="xs:string" minOccurs="0" />
<xs:element name="ad_ext" type="xs:string" minOccurs="0" />
<xs:element name="ad_ref" type="xs:string" minOccurs="0" />
<xs:element name="ad_sort" type="xs:string" minOccurs="0" />
<xs:element name="ad_country" type="xs:string" minOccurs="0" />
<xs:element name="ad_attn2" type="xs:string" minOccurs="0" />
<xs:element name="ad_phone2" type="xs:string" minOccurs="0" />
<xs:element name="ad_ext2" type="xs:string" minOccurs="0" />
<xs:element name="ad_fax" type="xs:string" minOccurs="0" />
<xs:element name="ad_fax2" type="xs:string" minOccurs="0" />
<xs:element name="ad_line3" type="xs:string" minOccurs="0" />
<xs:element name="ad_user1" type="xs:string" minOccurs="0" />
<xs:element name="ad_user2" type="xs:string" minOccurs="0" />
<xs:element name="ad_lang" type="xs:string" minOccurs="0" />
<xs:element name="ad_pst_id" type="xs:string" minOccurs="0" />
<xs:element name="ad_date" type="xs:string" minOccurs="0" />
<xs:element name="ad_county" type="xs:string" minOccurs="0" />
<xs:element name="ad_temp" type="xs:boolean" minOccurs="0" />
<xs:element name="ad_bk_acct1" type="xs:string" minOccurs="0" />
<xs:element name="ad_bk_acct2" type="xs:string" minOccurs="0" />
<xs:element name="ad_format" type="xs:int" minOccurs="0" />
<xs:element name="ad_vat_reg" type="xs:string" minOccurs="0" />
<xs:element name="ad_coc_reg" type="xs:string" minOccurs="0" />
<xs:element name="ad_gst_id" type="xs:string" minOccurs="0" />
<xs:element name="ad_tax_type" type="xs:string" minOccurs="0" />
<xs:element name="ad_taxc" type="xs:string" minOccurs="0" />
<xs:element name="ad_taxable" type="xs:boolean" minOccurs="0" />
<xs:element name="ad_tax_in" type="xs:boolean" minOccurs="0" />
<xs:element name="ad_conrep" type="xs:string" minOccurs="0" />
<xs:element name="ad_edi_tpid" type="xs:string" minOccurs="0" />
<xs:element name="ad_edi_ctrl" type="xs:string" minOccurs="0" />
<xs:element name="ad_timezone" type="xs:string" minOccurs="0" />
<xs:element name="ad_userid" type="xs:string" minOccurs="0" />
<xs:element name="ad_mod_date" type="xs:string" minOccurs="0" />
<xs:element name="ad_edi_id" type="xs:string" minOccurs="0" />
<xs:element name="ad_barlbl_prt" type="xs:string" minOccurs="0" />
<xs:element name="ad_barlbl_val" type="xs:string" minOccurs="0" />
<xs:element name="ad_calendar" type="xs:string" minOccurs="0" />
<xs:element name="ad_edi_std" type="xs:string" minOccurs="0" />
<xs:element name="ad_edi_level" type="xs:string" minOccurs="0" />
<xs:element name="ad__qad01" type="xs:string" minOccurs="0" />
<xs:element name="ad__qad02" type="xs:string" minOccurs="0" />
<xs:element name="ad__qad03" type="xs:string" minOccurs="0" />
<xs:element name="ad__qad04" type="xs:string" minOccurs="0" />
<xs:element name="ad__qad05" type="xs:string" minOccurs="0" />
<xs:element name="ad__chr01" type="xs:string" minOccurs="0" />
<xs:element name="ad__chr02" type="xs:string" minOccurs="0" />
<xs:element name="ad__chr03" type="xs:string" minOccurs="0" />
<xs:element name="ad__chr04" type="xs:string" minOccurs="0" />
<xs:element name="ad__chr05" type="xs:string" minOccurs="0" />
<xs:element name="ad_tp_loc_code" type="xs:string" minOccurs="0" />
<xs:element name="ad_ctry" type="xs:string" minOccurs="0" />
<xs:element name="ad_tax_zone" type="xs:string" minOccurs="0" />
<xs:element name="ad_tax_usage" type="xs:string" minOccurs="0" />
<xs:element name="ad_misc1_id" type="xs:string" minOccurs="0" />
<xs:element name="ad_misc2_id" type="xs:string" minOccurs="0" />
<xs:element name="ad_misc3_id" type="xs:string" minOccurs="0" />
<xs:element name="ad_wk_offset" type="xs:int" minOccurs="0" />
<xs:element name="ad_inv_mthd" type="xs:string" minOccurs="0" />
<xs:element name="ad_sch_mthd" type="xs:string" minOccurs="0" />
<xs:element name="ad_po_mthd" type="xs:string" minOccurs="0" />
<xs:element name="ad_asn_data" type="xs:string" minOccurs="0" />
<xs:element name="ad_intr_division" type="xs:string" minOccurs="0" />
<xs:element name="ad_tax_report" type="xs:boolean" minOccurs="0" />
<xs:element name="ad_name_control" type="xs:string" minOccurs="0" />
<xs:element name="ad_last_file" type="xs:boolean" 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_type>customer</ad_type>
<ad_attn />
<ad_phone />
<ad_ext />
<ad_ref />
<ad_sort>COMPANY X</ad_sort>
<ad_country>MEXICO</ad_country>
<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_date>
<ad_county />
<ad_temp>false</ad_temp>
<ad_bk_acct1 />
<ad_bk_acct2 />
<ad_format>0</ad_format>
<ad_vat_reg />
<ad_coc_reg />
<ad_gst_id />
<ad_tax_type />
<ad_taxc>1</ad_taxc>
<ad_taxable>true</ad_taxable>
<ad_tax_in>false</ad_tax_in>
<ad_conrep />
<ad_edi_tpid />
<ad_edi_ctrl>;;;;</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_ctry>
<ad_tax_zone>MEXICO</ad_tax_zone>
<ad_tax_usage>BUSES</ad_tax_usage>
<ad_misc1_id />
<ad_misc2_id />
<ad_misc3_id />
<ad_wk_offset>0</ad_wk_offset>
<ad_inv_mthd />
<ad_sch_mthd />
<ad_po_mthd />
<ad_asn_data />
<ad_intr_division />
<ad_tax_report>false</ad_tax_report>
<ad_name_control />
<ad_last_file>false</ad_last_file>
<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 />
<ad_line2 />
<ad_city />
<ad_state />
<ad_zip />
<ad_type>customer</ad_type>
<ad_attn />
<ad_phone />
<ad_ext />
<ad_ref />
<ad_sort>COMPANY Y</ad_sort>
<ad_country>MEXICO</ad_country>
<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_date>
<ad_county />
<ad_temp>false</ad_temp>
<ad_bk_acct1 />
<ad_bk_acct2 />
<ad_format>0</ad_format>
<ad_vat_reg />
<ad_coc_reg />
<ad_gst_id />
<ad_tax_type />
<ad_taxc>1</ad_taxc>
<ad_taxable>true</ad_taxable>
<ad_tax_in>false</ad_tax_in>
<ad_conrep />
<ad_edi_tpid />
<ad_edi_ctrl>;;;;</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_ctry>
<ad_tax_zone>MEXICO</ad_tax_zone>
<ad_tax_usage>BUSES</ad_tax_usage>
<ad_misc1_id />
<ad_misc2_id />
<ad_misc3_id />
<ad_wk_offset>0</ad_wk_offset>
<ad_inv_mthd />
<ad_sch_mthd />
<ad_po_mthd />
<ad_asn_data />
<ad_intr_division />
<ad_tax_report>false</ad_tax_report>
<ad_name_control />
<ad_last_file>false</ad_last_file>
<ad_domain>VIM</ad_domain>
<oid_ad_mstr>0</oid_ad_mstr>
</ad_mstr>
</NewDataSet>
</diffgr:diffgram>
</DataSet>
I think there is no need to explain line number 2.
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 }
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 but I’m adding it below:
Line73 static void execSql(SqlConnection objCon, string sqlText)
Line74 {
Line75 SqlCommand objCom = new SqlCommand();
Line76 objCom.Connection = objCon;
Line77 objCom.CommandType = CommandType.Text;
Line78 objCom.CommandText = sqlText;
Line79 objCom.ExecuteNonQuery();
Line80 }
I’m taking these 3 last function from the article A generic bulk insert using DataSets and OpenXML from CodeProject (http://www.codeproject.com/KB/database/generic_OpenXml.aspx?fid=171911&df=90&mpp=25&noise=3&sort=Position&view=Quick).
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:
SET NOCOUNT ON DECLARE @hDoc INT EXEC sp_xml_preparedocument @hDoc OUTPUT,
'<NewDataSet> <xs:schema id=''NewDataSet'' xmlns='''' xmlns:xs=''http://www.w3.org/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=''ad_line2'' type=''xs:string'' /> <xs:attribute name=''ad_city'' type=''xs:string'' /> <xs:attribute name=''ad_state'' type=''xs:string'' /> <xs:attribute name=''ad_zip'' type=''xs:string'' /> <xs:attribute name=''ad_type'' type=''xs:string'' /> <xs:attribute name=''ad_attn'' type=''xs:string'' /> <xs:attribute name=''ad_phone'' type=''xs:string'' /> <xs:attribute name=''ad_ext'' type=''xs:string'' /> <xs:attribute name=''ad_ref'' type=''xs:string'' /> <xs:attribute name=''ad_sort'' type=''xs:string'' /> <xs:attribute name=''ad_country'' type=''xs:string'' /> <xs:attribute name=''ad_attn2'' type=''xs:string'' /> <xs:attribute name=''ad_phone2'' type=''xs:string'' /> <xs:attribute name=''ad_ext2'' type=''xs:string'' /> <xs:attribute name=''ad_fax'' type=''xs:string'' /> <xs:attribute name=''ad_fax2'' type=''xs:string'' /> <xs:attribute name=''ad_line3'' type=''xs:string'' /> <xs:attribute name=''ad_user1'' type=''xs:string'' /> <xs:attribute name=''ad_user2'' type=''xs:string'' /> <xs:attribute name=''ad_lang'' type=''xs:string'' /> <xs:attribute name=''ad_pst_id'' type=''xs:string'' /> <xs:attribute name=''ad_date'' type=''xs:string'' /> <xs:attribute name=''ad_county'' type=''xs:string'' /> <xs:attribute name=''ad_temp'' type=''xs:boolean'' /> <xs:attribute name=''ad_bk_acct1'' type=''xs:string'' /> <xs:attribute name=''ad_bk_acct2'' type=''xs:string'' /> <xs:attribute name=''ad_format'' type=''xs:int'' /> <xs:attribute name=''ad_vat_reg'' type=''xs:string'' /> <xs:attribute name=''ad_coc_reg'' type=''xs:string'' /> <xs:attribute name=''ad_gst_id'' type=''xs:string'' /> <xs:attribute name=''ad_tax_type'' type=''xs:string'' /> <xs:attribute name=''ad_taxc'' type=''xs:string'' /> <xs:attribute name=''ad_taxable'' type=''xs:boolean'' /> <xs:attribute name=''ad_tax_in'' type=''xs:boolean'' /> <xs:attribute name=''ad_conrep'' type=''xs:string'' /> <xs:attribute name=''ad_edi_tpid'' type=''xs:string'' /> <xs:attribute name=''ad_edi_ctrl'' type=''xs:string'' /> <xs:attribute name=''ad_timezone'' type=''xs:string'' /> <xs:attribute name=''ad_userid'' type=''xs:string'' /> <xs:attribute name=''ad_mod_date'' type=''xs:string'' /> <xs:attribute name=''ad_edi_id'' type=''xs:string'' /> <xs:attribute name=''ad_barlbl_prt'' type=''xs:string'' /> <xs:attribute name=''ad_barlbl_val'' type=''xs:string'' /> <xs:attribute name=''ad_calendar'' type=''xs:string'' /> <xs:attribute name=''ad_edi_std'' type=''xs:string'' /> <xs:attribute name=''ad_edi_level'' type=''xs:string'' /> <xs:attribute name=''ad__qad01'' type=''xs:string'' /> <xs:attribute name=''ad__qad02'' type=''xs:string'' /> <xs:attribute name=''ad__qad03'' type=''xs:string'' /> <xs:attribute name=''ad__qad04'' type=''xs:string'' /> <xs:attribute name=''ad__qad05'' type=''xs:string'' /> <xs:attribute name=''ad__chr01'' type=''xs:string'' /> <xs:attribute name=''ad__chr02'' type=''xs:string'' /> <xs:attribute name=''ad__chr03'' type=''xs:string'' /> <xs:attribute name=''ad__chr04'' type=''xs:string'' /> <xs:attribute name=''ad__chr05'' type=''xs:string'' /> <xs:attribute name=''ad_tp_loc_code'' type=''xs:string'' /> <xs:attribute name=''ad_ctry'' type=''xs:string'' /> <xs:attribute name=''ad_tax_zone'' type=''xs:string'' /> <xs:attribute name=''ad_tax_usage'' type=''xs:string'' /> <xs:attribute name=''ad_misc1_id'' type=''xs:string'' /> <xs:attribute name=''ad_misc2_id'' type=''xs:string'' /> <xs:attribute name=''ad_misc3_id'' type=''xs:string'' /> <xs:attribute name=''ad_wk_offset'' type=''xs:int'' /> <xs:attribute name=''ad_inv_mthd'' type=''xs:string'' /> <xs:attribute name=''ad_sch_mthd'' type=''xs:string'' /> <xs:attribute name=''ad_po_mthd'' type=''xs:string'' /> <xs:attribute name=''ad_asn_data'' type=''xs:string'' /> <xs:attribute name=''ad_intr_division'' type=''xs:string'' /> <xs:attribute name=''ad_tax_report'' type=''xs:boolean'' /> <xs:attribute name=''ad_name_control'' type=''xs:string'' /> <xs:attribute name=''ad_last_file'' type=''xs:boolean'' /> <xs:attribute name=''ad_domain'' 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'' />
.
.
<ad_mstr ad_addr=''SOCIOS4'' ad_name=''COMPANY Y'' ad_line1='''' ad_line2='''' ad_city='''' ad_state='''' ad_zip='''' ad_type=''customer'' ad_attn='''' ad_phone='''' ad_ext='''' ad_ref='''' ad_sort=''COMPANY Y'' 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_mthd='''' 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 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 ?), then I have an error stating:
Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Line 1
XML parsing error: Missing equals sign between attribute and attribute value.
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.
Why is this happening ?
I’ve read many articles about wrong encodings and invalid characters but I still don’t get it, can you help me ?
Can you tell me exactly what I should do ?
If you need extra info, please, let me know.
Thanks a lot.
BR,
Francisco
<div class="ForumMod">modified on Friday, April 24, 2009 9:46 AM</div>
|
|
|
|
|
Well SqlBulkCopy is the real soloution, I'm pretty sure this example has wasted lots of peoples time trying to sort out different characters.
But if you must, you're probably looking to replace "xxx.WriteXml(sw, System.Data.XmlWriteMode.WriteSchema)" for something like:
System.Xml.XmlWriterSettings settings = new System.Xml.XmlWriterSettings();
settings.Encoding = Encoding.ASCII; (or UTF8)
using (System.Xml.XmlWriter writer = System.Xml.XmlWriter.Create(sw, settings))
{
xxx.WriteXml(writer, XmlWriteMode.WriteSchema);
writer.Close();
}
|
|
|
|
|
have anyone tried this on a table with identity column?
I am doing this with in stored procedure, but it fails or I don't know how to retain the original id of the identity column. I can remove the SET Identity_insert on and have new id generated, but it brings the new issue, that I need to find the new id generated to use in other table.
Or the question is, how to get the openxml to return original identity column value?
stored procedure that I use:
-------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <author,,prabu vontlin="">
-- Create date: <create date,,04="" 23="" 2007="">
-- Description: <description,,to get="" all="" the="" data="" from="" database="" as="" xml="">
-- =============================================
ALTER PROCEDURE [dbo].[sp_ImportDBFromXML_TEST]
(@outputXML nvarchar(max))
AS
BEGIN
SET NOCOUNT ON;
DECLARE @returnValue int
DECLARE @userTableName nvarchar(50)
DECLARE @sql nvarchar(max)
DECLARE @COLNAMES NVARCHAR(1000)
SET @returnValue = 1
IF EXISTS( SELECT name FROM sysobjects where xtype = 'U' and name like 't_userTables')
DROP TABLE t_userTables
SELECT name INTO t_userTables FROM sysobjects where xtype = 'U' and name <> 't_userTables'
DECLARE hC CURSOR LOCAL FAST_FORWARD FOR
SELECT name FROM t_userTables
OPEN hC
FETCH hC INTO @userTableName
WHILE (@@fetch_status <> -1)
BEGIN
SET @sql = 'DELETE FROM ' + @userTableName
EXEC (@sql)
SET @COLNAMES = ''
SELECT @COLNAMES = CASE WHEN @COLNAMES = ''
THEN NAME
ELSE
@COLNAMES + ',' + NAME
END
FROM SYSCOLUMNS
WHERE ID = OBJECT_ID(@userTableName)
SET @sql = 'IF (objectproperty(object_id(''' + @userTableName + '''),''TableHasIdentity'') = 1) BEGIN SET IDENTITY_INSERT ' + @userTableName + ' ON END;'
SET @sql = @sql + ' DECLARE @idoc int ; EXEC sp_xml_preparedocument @idoc OUTPUT, ''' + @outputXML + ''' ;' +
-- 'INSERT INTO ' + @userTableName +
' SELECT ' + @COLNAMES +
' FROM OPENXML(@idoc, ''/DIGIOPSERVER/' + @userTableName + ''') ' +
' WITH ' + @userTableName +
';' + 'EXEC sp_xml_removedocument @idoc ; '
SET @sql = @sql + 'IF (objectproperty(object_id(''' + @userTableName + '''),''TableHasIdentity'') = 1) BEGIN SET IDENTITY_INSERT ' + @userTableName + ' OFF END;'
EXEC (@sql)
FETCH hC INTO @userTableName
END
CLOSE hC
DEALLOCATE hC
SET @returnValue = 0
return @returnValue
END
--------------------------------------------------------------------------------
Prabu Vontlin
sriprabu@hotmail.com
|
|
|
|
|
Careful here, a few notes:
1) this article is pre- .net 2.0. The OpenXML code is outdated and the new .net libraries take care of a lot of these issues.
2) I'm a little confused... why would you want to know AN (singular) idenity when generating bulk insert... bulk implies massive amounts of data -- which is the ONLY reason one would have used this mechanism.
3) Cursors == long length of rope. Very easy to hang yourself and can and should be avoided at all costs.
sorry I can't actually answer your question.
|
|
|
|
|
First of all, Thank you and appreciate your response.
I am using SQL EXPRESS 2005, to run this stored procedure. The reason i am doing this, is to export all the data in xml file for backup, and also in case if software got re installed or data lost for any reason, the user can import from xml file. this is the easy way i found to export all data from sql database and import back. Sinec DTS is not available anymore in SQL Express 2005.
Regarding the IDENTITY Column, as you notice, the stored procedure is written to loop through all the user created tables in the database, without hardcoding any table names. I wrote a similar stored procedure which works fine to export data, and the issue comes only when I import data on the those tables which have Identity columns as primary keys, as this openxml does not read the primary keys or identity columns.
|
|
|
|
|
When the column contains a single quote, bulk insert fails. How to correct this problem?
Thanks in advance.
"We don't see things how they are. We see things as we are." -Talmud
|
|
|
|
|
Please note that this is some pretty old code. Since this release, .Net 2.0 addresses bulk insert and so does SQL 2005 much better than this hack.
But to answer you question, you must escape the single quote. The single quote only means something in TSQL, so thats where you must escape it. To escape a single quote in TSQL, you use another single quote.
SELECT 'My column''s value'
|
|
|
|
|
Thank You for posting this sample.
I had the exact circumstances you had described, but fortunately I was able to find your post as I searched the web.
I do not need to delete rows using this method. But, I did discover that loading 1000 or more data rows at a time, vs. 100 rows, did not seem to give me much more of a performance gain, if any. And the 1000 rows actually hung during one execution.
I placed some counters in my code to send 100 row blocks to sql at a time.
This also allows me to display a nice progress bar of the load progress.
Thanks again for your code sample.
|
|
|
|
|
Not only smalldates fail, all dates fail!
It seems that the writexml formats a datetime column like this:
2005-10-01T07:00:00.0000000+02:00
When running this in T-SQL you will receive the error:
Server: Msg 241, Level 16, State 1, Line 4
Syntax error converting datetime from character string.
Is there a way to format the date in any way?
THNX!
|
|
|
|
|
I definitely got DateTime to work, as my comments below state. My fix was to simply change the column from smalldatetime to datetime. perhaps it could be your source. Check your dataset's value type.
Also, this is a pretty old article and I'm on to 2.0 now. I've seen that Microsoft's Patterns & Practices group has released Enterprise Libraries for 2.0, which contains stubs for bulk insert in the Data package. I highly recommend using all of the EntLib when possible. It saves tons of time.
|
|
|
|
|
|
I worked around the issue with the following convert in my select statement
CONVERT(DATETIME,substring(convert(varchar(50), replace([datecollected], 'T', ' ')),1,19),120) [DateCollected],
|
|
|
|
|
This article was very useful for me in using OpenXML. But when I want to update just a single row in a table that has close to 18 lac records, OpenXML can seem to be slow. Even a simple Update statement works fast.
One way to speed it up is to use a temp table to do the job.
1. First create the document handle.
2. Using the handle, select the records into a temp table
3. Use the temp table to join with the actual table and do the desired operation.
For me, it speeded up the process from 1 min to 2 secs. Note that the table record size is 18 lacs.
Try it out and let all of us know ur feedback
Megatop
|
|
|
|
|
That seems strange, I am inserting around 4000 rows over the internet to a shared database, using OpenXML in a couple of minutes.
|
|
|
|
|
thanks for your code, its a good starting point for a problem i have to solve
|
|
|
|
|
I tried the pcode for string variables( I tried storing them as Varchar) this stores only the first letter of the string variables. Is there any work around for this.
Thanks in Advance.
Sidhartha Shenoy
|
|
|
|
|
I'm not sure I understand what you mean by pcode. I know for a fact, however, that I had a problem with strings because of apostrophe's in vb and backslashes in c#.
To debug your insert, place a stop before it executes and take a look at the sql code. Take it into Query Analyzer and try to run it.
Might sound stupid, but make sure your database table's varchar settings are > 1 character (ie. varchar(50)).
fc
|
|
|
|
|
Ya thank you I got it resolved.
sid here
|
|
|
|
|
Hi,
thanks for the great application. I was wondering if you've encountered any problems inserting boolean values into sql server. as it seems to want a 1 or a 0, but objDS.writeXML will fail trying to write 1 & 0. Have you any ideas for a genreic boolean insert?
thanks,
chris
|
|
|
|
|
I have not... There are some limits with data conversion I've already uncovered, its certainly not surprising that there are more. Try using an int or string value in the dataset. Maybe there's an autoconversion that takes place using certain data type/pairs.
Clearly MSFT dropped the ball on this version of OPENXML by making it so stringent. The only way around it is really to manually fudge the XML before you insert it.... perhaps do a string search and replace or flex your xslt skilz
|
|
|
|
|
You wrote:
Have you any ideas for a genreic boolean insert?
I know this is an old post on OpenXML and you have probably already worked around the issue. I wish I would have seen this when you posted, I ran across this issue a little before this article was published. I solved the problem with a custom function that I called in my stored procedure. Here is the function:
<br />
USE [pubs]<br />
GO<br />
<br />
SET ANSI_NULLS OFF<br />
GO<br />
SET QUOTED_IDENTIFIER OFF<br />
GO<br />
CREATE FUNCTION [pubs].[Bool2Bit](@bool_Value nvarchar(100))<br />
RETURNS bit<br />
AS<br />
Begin<br />
DECLARE @ISBOOL bit<br />
SELECT @ISBOOL =<br />
Case @bool_Value<br />
WHEN 'True' then 1 <br />
WHEN 't' then 1<br />
<br />
WHEN 'False' then 0 <br />
WHEN 'f' then 0<br />
<br />
End <br />
<br />
RETURN (@ISBOOL)<br />
End<br />
You can use this by calling the function in your insert/update sql like the following:
<br />
SET ANSI_NULLS OFF<br />
GO<br />
SET QUOTED_IDENTIFIER OFF<br />
GO<br />
<br />
CREATE PROCEDURE [sp_Insert_Openxml]<br />
@strXML ntext<br />
AS<br />
DECLARE @iDoc int<br />
<br />
EXECUTE sp_xml_preparedocument @iDoc OUTPUT, @strXML<br />
<br />
INSERT INTO [TABLENAME] ([NAME],[bitcolumnname],[SomeOtherColumn])<br />
(SELECT [NAME],<br />
BOOL2BIT([yesNO]) [yesNo],<br />
[SomeOtherColumn]<br />
FROM OpenXML(@iDoc, '/NewDataSet/Table', 2)<br />
WITH<br />
(NAME nvarchar(50),<br />
yesNO nvarchar(50),<br />
[SomeOtherColumn] nvarchar(50)<br />
)<br />
<br />
EXECUTE sp_xml_removedocument @iDoc<br />
|
|
|
|
|
I ported this to VB and had the same problem. The XML needed to have quotes around the 1/0 representation of "true" and "false". I used the following line to work around it:
sqlText = sqlText.Replace("""true""", """1""").Replace("""false""", """1""")
Depending on your data, you might want to incorporate the = sign into your replacement strings
To understand recursion, you must first understand recursion.
|
|
|
|
|