Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# SQL-Server-2008 WebService , +
Hello all,
 
I have a webservice which returns a XML and i want to store these records into sql server table. The XML is generated from the same table format in another SQL table. Is there any way to do this? Lets say its some type of synchronizing tables.
 
Thanks in advance.
Posted 8-Feb-13 1:20am
IviKAZAZI1.4K
Comments
ryanb31 at 8-Feb-13 8:04am
   
Sure. Just parse the XML and send it to SQL. Where exactly are you stuck?
IviKAZAZI at 8-Feb-13 8:34am
   
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?
Rate this: bad
good
Please Sign up or sign in to vote.

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  
v3
Rate this: bad
good
Please Sign up or sign in to vote.

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  
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 300
1 Maciej Los 180
2 Richard MacCutchan 115
3 arvind mepani 104
4 Tino Fourie 92


Advertise | Privacy | Mobile
Web03 | 2.8.140709.1 | Last Updated 9 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid