Custom Query

Custom Queries are SQL statements loaded into the Custom Query section of iVector, which return a set of XML back through the iVectorconnect Rest API.

Each custom query will have a name that can be used to call it, also from 0 to >1 parameters to pass variable values into the custom query.

Custom Queries are set up within the Custom Queries form within iVector. Each Custom Query has to have a unique name. The query itself needs to be SQL Syntax, and the query always needs to end with an XML directive, for example

select * from geographylevel1 for xml path('Country'), root('Countries')

If you do not add the XML directive at the end of the query, the custom query will fail on generation.

Finally an optional set of parameters can also be applied. The query needs to refer to the parameter using parameters numbered from 0 upwards, and need to be covered by {} curly brackets.

Also note that custom queries should only be used for simple lookup data. Also the queries written should be performant. Do not use for queries that will return a high volume of records, or include a high number of fields including char(max) or xml format

Request

XML Structure

The structure of this XML will be

Response

The response returned will contain the fields selected in the Custom Query to be returned in the request

XML Structure

Examples

Request

This request will request the record with an ID of 51 from a CMS Query called "ExtraCMS".

<Login>TestLogin</Login>

<Password>TestPassword</Password>

</LoginDetails>

<QueryName>ExtraCMS</QueryName>

</Parameters>

</CustomQueryRequest>

Response

</ReturnStatus>

<Extra>

<ExtraName>$200 Disney Gift Card</ExtraName>

</Extra>

<Extra>

<ExtraName>FREE I-Drive NASCAR Race, Play, Eat Package</ExtraName>

</Extra>

</Extras>

</CustomXML>

</CMSQueryResponse>

Page updated

Report abuse