Skip Navigation
Expand
ROQL queries in CWS for SOAP API
Answer ID 10085   |   Last Review Date 03/18/2019

How would I perform a ROQL query through the Connect Web Services for SOAP API?

Environment:

Oracle B2C Service, all supported versions

This answer focuses on executing ROQL queries through the SOAP API using a web testing tool like SoapUI. For more general information about accessing the SOAP API, please review the answer linked below:
Answer ID 9830: Performing a simple Connect Web Services for SOAP request.

Resolution:

There are two types of ROQL query operations:

1. QueryCSV (or tabular query)
- The QueryCSV method processes a ROQL query string and returns data in tabular format. Tabular queries are not restricted to return objects of one type; they can return data from multiple objects, although each column in the table must have a simple data type.
- A maximum of 20,000 ROQL output rows per QueryCSV request
- Generally more efficient than Object Queries
- A query string may contain multiple queries separated by semicolons, limited to 100,000 characters
- The use of the reporting or operational (production) database can be enforced through the USE clause.

Sample query:

SELECT Incident.ReferenceNumber, Incident.PrimaryContact.Contact.* FROM Incident
 
- sample request (make sure to replace the credentials):
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
    <soapenv:Header>
        <ns7:ClientInfoHeader xmlns:ns7="urn:messages.ws.rightnow.com/v1_4" soapenv:mustUnderstand="0">
            <ns7:AppID>Basic Query CSV</ns7:AppID>
        </ns7:ClientInfoHeader>
        <wsse:Security xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" mustUnderstand="1">
            <wsse:UsernameToken>
                <wsse:Username>comland</wsse:Username>
                <wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">Connect1</wsse:Password>
            </wsse:UsernameToken>
        </wsse:Security>
    </soapenv:Header>
    <soapenv:Body>
        <ns7:QueryCSV xmlns:ns7="urn:messages.ws.rightnow.com/v1_4">
            <ns7:Query>SELECT Incident.ReferenceNumber, Incident.PrimaryContact.Contact.* FROM Incident</ns7:Query>
            <ns7:PageSize>10000</ns7:PageSize>
        </ns7:QueryCSV>
    </soapenv:Body>
</soapenv:Envelope>  


 
 
2. QueryObjects
- Generally less efficient than QueryCSV, because it returns entire objects, as opposed to selected columns
- Returns an array of object templates
- May return a maximum of 10,000 objects
- Objects need to be cast back to the appropriate sub-type (not applicable in SoapUI testing)

 
 
Sample query:
 
SELECT O.Contacts FROM Organization O WHERE O.ID = 1;
 
- sample request (make sure to replace the credentials):
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
    <soapenv:Header>
        <ns7:ClientInfoHeader xmlns:ns7="urn:messages.ws.rightnow.com/v1_4" soapenv:mustUnderstand="0">
            <ns7:AppID>Basic Object Query</ns7:AppID>
        </ns7:ClientInfoHeader>
        <wsse:Security xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" mustUnderstand="1">
            <wsse:UsernameToken>
                <wsse:Username>comland</wsse:Username>
                <wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">Connect1</wsse:Password>
            </wsse:UsernameToken>
        </wsse:Security>
    </soapenv:Header>
    <soapenv:Body>
        <ns7:QueryObjects xmlns:ns7="urn:messages.ws.rightnow.com/v1_4">
            <ns7:Query>SELECT O.Contacts FROM Organization O WHERE O.ID = 1;</ns7:Query>
            <ns7:ObjectTemplates xmlns:ns4="urn:objects.ws.rightnow.com/v1_4" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ns4:Contact">
                <ns4:Notes />
            </ns7:ObjectTemplates>
            <ns7:PageSize>10000</ns7:PageSize>
        </ns7:QueryObjects>
    </soapenv:Body>
</soapenv:Envelope>
 



For more information please review the SOAP API documentation:  

Answer ID 5169: Technical Documentation and Sample Code