[Solved] Insert to SqlServer Database from WebService XML return.

Solution 2

 HttpWebRequest request
                   = WebRequest.Create("http://localhost/~~~~~~~/Service1.svc/~~~~~") as HttpWebRequest;
 
            // Get response  
            using (HttpWebResponse response = request.GetResponse() as HttpWebResponse)
            {
                // Load data into a dataset  
                DataSet ds = new DataSet();
                ds.ReadXml(response.GetResponseStream());
 
                // Print dataset information  
//Function to do whatever with the dataset,in my example insert to db.
                PrintDataSet(ds);
            }

Permalink

Posted 8 Feb '13 - 22:29

IviKAZAZI1.2K

Edited 8 Feb '13 - 22:30

Solution 1

Partial:

--CREATE SCHEMA [cpqa]
USE [cpqaAnswers]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[cpqa].[tblIK]') AND type in (N'U'))
DROP TABLE [cpqa].[tblIK]
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[cpqa].[tblIKIdx]') AND type in (N'U'))
DROP TABLE [cpqa].[tblIKIdx]
CREATE TABLE [cpqaAnswers].[cpqa].[tblIK](
	[xml_in][xml]										
	)
	
CREATE TABLE [cpqaAnswers].[cpqa].[tblIKIdx](
	[idx][int]IDENTITY(1,1) NOT NULL,
		[xml_in][xml]										
		)
		
INSERT INTO [cpqaAnswers].[cpqa].[tblIK]
	SELECT * FROM OPENROWSET(BULK 'C:\Users\IK\logical.xml', SINGLE_BLOB) AS [whatever]	
	
INSERT INTO [cpqaAnswers].[cpqa].[tblIKIdx]
	SELECT [xml_in] FROM [cpqaAnswers].[cpqa].[tblIK]

Single "record" as XML, indexed ...

SELECT * FROM [cpqaAnswers].[cpqa].[tblIKIdx]

The SQL2008 part anyway ...

[edit]
For the re-output part certain things have to be known about the "root" obviously; looking at the XML used for input in logical.xml ... Something like this then:

SELECT [xml_in] FROM [cpqaAnswers].[cpqa].[tblIKIdx]
    FOR XML PATH('kangaroo'), ROOT('Root')

[End edit]]

Permalink

Posted 8 Feb '13 - 7:15

RedDK5.4K

Edited 9 Feb '13 - 13:38

ryanb31 - 8 Feb '13 - 8:04

Sure. Just parse the XML and send it to SQL. Where exactly are you stuck?

IviKAZAZI - 8 Feb '13 - 8:34

The service method returns the xml as a List < T >,how can i then insert this to the sql table? Or if there is any other way,to get the service output just from the request/response,and then insert this to the sql table?

0	Sergey Alexandrovich Kryukov	545
1	Maciej Los	270
2	Slacker007	240
3	OriginalGriff	235
4	Aarti Meswania	185

0	Sergey Alexandrovich Kryukov	9,118
1	OriginalGriff	7,134
2	CPallini	3,803
3	Rohan Leuva	3,135
4	Maciej Los	2,558

Insert to SqlServer Database from WebService XML return.

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

		9,885,179 members (35,138 online) Visit CodeProject.TV Discuss CodeProject.TV Sign in Email Password Forgot your password? Sign in using

Interested
Ignored

Insert to SqlServer Database from WebService XML return.

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us