• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 131
  • Last Modified:

C# code to extract the information from XML and store them to database.

Hi,

Here is the XML file which has 2 entry. Goal is to puch all the values in this XML file to a database.

1. All tags in the XML file will be column in database.
2. Code should read all the entry one by one and save the data within respective tags to the database.

Thanks.

XML file

<feed xmlns:d="test">
  <title type="text"></title>
  <entry>
    <id>uuid:dc683734-ae2c-4303-b183-3ef759072b1a;id=1064283</id>
    <title type="text"></title>
    <updated>2014-09-02T13:37:10Z</updated>
    <author /><content type="application/xml">
      <m:properties>
      <d:CompanyID m:type="Edm.Int32">964</d:CompanyID>
        <d:OSName>&lt;All&gt;</d:OSName>
        <d:DataRange>Data range 5/28/2014 - 8/24/2014      Report data last updated 8/25/2014</d:DataRange>
        <d:ProductName>Limited1</d:ProductName>
        <d:ProductVersion>10.3.9</d:ProductVersion>
        <d:ReleaseStatus>PreRelease</d:ReleaseStatus>
        <d:FailureModeGroupHeader>Top user failures</d:FailureModeGroupHeader>
        <d:FailureName>FailureName1.dll!Unloaded</d:FailureName>
        <d:FailureTypeName>Crash32</d:FailureTypeName>
        <d:Hits m:type="Edm.Double">332874</d:Hits>
        <d:BugID m:type="Edm.Int32">18887</d:BugID>
        <d:ArchName>oth</d:ArchName>
        <d:Arch_Hits>912</d:Arch_Hits>
        <d:ArchPercentage m:type="Edm.Double">1</d:ArchPercentage>
        <d:ArchAllOtherPercentage m:type="Edm.Double">0</d:ArchAllOtherPercentage>
      </m:properties>
    </content>
  </entry>
  <entry>
    <id>uuid:dc683734-ae2c-4303-b183-3ef759072b1a;id=1064290</id>
    <title type="text"></title>
    <updated>2014-09-02T13:37:10Z</updated>
    <author />
    <content type="application/xml">
      <m:properties>
        <d:CompanyID m:type="Edm.Int32">964</d:CompanyID>
        <d:OSName>&lt;All&gt;</d:OSName>
        <d:DataRange>Data range 5/28/2014 - 8/24/2014      Report data last updated 8/25/2014</d:DataRange>
        <d:ProductName>Limited1</d:ProductName>
        <d:ProductVersion>10.3.9</d:ProductVersion>
        <d:ReleaseStatus>PreRelease</d:ReleaseStatus>
        <d:FailureModeGroupHeader>Top user failures</d:FailureModeGroupHeader>
        <d:FailureName>FailureName2.dll!Unloaded</d:FailureName>
        <d:FailureTypeName>Crash32</d:FailureTypeName>
        <d:Hits m:type="Edm.Double">332874</d:Hits>
        <d:BugID m:type="Edm.Int32">18887</d:BugID>
        <d:ArchName>oth</d:ArchName>
        <d:Arch_Hits>912</d:Arch_Hits>
        <d:ArchPercentage m:type="Edm.Double">1</d:ArchPercentage>
        <d:ArchAllOtherPercentage m:type="Edm.Double">0</d:ArchAllOtherPercentage>
      </m:properties>
    </content>
  </entry>
</feed>
0
searchsanjaysharma
Asked:
searchsanjaysharma
1 Solution
 
s_chilkuryCommented:
The best way is to split the XML into simpler XML (node/record of one)


XDocument doc = XDocument.Load("your.xml");
var newXMLs = doc.Descendants("DOC")
                 .Select(d => new XDocument(new XElement("DATABASE", d)));
foreach (var newXML in newXMLs)
{
    newXML.Save("newfilename.xml");
}

And then, insert all those each xml file with iteration using the following code:

DataSet reportData = new DataSet();
reportData.ReadXml(Server.MapPath(”report.xml”));

SqlConnection conn = new SqlConnection(”conn_str”);
SqlBulkCopy bc = new SqlBulkCopy(conn);
bc.DestinationTableName = “table”;

//map Cols
bc.ColumnMappings.Add(”field1”, “node1”);
bc.ColumnMappings.Add(”field2”, “node2”);

conn.Open();

bc.WriteToServer(reportData.Tables[4]);
conn.Close();
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now