Hi, I can't test this because you're using libraries I don't have access to.
However, something like this should do exactly what I believe to understand you are asking for: xquery version "1.0-ml"; declare namespace olympus = "http://www.alm.com/olympus";; declare namespace this = "this"; import module namespace scope="http://alm.com/modules/scope"; at "olympus/modules/scope.xqy"; import module namespace pacer = "http://alm.com/modules/pacer"; at "olympus/modules/pacer.xqy"; (:450075101|450040301|450033801|450025401:) let $results := for $id in cts:search(xdmp:directory("/olympus/scope-xml/")//Coporate_Profile_ID/text() let $sub-ids:= scope:build-id-sub-list($id) let $cases:=pacer:get-cases($sub-ids,"") let $profile:=scope:fetch-profile($id,"",0,-1,0,0,"","","") let $fields:=$profile//( Coporate_Profile_ID| Main_Details/Company_Name| Main_Details/No_of_Subsidiaries) let $subsidiaries:=$profile//Subsidiaries let $subs:= for $sub in $subsidiaries/Subsidiary let $name:=$sub/Subsidiary_name let $sid:=$sub/Subsidiary_Profile_ID let $count:=pacer:get-cases($sid/text(),"")/@total return <Subsidiary>{$name}{$sid}<case-count>{$count}</case-count></Subsidiary> return <Result> {$fields} {$subs} </Result> return <Results> {$results} </Results> A tip on efficiency, using cts:element-values() ( https://docs.marklogic.com/cts:element-values ) is going to be more performant than cts:search() when you only want unique values rather than entire documents returned. As your database grows, either solution may not scale particularly well so you'll want to assess your goals with the reports and what you are really hoping to achieve. Finally, I'm a bit confused on what you're trying to do with the resulting document. If you could expand on your goals I may be able to offer an alternative approach to appending documents to manage multiple reports. Best, Rob Rob Szkutak Associate Consultant MarkLogic Corporation [email protected] Cell +1.716.562.8464 www.marklogic.com<http://www.marklogic.com> ________________________________ From: [email protected] [[email protected]] on behalf of Kari Cowan [[email protected]] Sent: Thursday, June 18, 2015 3:55 PM To: MarkLogic Developer Discussion Subject: Re: [MarkLogic Dev General] Generating Reports in ML To be clear, I can take a single result and return it into a xdmp:document-insert statement – is there a method available to iteratively append to that document? From: [email protected] [mailto:[email protected]] On Behalf Of Kari Cowan Sent: Thursday, June 18, 2015 8:47 AM To: [email protected] Subject: [MarkLogic Dev General] Generating Reports in ML I have a query that returns a set of results for a single record. I want to loop through all records (under 1000 records), and create a spreadsheet or document. Assume I could do a search on cts:search(xdmp:directory("/olympus/scope-xml/"), and then a value query on a node called “Coporate_Profile_ID” - that would return the id’s I want below. How would I then create the report? (tips on efficient code tips to return the id’s are appreciated as well) Below is my query and sample results for 1 record. Any suggestions? xquery version "1.0-ml"; declare namespace olympus = "http://www.alm.com/olympus";; declare namespace this = "this"; import module namespace scope="http://alm.com/modules/scope"; at "olympus/modules/scope.xqy"; import module namespace pacer = "http://alm.com/modules/pacer"; at "olympus/modules/pacer.xqy"; (:450075101|450040301|450033801|450025401:) let $id:="450025401" let $sub-ids:= scope:build-id-sub-list($id) let $cases:=pacer:get-cases($sub-ids,"") let $profile:=scope:fetch-profile($id,"",0,-1,0,0,"","","") let $fields:=$profile//( Coporate_Profile_ID| Main_Details/Company_Name| Main_Details/No_of_Subsidiaries) let $subsidiaries:=$profile//Subsidiaries let $subs:= for $sub in $subsidiaries/Subsidiary let $name:=$sub/Subsidiary_name let $id:=$sub/Subsidiary_Profile_ID let $count:=pacer:get-cases($id/text(),"")/@total return <Subsidiary>{$name}{$id}<case-count>{$count}</case-count></Subsidiary> return <Result> {$fields} {$subs} </Result> ………………………… <Result> <Coporate_Profile_ID>450025401</Coporate_Profile_ID> <Company_Name>AOL Inc.</Company_Name> <No_of_Subsidiaries>13</No_of_Subsidiaries> <Coporate_Profile_ID>450025401</Coporate_Profile_ID> <Coporate_Profile_ID></Coporate_Profile_ID> <Subsidiary> <Subsidiary_name>Adap.tv, Inc</Subsidiary_name> <Subsidiary_Profile_ID>450025404</Subsidiary_Profile_ID> <case-count total="0"></case-count> </Subsidiary> <Subsidiary> <Subsidiary_name>AOL Advertising Inc</Subsidiary_name> <Subsidiary_Profile_ID>450025405</Subsidiary_Profile_ID> <case-count total="2"></case-count> </Subsidiary> <Subsidiary> <Subsidiary_name>AOL Advertising Inc</Subsidiary_name> <Subsidiary_Profile_ID>450025405</Subsidiary_Profile_ID> <case-count total="2"></case-count> </Subsidiary> <Subsidiary> <Subsidiary_name>AOL Canada Corp</Subsidiary_name> <Subsidiary_Profile_ID>450025406</Subsidiary_Profile_ID> <case-count total="0"></case-count> </Subsidiary> <Subsidiary> <Subsidiary_name>AOL Holdings (Lux) S. r.l</Subsidiary_name> <Subsidiary_Profile_ID>450025409</Subsidiary_Profile_ID> <case-count total="0"></case-count> </Subsidiary> <Subsidiary> <Subsidiary_name>AOL Internet Services Holdings Inc</Subsidiary_name> <Subsidiary_Profile_ID>450025407</Subsidiary_Profile_ID> <case-count total="0"></case-count> </Subsidiary> <Subsidiary> <Subsidiary_name>AOL Israel Ltd</Subsidiary_name> <Subsidiary_Profile_ID>450025427</Subsidiary_Profile_ID> <case-count total="0"></case-count> </Subsidiary> <Subsidiary> <Subsidiary_name>Convertro, Inc</Subsidiary_name> <Subsidiary_Profile_ID>450025428</Subsidiary_Profile_ID> <case-count total="0"></case-count> </Subsidiary> <Subsidiary> <Subsidiary_name>Gravity.com, Inc</Subsidiary_name> <Subsidiary_Profile_ID>450025429</Subsidiary_Profile_ID> <case-count total="0"></case-count> </Subsidiary> <Subsidiary> <Subsidiary_name>MapQuest, Inc</Subsidiary_name> <Subsidiary_Profile_ID>450025410</Subsidiary_Profile_ID> <case-count total="2"></case-count> </Subsidiary> <Subsidiary> <Subsidiary_name>TechCrunch, Inc</Subsidiary_name> <Subsidiary_Profile_ID>450025414</Subsidiary_Profile_ID> <case-count total="0"></case-count> </Subsidiary> <Subsidiary> <Subsidiary_name>TheHuffingtonPost.com, Inc</Subsidiary_name> <Subsidiary_Profile_ID>450025415</Subsidiary_Profile_ID> <case-count total="2"></case-count> </Subsidiary> <Subsidiary> <Subsidiary_name>Vidible, Inc.</Subsidiary_name> <Subsidiary_Profile_ID>450025430</Subsidiary_Profile_ID> <case-count total="0"></case-count> </Subsidiary> </Result> ________________________________ Learn more about ALM, visit http://www.alm.com . – ALM, an Integrated Media Company, is a leading provider of news and information for the legal and commercial real estate markets. ALM’s market-leading brands include The American Lawyer, Corporate Counsel, GlobeSt.com, Insight Conferences, Law.com, Law Journal Press, LegalTech, The National Law Journal and Real Estate Forum. ________________________________ Learn more about ALM, visit http://www.alm.com . – ALM, an Integrated Media Company, is a leading provider of news and information for the legal and commercial real estate markets. ALM’s market-leading brands include The American Lawyer, Corporate Counsel, GlobeSt.com, Insight Conferences, Law.com, Law Journal Press, LegalTech, The National Law Journal and Real Estate Forum.
_______________________________________________ General mailing list [email protected] Manage your subscription at: http://developer.marklogic.com/mailman/listinfo/general
