Query for Multiple Rows as XML

[Former Community Member]

Ok I've seen this topic in the forums, but have failed to get this to work within my process. Basically I have a process that has a user fill out a form, and upon submittal the form writes it's information to a database (via a data connection within the form that invokes a separate process upon submittal.), once this is done the main process is invoked and it's first step is to query that same database and bring back the data in a lower (now exposed) portion of the form that contains certain fields that track the issue history. The query brings back the xml document and I store it in an xml variable (xml_output). I then take this information and use the Set Value object to assign the returned nodes values to the location fields in the form. This works if the query returns only one row. But once the form is moved beyond the initial Task Assignment, (first user), and query returns more than just one row, the added nodes to not show up on the form, i.e I still just get information about the first row in my form. From what I've read the form should be able to recognize the xpath expression, and bring back all the nodes, by in a sense using instance manager to create an instance for each node on the form. My question is how can I get this to work. Jasmin if your still out there... Help!



Also We're using Adobe LiveCycle ES 8.0

created the process using Workbench

the form was reader enabled and saved as a dynamic PDF.



Thanks

Mike

[Former Community Member]

Are the multiple rows appearing in the data stream? If so is the form set to render as dynamic? To verify this open the template in Designer and go to File/Form Properties. Under the Defaults Tab the last item is a dropdown that tells the server how to render the form. You want to set that as dynamic.

[Jasmin_Charbonn]

"From what I've read the form should be able to recognize the xpath expression, and bring back all the nodes, by in a sense using instance manager to create an instance for each node on the form".



That's not totally true. Let me try to explain a bit here. LiveCycle allows you to merge xml data with a form. This data could drive dynamic sections to get created automatically (for example create a variable number of rows based on the number of instances of a node).



But in your case this "variable" number of rows is coming from the xml coming from the database and is not reflected probably in the xml that gets merged with the form, which is the data node of the xfaForm variable.



The setValue won't automatically set all the nodes from the db xml to the xfaForm. It'll only give you the first instance.



My suggestion here would be to use the setValue to update an entire section of the xfaForm variable as oppose to one field value. For example, maybe your rows in the from are within a container called ROWS. You could use the setValue to update the node ROWS which would contain all the rows. If the form is design to bound to the data within ROWS, the section will be created automatically.



I'm not sure if this is clear, but at least it'll start some discussion.



Jasmin

[Former Community Member]

Thank you for the responses. Frank I have tried the override default rendering, and that seemed to cause me alot of other problems inside of Workspace. Maybe I was doing something wrong, but I couldn't even get the form to render. And that's something I probably should have added to my first message, this form needs to work inside of Workspace.

Jasmin I'm not quite sure I understand what you're saying. I'm a bit of a noob at LiveCycle so you'll have to excuse my ignorance. It sounds like you're saying to use the set value to match the form location (which would be a section/subform on the form) with the Element section of the xml file (in my case it would be root/issue ). I'll give that a shot. Is there anything else that I need to do to the form. So far what I have is a dynamic form saved as a dynamic PDF, and I've created and XML schema to be associated with the file, but it is not embedded in the form. The XML schema is mainly so that I can navigate to fields via xpath inside of Workbench. Let me know if this is wrong. Along with that I have an XML schema file for the xml that gets returned from the Query Multiple Rows as XML operation. That allows me to easily navigate through the nodes to assign node values to the proper field values. Inside the form itself, I have two nested subforms, one named flowed the other is positioned. The names denote their relative properties, hence Flowed is a flowed subform and positioned is positioned. The positioned subform is nested inside of the flowed subform and set to repeat subform for each data item. Just wanted to explain the situation a little more. I will try using the Set Value operation to set the root/issue = to the positioned subform, as I have interpreted your response as saying. If I'm incorrect please clarify. I want to thank everyone for their help with this.



Mike

[Jasmin_Charbonn]

" So far what I have is a dynamic form saved as a dynamic PDF, and I've created and XML schema to be associated with the file, but it is not embedded in the form. The XML schema is mainly so that I can navigate to fields via xpath inside of Workbench. Let me know if this is wrong"<br /><br />That's perfect. I do that all the time for the same reason.<br /><br />Now concerning the data. What I'm trying to say, is that your repeating subform in your XDP is bound to an element in the schema that defines which element will drive the number of occurrences of your subform. In your case that might be the element 'issue'. <br /><br />As an example, if you merge a xml like this<br /><br /><root><br /><All_Issues><br /> <issue>My First Issue</issue><br /> <issue>My Second Issue</issue><br /> <issue>My Third Issue</issue><br /></All_Issues><br /></root><br /><br />Then you'll might get three instances of the subform.<br /><br />Now from the process side of things you need to build the same structure for the rows to work.With xPath you can only set one element at a time, so you would only be able to set 1 'issue' at a time. Because of that I'm suggesting to update <issues> (which contains all the issues). That way you don't have to worry about the number of 'issue' you deal with.<br /><br />So if you db query returns something like<br /><br /><dbquery><br /> <resultset><br /> <issue>data 1</issue><br /> <issue>data 2</issue><br /> <issue>data 3</issue><br /> </resultset><br /></dbquery><br /><br />you could map the issues from the db query to the xfaForm variable with a single xPath statement. Something like<br /><br />xfaForm/data/datasets/data/All_Issues = xmlvar/dbquery/resultset<br /><br />That way you populate the All_Issues node directly from the query from the database. <br /><br />I hope that a bit more clear.<br /><br />Jasmin

[Former Community Member]

Ok I tried it an no dice. Here is a snippet of the xml I'm working with:



0016-03-30
Mike
Public Works
alone in the dark
Type sleep
-1
Council
FYI
Important


0016-03-30
Mike
Public Works
alone in the dark
Type sleep
-1
Council
FYI
Important


0016-04-30
Me
Public Works
Tony Blue
Who knew
-1
Council
FYI
Important



And my Xpath expression looked like this:

/process_data/formData/object/data/xdp/datasets/data/form1/Page1/flowed/positioned = /process_data/xml_Query_Return/root/issue

Bottom portion of the form still comes up blank.

Any suggestions?

[Jasmin_Charbonn]

Ok, you're mixing a few concepts here. Let's take a step back.



Let's forget about Workbench for now and focus on the form and the data being merged with it.



Form Designer allows you to create a form template (XDP). LiveCycle Forms allows you to merge a data file (XML) with that XDP to populate a form. This data can drive dynamic sections in the form.



Now in Form Designer your form contains a certain structure (from hierarchy tab). You have a subform name (flowed and position), field names etc. Now you can bind xml elements from the data that's going to be merge with element of you form. You can set that the xml element 'issue' will be bound to the subform 'position'. This binding has to be done in the form design. You can't set that with an xPath from Workbench. In Workbench, you'll only be able to provide the XML to merge with the template. The logic is in the form design.



So you have some work to do on the XDP ahead of time. You can save the xml you're planning to use in a file on the file system and use it to preview the pdf within Form Designer to see if your binding is set properly. You can go under File/Form Properties/Preview and specify a data file to test with.



Make sure this works before you even attempt to use it the process. Once this works, you can use Workbench to prepare the same data structure, but Workbench won't do the mapping for you.



This topic might be an interested read http://livedocs.adobe.com/livecycle/8.2/wb_designer/000102.html



Jasmin

[Former Community Member]

Ok I think I finally get it. Just a couple of questions, should I use the xml schema file that I have describing the xml file to do the binding, or should I just bind using the xml file? The other question is do I need to bind (I'm reading bind as embed, tell me if this is wrong.) the xml schema file that I have for the form to the form? Thanks for all your help, Jasmin. I'm going over the livedoc that you posted now. Good stuff.



Mike

[Jasmin_Charbonn]

"should I use the xml schema file that I have describing the xml file to do the binding, or should I just bind using the xml file?"



It's up to you. You can do both. The advantage of using a schema is that you can embed it in the form and it allows you to use xPath to go down the data node in Workbench.



"The other question is do I need to bind (I'm reading bind as embed, tell me if this is wrong.)the xml schema file that I have for the form to the form"



The binding is necessary to tell which element in the schema (or xml data file) is going to drive the number of subforms. Just like if you needed to populate a field with a value from the schema, you would need to bind the field to the schema element to get the value in the field.



Jasmin

[Jasmin_Charbonn]

Did you try to merge the data file with the XDP within Form Designer and test with PDF Preview?



Jasmin

[Former Community Member]

Sorry I for the late response. I work on my Mac and have to switch to my Windows Server environment to get to my LiveCycle Environment. Success!!! Yes I bound the fields to the xml elements and the Positioned subform to the Issue node, merged the data for preview, and the form created the additional subforms as needed. I had a brief issue because it only returned the first two nodes, but that was because the initial form design was had that particular subform as positioned. I need to have the entire page as flowed so that it will move down and create additional pages as needed. I can fix that. Need to redesign the form, but you've just helped me over a HUGE hurdle, Jasmin. Thank you. Now once I've corrected the issues within the form, and get it flowing the way I need, with all of the fields bound to the correct nodes, how would I simulate the injecting of the XML data into the form using the Set Value function within a process? Would I just use an Xpath expression that just set the xml issue node equal to the positioned subform?



Thanks,

Mike



BTW - Another intereting thing that happened was when I attempted to use an XSD file to bind the fields to, my XSD only produced 1 subform (issues) and 2 fields (Type and Data). When I used the xml file I received all of the nodes? Just thought this was interesting, as I figured the XSD file would provide the nodes, as it was the 'description' file. Anyway just found it interesting, the xml files works just fine.

[Former Community Member]

Ok now I've run into a couple of issues.



1) it seems that when I bind the fields to the xml it changes the form schema that is produced. So I no longer see all of the fields in the top portion of the form in Xpath. Only the ones that were binded.



2) In my process the xfaform variable is no longer holding the data that was input by the previous person, i.e I fill out a form to start the process, when I submit, and the next person gets the form, they see nothing.



What I did was take the xml result that was returned from my process, and create a xsd file from that. Then I created a data connection in the form, and Binded the subform positioned with the issue nodes, and all child nodes to the corresponding fields. Mind you this is only the bottom portion of the form. The upper and middle portion are bound to a web service that updates a database upon submittal. Once I binded the 'positioned' subform with the issues node, I used some test xml that is returned via my database query as a data file in the form preview, and It worked just as I wanted. Multiple rows multiple fields under it. But now the problem was that once in the process, and I submitted the form, the next user that views the form, gets a blank form??? I also noticed that the form schema is now different. What am I doing wrong???



Mike

[Jasmin_Charbonn]

Sorry for the delay. I'm at a customer site.



"Another intereting thing that happened was when I attempted to use an XSD file to bind the fields to, my XSD only produced 1 subform (issues) and 2 fields (Type and Data). When I used the xml file I received all of the nodes?"



This is normal.The schema just give you the grammar of how the xml will look like. The schema can specify that you can get 1 or many instance of a particular xml node. The xml will implement the schema definition. So your xml can contain multiple nodes if the schema permits.



It's not recommended to create a data connection from the XML file, because it might just be one implementation of the schema. Maybe in this particular instance you have three nodes for your subform, but in another instance you'll have four. The schema is more generic in the sense that it'll tell you how many node the xml can contain.



"What I did was take the xml result that was returned from my process, and create a xsd file from that. Then I created a data connection in the form, and Binded the subform positioned with the issue nodes, and all child nodes to the corresponding fields."



When you have data connection to your form that uses a schema, then the form's data will follow that schema. That means when you submit the form, the data will follow the schema structure. Usually people have a schema for the entire form.



" it seems that when I bind the fields to the xml it changes the form schema that is produced. So I no longer see all of the fields in the top portion of the form in Xpath. Only the ones that were binded."



Again same idea here. The form data will follow the data connection. If you use a schema, it'll follow the schema definition. If you use a xml file, it'ss follow the xml file structure.



"In my process the xfaform variable is no longer holding the data that was input by the previous person, i.e I fill out a form to start the process, when I submit, and the next person gets the form, they see nothing."



I would check the variable you use on the second user step. Also make sure the render service that renders the xfaForm variable has the data parameter set to Task-> Form Data.



Jasmin

[Former Community Member]

Ok have done the binding of the schema to the form, and I'm getting the information to flow as it's supposed to. The problem I'm having now, (I know I'm having a bunch.) is that once I've placed the form into my process, and I use the set value operation to pre-fill information I get the form to flow, but the rest of the information that was previously filled in is blank. So I have checked the variable and nothing wrong there. It appears the problem is how I'm using the set value operation. If I don't use the set value operation, the form retains it's information. And if I use the set value by trying to set the subform value, the form retains it's information, but no pre-fill. I'm going to try to be a little more detailed here. The xml that is returned looks like this:


0016-03-30
Mike
Public Works
alone in the dark
Type sleep
-1
Council
FYI
Important



Flowed and positioned are actual subforms on the form that have their properties set as their names indicates. The entire path is /form1/Page1/flowed/positioned . The way I was able to get the form to flow correctly upon the injection of this xml data, was to bind the subforms in the form in this manner, positioned[*]. Designer didn't place the [*] for me so I had to do this myself. But once I previewed the document, the form pre-populated and created the necessary number of subforms and placed information into the fields correctly.
Now on the process side, I have created an xfaform variable that stores the form (bound to xsd.) information, and another xml variable that stores the query return that looks like the xml listed above (has an xsd associated with it for navigating xpath). My first operation performs the query (JDBC- Query Multiple Rows as XML) and stores it into the xml variable. The next step of the process is the Set Value operation which is supposed to inject the xml data retrieved from the query into the form. Initially I tried the an xpath similar to the following:

/process_data/object/data/form1/page1/flowed/positioned = /xml_data/flowed/positioned.

No dice. On this one the form retains the information filled out by the user (didn't explain this earlier, the form is filled out first, and upon submittal the process starts.), but doesn't pre-populate the bottom part of the form. Next I tried the following xpath:

/process_data/object/data/form1/page1/flowed = /xml_data/flowed/positioned.

This time it retained the form information which was filed in by the user, but the bottom part is becomes hidden! Didn't understand this. But I started to kinda think that I had to navigate he xpath in order to correctly inject the xml into the form. That being said my next attempt was:

/process_data/object/data/form1/page1 = /xml_data

My thinking was that if on location, I stop at page1, the xml in the variable will allow it to navigate correctly through the form. Well it worked, but too well. The top part of the form is blank, but the bottom portion is pre-populated correctly. Now I'm stuck. Not sure how to get the rest of the information in the form. I thought about creating variables form each of the fields and then re-populating them after the injection of xml, but that didn't seem practical. I'm almost positive it's something I'm not doing correctly with xpath. Anyway, thanks for all your help on this.

Mike

[Former Community Member]

Ok it seems that I've gotten this to work. The way I've done it is by creating variables for all of the fields on the top of the form and assigning each fields values to them at the beginning of the process using the Set Value operation. Then I use the Query Multiple Rows as XML operation to perform the query and store the data into a xml variable. Then I use the Set Value operation to insert that data into the correct fields (which are bound to xml schema) via the Xpath expression /process_data/FORM_DATA/object/data/xdp/datasets/data/form1/Page1 = /process_data/XML_DATA. XML_DATA has as it's root element flowed, and as it's repeating element positioned, which is why I used Page1 as the entry location for the xml. I figured the xml data would be able to properly insert the form up to that point. So that:<br /><form1><br /> <Page1><br /><br />knew where to put the:<br /><Flowed><br /> <Positioned><br /><br />so essentially the entire thing looked like this:<br /><form1><br /> <Page1><br /> <Flowed><br /> <Positioned><br /> </Positioned><br /> </Flowed><br /> </Page1><br /></form1><br /><br />And this worked. It pre-populated my fields with the data and created the correct number of position nodes, which was based on the data that the query returned. This also created my other problem as it was updating the form, but the whole form. In other words, simply because there was no other data attached to XML_DATA it erased the top fields. So anything that was not in the Flowed or Positioned xml file got wiped out. I corrected this by creating variables for each of the fields that were being erased, and once I had their data, after the XML_data was injected, I injected these variables back into the form, one by one. It was a rather painful process, but I finally got it to work. I was hoping someone could tell me if there was a better way. I do not want to have to do this on some upcoming forms that have the same requirements, but about 3x as many fields.<br /><br />Thanks,<br />Mike

[Jasmin_Charbonn]

I'm sure there is a way you can avoid using a process variable for the fields that are part of the header.<br /><br />What you can do is just add a extra subform to create one more level in your structure. For example :<br /><br /><form1><br /> <Page1><br /> <header><br /> <header fields><br /> </header><br /> <repeating><br /> <Flowed><br /> <Positioned><br /> </Positioned><br /> </Flowed><br /> </repeating><br /> </Page1><br /></form1> <br /><br />With that structure you can have an xPath expression that sets the value of "repeating" without having an impact on "header". Remember, your xml doesn't have to match the form structure. As long as you do your binding correctly you can have any structure you want.<br /><br />It's just a though.<br /><br />Jasmin

[Former Community Member]

Took me awhile to get back to you, ( new projects popping up everyday here.), but I've finally had a chance to get back to this and indeed your suggestion of adding a extra subform layer does facilitate the proper filling out of the repeating nodes while leaving the other data previously filled out unchanged. Perfect!! So it just looks like as long as you have a little planning in the design of the form up front(don't know how I would have done this without the XML schema.) and knowing what section you're going to have grow, you only need to provide three levels of subforms to properly fill out your form. So that If you have fields in a subform named 'A', you would need that subform wrapped inside two other subforms. The function of the other two subforms is mainly to provide an easy way to navigate through the rest of the xml your form will generate so that the xml you are trying to inject (your query return) can "find it's way" through the node tree and properly fill out the rest of the form. As a side note, I'm not sure but I believe that tables because of the way they are structured, already provide you with two of the subforms, (Table, and Row), so in theory you would only need to wrap a table with one subform. Thanks a bunch for your help with this, Jasmin. Hopefully this post will help other developer's who have had issues with this.

[Elaine_Schmitz]

What?



"So that If you have fields in a subform named 'A', you would need that subform wrapped inside two other subforms. The function of the other two subforms is mainly to provide an easy way to navigate through the rest of the xml your form will generate so that the xml you are trying to inject (your query return) can "find it's way" through the node tree and properly fill out the rest of the form"

[Jasmin_Charbonn]

If you use a SetValue yes. When the setValue adds a child node to a parent node it replace whatever was there with the content you're trying to add. You can't just insert a child node in bewteen other child nodes.



If you don't want to do that you can always use the Script service, use a XSLT to create the new nodes or build a custom component.



Jasmin

[Aditya_S_]

Jasmin - I have never worked with XSLT. Do you have any tuttorial or sample links ?



Aditya