Direction on normalizing XML data

I am hoping to find that someone can give me direction with figuring out this problem.  I am sure there are multiple ways to accomplish this.  I am trying to do this in C# and any direction to any native or third party libraries would be appreciated.
I have thousands of XML files that contain data for documents.  Below is a sample of what I have.  What I am looking to do is take the thousands of XML files and create a delimited file that can be loaded into Excel.  I will also need to develop some type of method to create a trumping order of what meta data to use for each record.

<METADATA>
<NAME>From</NAME>
<VALUE>ASDF@ASDF.COM</VALUE>
</METADATA>

<METADATA>
<NAME>To</NAME>
<VALUE>asdf@asdf.com</VALUE>
</METADATA>

<METADATA>
<NAME>DateSent</NAME>
<VALUE>1/1/1900</VALUE>
</METADATA>

<METADATA>
<NAME>DateCreated</NAME>
<VALUE>1/1/1900</VALUE>
</METADATA>

<METADATA>
<NAME>Author</NAME>
<VALUE>Mr. Smith</VALUE>
</METADATA>

What I would like to do is build some type of XML file that indicates how this should be formatted.  Below is what I have in mind, this would reference the XML above.

<metadata>
<name>date the object was created</name>
<order>
<metadataname> DateCreated</metadataname>
<metadataname> DateSent </metadataname>
</order>
</metadata>

<metadata>
<name>who made this document</name>
<order>
<metadataname>Author </metadataname>
<metadataname> From </metadataname>
</order>
</metadata>

Hopefully this makes sense.  Any direction or ideas would be greatly appreciated.
rye004Asked:
Who is Participating?
 
MurpheyConnect With a Mentor Application ConsultantCommented:
Hi,

Th format of an XML can be created in a XSD, here you can define the datatype, length, required yes/no.  You can define sub structures end if they have ocurrences and more.

See example below, and moer eplanation on http://www.w3schools.com/schema/schema_example.asp

<?xml version="1.0" encoding="ISO-8859-1" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">

<!-- definition of simple elements -->
<xs:element name="orderperson" type="xs:string"/>
<xs:element name="name" type="xs:string"/>
<xs:element name="address" type="xs:string"/>
<xs:element name="city" type="xs:string"/>
<xs:element name="country" type="xs:string"/>
<xs:element name="title" type="xs:string"/>
<xs:element name="note" type="xs:string"/>
<xs:element name="quantity" type="xs:positiveInteger"/>
<xs:element name="price" type="xs:decimal"/>

<!-- definition of attributes -->
<xs:attribute name="orderid" type="xs:string"/>

<!-- definition of complex elements -->
<xs:element name="shipto">
  <xs:complexType>
    <xs:sequence>
      <xs:element ref="name"/>
      <xs:element ref="address"/>
      <xs:element ref="city"/>
      <xs:element ref="country"/>
    </xs:sequence>
  </xs:complexType>
</xs:element>

<xs:element name="item">
  <xs:complexType>
    <xs:sequence>
      <xs:element ref="title"/>
      <xs:element ref="note" minOccurs="0"/>
      <xs:element ref="quantity"/>
      <xs:element ref="price"/>
    </xs:sequence>
  </xs:complexType>
</xs:element>

<xs:element name="shiporder">
  <xs:complexType>
    <xs:sequence>
      <xs:element ref="orderperson"/>
      <xs:element ref="shipto"/>
      <xs:element ref="item" maxOccurs="unbounded"/>
    </xs:sequence>
    <xs:attribute ref="orderid" use="required"/>
  </xs:complexType>
</xs:element>

</xs:schema>

Open in new window

0
 
MurpheyApplication ConsultantCommented:
BTW, You can also work with attributes:

<METADATA Type="From">ASDF@ASDF.COM</METADATA>
<METADATA  Type="To">asdf@asdf.com</METADATA>
<METADATA Type ="DateSent">1/1/1900</METADATA>
<METADATA Type="DateCreated">1/1/1900</METADATA>
<METADATA Type="Author">Mr. Smith</METADATA>
0
 
rye004Author Commented:
This is very helpful and looks like it should work.  Thanks!
0
 
MurpheyApplication ConsultantCommented:
Hi rye004,

When you are able to use a validate function, it is very useful.
I use the XSD to generate my XML examples and to validate the incoming XML data.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.