Saving xml data in Oracle in C#

Question

I have following xml file:

   <os:tax>
    <os:cat name="abc" id="1">
        <os:subcat name="bcd" id="11">
            <os:t name="def" id="111">
                <os:cut name="hello" id="161" cutURL="/abc/a.html"/>
                <os:cut name="hello2" id="162" cutURL="/abc1/a1.html"/>
                <os:cut name="hello3" id="163" cutURL="/abc4/a3.html"/>
            </os:t>
        </os:subcat>
    </os:cat>
    <os:cat name="def" id="2">
        <os:subcat name="bcd" id="22">
            <os:t name="def" id="222">
                <os:cut name="hello" id="171" cutURL="/abcs/a.html"/>
                <os:cut name="hello2" id="172" cutURL="/abcs1/a1.html"/>
                <os:cut name="hello3" id="173" cutURL="/abcs4/a3.html"/>
            </os:t>
        </os:subcat>
    </os:cat>
</os:tax>

it has more os:cat under it. Just showing two here for ease of use. I have table like this in oracle:

ID        os_lev         title          parent_id         cut_url
1           cat           abc            null              null
11          subcat        bcd            1                 null
111
161
162
163
2
22
...

I want to fill up this table like this. I want to know what is the best way to do this with console app in c#? I am doing is:

XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load("C:\\getxml.xml");

XmlNodeList tax= xmlDoc.GetElementsByTagName("os:tax");

foreach (XmlNode node in tax)
{
    //here i will save all the nodes? What is the best way to do this?
    //Also should i do a insert in oracle right here?
}

Should this foreach be inside try loop?

How many records in the table from document, and how often do they must they change. There are lots of ways to skin this cat.For instance XMLDocument will load the entire thing XMLReader a node. — Tony Hopkinson
– Tony Hopkinson, Commented Nov 18, 2011 at 22:21
@Tony - I am using LINQ to get all the data i want to know how i can move that data to oracle database. Table should have as many record as many nodes are in the file. — Gurnor
– Gurnor, Commented Nov 19, 2011 at 0:45

Tony Hopkinson · Accepted Answer · 2011-11-20 01:26:39Z

There's a probably a way of doing this in LINQ in the form of an Insert Into Select .. From MyXLDoc, but I haven't learnt enough to give you it, yet. So continuing with XmlDocument.

Create a connection to your oracle database. Define a command object on the connection. Set the SQL to Insert MyTable(ID,os_lev,title,parent_id,cut_url) Values(@ID,@os_lev,@title,@parentID,@cut_url)

Now all you have to do is loop through the xml dig out the values, set the relevant parameter and the execute the query.

public void SaveInDB(IdbCommand argCommand,String argFileName)
{
  using(FileStream fs = new FileStream(argFileName, FileMode.Open, FileAccess.Read))
  {
    XmlDocument doc = new XmlDocument();
    doc.Load(fs);
    XmlNamespaceManager ns = new XmlNameSpaceManager(doc.NameTable);
    ns.Add("os",doc.DocumentElement.NamespaceUri);
    foreach(XmlNode catNode in doc.DocumentElement.SelectNodes("os:cat",ns))
    {
       argCommand.Parameters["ID"].Value = catNode.Attributes["id"].Value; // convert to an int?
       argCommand.Parameters["os_lev"].Value = catNode.LocalName;
       argCommand.Parameters["title"].Value = catNode.Attributes["name"].Value;
       argCommand.Parameters["parent_id"].Value = DBnull;
       argCommand.Parameters["cut_url"].Value = DBNull;
       argCommand.ExecuteNonQuery();
       foreach(XMlNode subcatNode in catNode.SelectNodes("os:subcat",ns)
       {
         SaveSubcatInDB(argCommand,subcatNode,ns);
       } 
    }
  }
}

private static void SaveSubcatInDB(IdbCommand argCommand, XmlNode argNode, XmlNameSpaceManager argNs)
{
  // you get the idea.
}

NB no error checking in this so if say your xml is messed up and name or id attributes are missing or if id is to be an int and it isn't, chunks will be blown. Also I wrote this off the top of my head, so ou might get a squiggly or two... And this is the quiick and dirty, inelegant, and pretty unreusable approach. It will be easy to understand and debug though.

PS don't forget the namespacemanager, Xpath will just not find anything with a prefix unless you tell it what's what.

Collectives™ on Stack Overflow

Saving xml data in Oracle in C#

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related