8 Replies Latest reply: Apr 4, 2014 4:02 AM by Swapnil K RSS

    How to embed dynamic generated SQL in XML file of Data Definition???

    Swapnil K

      Hi All,

       

      I have below XML file which is attached to RTF template data definition as below. Also, I have a funcion which will return to create dynamic query as given below.

       

      <?xml version="1.0" encoding="UTF-8"?> 
      <dataTemplate name="XYZ" description="iExpenses Report" Version="1.0"> 
      <dataQuery> 
      <sqlStatement name="Q2"> 
      <![CDATA[
      
      %%%%%%%%%%%%%%%%%%%%%%%%%%%% I want to insert dynamically generated SQL here as is %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
      ]]> 
      </sqlStatement> 
      </dataQuery> 
      <dataStructure> 
      <group name="G_my_table" source="Q2"> 
        <element name="Name" value="Name" /> 
        <element name="Age" value="Age" /> 
        <element name="Phone" value="Phone" /> 
      </group> 
      </dataStructure> 
      </dataTemplate>
      

       

      Select Name,Age,Phone from My_table;
      

       

      Now I want to combine both of them as below. I dont know how can I embed SQL AS IS(maybe by a function call?)

       

      <?xml version="1.0" encoding="UTF-8"?>  
      <dataTemplate name="XYZ" description="iExpenses Report" Version="1.0">  
      <dataQuery>  
      <sqlStatement name="Q2">  
      <![CDATA[ 
      Select Name,Age,Phone from My_table; <<<<<<<<<<<<<<<<<<<<<<<<< This SQL will be derived dynamically. The number of columns in the SELECT clause will change. How to I place it here AS IS?
      ]]>  
      </sqlStatement>  
      </dataQuery>  
      <dataStructure>  
      <group name="G_my_table" source="Q2">  
        <element name="Name" value="Name" />  
        <element name="Age" value="Age" />  
        <element name="Phone" value="Phone" />  
      </group>  
      </dataStructure>  
      </dataTemplate>
      
        • 1. Re: How to embed dynamic generated SQL in XML file of Data Definition???
          Swapnil K

          Hi Guys,

           

           

          I was able to resolve above requirement. It was not possible to create only above SQL dynamically. Instead, I created a procedure which would create & update the XML (attached to Data Definition) dynamically for every run.

           

          Regards,

          Swapnil K.

          • 2. Re: How to embed dynamic generated SQL in XML file of Data Definition???
            AlexAnd

            for using some dynamic action look at Lexical References in Building a Data Template&lt;/title&gt;&lt;meta http-equiv=&quot;Content-Type&quot; content=&quot;text/html; charset=us-a…

             

            if you use one SqlStatement section then you don't need to use DataStructure section

            all columns from selection will be in output

            so you need only dynamic part for columns list

             

            of course where may be some limitations based on your logic and what is "dynamic" in your case

            Instead, I created a procedure which would create & update the XML (attached to Data Definition) dynamically for every run.

            plz share solution with more details for all

            imho it's not good way

            • 3. Re: How to embed dynamic generated SQL in XML file of Data Definition???
              Swapnil K

              Hi Alex,

               

              See below thread to understand my complex requirement.

              How to create a dynamic RTF report which creates dynamic columns based on dynamic column selection from a table?

               

              My problem was that the number of columns in the SELECT clause was expected to change whenever any new column was added/deleted from the table my_table. I had to change this SQL query in <dataquery> and also create the corresponding elements below it dynamically. So I used dynamic XML creation approach by creating XML using a procedure. It is finally working now with no issues.

               

              Regards,

              Swapnil K.

              • 4. Re: How to embed dynamic generated SQL in XML file of Data Definition???
                AlexAnd

                hi,

                 

                1. <sqlStatement name="Q2"> 
                2. <![CDATA[
                3. SELECT Name
                4. ,Age
                5. ,Phone
                6. FROM my_table
                7. ]]> 
                8. </sqlStatement> 

                and

                 

                1. <sqlStatement name="Q2"> 
                2. <![CDATA[
                3. SELECT Name
                4. ,Age
                5. ,Phone
                6. ,Address
                7. ,Salary
                8. FROM my_table
                9. ]]> 
                10. </sqlStatement> 

                can be union as

                <sqlStatement name="Q2"> 

                <![CDATA[

                SELECT &column_list

                FROM my_table

                ]]> 

                </sqlStatement>

                and so

                if you use one SqlStatement section then you don't need to use DataStructure section

                all columns from selection will be in output

                so you need only dynamic part for columns list

                 

                the question is how template will accept dynamic number columns

                if number and names of columns only defined set then you can rely on names and template

                if number and names of columns then you can't

                you must create dynamic template also (may be xslt template)

                 

                so your approach is

                I created a procedure which would create & update the XML (attached to Data Definition) dynamically for every run. This dynamic XML contains SQL statement for data query which is built dynamically. I am then updating this XML using XDOLoader utility to the Data Definition. Then I am running my custom program to generate the excel output.

                so in one concurrent you use shell for updating xml data template by xdoloader

                and in second one to run report

                but what about template? is it dynamic or contain all max set of columns ?

                • 5. Re: How to embed dynamic generated SQL in XML file of Data Definition???
                  Swapnil K

                  Hi,

                   

                  I built the template "dynamically" . In select query I used total 100 columns buffer. If on day1, 3 columns are fetched then rest97 columns are added after it in select and element with value as "Blank". On Day2, it it will be 5 columns and 95 Blank columns. I added alias like column1 column2...column100 which would remain constant irrespective of number of dynamic columns fetched and placed their fixed tags in template.


                  Dynamic SQL Query built on Day2.
                  SELECT Name column1
                  ,Age column2
                  ,Phone column3
                  ,Address column4
                  ,Salary column5
                  ,Null column6
                  .
                  .
                  .
                  ,Null column100
                  
                  FROM my_table;
                  

                  And in template added the condition, if the @column1 != 'Blank' then display the column else hide the entire column. this way it will on day1 only show 3 columns column1 column2 column3 and hide rest 97 columns as their value is Blank!

                  On how I displayed the columns header name dynamically instead of column1 column2 in the report, its a complete different story.

                   

                  Regards,

                  Swapnil K.

                  • 6. Re: How to embed dynamic generated SQL in XML file of Data Definition???
                    AlexAnd

                    hehe

                     

                     

                    different story.

                    go go

                     

                    very interesting report

                    plz post the essay after all for forum's readers

                    • 7. Re: How to embed dynamic generated SQL in XML file of Data Definition???
                      user13329734

                      hi, I want to create a store Procedure to create XML file.

                      Need to create one generic stored procedure which will take table name as input parameter and create xml file with below format.

                      Xml file name should be table name.

                       

                      <XML>
                      <TABLENAME></TABLENAME>
                      <RECORDS>
                      <RECORD>
                                <COLNAME>AAA</COLNAME>
                                <COLNAME>AAA</COLNAME>
                      <RECORD>
                      <RECORD>
                                <COLNAME>AAA</COLNAME>
                                <COLNAME>AAA</COLNAME>
                      <RECORD>
                      <RECORD>
                                <COLNAME>AAA</COLNAME>
                                <COLNAME>AAA</COLNAME>
                      <RECORD>
                      <RECORD>
                                <COLNAME>AAA</COLNAME>
                                <COLNAME>AAA</COLNAME>
                      <RECORD>
                      <RECORD>
                                <COLNAME>AAA</COLNAME>
                                <COLNAME>AAA</COLNAME>
                      <RECORD>
                      </RECORDS>
                      </XML>
                       

                       

                      <TABLENAME>  = actual table name(case should be same as in database)

                      <COLNAME> = actual column names from table(case should be same as in database)

                      Keep other tags as it is. Repeat <RECORD> tag for each row retrieved.

                      • 8. Re: How to embed dynamic generated SQL in XML file of Data Definition???
                        Swapnil K

                        Hi

                         

                         

                        Did you resolve this??

                         

                        Regards,

                        Swapnil K.