Solved

Use a generic XSD to populuate a database from XML

Posted on 2012-04-04
6
486 Views
Last Modified: 2012-04-24
I am not very good at XML or XSD but trying to understand it better and looking for an adhoc solution to population of a database.  I have an XML File that sets the tablename, primary field, and some additional fields for a table with values.  What I need is to replace a current XSD in a program (that knows the database schema) with otherXSD that will take the new xml file and populate a database based on the what is found in the XML (table name and fields) File and not pre-determined in the XSD File.

In the code the entity type is table and in this case UNITS, keyname is the primary key field and there is a description field.

Am I barking up a bad tree here or is it possible to have a generic XSD that reads an XML and will load in a data set of records.

<?xml version='1.0' encoding='ISO-8859-1' ?>
<Job xmlns='DRAFT'
     xmlns:xsi = 'http://www.w3.org/2001/XMLSchema-instance'
     xmlns:xsd = 'http://www.w3.org/2001/XMLSchema'
     name='Batch Load'
     isTransactional='true'>

   <SimpleQuery name='Create Units' workflow='Create'>
      <EntityList>
         <Entity type='units' keyname='g'>
            <Field name='description'>grams</Field>
         </Entity>
         <Entity type='units' keyname='kg'>
            <Field name='description'>kilograms</Field>
         </Entity>
      </EntityList>
   </SimpleQuery>

</Job>
0
Comment
Question by:gilnari
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 23

Expert Comment

by:Christopher Kile
ID: 37814010
I'm having a little trouble understanding exactly what you want.  It sounds like you want to know if there is a way to use an XSD schema to enable the import of an XML file into a data structure suitable for writing into a database.  The answer is, yes.  In NET 1.1, the DataSet object had methods .ReadXMLSchema() and .ReadXML().  The schema reading function created tables and relationships as defined in the schema, and the XML function populated those tables with whatever was in the XML file.  A XMLValidatingReader() could be used to validate that the XML file followed the schema prior to your attempt at import, making it easier to identify discrecpancies between your XML and your XSD.  Does this sound like what you want to do?
0
 

Author Comment

by:gilnari
ID: 37824388
Do you have example as laid out in NET 1.1?  But essential that is what I am looking for an example of how these combination works.  With that I should be able to break it down to what I need.
0
 
LVL 23

Accepted Solution

by:
Christopher Kile earned 500 total points
ID: 37853367
www.w3schools.comThis is the code that I used for loading and validating the XML for my project:

            Dim beforeXML As XmlDocument = New XmlDocument
            Dim beforeXMLReader As XmlTextReader = New XmlTextReader(urlBeforeXML)
            Dim beforeXMLValidatingReader As XmlValidatingReader = New XmlValidatingReader(beforeXMLReader)

            Dim afterXML As XmlDocument = New XmlDocument
            Dim afterXMLReader As XmlTextReader = New XmlTextReader(urlAfterXML)
            Dim afterXMLValidatingReader As XmlValidatingReader = New XmlValidatingReader(afterXMLReader)

            Dim xsc As New XmlSchemaCollection
            xsc.Add(urnTag, urlSchema) 'XSD schema

            afterXMLValidatingReader.ValidationType = ValidationType.Schema
            AddHandler afterXMLValidatingReader.ValidationEventHandler, AddressOf ValidationCallBack
            afterXMLValidatingReader.Schemas.Add(xsc)

            m_success = True
            While afterXMLValidatingReader.Read()
            End While
            afterXMLValidatingReader.Close()

            beforeXMLValidatingReader.ValidationType = ValidationType.Schema
            AddHandler beforeXMLValidatingReader.ValidationEventHandler, AddressOf ValidationCallBack
            beforeXMLValidatingReader.Schemas.Add(xsc)

            m_success = True
            While beforeXMLValidatingReader.Read()
            End While
            beforeXMLValidatingReader.Close()


            Dim dsBefore As New DataSet
            Dim dsAfter As New DataSet
            Dim schemaXMLReader As XmlTextReader = New XmlTextReader(urlSchema)

            dsBefore.ReadXmlSchema(New XmlTextReader(urlSchema))
            beforeXMLReader = New XmlTextReader(urlBeforeXML)
            dsBefore.ReadXml(beforeXMLReader)
            dsBefore.AcceptChanges()
            beforeXMLReader.Close()


            dsAfter.ReadXmlSchema(New XmlTextReader(urlSchema))
            afterXMLReader = New XmlTextReader(urlAfterXML)
            dsAfter.ReadXml(afterXMLReader)
            dsAfter.AcceptChanges()
            afterXMLReader.Close()

Open in new window


This is the code for the handler for validation errors
        Private Shared Sub ValidationCallBack(ByVal sender As Object, ByVal args As ValidationEventArgs)
            m_success = False
        End Sub 'ValidationCallBack

Open in new window


Establishing your XSD grammar will be the most difficult part.  I recommend the XSD tutorial at www.w3schools.com
0
 

Author Closing Comment

by:gilnari
ID: 37887692
That is exactly what I need to get my mind around what I am trying to accomplish.  Thank you
0
 
LVL 23

Expert Comment

by:Christopher Kile
ID: 37887704
You are quite welcome :)
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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question