Link to home
Start Free TrialLog in
Avatar of Russellbrown
RussellbrownFlag for United States of America

asked on

XML - How to convert a VBA code into C#

Hi, would appreciate your help to the following issue:

I like to extract data values from an xml file with the following format:
<context id="c00246">
  <entity>
    <identifier scheme="http://www.sec.gov/CIK">0000850693</identifier>
  </entity>
  <period>
    <instant>2010-12-31</instant>
  </period>
</context>

Open in new window

I am able to extract the instant value i.e. 2010-12-31 using the following vba code:
strContextPeriod = oInstance.SelectSingleNode("//xbrli:context[@id='" & strContextID & "']/xbrli:period/xbrli:instant").Text where strContextID  = "c00246"

Open in new window

I would like to find the equivalent code in C#.

Specifications: Visual Studio 2013, System.Xml, XmlTextReader reader = new XmlTextReader(textBox1.Text);

I tried with the following codes:
while (reader.Read())
            {
                switch (reader.NodeType)
                {
                              case XmlNodeType.Element: // The node is an element.
                    
                        if (reader.Name.IndexOf("[b]context[/b]", 0) == 0)
                                {
                                      textBox4.AppendText(reader.Name + "====" + reader.GetAttribute("period") + "]" + reader.ReadInnerXml() + "\r\n"); 
                                             }
                                   break;
                }
            }

Open in new window

and only successful in extracting out the context blocks. Would need assistance to extract the instant value only. Thank you.
Avatar of Bob Learned
Bob Learned
Flag of United States of America image

You could use either LINQ-to-XML XDocument or System.Xml.XmlDocument and get similar syntax:

LINQ-to-XML:
var document = new XDocument();
document.Parse(xmlText);
var parentElement = document.Descendants("Parent").FirstOrDefault();

Open in new window


XmlDocument
var document = new XmlDocument();
document.LoadXml(xmlText);
var parentElement = document.SelectSingleNode("//Parent");

Open in new window

Avatar of Russellbrown

ASKER

Thank you for the reply. I have the tried the second suggestion and came up with mixed results:

1. when using a simple xml file i.e.
 
XmlDocument doc = new XmlDocument();
 doc.Load("Books.xml")
XmlNodeList nodes = doc.DocumentElement.SelectNodes("/catalog/book");

Open in new window

I am able to get the correct nodes.count.

2. when using an actual URL:

doc.Load("http://www.sec.gov/Archives/edgar/data/850693/000119312511050632/agn-20101231.xml");
var nodes = doc.SelectSingleNode("/context");

Open in new window

or
XmlNodeList nodes = doc.DocumentElement.SelectNodes("/xbrl/context");

Open in new window

I got a null nodes.count. Have tried several permutations for the xpath  i.e. //xbrl/context etc but not able to achieve a proper extraction.
Try this:

XmlNodeList nodes = doc.DocumentElement.SelectNodes("//context");

This will get you the <context> element no matter where it is in the hierarchy.
Ran the code:
XmlDocument doc = new XmlDocument();
doc.Load("http://www.sec.gov/Archives/edgar/data/850693/000119312511050632/agn-20101231.xml");
XmlNodeList nodes = doc.DocumentElement.SelectNodes("//context");

Open in new window


I still get:
?nodes.Count
0
ASKER CERTIFIED SOLUTION
Avatar of Bob Learned
Bob Learned
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial