14 Replies Latest reply on Apr 29, 2008 1:15 PM by (Danny_Marandola)

    Using  Query Multiple to XML

    Level 1
      Hi,

      I am trying to use the service Query Multiple to XML which Queries the database using a SQL statement and returns the result set as XML data.
      I configure and test (using test functionality in this service to see what is gonig on) this service in my workflow. As the result of this test, I saw that this wuery returns a piece of data in xml format like this :


         
              Özlem Aksel
         

         
              Cihan Aydın
         

         
              Fatih Büyüktaş
         

         
              Melik Akyıldız
         

         
              Aydın Türkdoğan
         



      In Livecycle es samples, there is a sample about multiple query to xml,
      but this sample just show that this service returns XML element. But, does not show, how to use it in forms.

      My aim is, populating a dropdown list in my form, with the data returned by this query. Can anyone tell me how can use this data to populate my dropdown list?

      Thanks in advance

      Best regards

      Cihan Aydın
        • 1. Re: Using  Query Multiple to XML
          Jasmin Charbonneau Level 4
          You'll need to merge that XML to with your template using LC Forms/ renderPDFForm and then in you XDP, bind you drop-down to one of the XML node to populate it.

          Do a search (under Help) in Form Designer for "dynamically populate drop-down" and you'll see a pretty good example of that.

          Jasmin
          • 2. Re: Using  Query Multiple to XML
            Level 1
            Hi,<br /><br />I already read the example you mentioned.But exactly the problem is how can I assign that xml as it shown on my dropdown.<br />For example if its a single query xml then, it is ok. I just map the fields. But in this case I have a variable in xml format and a dropdown field, reachable via xpath. By the way, this dropdown is aldeady bounded to a node in my XDP. Also, As I use XDP, I have to use renderPDFForm in order to render my form.<br /><br />You proposed me last week to use the renderpdfform in order to prepopulate the form with user name. It worked, but as my render service is renderPDFForm, in every step, this is executed. For example I start the process as process creator. I see my name in the field that represents always the process creator (not the current user).<br />I fill the other fields and submit the form.<br />Then form passes the manager. When manager opens the form, as the renderPDFForm is re-executed, he sees his name in the field in question. Also the fields filled by previous user are emptied.<br />Because, I assigned docdata in renderPDFForm as below :<br /><br />concat("<?xml version='1.0' encoding='UTF-8'?><br /><DOFApp><br />  <TalepEdenFields><br />       <TalepEdenIsim>",string(/process_data/@kullanici),"</TalepEdenIsim><br />  </TalepEdenFields><br /></DOFApp>")<br /> <br />In fact, I could not say that, preserve the other parts of my docdata, in renderPDFForm.<br /><br />So how can I preserve my data and merge only the part for my xml.<br />Because the only variable I can reach in renderpdfform is docdata. I coud not specify the sub nodes. <br /><br />Could you please give me an example about merging a document with an xml variable.?<br /><br />Thanks&Regards<br /><br />Cihan
            • 3. Re: Using  Query Multiple to XML
              Jasmin Charbonneau Level 4
              You need to put docdata back into an XML variable (in your render process) and then use a setValue to set the appropriate node with the value you want to update and then put it back in docdata and pass it to renderPDFForm.

              Jasmin
              • 4. Re: Using  Query Multiple to XML
                Level 1
                I would like to use the Query Multiple service to populate a dropdown as well.

                Would like to find out, what's the answer to Cihan's question?
                >>But exactly the problem is how can I assign that xml as it shown on my dropdown.

                And, like to find out if there's a better way? Actually, my question is, what's the best practice for getting several rows of data from database and use it to populate a dropdown on the form?

                Any tips/hints/details will be appreciated.

                thanks
                James

                btw, I have learned a different method to populate dropdowns, that is: put the XML result in a hidden string field on the form; then, write a piece of JavaScript within your form, for the PDF Form Ready event, to Parse this XML string and build the Dropdown. But this method sounds a bit cumbersome.
                • 5. Re: Using  Query Multiple to XML
                  Level 1
                  There are two things you need to do -
                  First you would need to create a comma separated string of usernames(if not send me a email I can give you a component which would do that for you)
                  Then in Acrobat you use the following code to iterate through the list and add the items to a drop down list
                  xfa.connectionSet.DataConnection1.execute(0);
                  var astrChoiceList;
                  var choices;

                  // Clear drop-down list box
                  ListOFGroups.clearItems();
                  ListOFGroups.rawValue ="";
                  //GroupNamesFound is a hidden field which stores the value returned by the webservice call

                  astrChoiceList = GroupNamesFound.rawValue;

                  // add each choice to the dropdown list
                  choices = astrChoiceList.split(",");
                  var numchoices = choices.length;

                  for (var i = 0; i < numchoices; i++)
                  {
                  ListOFGroups.addItem(choices[i], choices[i]);

                  }

                  If you need help mail to mergeandfuse@gmail.com
                  • 6. Re: Using  Query Multiple to XML
                    Level 1
                    Hi, <br /><br />I found a solution for my problem.<br />I try to explain it below :<br /><br />In order to get user names and fill them to a combo,I use a simple form which consist of only a combo of user names with the following <br />xml schema :<br /><br /><?xml version="1.0" encoding="UTF-8"?><br /><xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"><br />    <xsd:annotation><br />        <xsd:documentation xml:lang="en"><br />           MultipleSql deneme<br />        </xsd:documentation><br />    </xsd:annotation><br />    <xsd:element name="UserListApp" type="userlistFormType"/><br />   <br />    <xsd:complexType name="userlistFormType"><br />        <xsd:sequence><br />            <xsd:element name="userList"    type="userlistType"/><br />            <xsd:element name="tempdata"    type="xsd:string"  /><br />           </xsd:sequence><br />    </xsd:complexType><br />    <br />    <xsd:complexType name="userlistType" ><br />        <xsd:sequence><br />            <xsd:element name="users"    type="user"  minOccurs="1" maxOccurs="unbounded" /><br />        </xsd:sequence><br />    </xsd:complexType><br />   <br />    <br />    <xsd:complexType name="user" ><br />        <xsd:sequence><br />            <xsd:element name="name"    type="xsd:string"  /><br />        </xsd:sequence><br />    </xsd:complexType><br /></xsd:schema><br /><br />Then, in process design, I put an query to xml service with the following configuration :<br /><br />SQL stalement :<br />SELECT commonname from  edcprincipalentity where organization=?<br /><br />XML information :<br /><br />root Element name : userList<br />repeating element names : users<br /><br />XML output :<br /><br />define a process variable of XML type (im my ex : xml_output)<br />which returns :<br /><br /><userList><br />    <users><br />        <name type="VARCHAR">Fatih Büyükta&#351;</name><br />    </users><br />    <users><br />        <name type="VARCHAR">Ayd&#305;n Türkdo&#287;an</name><br />    </users><br />    <users><br />        <name type="VARCHAR">Özlem Aksel</name><br />    </users><br />      <users><br />        <name type="VARCHAR">Cihan Ayd&#305;n</name><br />    </users><br /></userList><br /><br />Then using a set value service, put this piesce of xml to  form data<br /><br />/process_data/<FORM VARIBLE NAME>/object/data/xdp/datasets/data/UserListApp<br />=<br />/process_data/xml_output<br /><br />Then, in form,<br /><br />add this script to combo box's initialize event :<br /><br />var oUsersNode = xfa.data.UserListApp.userList;<br />var oUsersNodeList = oUsersNode.resolveNodes("users[*].name");<br /><br />for (var i = 0; i < oUsersNodeList.length; i++)<br />{<br />    this.addItem(oUsersNodeList.item(i).value);<br /><br />}<br /><br />If you send your e-mail I can send you the complete example.<br /><br />Best regards<br /><br />Cihan
                    • 7. Re: Using  Query Multiple to XML
                      Level 1
                      Thanks for info.

                      My email is jdee66888@gmail.com

                      look forward to read your example.
                      • 8. Re: Using  Query Multiple to XML
                        Jasmin,
                        I am not able to update XML nodes in set value service in the process. Can you give me some example regarding this?
                        I am doing like
                        /process_data/inputFormXML/PDOutput/Context/FMCollection/Forms[number(1)]/FormName = 'Changed Name'

                        but its not updating the data in the node.

                        Please help. Thanks!
                        • 9. Re: Using  Query Multiple to XML
                          Level 1
                          Jasmin,
                          I am not able to update XML nodes in set value service in the process. Can you give me some example regarding this?
                          I am doing like
                          /process_data/inputFormXML/PDOutput/Context/FMCollection/Forms[number(1)]/FormName = 'Changed Name'

                          but its not updating the data in the node.

                          Please help. Thanks!
                          • 10. Re: Using  Query Multiple to XML
                            Jasmin Charbonneau Level 4
                            I ran into similar issues a while ago trying to do the same thing. I couldn't figure it out. I had to build a custom component and/or script service to get around the issue.

                            I tried it with the Alpha version of LiveCycle 8.2 and it seems to be fine. It could be a bug with the current version.

                            You can check with support at otsupp@adobe.com

                            Sorry.

                            Jasmin
                            • 11. Re: Using  Query Multiple to XML
                              HowardTreisman Level 1
                              All
                              I've got several comments...
                              1. Cihan, that's a very useful technique. There is, however, an easier way to bind the combo box to the data, without having to write all that code. You can turn on a feature called dynamic binding in Form Designer, and just point to the data. This will do the work for you. Go to the binding tab (from memory), and find the little dropdown menu icon to turn dynamic binding on. Then several areas will show with a blue underline instead of static text.
                              2. There are several techniques for converting XML to a CSV field - you can use XSLT component, or our XQuery component.
                              http://avoka.dnsalias.com/confluence/display/Public/Transforming+XML+Documents
                              You can then use some simple Javascript code in your form, to populate the combo from the comma-separated value in the hidden field. This is described here:
                              http://www.avoka.com/apps/checkcookie?location=/resources/resources/PopulatingaDropDownLis tusingSQLPlus.pdf
                              3. If you're wanting to insert the data into a table in the form, rather than a combo box, there is an example here:
                              http://avoka.dnsalias.com/confluence/display/Public/Inserting+XML+data+into+a+form+variabl e
                              Hope this all helps...
                              Howard
                              http://www.avoka.com
                              • 12. Re: Using  Query Multiple to XML
                                Level 1
                                Hi Howard,

                                Does your suggestion about my method is to eliminate the JS code only?
                                Should I have to do all the stuff about getting and setting the xml data on server side like I did?
                                • 13. Re: Using  Query Multiple to XML
                                  HowardTreisman Level 1
                                  yes, exactly.
                                  Howard
                                  • 14. Re: Using  Query Multiple to XML
                                    Level 1
                                    Hi Cihan..

                                    Thanks very much for the post. Could you please send me the full example for the query multiple as xml. my email is danny.marandola@mediacontinuum.com

                                    Regards