Click here to Skip to main content
12,885,742 members (40,971 online)
Rate this:
Please Sign up or sign in to vote.
See more: C# SQL-Server SQL-Server-2008 , +
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
ryanb31 8-Feb-13 8:04am
Sure. Just parse the XML and send it to SQL. Where exactly are you stuck?
IviKAZAZI 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
Please Sign up or sign in to vote.

Solution 1

USE [cpqaAnswers]
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](
CREATE TABLE [cpqaAnswers].[cpqa].[tblIKIdx](
	[idx][int]IDENTITY(1,1) NOT NULL,
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 ...

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]]
Rate this: bad
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();
                // Print dataset information  
//Function to do whatever with the dataset,in my example insert to db.

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

    Print Answers RSS
Top Experts
Last 24hrsThis month

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

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100