Extracting Data from XML file

Question

Post reply

Extracting Data from XML file

Jobs90312

Mr or Mrs. 500

Points: 533

May 5, 2023 at 11:57 am

#4184715

Hi all,

I have a XML file which consists this type of data:

<CONSOLIDATED_LIST>
<INDIVIDUALS>
<INDIVIDUAL>
<DATAID>12345</DATAID>
<VERSIONNUM>1</VERSIONNUM>
<FIRST_NAME>ABC</FIRST_NAME>
<SECOND_NAME>XYZ</SECOND_NAME>
<THIRD_NAME/>
<UN_LIST_TYPE>TEST TYPE</UN_LIST_TYPE>
<REFERENCE_NUMBER>TEST REFNUM</REFERENCE_NUMBER>
<LISTED_ON>2016-11-30</LISTED_ON>
<COMMENTS1>TESTCOMMENT
</COMMENTS1>
<DESIGNATION>
<VALUE>TEST DESIGN</VALUE>
</DESIGNATION>
<NATIONALITY>
<VALUE>TEST NAT</VALUE>
</NATIONALITY>
<LIST_TYPE>
<VALUE>TEST TYPE</VALUE>
</LIST_TYPE>
<LAST_DAY_UPDATED>
<VALUE/>
</LAST_DAY_UPDATED>
<INDIVIDUAL_ALIAS>
<QUALITY/>
<ALIAS_NAME/>
</INDIVIDUAL_ALIAS>
<INDIVIDUAL_ADDRESS>
<COUNTRY/>
</INDIVIDUAL_ADDRESS>
<INDIVIDUAL_DATE_OF_BIRTH>
<TYPE_OF_DATE>EXACT</TYPE_OF_DATE>
<DATE>1964-07-17</DATE>
</INDIVIDUAL_DATE_OF_BIRTH>
<INDIVIDUAL_PLACE_OF_BIRTH/>
<INDIVIDUAL_DOCUMENT>
<TYPE_OF_DOCUMENT>Passport</TYPE_OF_DOCUMENT>
<NUMBER> 381310014</NUMBER>
</INDIVIDUAL_DOCUMENT>
<SORT_KEY/>
<SORT_KEY_LAST_MOD/>
</INDIVIDUAL>
</INDIVIDUALS>
</CONSOLIDATED_LIST>

I am trying to read this data for importing it to my SQL tables.

I have written below C# code:

        public void Main()
        {
try
            {
                XmlTextReader xtr_20230503 = new XmlTextReader(Dts.Variables["User::v000373_source_variable_20230405"].Value.ToString());
                while (xtr_20230503.Read())
                {
                    if (xtr_20230503.NodeType == XmlNodeType.Element && (xtr_20230503.Name == "DATAID" || xtr_20230503.Name == "VALUE"))
                    {
                        MessageBox.Show(xtr_20230503.ReadElementString());
                    }
                }

            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                throw new NullReferenceException("FAILED");
            }

            Dts.TaskResult = (int)ScriptResults.Success;
}

Above code is able to access data from the last sub node for example

<CONSOLIDATED_LIST>

<DATAID>

OR

<CONSOLIDATED_LIST>

<VALUE>

OR

<CONSOLIDATED_LIST>

<VALUE>

But I am trying to figure out how can I access data by actually providing the specific node like from Designation/Value or List_Type/Value

It is only fetching data from Value nodes of each parent's node.

Is there any other simpler method available to extract data from XML files using C#?

Regards

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246944 More actions · Answer 1

As you've posted in a SQL Server forum, are you looking for a T-SQL solution?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Dennis Jensen SSChasing Mays Points: 604 More actions · Answer 2

I am not sure if this will help any but LMGTFY got the following item that might prove useful. However, as Phil Parkin pointed out this is an SQL forum and what you are wanting, it seems, is the how to do it in C#. Just note there are numerous folks out there that have already created the XML Wheel -- so yeah there is most likely no need to reinvent it -- just find the Wheel that best fits your vehicle and use it and/or tweak it if necessary.

https://urda.com/blog/2010/08/30/extracting-information-from-xml-with-csharp#:~:text=C%23%20has%20methods%20built%20in%20that%20can%20read,XML%20file%20in%20C%23%20for%20you%20to%20see.

This reply was modified 1 year, 6 months ago by Dennis Jensen. Reason: fix typos
This reply was modified 1 year, 6 months ago by Dennis Jensen. Reason: fix typos

Jobs90312 Mr or Mrs. 500 Points: 533 More actions · Answer 3

Dennis Jensen wrote:

I am not sure if this will help any but LMGTFY got the following item that might prove useful. However, as Phil Parkin pointed out this is an SQL forum and what you are wanting, it seems, is the how to do it in C#. Just note there are numerous folks out there that have already created the XML Wheel -- so yeah there is most likely no need to reinvent it -- just find the Wheel that best fits your vehicle and use it and/or tweak it if necessary.

https://urda.com/blog/2010/08/30/extracting-information-from-xml-with-csharp#:~:text=C%23%20has%20methods%20built%20in%20that%20can%20read,XML%20file%20in%20C%23%20for%20you%20to%20see.%5B/quote%5D

Thank you for providing the link, it is really helpful. I have few queries though.

The XML file or data there looks like this:

<?xml version="1.0" encoding="utf-8" ?>
<People>
  <Person>
    <FirstName>Peter</FirstName>
    <LastName>Urda</LastName>
    <Age>21</Age>
    <Gender>M</Gender>
  </Person>
  <Person>
    <FirstName>Joe</FirstName>
    <LastName>White</LastName>
    <Age>30</Age>
    <Gender>M</Gender>
  </Person>
</People>

But if for example data looks like this:

<?xml version="1.0" encoding="utf-8" ?>
<People>
  <Person>
    <FirstName>Peter</FirstName>
    <Age>21</Age>
    <Gender>M</Gender>
  </Person>
  <Person>
    <FirstName>Joe</FirstName>
    <LastName>White</LastName>
  </Person>
</People>

Basically LastName tag from first block and Age and Gender tags from second block is not provided or missing, then the code on that page gives error "object reference not set to an instance".

How can I bypass this?