I need to parse a XML response in C# and load in SQL. Just to brief,i know how to use XMLSerializer to parse the xml, so that is not i am looking for. My concern is my XML structure which i received from from web request. Below is the subset of xml, i received from xml
<apiXML>
<recordList>
<record id="31" >
<administration_name>admin1</administration_name>
<creator>Leekha, Mohit</creator>
<object_category>painting</object_category>
<object_number>1243</object_number>
<id>31</id>
<reproduction.reference>2458.jpg</reproduction.reference>
<title lang="nl-NL" invariant="false">The Title1</title>
<title lang="nl-NL" invariant="false">The Title</title>
<title lang="nl-NL" invariant="false">Different Title</title>
</record>
<record id="32" >
<administration_name>admin1</administration_name>
<creator>Leekha, Mohit</creator>
<object_category>painting</object_category>
<object_number>AL1111</object_number>
<id>32</id>
<reproduction.reference>AL1111.jpg</reproduction.reference>
<title lang="nl-NL" invariant="false">Health</title>
</record>
<record id="34" >
<administration_name>admin2</administration_name>
<creator>Leekha,Mohit</creator>
<creator>System</creator>
<object_category>earthenware</object_category>
<object_category>ABC</object_category>
<object_category>Remote</object_category>
<object_number>Z.567 & X-124</object_number>
<id>34</id>
<reproduction.reference>Z.567 & X-124(1).jpg</reproduction.reference>
<reproduction.reference>Z.567 & X-124(2).jpg</reproduction.reference>
<reproduction.reference>Z.567 & X-124(3).jpg</reproduction.reference>
</record>
</recordList>
</apiXML>
My Concerns:
- Some records have multiple data members with same name. Like record id 31 has 3 titles
- Number of columns are different for each record.
So all i am asking is for suggestions how could i deal with scenario. Any suggestions are welcome
You'll need a couple of supporting classes to get that XML deserialized as is, as you didn't specify any other requirements.
Your database wold have tables for your record elements and all the collections within.
Serialization classes
Those classes will hold an in memory representation of your XML. At the root will be the
Api
class.Deserialization
With the classes correctly annotated deserializing the XML only needs a couple of lines:
Processing and building up tables
In the variable
api
we now have the in-memory object graph which you can project on a relational database schema. For normalized model you'll need the following tables:Between these tables you'll need link tables that follow all the same convention like the one between Record and Creator:
I'll assume you know how to create those tables and create a connection to your database.
That concludes the code and structure you'll need to store that SQL in an RDBMS database without losing information.