Skip to Content

Steps:

1.Go to local object library in data services designer. Click Formats -> XML Schemas

       /wp-content/uploads/2013/04/1_202873.png

2.Right click on XML schemas and click on new

     /wp-content/uploads/2013/04/2_202878.png

3.  A new window pops up. Give the desired format name under the tab Format name

     /wp-content/uploads/2013/04/3_202879.png

1. 4.We have to browse the xml schema file. Files in .XSD format have to be used. For this we can use any tools available to convert a .xml file  into .xsd file.

The content of xml file used in this example is

             /wp-content/uploads/2013/04/4_202888.png

After converting to .XSD file the contents becomes as given below

      /wp-content/uploads/2013/04/5_202892.png

5. Import the xml schema.

     /wp-content/uploads/2013/04/6_202893.png

1.  6. If the file imported is not proper (format issue),then an error will be thrown while importing.

For E.g.:

   /wp-content/uploads/2013/04/7_202894.png

     Rectify the error and import the proper xml schema.

7.After importing successfully give the corresponding Root element name from the drop down.

      /wp-content/uploads/2013/04/8_202895.png

1.   8.Click Ok.

The imported schema will be found under XML Schemas in local object library.

      /wp-content/uploads/2013/04/9_202896.png

To report this post you need to login first.

17 Comments

You must be Logged on to comment or reply to a post.

      1. Balakrishna P

        Convert your xml file to XSD(check online for converters/tools) and follow the steps mentioned in the above document to create XML Schema in BODS.

        (0) 
    1. Varsha Sonik

      Hey Bala, i  took a sample example same as my client  requirement..m getting error..i have attached the screenshots plz help me out..problem with Mapping..

       

      Error1.png

      (0) 
      1. Varsha Sonik

        [Query:Query]

        Invalid mapping expression for column <Query.orderid>. Additional information: <Cannot parse expression.

         

         

        Table <Delivery1_no.XML_file_test> for column <orderid> does not occur in the FROM clause. For a top-level query, all columns must belong to some table in the FROM clause.. (BODI-1112351)>. (BODI-1111081)

         

         

        Plz anyone help me

        (0) 
        1. Varsha Sonik

          Order Id is equal and common for both.that’s wy i used that Mapping Expresssing,,,if m  removing = ,symbol then below mentioned error.

           

          [Query:Query]

          Not all the tables are joined. (BODI-1110413)

          (0) 
          1. Venkata Ramana Paidi

            Hi Varsha,

             

            Your image looks like you are extracting the data from single nested xml file and loading into the table.

             

            I need few clarifications on your flow.

             

            1. If it is single xml why you used two xml transfer transforms?  You can use single xml transfer transform and drag the required columns. The extraction from xml is not that much difficult compare to loading data into nested xml file from tables/files.

             

            2.  I will tell the reason for the above errors and scenario.

             

            3.  If you are going to load the data into nested xml file by using two(more) source tables/files, we may get the error like doesn’t occur in the from clause and check from clause etc. Then  you have to check in the from clause like  all tables are selected or not.  If one of the table is not selected but one/few field(s) from the unselected table mapped to target , you will face this issue.

             

             

            4. When you are loading into the nested target table we have to careful in the joins .  For example if you want to join header and line items tables  and load into target means , You have to give the join condition under line item schema not under header schema . For give the join under line item you have to make line item schema as current schema.

             

             

            Thanks & Regards,

            Ramana

            (0) 
              1. Venkata Ramana Paidi

                Hi Varsha,

                 

                I will explain through one scenario from table/file to nested xml and nested xml to table/file.

                 

                I am taking simple source tables EMP & DEPT tables .  my target is nested xml looks like DEPTNO,DNAME,LOC. If you click on DEPTNAME or DEPTNO  we have to list out the employees in the corresponding department.

                 

                So let’s assume the query is nested like


                Query

                   +- DEPT

                        DEPTNO

                        DNAME

                        LOC

                     +- EMP

                          EMPNO

                          ENAME

                          SAL

                          DEPTNO

                .



                For each schema you have to ask yourself “How many elements do I want to have?”


                and dept has the dept columns plus the emp schema, emp has the emp columns.
                 
                This query has as inputs three sources, the EMP, DEPT table plus a Row_Generation transform generating one row.
                 
                How many repeats of the XML root nodes do you want to have? One obviously, an XML has to have one and only one root node. So we make the Query the current and modify the FROM clause so that only the Row_gen is listed.
                How many times do we want to see the DEPT tag? Once per DEPT row. So we make the DEPT schema the current and modify the FROM clause to be DEPT only. Now all columns within the DEPT schema can be set to values coming from the DEPT input table (plus the parent Row_Gen table but we don’t need that).
                Same thing for the EMP schema. How many EMP tags do we want to see inside each DEPT? As many EMPs as the department has. Hence we make that schema the current, the FROM clause is set to EMP only and we need a where clause for this schema: EMP.DEPTNO = DEPT.DEPTNO.
                  

                 

                 

                 

                 

                 

                For QT_GET  , from clause we have to select Row_Generation only . Because we need QT_GET only once  it is just output schema.

                 

                 

                For DEPT schema( right click make as current) we need to select DEPT table only . We need all the departments should be occur .

                 

                 

                In the EMP schema ( right click make as current)  from clause should be  from EMP only as we are taking fields from EMP table.

                 

                 

                Below screenshot for nesting  as my target schema.

                 

                 

                 

                Output is like this .

                 

                 

                Because of Row_generation it occurred only once not many as no of departments.

                 

                Departments occurred based of no of departments only not join with EMP as we selected only DEPT in the from clause.

                 

                 

                If we click on the EMP schema of department 10 we can see the above result.  This is because of join condition between DEPT & EMP and from clause only EMP .  If we select DEPT also it causes Cartesian product and we will not get desired result.

                 

                I hope you understand the nesting from flat structures to nested structures.

                 

                Thanks & Regards,

                Ramana.

                (0) 
              2. Venkata Ramana Paidi

                Hi Neha,

                 

                In my previous post I explained about nesting as i told told it is some how expensive. In this I am replying for your question from loading data from xml to flat structures like tables/files.

                 

                I am taking the output of the previous example as source here .

                 

                 

                See the input data of the xml .

                 

                 

                Here we can approach two ways to do un-nesting . one is xml pipe line is straight forward  and second one using query transform .

                 

                1. XML pipeline .

                 

                 

                Drag the required columns to the output schema  from input schema. It is very striaght forward.

                 

                2. Query transform .

                 

                 

                Drag the root schema(QT_GET_nt_1) as shown in the above picture to the output schema.

                 

                Then right click on the root schema(QT_GET_nt_1) and select the option Unnest with sub-schemas. Then you will see arrow symbol on that schema and child schema also as shown in the screenshot.

                 

                 

                You can see schema is un-nested , we can directly drag the required fields.

                 

                Here if we want to load different tables then use different query transforms like DEPT & EMP as shown in the 1st screenshot  and drag the required fields for the table.

                 

                Here DEPT table is master table while dragging fields we have to use distinct as it was combination of EMP & DEPT data .

                 

                 

                 

                I hope you will understand this .

                 

                Thanks & Regards,

                Ramana.

                (0) 
                  1. Varsha Sonik

                    Hi, Venkata


                    Please help me out.
                    Input


                    Atlas_Id                                                Sr_id
                    X/P01/ZOOMAR UK                      Zoomarine -One of it’s Kind%Ticket Only


                    Output
                    Field1           Field2                     Filed3                                              Sr_id
                    X                     PO1                ZOOMAR UK                Zoomarine One of its Kind Ticket Only


                    (0) 
                    1. Venkata Ramana Paidi

                      Hi Varsha,

                       

                      I am not clear about your question.  You are asking related to XML or logic for conversion from input to output.

                       

                      In your input you have two fields and you are splitting first field into 3 fields based on delimiter “/”.  You can use word_ext function to splitting the single column into multiple columns based on delimiter.

                       

                      Field1= word_ext(Atls_Id,1,’/’);

                      Field2= word_ext(Atls_Id,2,’/’);

                      Field3= word_ext(Atls_Id,3,’/’);

                       

                      Thanks & Regards,

                      Ramana.

                      (0) 

Leave a Reply