c# xml multiple item to sql?

[fabregas42]

<item>
<HaberKodu </HaberKodu>
<Kategori</Kategori>
<Sehir</Sehir>
<title</title>
<description></description>
<pubDate></pubDate>
<images>
<image> 1 </image>
<image> >2 </image>
<image> 3 </image>
<image> 4 </image>
<image> 5 </image>
<image> 6 </image>
</images>
<small_images>
<small_image>1 </small_image>
<small_image> 2 </small_image>
<small_image> 2 </small_image>
</small_images>
<Aciklamalar>
<Aciklamala>1 </Aciklamala>
<Aciklamala> 2 </Aciklamala>
<Aciklamala> 3 </Aciklamala>
</Aciklamalar>
<link> </link>
/item>




 protected void Button1_Click(object sender, EventArgs e)
    {

        XmlDocument xml = new XmlDocument();  
        xml.Load("XML FİLE");

        XmlNodeList kayitlar = xml.SelectNodes("/rss/channel/item");

        foreach (XmlNode item in kayitlar)
        {

            string row1= item.ChildNodes[1].InnerText;
            string row2= item.ChildNodes[2].InnerText;
            string row3= item.ChildNodes[3].InnerText;
            string row4= item.ChildNodes[4].InnerText;
            string row5= item.ChildNodes[5].InnerText;
            string row6= item.SelectNodes("/small_images/small_image[0]").ToString();
                string row7= item.SelectNodes("/small_images/small_image[1]").ToString();
                string row8= item.SelectNodes("/small_images/small_image[2]").ToString();
                string row9= item.SelectNodes("/small_images/small_image[3]").ToString();
                string row10= item.SelectNodes("/small_images/small_image[4]").ToString();
                string row11= item.SelectNodes("/small_images/small_image[5]").ToString();  
            string row12= item.SelectNodes("/small_images/small_image[0]").ToString();
            string row13= item.SelectNodes("/small_images/small_image[1]").ToString();
            string row14= item.SelectNodes("/small_images/small_image[2]").ToString();
            string row15= item.SelectNodes("/Aciklamalar/Aciklamala[0]").ToString();
            string row16= item.SelectNodes("/Aciklamalar/Aciklamala[1]").ToString();
            string row17= item.SelectNodes("/Aciklamalar/Aciklamala[2]").ToString();
            string row18= item.ChildNodes[9].InnerText;

            SqlConnection bag = new SqlConnection(ConfigurationManager.ConnectionStrings[""].ConnectionString);

            string ekle = "insert into TABLE(row1,row2,row3,row4,row5,row6,row6,row7,row8,row9,row10,row11,row12,row13,row14,row15,row16,row17,row18) values('" + row1 + "','" + row2+ "','" + row3+ "','" + row4+ "','" + row5+ "','" + row6+ "','" + row7+ "','" + row8+ "','" + row9+ "','" + row10+ "','" + row11+ "','" + row12+ "','" + row13+ "','" + row14+ "','" + row15+ "','" + row16+ "','" + row17+ "','" + row18+ "')";

            SqlDataAdapter da = new SqlDataAdapter(ekle, bag);

            DataSet ds = new DataSet();
            da.Fill(ds);               
        }
    }

mssql insert to multiple tag System.Xml.XPathNodeList null object.

mssql insert allrow

Help

Thanks..

[JOSheaIV]

First of all, I hope the XML you posted isn't what you are trying to actually read in ... because you won't (specifically the 'item' close node, and the 'HaberKodu', 'Kategori', 'Sehir', and 'title' open nodes). They are missing characters (< or >) It should look like

<item>
<HaberKodu> </HaberKodu>
<Kategori></Kategori>
<Sehir></Sehir>
<title></title>
<description></description>
<pubDate></pubDate>
<images>
<image> 1 </image>
<image> >2 </image>
<image> 3 </image>
<image> 4 </image>
<image> 5 </image>
<image> 6 </image>
</images>
<small_images>
<small_image>1 </small_image>
<small_image> 2 </small_image>
<small_image> 2 </small_image>
</small_images>
<Aciklamalar>
<Aciklamala>1 </Aciklamala>
<Aciklamala> 2 </Aciklamala>
<Aciklamala> 3 </Aciklamala>
</Aciklamalar>
<link> </link>
</item>

Now assuming the XML is not the issue, this is how you would read this in (I am going to use a List). Now because I am putting it in a list you could choose to Clear() the list after each SQL statement

    List<string> RetrievedValues = new List<string>();

    XmlDocument ReadInDocument = new XmlDocument();
    ReadInDocument.Load("INSERT FILENAME");

    foreach(XmlNode node in ReadInDocument.SelectNodes(".//rss/channel/item"))
    {    
        RetrievedValues.Add(node.SelectSingleNode("HaberKodu").InnerText);
        RetrievedValues.Add(node.SelectSingleNode("Kategori").InnerText);
        RetrievedValues.Add(node.SelectSingleNode("Sehir").InnerText);
        RetrievedValues.Add(node.SelectSingleNode("title").InnerText);
        RetrievedValues.Add(node.SelectSingleNode("description").InnerText);
        RetrievedValues.Add(node.SelectSingleNode("pubDate").InnerText);

        foreach(XmlNode ImageNodes in node.SelectNodes(".//images/image"))
        {
            //Retrieves each of the image node in images
            RetrievedValues.Add(ImageNodes.InnerText);
        }

        foreach(XmlNode SmallImageNodes in node.SelectNodes(".//small_images/small_image"))
        {
            RetrievedValues.Add(SmallImageNodes.InnerText);
        }

        foreach(XmlNode AciklamalarNodes in node.SelectNodes(".//Aciklamalar/Aciklamala"))
        {
            RetrievedValues.Add(AciklamalarNodes.InnerText);
        }

        RetrievedValues.Add(node.SelectSingleNode("link").InnerText);
    }

That should read in the XML you provided, storing each value in the List

I assume you got the SQL down? It's not what I am used to seeing (I personally have a library I built that simplies the work needed to do SQL) so let us know if you need some help with that

[fabregas42]

Thanks for the help . But I will how to insert sql result. List item ?

[fabregas42]

C# List insert sql
Column1,Column2,Column3 ........

[fabregas42]

Helpppp :(

[Fenrir()]

assuming the nodes in the XML are static you can just call an item from the list above by referencing the index. seeing as you know what item would be at what index string row1 = RetrievedValues[0].ToString(); I'm assuming this is what you're talking about when you say

C# List insert sql
Column1,Column2,Column3 ........

[JOSheaIV]

Hello, Sorry it took me awhile to get back, I had some stuff come up.

Alright so we have your foreach loop.

So you could then update the code as followed, note that I added the code around the original code you provided.

SqlConnection connection = new SqlConnection("INSERT CONNECTION STRING");
        SqlCommand command = new SqlCommand();
        command.Connection = connection;

        connection.Open(); //you can open the connection now and close it later

        List<string> RetrievedValues = new List<string>();

        XmlDocument ReadInDocument = new XmlDocument();
        ReadInDocument.Load("INSERT FILENAME");

        foreach(XmlNode node in ReadInDocument.SelectNodes(".//rss/channel/item"))
        {
            RetrievedValues.Add(node.SelectSingleNode("HaberKodu").InnerText);
            RetrievedValues.Add(node.SelectSingleNode("Kategori").InnerText);
            RetrievedValues.Add(node.SelectSingleNode("Sehir").InnerText);
            RetrievedValues.Add(node.SelectSingleNode("title").InnerText);
            RetrievedValues.Add(node.SelectSingleNode("description").InnerText);
            RetrievedValues.Add(node.SelectSingleNode("pubDate").InnerText);

            foreach(XmlNode ImageNodes in node.SelectNodes(".//images/image"))
            {
                //Retrieves each of the image node in images
                RetrievedValues.Add(ImageNodes.InnerText);
            }

            foreach(XmlNode SmallImageNodes in node.SelectNodes(".//small_images/small_image"))
            {
                RetrievedValues.Add(SmallImageNodes.InnerText);
            }

            foreach(XmlNode AciklamalarNodes in node.SelectNodes(".//Aciklamalar/Aciklamala"))
            {
                RetrievedValues.Add(AciklamalarNodes.InnerText);
            }

            RetrievedValues.Add(node.SelectSingleNode("link").InnerText);

            command.CommandTest = String.Format("INSERT INTO {0} (row1,row2,row3,row4,row5,row6,row6,row7,row8,row9,row10,row11,row12,row13,row14,row15,row16,row17,row18) VALUES ('{2}')", TableName, string.Join("',", RetrievedValues.ToArray()));
            //Make sure you replace the "TableName" value with the correct table name.

            try
            {
                command.ExecuteNonQuery();
            }
            catch(Exception error)
            {
                throw new Exception(error.Message);
            }

            RetrievedValues.Clear(); //clear the data just inserted.            
        }

        connection.Close();

NOW! And please don't ignore this piece, you may want to add this into a try/catch. The XML Load can blow errors, and opening and closing connections can as well and should be caught.

In the example above I open a connection to your database before you start reading the XML and don't close it till your are done. You can choose to open it before the insert and then close it after, I just personally don't like opening and closing the connection like that.

Also, I hope those String.Format pieces of code make sense