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

[Former Member]

Data Transfer from XML file to Database by using XSD format

Introduction:-

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

We are not covering the creation of XSD 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 XML Schemas File Format:-

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

     XSD file created for the above xml file is as follows.

1. Creating XML Schema File Format.

• Select Format tab from Local Object Library, right click on "XML Schemas" & select "New".
• "Import XML Schema Format" window appears.

             Give all required details.

         Format Name:- name of the file format(any name)

         File Name:- full path of the xsd file.

         Root Element Name:- Root element name of the xml file(here it is Employee)

• 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 Project

      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 XML Schema format created earlier into the dataflow & mark it as a "Make XML File Source".

• Add details to the XML File Format.

             Double click on File Format & provide the name of the source xml file.

             Enable Validation to validate the xml file with provided XSD format.

              

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

  5. Open the query & do Mapping.

• Select "Employee_nt_1" node & drag it to RHS.

• Right click on "Employee_nt_1" node & UnNest it.

        We are un nesting the nodes because we don't want them to be made available at the target side.

• Right click on the "ID" field & mark it as a Primary Key.

  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.

  8. Execute the job.

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

  9. Check the output in the table.

• Click on the Magnifying Glass icon of the table.