Extract only title from specific folder

[sunitac2231600]

Dear All,

I need to extract only title from the path /content/geometrixx/ , as shown in below screenshot.

How can I do ? Is there any report I can extract "or" anything else I can do to get all the country titles under /content/geometrixx/

[smacdonald2008]

YOu can use the JCR SQL2 to perform a lookup of all the nodes and then get the properties of the result nodes. Use this JCR SQL2 code:

import javax.jcr.Repository;
import javax.jcr.Session;
import javax.jcr.SimpleCredentials;
import javax.jcr.Node;
import javax.jcr.query.Query;

import org.apache.jackrabbit.commons.JcrUtils;

public class TestJCR {

    public static void main(String [] args)
    {

        try {

            String aemUrl = "http://localhost:4502/crx/server" ;
            //Create a connection to the CQ repository running on local host
            Repository repository = JcrUtils.getRepository(aemUrl);

            //Create a Session
            javax.jcr.Session session = repository.login( new SimpleCredentials("admin", "admin".toCharArray()));

            //Obtain the query manager for the session ...
            javax.jcr.query.QueryManager queryManager = session.getWorkspace().getQueryManager();

            //Specify the AEM JCR path where examples users are stored at /home/users/test

            String sqlStatement = "select * from [cq:PageContent] as p where isdescendantnode (p, [/content/geometrixx])";

            javax.jcr.query.Query query = queryManager.createQuery(sqlStatement,"JCR-SQL2");

            //Execute the query and get the results ...
            javax.jcr.query.QueryResult result = query.execute();

            //Iterate over the nodes in the results ...
            javax.jcr.NodeIterator nodeIter = result.getNodes();

            long mySize = nodeIter.getSize();

            while ( nodeIter.hasNext() ) {

                //For each node-- get the path of the node
                javax.jcr.Node node = nodeIter.nextNode();

                //check to see if node has jcr.title
                String title = "" ;
                if (node.hasProperty("jcr:title"))
                {
                  title = node.getProperty("jcr:title").getString()     ;
                   System.out.println(title);
                }

            }

            // Save the session changes and log out
            session.save();
            session.logout();

        }
        catch(Exception e){
            e.printStackTrace();
        }

    }

}

This give me the result set:

Board of Directors
Press Center
Press Center Asset Editor
Press Center Asset Viewer
Discover Geometrixx
Company
News
Articles
Press Releases
Management Team
Search
Community
GeoBlog
TechSummit
DSC Berlin
Events
User Conference
Event Edit Form
Event View Form
ShapeCon Las Vegas
Customer Satisfaction Survey
Thank you
Support
English
Newsletter
Sitemap
Account Request
Thank You
Account
Login
My Geometrixx
Search
Change Password
Success
View Profile
Profiles
Edit Profile
Forgot Password
Thank You
Toolbar
Contact
Feedback
Overview
Square
Features
Overview
Mandelbrot Set
Features
Products
Overview
Circle
Features
Overview
Triangle
Features
Banking Services
Strategic Consulting
Services
Certification Services
Empresa
Comunidad
Eventos
Soporte
Español
Productos
Servicios
Azienda
Forum
Eventi
Supporto
Italiano
Prodotti
Servizi
Société
Communauté
Événements
Support
Français
Produits
Services
会社
コミュニティ
イベント
サポート
日本語
製品
サービス
公司
社区
事件
支持
简体中文
产品
服务
Unternehmen
Community
Events
Support
Deutsch
Produkte
Dienstleistungen

[sunitac2231600]

Hi,

Is there any report I can create so that it will extract the only title name and put in excel.

The output of the query should be as below. 

English
Français
Deutsch
Español
Italiano
日本語

[kautuk_sahni · Accepted solution]

sunitac2231600 wrote...

Hi,

Is there any report I can create so that it will extract the only title name and put in excel.

The output of the query should be as below. 

English
Français
Deutsch
Español
Italiano
日本語

For making queries, please refer :-

Link:- http://aempodcast.com/2015/aem-resources/aem-queries-xpath-jcr-sql2-query-builder-syntaxes/

Link:- http://labs.6dglobal.com/blog/2014-10-07/9-jcr-sql-2-queries-every-aem-dev-should-know/

i.e. 

SELECT * FROM [cq:PageContent] WHERE [jcr:title] IS NOT NULL

Assuming that your query is ready, the next problem is to convert the data intot CSV or Excel, for that please refer to :

Link:- http://help-forums.adobe.com/content/adobeforums/en/experience-manager-forum/adobe-experience-manage...

I hope this would help you.

~kautuk

[smacdonald2008]

We have an article that shows how to query and dump into excel: http://scottsdigitalcommunity.blogspot.ca/2013/11/developing-java-swing-application-that.html?m=0. Replace the query logic in that article with the JCR SQL2 shown here.

[dan_klco]

FYI since labs.6dglobal.com is down, here is the same post on JCRSQL2 queries on my personal blog:

https://www.danklco.com/posts/2014/10/07/9-jcr-sql-2-queries-every-aem-dev-should-know/