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

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.
0
rye004
Asked:
rye004
  • 3
1 Solution
 
theo kouwenhovenCommented:
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
 
theo kouwenhovenCommented:
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
 
theo kouwenhovenCommented:
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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