load data from xml to sql server db

Posted by Rohithrs under C# on 7/4/2011 | Points: 10 | Views : 4033 | Status : [Member] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi,
XML file :

<?xml version="1.0" encoding="UTF-8"?>
<articles>
<article>
<id>000001</id>
<pagetitle>Page Title</pagetitle>
<keywords>Keywords</keywords>
<description>description</description>
<pubDate>Wed, 02 Oct 2010 13:00:00 GMT</pubDate>
<updated>Wed, 02 Oct 2010 13:00:00 GMT</updated>

<author id="1">
<name>Joe</name>
<url>http://www.media.com</url>
</author>

<categories>
<category id="123">
<name>First Category</name>
<parent>0</parent>
</category>

<category id="456">
<name>First Sub-Category</name>
<parent>123</parent>
</category>

<category id="789">
<name>Second Sub-Category</name>
<parent>123</parent>
</category>
</categories>

<image id="1">
<thumbnail>
<url>http://imageurl.com/thumbnail.jpg</url>
<width>100</width>
<height>150</height>
</thumbnail>

<large>
<url>http://imageurl.com/image.jpg</url>
<width>300</width>
<height>450</height>
</large>

<caption>Image Caption</caption>
<description>Image description</description>
</image>

<headline>Article Headline</headline>
<summary>This is an article summary</summary>
<body>This is the articles main body</body>
</article>
</articles>

For this, i need to store article, author, Categories and Image node data into sql server database (different table).
can any one send me source code for this

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Ndebata on: 7/4/2011 [Member] Starter | Points: 25

0	There are many ways you can do this. One way could be In the code first get that data from XML and load them in a Article object. Class Structure public class MAuther { public string id { get; set; } public string name { get; set; } public string url { get; set; } } public class MCategory { public string id { get; set; } public string name { get; set; } public string parent { get; set; } } public class MPicture { public string url { get; set; } public int width { get; set; } public int height { get; set; } } public class MImage { public MImage() { this.thumbnail = new MPicture(); this.large = new MPicture(); } public string id { get; set; } public MPicture thumbnail { get; set; } public MPicture large { get; set; } public string caption { get; set; } public string description { get; set; } } public class MArticle { public MArticle() { this.auther = new MAuther(); this.categories = new List<MCategory>(); this.image = new MImage(); } public string id { get; set; } public string pagetitle { get; set; } public string keywords { get; set; } public string description { get; set; } public string pubDate { get; set; } public string updated { get; set; } public string headline { get; set; } public MAuther auther { get; set; } public List<MCategory> categories { get; set; } public MImage image { get; set; } public string summary { get; set; } public string body { get; set; } } Create an object of MArticle MArticle article=new MArticle(); Populate Object Values XElement xe=XElement.Load("Here provide path of your xml file"); var xarticle = xe.Element("article"); article.id = xarticle.Element("id").Value; article.pagetitle = xarticle.Element("pagetitle").Value; article.keywords = xarticle.Element("keywords").Value; article.description = xarticle.Element("pubDate").Value; article.pubDate = xarticle.Element("pubDate").Value; article.updated = xarticle.Element("updated").Value; article.headline = xarticle.Element("headline").Value; article.summary = xarticle.Element("summary").Value; article.body = xarticle.Element("body").Value; var xauther = xarticle.Element("author"); if (xauther != null) { article.auther.id = xauther.Attribute("id").Value; article.auther.name = xauther.Element("name").Value; article.auther.url = xauther.Element("url").Value; } var xcategories = xarticle.Element("categories"); if (xcategories != null && xcategories.HasElements) { foreach (var item in xcategories.Elements()) { MCategory category = new MCategory(); category.id = item.Attribute("id").Value; category.name = item.Element("name").Value; category.parent = item.Element("parent").Value; article.categories.Add(category); } } var ximage = xarticle.Element("image"); if (ximage != null) { article.image.id = ximage.Attribute("id").Value; var xthumbnail = ximage.Element("thumbnail"); if (xthumbnail != null) { article.image.thumbnail.url = xthumbnail.Element("url").Value; article.image.thumbnail.width = Convert.ToInt32(xthumbnail.Element("width").Value); article.image.thumbnail.height = Convert.ToInt32(xthumbnail.Element("height").Value); } var xlarge = ximage.Element("large"); if (xlarge != null) { article.image.large.url = xlarge.Element("url").Value; article.image.large.width = Convert.ToInt32(xlarge.Element("width").Value); article.image.large.height = Convert.ToInt32(xlarge.Element("height").Value); } article.image.caption = ximage.Element("caption").Value; article.image.description = ximage.Element("description").Value; } Then you can use this object where ever you need, Save it to database or .... Thanks, Debata Rohithrs, if this helps please login to Mark As Answer. \| Alert Moderator

There are many ways you can do this.
One way could be
In the code first get that data from XML and load them in a Article object.
Class Structure

    public class MAuther

    {

        public string id { get; set; }

        public string name { get; set; }

        public string url { get; set; }

    }

    public class MCategory

    {

        public string id { get; set; }

        public string name { get; set; }

        public string parent { get; set; }

    }

    public class MPicture

    {

        public string url { get; set; }

        public int width { get; set; }

        public int height { get; set; }

    }

    public class MImage

    {

        public MImage()

        {

            this.thumbnail = new MPicture();

            this.large = new MPicture();

        }

        public string id { get; set; }

        public MPicture thumbnail { get; set; }

        public MPicture large { get; set; }

        public string caption { get; set; }

        public string description { get; set; }

    }

    public class MArticle

    {

        public MArticle()

        {

            this.auther = new MAuther();

            this.categories = new List<MCategory>();

            this.image = new MImage();

        }



        public string id { get; set; }

        public string pagetitle { get; set; }

        public string keywords { get; set; }

        public string description { get; set; }

        public string pubDate { get; set; }

        public string updated { get; set; }

        public string headline { get; set; }



        public MAuther auther { get; set; }

        public List<MCategory> categories { get; set; }

        public MImage image { get; set; }



        public string summary { get; set; }

        public string body { get; set; }

    }

Create an object of MArticle

MArticle article=new MArticle();

Populate Object Values

   XElement xe=XElement.Load("Here provide path of your xml file");

            var xarticle = xe.Element("article");



            article.id =  xarticle.Element("id").Value;

            article.pagetitle = xarticle.Element("pagetitle").Value;

            article.keywords = xarticle.Element("keywords").Value;

            article.description = xarticle.Element("pubDate").Value;

            article.pubDate = xarticle.Element("pubDate").Value;

            article.updated = xarticle.Element("updated").Value;

            article.headline = xarticle.Element("headline").Value;

            article.summary = xarticle.Element("summary").Value;

            article.body = xarticle.Element("body").Value;



            var xauther = xarticle.Element("author");

            if (xauther != null)

            {

                article.auther.id = xauther.Attribute("id").Value;

                article.auther.name = xauther.Element("name").Value;

                article.auther.url = xauther.Element("url").Value;

            }



            var xcategories = xarticle.Element("categories");

            if (xcategories != null && xcategories.HasElements)

            {

                foreach (var item in xcategories.Elements())

                {

                    MCategory category = new MCategory();

                    category.id = item.Attribute("id").Value;

                    category.name = item.Element("name").Value;

                    category.parent = item.Element("parent").Value;



                    article.categories.Add(category);

                }

            }



            var ximage = xarticle.Element("image");

            if (ximage != null)

            {

                article.image.id = ximage.Attribute("id").Value;

                var xthumbnail = ximage.Element("thumbnail");

                if (xthumbnail != null)

                {

                    article.image.thumbnail.url = xthumbnail.Element("url").Value;

                    article.image.thumbnail.width = Convert.ToInt32(xthumbnail.Element("width").Value);

                    article.image.thumbnail.height = Convert.ToInt32(xthumbnail.Element("height").Value);

                }

                var xlarge = ximage.Element("large");

                if (xlarge != null)

                {

                    article.image.large.url = xlarge.Element("url").Value;

                    article.image.large.width = Convert.ToInt32(xlarge.Element("width").Value);

                    article.image.large.height = Convert.ToInt32(xlarge.Element("height").Value);

                }

                article.image.caption = ximage.Element("caption").Value;

                article.image.description = ximage.Element("description").Value;

            }

Then you can use this object where ever you need, Save it to database or ....

Thanks,
Debata

Rohithrs, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Lakn2 on: 7/5/2011 [Member] Starter | Points: 25

0	try this http://www.codeproject.com/KB/database/insxmldatasqlsvr.aspx Thanks&Regards LakshmiNarayana Nalluri. Rohithrs, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts