***************************************** Here is my C# webservice Code *****************************************************
using System.Data;
using System.Data.SqlClient;
using System.Xml;
using System.Text;
using System.Configuration;
using System.Xml.Linq;
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
// [System.Web.Script.Services.ScriptService]
public class Service : System.Web.Services.WebService
{
SqlConnection con;
SqlDataAdapter adap;
DataSet ds, ds1;
string connStr = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
[WebMethod]
public DataSet webservices()
{
con = new SqlConnection(@"Data Source=ZUBAIRKHALIL;Initial Catalog=plrmaa;Integrated Security=True;MultipleActiveResultSets=True");
adap = new SqlDataAdapter("select * from CenMst", con);
ds1 = new DataSet();
adap.Fill(ds1, "CenMst");
the problem is that when i create webservice its insert empty value in table mean null value, so please help me how its correct work , i want to insert value from xml file into database table, but some problem in code or store procedure , so please correct
the mistake
Please modify your stored procedure like below, it works fine.
USE Demo_test
GO
/****** Object: StoredProcedure [dbo].[GetData] Script Date: 12/10/2012 19:52:52 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
alter
procedure [dbo].[GetData]
(
@xmlstr ntext)
as
begin
declare @hDoc int
exec sp_xml_preparedocument @hDoc OUTPUT,@xmlstr
insert into cenmst (cennbr, fulnme, adr1, adr2, ctynme ,stecde , zipcde, telnbr, faxnbr,timstp)
select cennbr, fulnme, adr1, adr2, ctynme ,stecde , zipcde, telnbr, faxnbr,timstp
from (
SELECT * FROM OPENXML(@hDoc, '/mDoc/mRow/mCol', 1)
WITH ( id VARCHAR(11),
VALUE VARCHAR(50) 'text()')) as sourceTable
pivot (min(value) for id
in ([CenNbr],[FulNme],[Adr1],[Adr2],[CtyNme],[SteCde],[ZipCde],[TelNbr],[FaxNbr],[TimStp]))AS P
EXEC sp_xml_removedocument @hDoc
end
Thanks.
Please mark the replies as answers if they help or unmark if not.
Feedback to us
one main problem is that , the store procedure insert data but not specific column, mistakes is that i have two table and one name is cenmst and another is cemt, but the adr1 column in cenmst table and also in cemt table, the problems is that when i insert
the data of cenmst table , all column insert properly but the adr1 data not insert correctly, they get the data of cemt another table and insert into cenmst table, i think the problem in store procedure when i select data from xml file, so please correct this
problem for me.
******************** table xml file ***************************
i am trying to load the xml file into a dataset and insert into a data base. i went through the your post
http://csharp.net-informations.com/xml/xml-to-database.htm. but i am getting the error "cannot add a column named TD a nested table with the same name already belongs to this datatable".
PFB the xml file structure . Can you please help me out to resolve the issue.
elizabeth143
Member
17 Points
25 Posts
how to insert XML value into Database Table in C#
Dec 10, 2012 05:36 PM|LINK
here is my xml file webservice.xml
<?xml version="1.0" encoding="UTF-8" ?>
<mDoc>
<mRow id="CenMst">
<mCol id="CenNbr">MDEMO</mCol>
<mCol id="FulNme">MAACO</mCol>
<mCol id="Adr1">610 FREEDOM BUSINESS CENTER</mCol>
<mCol id="Adr2"></mCol>
<mCol id="CtyNme">KOP</mCol>
<mCol id="SteCde">PA</mCol>
<mCol id="ZipCde">19406</mCol>
<mCol id="TelNbr">(610)265-6606</mCol>
<mCol id="FaxNbr">(610)337-6107</mCol>
<mCol id="TimStp">1280</mCol>
</mRow>
</mDoc>
***************************************** Here is my C# webservice Code *****************************************************
using System.Data;
using System.Data.SqlClient;
using System.Xml;
using System.Text;
using System.Configuration;
using System.Xml.Linq;
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
// [System.Web.Script.Services.ScriptService]
public class Service : System.Web.Services.WebService
{
SqlConnection con;
SqlDataAdapter adap;
DataSet ds, ds1;
string connStr = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
[WebMethod]
public DataSet webservices()
{
con = new SqlConnection(@"Data Source=ZUBAIRKHALIL;Initial Catalog=plrmaa;Integrated Security=True;MultipleActiveResultSets=True");
adap = new SqlDataAdapter("select * from CenMst", con);
ds1 = new DataSet();
adap.Fill(ds1, "CenMst");
ds = new DataSet();
ds.ReadXml(Server.MapPath("MDEMO_data.xml"));
string strxml = XDocument.Load(Server.MapPath("MDEMO_data.xml")).ToString();
SqlConnection sqlconn = new SqlConnection(connStr);
SqlCommand sqlcmd = new SqlCommand();
sqlcmd.Connection = sqlconn;
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.CommandText = "GetData";
sqlcmd.Parameters.AddWithValue("@xmlstr", strxml);
sqlconn.Open();
sqlcmd.ExecuteNonQuery();
sqlconn.Close();
return ds1;
}
}
**************************** here Is my Store Procedure ********************************
USE [plrmaa]
GO
/****** Object: StoredProcedure [dbo].[GetData] Script Date: 12/10/2012 19:52:52 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
ALTER
procedure [dbo].[GetData]
(
@xmlstr ntext)
as
begin
declare @hDoc int
exec sp_xml_preparedocument @hDoc OUTPUT,@xmlstr
insert into cenmst (cennbr, fulnme, adr1, adr2, ctynme ,stecde , zipcde, telnbr, faxnbr,timstp)
select xml.cennbr, xml.fulnme,xml.adr1,xml.adr2,xml.ctynme,xml.stecde, xml.zipcde, xml.telnbr, xml.faxnbr,xml.timstp
from OPENXML(@hDoc,'/mDoc/mRow/mCol',2)
with(cennbr char(6) 'CenNbr',
fulnme char(36) 'FulNme',
adr1 char(36) 'Adr1',
adr2 char(36) 'Adr2',
ctynme char(20) 'CtyNme',
stecde char(2) 'SteCde',
zipcde char(10) 'ZipCde',
telnbr char(14) 'TelNbr',
faxnbr char(14) 'FaxNbr',
timstp int 'TimStp')xml
exec sp_xml_removedocument @hDoc
end
************************** here is my table script ****************************8
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[cenmst](
[cennbr] [char](6) NULL,
[fulnme] [char](36) NULL,
[adr1] [char](36) NULL,
[adr2] [char](36) NULL,
[ctynme] [char](20) NULL,
[stecde] [char](2) NULL,
[zipcde] [char](10) NULL,
[telnbr] [char](14) NULL,
[faxnbr] [char](14) NULL,
[upddte] [char](8) NULL,
[updtim] [char](8) NULL,
[updusr] [char](6) NULL,
[timestamp] [int] NULL,
[timstp] [int] NULL
) ON [PRIMARY]
GO
the problem is that when i create webservice its insert empty value in table mean null value, so please help me how its correct work , i want to insert value from xml file into database table, but some problem in code or store procedure , so please correct the mistake
thanks
sen338
Member
498 Points
118 Posts
Re: how to insert XML value into Database Table in C#
Dec 10, 2012 11:49 PM|LINK
Hi,
Just change
select xml.cennbr, xml.fulnme,xml.adr1,xml.adr2,xml.ctynme,xml.stecde, xml.zipcde, xml.telnbr, xml.faxnbr,xml.timstp
from OPENXML(@hDoc,'/mDoc/mRow/mCol',1)
--OPENXML(@hDoc,'/mDoc/mRow/mCol',2)
elizabeth143
Member
17 Points
25 Posts
Re: how to insert XML value into Database Table in C#
Dec 11, 2012 11:01 AM|LINK
not working same issue give me, its insert at a time 10 values in table and empty vaules insert in table
please do something
thanks
Chen Yu - MS...
All-Star
21584 Points
2493 Posts
Microsoft
Re: how to insert XML value into Database Table in C#
Dec 13, 2012 06:43 AM|LINK
Hi elizabeth143,
Please modify your stored procedure like below, it works fine.
USE Demo_test GO /****** Object: StoredProcedure [dbo].[GetData] Script Date: 12/10/2012 19:52:52 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO alter procedure [dbo].[GetData] ( @xmlstr ntext) as begin declare @hDoc int exec sp_xml_preparedocument @hDoc OUTPUT,@xmlstr insert into cenmst (cennbr, fulnme, adr1, adr2, ctynme ,stecde , zipcde, telnbr, faxnbr,timstp) select cennbr, fulnme, adr1, adr2, ctynme ,stecde , zipcde, telnbr, faxnbr,timstp from ( SELECT * FROM OPENXML(@hDoc, '/mDoc/mRow/mCol', 1) WITH ( id VARCHAR(11), VALUE VARCHAR(50) 'text()')) as sourceTable pivot (min(value) for id in ([CenNbr],[FulNme],[Adr1],[Adr2],[CtyNme],[SteCde],[ZipCde],[TelNbr],[FaxNbr],[TimStp]))AS P EXEC sp_xml_removedocument @hDoc endThanks.
Feedback to us
Develop and promote your apps in Windows Store
elizabeth143
Member
17 Points
25 Posts
Re: how to insert XML value into Database Table in C#
Dec 14, 2012 12:38 PM|LINK
one main problem is that , the store procedure insert data but not specific column, mistakes is that i have two table and one name is cenmst and another is cemt, but the adr1 column in cenmst table and also in cemt table, the problems is that when i insert the data of cenmst table , all column insert properly but the adr1 data not insert correctly, they get the data of cemt another table and insert into cenmst table, i think the problem in store procedure when i select data from xml file, so please correct this problem for me.
******************** table xml file ***************************
<mRow id="CenMst"> // table name (CenMst)
<mCol id="CenNbr">MDEMO</mCol>
<mCol id="FulNme">MAACO</mCol>
<mCol id="Adr1">610 FREEDOM BUSINESS CENTER</mCol>
<mCol id="Adr2"></mCol>
<mCol id="CtyNme">KOP</mCol>
<mCol id="SteCde">PA</mCol>
<mCol id="ZipCde">19406</mCol>
<mCol id="TelNbr">(610)265-6606</mCol>
<mCol id="FaxNbr">(610)337-6107</mCol>
<mCol id="TimStp">1280</mCol>
</mRow>
********************* 2nd table xml file ******************************
<mRow id="cemt">// table name (cemt)
<mCol id="AptDte"></mCol>
<mCol id="Adr1">4745 COLEMONT PL</mCol>
<mCol id="Adr2"></mCol>
<mCol id="CtyNme">ANTIOCH</mCol>
<mCol id="MdaTyp"></mCol>
<mCol id="TaxFlg">Y</mCol>
<mCol id="TaxNbr"></mCol>
<mCol id="CarYy">2008</mCol>
<mCol id="CarDte">0</mCol>
</mRow>
see the adr1 and adr2 is also in 2nd table, and when i insert data its get the 2nd table and insert into 1st table
RameshRajend...
Star
7983 Points
2099 Posts
Re: how to insert XML value into Database Table in C#
Dec 14, 2012 01:14 PM|LINK
Rewind this : http://csharp.net-informations.com/xml/xml-to-database.htm
elizabeth143
Member
17 Points
25 Posts
Re: how to insert XML value into Database Table in C#
Dec 14, 2012 04:47 PM|LINK
thanks dear
RameshRajend...
Star
7983 Points
2099 Posts
Re: how to insert XML value into Database Table in C#
Dec 14, 2012 04:54 PM|LINK
okay...... dear
good luck. have a nice day.......Thanks for the mark us answer.
prasadpuvvad...
Member
2 Points
1 Post
Re: how to insert XML value into Database Table in C#
Mar 13, 2013 12:01 PM|LINK
Hi Ramesh,
i am trying to load the xml file into a dataset and insert into a data base. i went through the your post http://csharp.net-informations.com/xml/xml-to-database.htm. but i am getting the error "cannot add a column named TD a nested table with the same name already belongs to this datatable".
PFB the xml file structure . Can you please help me out to resolve the issue.
ds.ReadXml(xmlFile);
<MainReport namespace> <REPORT> <TITLE>REPORT </TITLE> <SUBTITLE/> <SUMMARY> <A>United States</GEOGRAPHY> <B>Home and Work</LOCATION> <PERIOD>Feb</TIMEPERIOD> <TARGET>Audience</TARGET> <MEDIA>Google</MEDIA> </SUMMARY> <TABLE> <THEAD> <TR> <TH>Media</TH> <TD id="274">Average Pages</TD> </TR> </THEAD> <TBODY> <TR> <TH id="1">Total</TH> <TD>34.8565659336236</TD> </TR> <TR> <TH id="1" attribute2 = "hello">Total</TH> <TD>34.8565659336236</TD> </TR> <TR> <TH id="1" attribute3 = "hai">Total</TH> <TD>34.8565659336236</TD> </TR> </TBODY> </REPORT> </MainReport>