Solved

Use a generic XSD to populuate a database from XML

Posted on 2012-04-04
6
446 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
You are quite welcome :)
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
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…
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…

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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now