How to write SQL inside XML SOAP request - salesforce marketing cloud API

Question

I am new to SOAP.

I am trying to get the Send Object information and I am able to pull all the data I need except

SendID

This is the request I make in postman

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <soapenv:Header>
        <fueloauth>{{sf_ps_access_token}}</fueloauth>
    </soapenv:Header>
    <soapenv:Body>
        <RetrieveRequestMsg xmlns="http://exacttarget.com/wsdl/partnerAPI">
            <RetrieveRequest>
                <ObjectType>Send</ObjectType>
                <Properties>ID</Properties>
                <Properties>BCCEmail</Properties>
                <Properties>SendID</Properties>
                <Properties>Client.PartnerClientKey</Properties>
                <Properties>PartnerKey</Properties>
                <Properties>CreatedDate</Properties>
                <Properties>ModifiedDate</Properties>
                <Properties>Client.ID</Properties>
                <Properties>Email.ID</Properties>
                <Properties>Email.PartnerKey</Properties>
                <Properties>SendDate</Properties>
                <Properties>FromAddress</Properties>
                <Properties>FromName</Properties>
                <Properties>Duplicates</Properties>
                <Properties>InvalidAddresses</Properties>
                <Properties>HardBounces</Properties>
                <Properties>SoftBounces</Properties>
                <Properties>OtherBounces</Properties>
                <Properties>ForwardedEmails</Properties>
                <Properties>UniqueClicks</Properties>
                <Properties>UniqueOpens</Properties>
                <Properties>NumberSent</Properties>
                <Properties>NumberDelivered</Properties>
                <Properties>NumberTargeted</Properties>
                <Properties>NumberErrored</Properties>
                <Properties>NumberExcluded</Properties>
                <Properties>Unsubscribes</Properties>
                <Properties>MissingAddresses</Properties>
                <Properties>Subject</Properties>
                <Properties>PreviewURL</Properties>
                <Properties>SentDate</Properties>
                <Properties>EmailName</Properties>
                <Properties>Status</Properties>
                <Properties>EmailSendDefinition.ObjectID</Properties>
                <Properties>EmailSendDefinition.CustomerKey</Properties>
                <Properties>Client.PartnerClientKey</Properties>
                <Properties>Email.PartnerKey</Properties>
                <Filter xsi:type="ns1:SimpleFilterPart" xmlns:ns1="http://exacttarget.com/wsdl/partnerAPI">
                    <Property>CreatedDate</Property>
                    <SimpleOperator>between</SimpleOperator>
                    <DateValue>2018-01-01</DateValue>
                    <DateValue>2021-12-09</DateValue>
                </Filter>
            </RetrieveRequest>
        </RetrieveRequestMsg>
    </soapenv:Body>
</soapenv:Envelope>

This is the error I am getting:

Error: Column 'dbo.tblJobs.SendID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

How do I write SQL inside the xml SOAP request?

Answer 1

My response on this one is late. I think the problem is ,

In Send object (<ObjectType>Send</ObjectType>) there is no field as SendID. If you need Job Id inorder to connect other tracking, you need to use "ID" attribute. You can further improve using BatchId

SendID is present in other event like clicks or open. You can use SendId in those event for filtering the data

Sample message for getting send info :

<?xml version="1.0" encoding="UTF-8"?>
<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://schemas.xmlsoap.org/ws/2004/08/addressing" xmlns:u="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
    <s:Header>
        <a:Action s:mustUnderstand="1">Retrieve</a:Action>
        <a:To s:mustUnderstand="1">https://{{et_subdomain}}.soap.marketingcloudapis.com/Service.asmx</a:To>
        <fueloauth xmlns="http://exacttarget.com">{{AccessToken}}</fueloauth>
    </s:Header>
    <s:Body xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <RetrieveRequestMsg xmlns="http://exacttarget.com/wsdl/partnerAPI">
         <Options>
        </Options>
         <RetrieveRequest>
            <ObjectType>Send</ObjectType>
            <Properties>Client.ID</Properties>            
            <Properties>Email.ID</Properties>            
            <Properties>EmailName</Properties>
            <Properties>Subject</Properties>
            <Properties>SendDate</Properties>
            <Properties>CreatedDate</Properties>
            <Properties>Status</Properties>
            <Properties>FromAddress</Properties>
            <Properties>FromName</Properties>            
            <Properties>ID</Properties>
            <Properties>NumberSent</Properties>
            <Properties>NumberDelivered</Properties>
            <Properties>ForwardedEmails</Properties>
            <Properties>NumberErrored</Properties>
            <Properties>NumberExcluded</Properties>            
            <Properties>ExistingUndeliverables</Properties>
            <Properties>ExistingUnsubscribes</Properties>            
            <Properties>SoftBounces</Properties>
            <Properties>HardBounces</Properties>
            <Properties>OtherBounces</Properties>
            <Properties>Unsubscribes</Properties>
            <Properties>UniqueOpens</Properties>
            <Properties>UniqueClicks</Properties>            
            <Properties>PreviewURL</Properties>
            <Filter xsi:type="ns1:SimpleFilterPart" xmlns:ns1="http://exacttarget.com/wsdl/partnerAPI">
               <Property>ID</Property>
               <SimpleOperator>between</SimpleOperator>
               <Value>1</Value><Value>10000</Value>
            </Filter>
         </RetrieveRequest>
      </RetrieveRequestMsg>
    </s:Body>
</s:Envelope>

Once you have the JobId(ID) from above , you can use it to get other tracking .

Using the job Id to get all related sent tracking :

<?xml version="1.0" encoding="UTF-8"?>
<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://schemas.xmlsoap.org/ws/2004/08/addressing" xmlns:u="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
    <s:Header>
        <a:Action s:mustUnderstand="1">Retrieve</a:Action>
        <a:To s:mustUnderstand="1">https://{{et_subdomain}}.soap.marketingcloudapis.com/Service.asmx</a:To>
        <fueloauth xmlns="http://exacttarget.com">{{AccessToken}}</fueloauth>
    </s:Header>
    <s:Body xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <RetrieveRequestMsg xmlns="http://exacttarget.com/wsdl/partnerAPI">
         <Options>
        </Options>
         <RetrieveRequest>
            <ObjectType>SentEvent</ObjectType>
            <Properties>Client.ID</Properties>            
            <Properties>SubscriberKey</Properties>            
            <Properties>EventDate</Properties>
            <Properties>SendID</Properties>
            <Properties>BatchID</Properties>
            <Filter xsi:type="ns1:SimpleFilterPart" xmlns:ns1="http://exacttarget.com/wsdl/partnerAPI">
               <Property>SendID</Property>
               <SimpleOperator>between</SimpleOperator>
               <Value>1</Value><Value>9000</Value>
            </Filter>
         </RetrieveRequest>
      </RetrieveRequestMsg>
    </s:Body>
</s:Envelope>