Hi,
is there a fast way to “join” 2 document lists in a FlowService on IS 6.1.
I read 2 tables from 2 different databases and have to join some of the attributes into one output document list.
This can be done by nested loops, but this doesn’t perform very well.
Using hashtables in java itself could maybe solve the problem, but this is also not a very convenient (serviceoriented) way.
Is there another possibility to achive such a joining?
Can the XQL/WQL solve this problem ? i don’t have any experience with them.
Using appendToDocumentList service you can accomplish this kind of task by making one doclist as a original document this should have all the necessary wanted attributes (fields).
hi RMG,
thx, but that is not the solution i’m searching for. the lists are not in the correct order.
i have 2 documentlists and before i can join the attributes from both lists i have to loop through the second documentlist for the according attributes until the element matches the first documentlist. this has to be done for each element in the first documentlist as the lists are not in the same order.
example:
List1:
+ User
yes Uli, XQL will be the way to go with this problem. You could use the queryDocument service with XQL to find the fields from an exact ID. for example in your example. you would have
LOOP all List1/ID
Mail2 = queryDocument(List2[ID=%List1/ID%]/Mail)
Map ListN/Mail = Mail2
END LOOP
The problem that I have is related to a very common requirement - joining two document lists using a key - like querying two database tables joined by a foreign key. I searched the forum and found numerous threads hovering around the same topic, just like this one and the solution seems to lie with the mysterious (for me) queryXMLNode. I read the BISReference Guide and tried to use queryXMLNode but to no avail… I just couldn’t make it to work. Populating the input (fields) beats me. I even tried to read the TN User guide for XQL reference, but that didn’t help either.
I guess I need some sample service to demonstrate how queryXMLNode works and how I can combine fields from two different document list on the basis of a key.
In my case, I have a string list ‘appIds’ (consisting of keys called appId) and a document list that looks like this:
I finally want to create a document list with one document for every ‘appId’ in the string list ‘appIds’. Each of these document should contain ‘appId’ and the corresponding ‘appName’ taken from the document list shown above. Guys I would greatly appreciate any help.
In essence, you want to use your document list as a lookup table to extract the appName based on appId, right?
You’ll loop over the items in your string list and use an QueryXMLNode statement in the loop to locate the appName item where apps\appId=‘%stringlist_appID%’
However, since you can’t use the % substitution variable inside an XQL query, you have to add another step.
Create a document with the same structure as the fields documentList but with only a single entry and some temporary name.
Use a map step to format the XQL query portion of the fields documentList with the substitution variable containing your appId element from the string list.
Then use a map statement with the appendToDocumentList transformer to
add your temporary document into a documentList with the same structure as the fields input parameter
Now instead of manually populating the fields parameter in Developer, just map the documentLIst you created. You won’t see the resulting variable, in Developer, but it will be available for mapping and you will see the results when you step though the Flow.
Thanks for your response but I still couldn’t make it work As you suggested, I looped over the string list and as the first step in the loop, I used a map to populate a temp document with following structure and values
temp
|-----name = someOutput
|-----resultType = String
|-----query = apps/appId==‘%currentAppId%’
|-----queryType = XQL
|-----onnull = continue
In the next step of the loop I collect these individual temp docs into a doc list using appendToDocumentList. Thereafter (outside of the loop) I used documentToXMLString->xmlStringToXMLNode to create a node object from the ‘apps’ documentList mentioned in my first message (the doc list in the pic) and used this node as input to queryXMLNode and mapped the temp doc list to the fields documentList of the queryXMLNode. But here’s the error that I get when I try to run this service:
com.wm.app.b2b.server.ServiceException: [ISC.0083.9024] Invalid or misplaced term “<expressiontoken:>”
I am sure that I am doing smth wrong because I know almost nothing about XQL and I guess that’s where the problem is… lemme know if I didn’t make myself clear. Thanks,
Rohit (I apologize for the duplicate post, but the reason of that duplicity was that I first searched the forum for similar topics cuz I didn’t wanna start a new thread… and among many threads that I found as a result of my search - there were these two… I posted my question in one and then realized that it was an archived thread… and thought that it wouldn’t come into notice… so posted my question again…!)
I don’t think XQL uses the double equal signs ‘==’ as a comparison operator. Try a single ‘=’.
Also, test the XQL query with a hardcoded value first before moving on to the dynamically generated string. When you get that working you will see more easily how the other is supposed to work. Walk before you you and all that.
This does work, you’ll get it.
Mark (No problem on the duplicate post. Just don’t make a habit of it.)
Let me first admit that i am not good in XQL Queries…
But i can give you a suggestion…
Assumptions
The variable name for the stringList
stringList_appsId …contains the stringList if appsId
apps… is a document list of document appId and appName.
Loop through the stringList_appsId
Loop through the documentList ( With a Label …
%stringListappsId%==%apps/appId%)
If they are equal form another document and later append the document to documentList
Finally u will have a result of documentList with appId same as appId in the stringList.
Mark - You were right about the single ‘=’, when I used a single ‘=’, the error that I was getting, disappeared, however, the output still showed as null. I don’t know if I should pursue the queryXMLNode any further… in fact I am thinking of taking help of your java services that you developed in your first attempt to achieve something similar. There’s just one more thing about queryXMLNode that I wanted to ask - does it have to be a well formed XML that should be passed as a node to this service - I’m asking this because the documentList that I have (apps) would not result into a well formed XML (it does not has a root element).
Rakesh - thanks for your suggestion, but looping through the stringList and documentList both, will produce too many combinations and comparison which will hit the performance as the no. of elements in these lists grow. Reading several other threads around the same topic, I realized that queryXMLNode is a better way to go in such scenarios. Ne way - thanks for helping.
Okay guys, I’ve got it to work… the last thought was useful (or so it seems) - the service expects a node of a well formed xml, so before I created a an xml node from my apps doc list, I added a root element by embedding the apps doc list in some temp doc. Thereafter I looped over the stringList_appIds, used a map to create ‘fields’ input to the service (esp to create the query field using //apps[appId='%stringList_appsId%], as mark pointed out that queryXMLNode does not allows variable replace in its input field ‘query’), then used queryXMLNode within that loop to get the bounded node (used the fields doc created in the map step above to feed input to the fields doc list of the service) and as the last step of the loop, used appendToDocumentList service to accumulate all the results.
I am attaching here a sample to help those who may stumble over the same block, however, as I said ‘Ive got it to work…’, means - I cannot say that the sample has service at its best… there may be redundant steps that may need to be get rid of. Also, in order to suit more to the name of the thread, I have used two doc lists in the sample as against a string list and a doc list that I originally mentioned. Mark n Rakesh - thanks for help!
Sample- joining 2 Doc List using queryXMLNode RohitWmUtils.zip (17.7 k)
*Reading the XQL Reference section of the TNUsersGuide will be helpful in creating your XQL queries.
Hello,
The solution will not have a visible representation in Developer, you can access the document list (IData object). You will loop over each element and use com.wm.data.IDataUtil.append(IData, IData) to group them in a single document. You can on thr class at:
{wmInstallPath}/Developer/doc/API/Java/com/wm/data/IDataUtil.html
Provide you install documentation. That should be it. Good day.
The pub.list:appendToDocumentList built-in service accepts a fromList parameter.
Map the second documentList to this parameter and it will be appended to the end of the first list.
While performance tuning guides identify the appendToDocumentList service as being resource intensive, making a single call to it to combine the two lists should be considerably less resource intensive than looping over both lists.
Hello,
You can ingnore my post. The formatting of your structure lead me to beleive you wanted a single “document” from the list. So yes, as Mark said, the appendToDocumentList service is the way to go. Sorry, Good day.
As you suggested, I looped over the string list and as the first step in the loop, I used a map to populate a temp document with following structure and values
Sample- joining 2 Doc List using queryXMLNode