Collapse
Submit a Service Request
Contact Information for Technical Support
My Service Notifications
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: