load data from xml to sql server db

Posted by Rohithrs under C# on 7/4/2011 | Points: 10 | Views : 3180 | Status : [Member] | Replies : 2
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




Responses

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

Up
0
Down
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

Up
0
Down
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

Login to post response