Data Transfer from XML File to Database by Using DTD Format.

[Former Member]

Data Transfer from XML file to Database by using DTD

Introduction:-

In this scenario we are transferring data from XML file to database by using Data Type Definition.

We are not covering the creation of DTD file in this scenario.

I) Creating DataStore for SQL database.

1. Logon to the SAP Business Object Data Designer.
2. In Local Object Library click on Datastore Tab.

   3. RIght click in the Local Object Library area & select "New".

      Window for creating new datastore will open.

            GIve all the details.

            DataStore Name:- any name you want to give.

            DataStore Type:- Database.

            Database Type:- here we are using Microsoft SQL Server

            Database version:- Microsoft SQL Server 2008

            Database Server Name:- server name

            User name & Password:- Details about User name & password.

  4.Click "OK" & the Datastore will be created & can be seen in the Local Object Library.

II) Creation of DTD File Format:-

      We are creating dtd file for the following type of xml file.

DTD File created for the above xml file is as follows

<?xml encoding="utf-8"?>

<!ELEMENT Employee (Details+)>

<!ELEMENT Details (Id, Name, Designation, Dept, Age, Address)>

<!ELEMENT Id (#PCDATA)>

<!ELEMENT Name (#PCDATA)>

<!ELEMENT Designation (#PCDATA)>

<!ELEMENT Dept (#PCDATA)>

<!ELEMENT Age (#PCDATA)>

<!ELEMENT Address (#PCDATA)>

1) Creating DTD File Format.

• Select Format tab from Local Object Library, right click on "DTD's" & select "New".
• "Import DTD Format" window appears.

      Give all required details.

• After filling all the information click "OK".
• Now XML Schema file format will be created & you can see it in the Local Object Library

III) Creation of  a job in Data Service Designer.

1. Create a project.

• Go to the "Project Area" in the Data Service Designer & click the icon for creating the project from menu bar.

  2. Create a Batch Job.

• Right click on the project & click on "New Batch job".
• Give appropriate name to the job.

  3.  Add a dataflow into the job.

• Select the job, drag dataflow from palette into it & name it.

  4. Build the scenario in the dataflow.

• Double click on the dataflow.
• Drag  DTD format created earlier into the dataflow & mark it as a "Make XML File Source".

• Add details to the DTD Format
• Double click on DTD Format & provide the name of the source xml file.
• Enable Validation to validate the xml file with provided DTD format.

• Drag a query into the dataflow & connect it to DTD format.

5. Open the query & do Mapping.

6. Inserting  target table.

   We can either import the already created table or we can use template table which           afterwards will actually be created in the database.

   We are using  template table here

• Drag a template table from palette into the dataflow.
• Give name to the table & select appropriate datastore.

• Click on "OK".
• Template table can be seen in the dataflow.
• Connect Template table to Query.

7. Save the Job & Validate it.

• Click "Validate All" icon from the tool bar.

• Following message is displayed if no error found in the job.

9. Execute the Job.

• Select the job, right click & press "Execute".

10. Check the output in the table.

• Click on the Magnifying Glass icon of the table.