?
Solved

Use a generic XSD to populuate a database from XML

Posted on 2012-04-04
6
Medium Priority
?
490 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 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Simple Linear Regression

770 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