如何在C#中导入XML数据 [英] How to import XML data in c#

问题描述

我想使用C#点网将XML数据导入SQL SERVER2000.

任何一个对此的解决方案.

我有下面提到的XML文件.

I want to import XML data using C# dot net to the SQL SERVER 2000.

Any one post solution for this.

I have an XML file like this below I mentioned.

- <shg_repayment_details>
- <data>
  <tally_rec_id_code>452</tally_rec_id_code> 
- <!-- To check already exit in our database 
  --> 
  <cluster_id>COICLT0001</cluster_id> 
  <unit_type>VIL</unit_type> 
  <vprc_id>COIVIL0001</vprc_id> 
  <shg_id>SHG1</shg_id> 
  <pip_no>10120aa</pip_no> 
  <name_of_the_member>BALA</name_of_the_member> 
  <type_of_loan>RF</type_of_loan> 
  <principal>500</principal> 
  <interest>600</interest> 
  </data>
- <data>
  <tally_rec_id_code>453</tally_rec_id_code> 
- <!-- To check already exit in our database 
  --> 
  <cluster_id>COICLT0001</cluster_id> 
  <unit_type>VIL</unit_type> 
  <vprc_id>COIVIL0001</vprc_id> 
  <shg_id>SHG2</shg_id> 
  <pip_no>10120BB</pip_no> 
  <name_of_the_member>BALAJI</name_of_the_member> 
  <type_of_loan>DL</type_of_loan> 
  <principal>300</principal> 
  <interest>500</interest> 
  </data>
  </shg_repayment_details>

推荐答案

好，您需要使用在标准.NET库中实现的一种方法来解析XML.这是我的简短概述:

Well, you need to parse XML using one the ways implemented in standard .NET libraries. Here is my short overview:

1. 使用System.Xml.XmlDocument类.它实现了DOM接口；如果文档太大，则这种方法最简单，也足够好.
   请参见
2. 使用类System.Xml.XmlTextReader; library/system.xml.xmldocument.aspx"target =" _ blank"title =" New Window> ^ ].
3. 使用类System.Xml.XmlTextReader;这是最快的读取方法，尤其是您需要跳过一些数据.
   请参见 http://msdn.microsoft.com/en-us/library/system.xml.xmlreader.aspx [ http://msdn.microsoft.com/en-us/library/system.xml.xmldocument.aspx [http://msdn.microsoft.com/en-us/library/bb387063.aspx [

1. Use System.Xml.XmlDocument class. It implements DOM interface; this way is the easiest and good enough if the size if the document is not too big.
   See http://msdn.microsoft.com/en-us/library/system.xml.xmldocument.aspx[^].
2. Use the class System.Xml.XmlTextReader; this is the fastest way of reading, especially is you need to skip some data.
   See http://msdn.microsoft.com/en-us/library/system.xml.xmlreader.aspx[^].
3. Use the class System.Xml.Linq.XDocument; this is the most adequate way similar to that of XmlDocument, supporting LINQ to XML Programming.
   See http://msdn.microsoft.com/en-us/library/system.xml.xmldocument.aspx[^], http://msdn.microsoft.com/en-us/library/bb387063.aspx[^].

—SA

嗨 如果要在SQL 2000中插入XML文本，则可以看到此链接[

Hi if you want to insert a XML text in SQL 2000 you can see this link[^]

but if you want to send a XML text to SQL by C#, this work is very easy:
Multiple CDATA elements are not consistantly supported across implementations. For example, you will have problems accessing them an XDocument or via SelectNodes. If you can change the input format that would make things easier.

This code hasn''t been tested and there''s no error handling or bad data checking, but it should get you started. Investigate using XPathDocument / XPathNavigator for performance and read my inline comments.

class XmlCsvImport
{
    public void ImportData(string xmlData, ConnectionStringSettings connectionSettings)
    {
        DbProviderFactory providerFactory = DbProviderFactories.GetFactory(connectionSettings.ProviderName);

        IDbConnection connection = providerFactory.CreateConnection();
        connection.ConnectionString = connectionSettings.ConnectionString;

        // TODO: Begin transaction

        XmlDocument doc = new XmlDocument();
        doc.LoadXml(xmlData);

        foreach (XmlNode tableNode in doc.SelectNodes("/transaction/table"))
        {
            IDbCommand command = CreatCommand(connection, tableNode);

            foreach (XmlNode rowNode in tableNode.SelectNodes("data/row"))
            {
                string[] values = GetRowValues(rowNode);

                if (values.Length != command.Parameters.Count)
                {
                    // TODO: Log bad row
                    continue;
                }

                this.FillCommand(command, values);
                command.ExecuteNonQuery();
            }
        }

        // TODO: Commit transaction
    }

    private IDbCommand CreatCommand(IDbConnection connection, XmlNode tableNode)
    {
        string tableName = tableNode.Attributes["name"].Value;

        IDbCommand command = connection.CreateCommand();
        command.Connection = connection;
        command.CommandType = CommandType.Text;

        XmlNodeList fieldNodes = tableNode.SelectNodes("fields/field");

        List<string> fieldNameList = new List<string>(fieldNodes.Count);

        foreach (XmlNode fieldNode in tableNode.SelectNodes("fields/field"))
        {
            string fieldName = fieldNode.Attributes["name"].Value;
            int fieldType = Int32.Parse(fieldNode.Attributes["type"].Value);
            int fieldSize = Int32.Parse(fieldNode.Attributes["size"].Value);

            IDbDataParameter param = command.CreateParameter();
            param.ParameterName = String.Concat("@", fieldNode.Attributes["name"]);
            param.Size = fieldSize;
            param.DbType = (DbType)fieldType; // NOTE: this may not be so easy
            command.Parameters.Add(param);

            fieldNameList.Add(fieldName);
        }

        string[] fieldNames = fieldNameList.ToArray();

        StringBuilder commandBuilder = new StringBuilder();
        commandBuilder.AppendFormat("INSERT INTO [{0}] (", tableName);

        string columnNames = String.Join("], [", fieldNames);
        string paramNames = String.Join(", @", fieldNames);

        command.CommandText = String.Concat(
            "INSERT INTO [", tableName, "] ([",
            columnNames,
            "]) VALUES (@",
            paramNames,
            ")"
            );

        return command;
    }

    private string[] GetRowValues(XmlNode row)
    {
        List<string> values = new List<string>();

        foreach (XmlNode child in row.ChildNodes)
        {
            if (child.NodeType == XmlNodeType.Text ||
                child.NodeType == XmlNodeType.CDATA)
            {
                values.Add(child.Value);
            }
        }

        return values.ToArray();
    }

    private void FillCommand(IDbCommand command, string[] values)
    {
        for (int i = 0; i < values.Length; i++)
        {
            IDbDataParameter param = (IDbDataParameter)command.Parameters[i];
            param.Value = values[i]; // TODO: Convert to correct data type
        }
    }
</string></string></string></string>

这篇关于如何在C#中导入XML数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！