将数据从xml doc保存到sqlserver 2008 [英] Saving data from xml doc to sqlserver 2008

问题描述

我正在做一个wpf C#应用程序，我想通过storeprocedure保存到数据库中.
该过程接受xml文档的位置.我不知道如何从xml保存数据并保存它.

这是我的代码，

Hi, I am doing a wpf C# app where i want to save into database through storeprocedure.
where that procedure accepts a xml document. I do not know how to save data from xml and save it.

Here is my code,

public partial class MainWindow : Window
    {
        private XmlDocument saveXml;
        List<string> attribute;
        private String id, name, dep;
        private SqlConnection con;
        private SqlCommand com;

        public MainWindow()
        {
            con = new SqlConnection();

            con.ConnectionString = "Data Source=192.168.2.88; database=Student; User id=sa; Password=123456;";

            saveXml = new XmlDocument();
            attribute = new List<string>();

            InitializeComponent();

            saveXml.LoadXml("<root></root>");

            for (int i = 0; i < 4; i++)
            {
                id = i.ToString();
                name = "N" + i.ToString();
                dep = "D" + i.ToString();

                attribute.Add("id");
                attribute.Add("name");
                attribute.Add("dep");

                XmlParsingLibrary.XmlEngine.getInstance().writeNode(saveXml, "Student", "//Root", attribute,id,name, dep);
                attribute.Clear();
            }

            //MessageBox.Show(saveXml.OuterXml);
        }

        private void Button_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                con.Open();
                com = new SqlCommand();
                com.Connection = con;
                com.CommandType = CommandType.StoredProcedure;
                com.CommandText = "ProcSave";

                SqlParameter inputUser2 = com.Parameters.AddWithValue("@xmlfile", saveXml);
                inputUser2.Direction = ParameterDirection.Input;
                com.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }
    }</string></string>

我的xml像

My xml like

<root>	
	<student id="1" name="N1" dep="D1" />
	<student id="2" name="N2" dep="D2" />
	<student id="3" name="N3" dep="D3" />	
</root>

推荐答案

U可以通过@XMLData AS XML将XML作为参数传递给Stored Proc.
然后可以使用
从此参数访问数据

U can pass your xml as a parameter to Stored Proc by @XMLData AS XML

and then can access data from this parameter using

SELECT

    XMLData.value('@id[1]','INT'),    XMLData.value('@name[1]','NVARCHAR(500)'),XMLData.value('@dep[1]','NVARCHAR(max)')
    FROM @XMLData.nodes('/root[1]') e(XMLData)

你好，亲爱的，

您可以编写一个存储过程，该过程将获取stoudent表记录并将其插入到Student表中.写入Xml文件后，请通过数据集进行读取，并检索包含学生记录的数据表.然后遍历数据表记录，并使用存储过程将记录插入到学生表中.

Hello Dear,

You can write a stored procedure which will take the stoudent table record and insert it into Student table. After writing the Xml file read it through dataset and retrieve the datatable which contains the student record. Then loop through the datatable record and use the stored procedure to insert your record into Student table.

这篇关于将数据从xml doc保存到sqlserver 2008的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！