Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn More

View all

Sign in to view all badges

Extract only title from specific folder

Avatar

Avatar
Validate 10
Level 3
sunitac2231600
Level 3

Likes

4 likes

Total Posts

90 posts

Correct Reply

0 solutions
Top badges earned
Validate 10
Validate 1
Ignite 5
Ignite 3
Ignite 10
View profile

Avatar
Validate 10
Level 3
sunitac2231600
Level 3

Likes

4 likes

Total Posts

90 posts

Correct Reply

0 solutions
Top badges earned
Validate 10
Validate 1
Ignite 5
Ignite 3
Ignite 10
View profile
sunitac2231600
Level 3

26-10-2016

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/

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar
Establish
Community Manager
kautuk_sahni
Community Manager

Likes

1,160 likes

Total Posts

6,265 posts

Correct Reply

1,144 solutions
Top badges earned
Establish
Coach
Originator
Contributor 2
Contributor
View profile

Avatar
Establish
Community Manager
kautuk_sahni
Community Manager

Likes

1,160 likes

Total Posts

6,265 posts

Correct Reply

1,144 solutions
Top badges earned
Establish
Coach
Originator
Contributor 2
Contributor
View profile
kautuk_sahni
Community Manager

27-10-2016

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

Answers (4)

Answers (4)

Avatar

Avatar
Validate 1
Level 5
dan_klco
Level 5

Likes

53 likes

Total Posts

88 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Shape 1
Ignite 5
Ignite 40
Ignite 30
View profile

Avatar
Validate 1
Level 5
dan_klco
Level 5

Likes

53 likes

Total Posts

88 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Shape 1
Ignite 5
Ignite 40
Ignite 30
View profile
dan_klco
Level 5

22-02-2018

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/

Avatar

Avatar
Validate 25
Level 10
smacdonald2008
Level 10

Likes

1,408 likes

Total Posts

12,671 posts

Correct Reply

2,278 solutions
Top badges earned
Validate 25
Validate 10
Validate 1
Give back 900
Give back 600
View profile

Avatar
Validate 25
Level 10
smacdonald2008
Level 10

Likes

1,408 likes

Total Posts

12,671 posts

Correct Reply

2,278 solutions
Top badges earned
Validate 25
Validate 10
Validate 1
Give back 900
Give back 600
View profile
smacdonald2008
Level 10

27-10-2016

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.

Avatar

Avatar
Validate 10
Level 3
sunitac2231600
Level 3

Likes

4 likes

Total Posts

90 posts

Correct Reply

0 solutions
Top badges earned
Validate 10
Validate 1
Ignite 5
Ignite 3
Ignite 10
View profile

Avatar
Validate 10
Level 3
sunitac2231600
Level 3

Likes

4 likes

Total Posts

90 posts

Correct Reply

0 solutions
Top badges earned
Validate 10
Validate 1
Ignite 5
Ignite 3
Ignite 10
View profile
sunitac2231600
Level 3

26-10-2016

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
日本語

Avatar

Avatar
Validate 25
Level 10
smacdonald2008
Level 10

Likes

1,408 likes

Total Posts

12,671 posts

Correct Reply

2,278 solutions
Top badges earned
Validate 25
Validate 10
Validate 1
Give back 900
Give back 600
View profile

Avatar
Validate 25
Level 10
smacdonald2008
Level 10

Likes

1,408 likes

Total Posts

12,671 posts

Correct Reply

2,278 solutions
Top badges earned
Validate 25
Validate 10
Validate 1
Give back 900
Give back 600
View profile
smacdonald2008
Level 10

26-10-2016

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