Expand my Community achievements bar.

Dive into Adobe Summit 2024! Explore curated list of AEM sessions & labs, register, connect with experts, ask questions, engage, and share insights. Don't miss the excitement.

Using Query Multiple to XML

Avatar

Former Community Member
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
14 Replies

Avatar

Level 10
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

Avatar

Former Community Member
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

Avatar

Level 10
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

Avatar

Former Community Member
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.

Avatar

Former Community Member
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

Avatar

Former Community Member
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

Avatar

Former Community Member
Thanks for info.



My email is jdee66888@gmail.com



look forward to read your example.

Avatar

Former Community Member
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!

Avatar

Former Community Member
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!

Avatar

Level 10
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

Avatar

Level 9
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/PopulatingaDropDownListusingSQLP...

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+variable

Hope this all helps...

Howard

http://www.avoka.com

Avatar

Former Community Member
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?

Avatar

Former Community Member
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