Solved

Use a generic XSD to populuate a database from XML

Posted on 2012-04-04
6
471 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
  • 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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
split XML field into many fields from MS Access 21 44
Programming Codes 2 22
Append data to an XML file 3 16
XML Node Counting in MS Access VBA 4 36
Read about achieving the basic levels of HRIS security in the workplace.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

828 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