从WebService XML返回插入到SqlServer数据库。 [英] Insert to SqlServer Database from WebService XML return.

问题描述

大家好，



我有一个webservice，它返回一个XML，我想将这些记录存储到sql server表中。 XML是从另一个SQL表中的相同表格格式生成的。有没有办法做到这一点？让我们说它的某些类型的同步表。



提前谢谢。

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.

推荐答案

部分：

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]

单个记录作为XML，编入索引......

Single "record" as XML, indexed ...

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

无论如何，SQL2008部分......



[edit]

对于重新输出部分，显然必须知道根的某些事情;查看用于在logical.xml中输入的XML ...然后这样：

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]]

 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);
            }

这篇关于从WebService XML返回插入到SqlServer数据库。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！