ansaurus

Question

Answer 1

A:

You can use an XPathNavigator object to parse the XML, and then use an SqlCommand object to insert the data into the tables. There are lots of code samples for both objects on the Internet.

John M Gant 2009-04-21 14:54:31

Answer 2

+2 A:

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

Richard Szalay 2009-04-21 15:32:05

Answer 3

+1 A:

I have this URL where there is XML data. I have to extract that data from URL and dump it into DW table. I am using SSIS Script Task for that.

This is how the data looks like:

-<currency>

 <csymbol>AED</csymbol>

 <cname>United Arab Emirates Dirhams</cname>

 <crate>3.6732001305</crate>

 <cinverse>0.2722421770</cinverse>

</currency>

−<currency>

 <csymbol>AFN</csymbol>

 <cname>Afghanistan Afghanis</cname>

 <crate>44.0000000000</crate>

 <cinverse>0.0227272727</cinverse>

</currency>

−<currency>

 <csymbol>ALL</csymbol>

 <cname>Albania Leke</cname>

 <crate>104.4100000001</crate>

 <cinverse>0.0095776267</cinverse>

</currency>

This is the code i'm using to load it into some Object type or something. But i dont know how to do that.

 public void Main()
    {
        String URLString = "http://www.xe.com/dfs/datafeed2.cgi?beeline";
        XmlDocument doc = new XmlDocument();
        XmlTextReader reader = new XmlTextReader(URLString);
        doc.Load(reader);

        XmlNodeList currencynodes = doc.SelectNodes("currency");
        foreach(XmlNode currency in currencynodes)
        {
            XmlNode csymbol = currency.SelectSingleNode("csymbol");
            string csymbolvalue = csymbol.Value;

            XmlNode cname = currency.SelectSingleNode("cname");
            string cnamevalue = cname.Value;

            XmlNode crate = currency.SelectSingleNode("crate");
            string cratevalue = crate.Value;

            XmlNode cinverse = currency.SelectSingleNode("cinverse");
            string cinversevalue = cinverse.Value;

            Dts.Variables["User::oCurrencyConversion"].Value = csymbol.Value;
        }

jack 2010-09-20 16:07:26

Answer 4

A:

Thanks John for the reply. Actually I am not good with Scripting in c#. I tried finding the samples but dint find any link which i can relate my issue to. Based on a few articles, i came up with the code above which took me like 2 days :)

It'll be really helpful if you can provide few lines which i can start off of.

I thought of posting a new question instead of using as an answer. You can use the link below.

http://stackoverflow.com/questions/3754297/writing-xml-data-from-url-to-object-in-ss-script-task

Thanks again

jack 2010-09-20 18:32:00

ansaurus

tags:

views:

answers:

Import XML to SQL using C#

related questions