‘Bad Request’ error while sending Soap API to update the query activity

apimarketing-cloudmarketingcloudapisoap-api

I'm using the Soap API to Update the Query activity. It works fine with a simple query, but when the query contains the special characters(&, (, ),-) mentioned below it gives the 'Bad Request' error.

Soap API:-

<?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">Start</a:Action>
        <a:To s:mustUnderstand="1">https://mcjx64fcjb6p0hs08tk4.soap.marketingcloudapis.com/Service.asmx</a:To>
        <fueloauth xmlns="http://exacttarget.com"></fueloauth>
    </s:Header>
    <s:Body xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <UpdateRequest
        xmlns="http://exacttarget.com/wsdl/partnerAPI">
        <Options></Options>
        <Objects xsi:type="QueryDefinition">
            <Client>
                <ID>57654765</ID>
            </Client>
            <ObjectID>77b38e46-f4f4-41cb-9fff</ObjectID>
            <QueryText>SELECT op.Name,op.accountid,op.course__c,op.email_on_hover__c 
                    FROM Opportunity_Salesforce op  
                    WHERE op.Reason_Lost__c IN ('Enrolled to Other School') 
                    AND op.Course__c IN ('Bachelor of Business in Banking and Cyber Forensics and Information Security (Double Major)','Bachelor of Commerce in Finance and Business Law','Bachelor of Commerce in International Business and Hospitality & Tourism Management (Double Major)') 
                    AND (Cast(op.createdDate as date) BETWEEN '5/28/2019' AND '6/15/2022') 
                    AND op.Contact_me__c = 'True' 
                    AND op.StageName IN('Open','Contacted','Appointment','Applied') 
                    AND (op.Opt_out_Email__c = 'False' OR op.Opt_out_Mobile__c = 'False')
            </QueryText>
            <TargetType>DE</TargetType>
            <DataExtensionTarget>
                <PartnerKey xsi:nil="true"></PartnerKey>
                <ObjectID xsi:nil="true"></ObjectID>
                <CustomerKey>FEC22DF1-C7E8-4E38</CustomerKey>
                <Name>Blast_DE</Name>
            </DataExtensionTarget>
            <TargetUpdateType>Overwrite</TargetUpdateType>
        </Objects>
    </UpdateRequest>
    </s:Body>
</s:Envelope>

Query:

SELECT op.Name,op.accountid,op.course__c,op.email_on_hover__c 
                    FROM Opportunity_Salesforce op  
                    WHERE op.Reason_Lost__c IN ('Enrolled to Other School') 
                    AND op.Course__c IN ('Bachelor of Business in Banking and Cyber Forensics and Information Security (Double Major)','Bachelor of Commerce in Finance and Business Law','Bachelor of Commerce in International Business and Hospitality & Tourism Management (Double Major)') 
                    AND (Cast(op.createdDate as date) BETWEEN '5/28/2019' AND '6/15/2022') 
                    AND op.Contact_me__c = 'True' 
                    AND op.StageName IN('Open','Contacted','Appointment','Applied') 
                    AND (op.Opt_out_Email__c = 'False' OR op.Opt_out_Mobile__c = 'False')

Best Answer

Your QueryText property contains characters that are part of XML markup. You can either HTML encode your query or place it inside a CDATA section:

<?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">Start</a:Action>
        <a:To s:mustUnderstand="1">https://mcjx64fcjb6p0hs08tk4.soap.marketingcloudapis.com/Service.asmx</a:To>
        <fueloauth xmlns="http://exacttarget.com"></fueloauth>
    </s:Header>
    <s:Body xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <UpdateRequest
        xmlns="http://exacttarget.com/wsdl/partnerAPI">
        <Options></Options>
        <Objects xsi:type="QueryDefinition">
            <Client>
                <ID>57654765</ID>
            </Client>
            <ObjectID>77b38e46-f4f4-41cb-9fff</ObjectID>
            <QueryText><![CDATA[SELECT op.Name,op.accountid,op.course__c,op.email_on_hover__c 
                    FROM Opportunity_Salesforce op  
                    WHERE op.Reason_Lost__c IN ('Enrolled to Other School') 
                    AND op.Course__c IN ('Bachelor of Business in Banking and Cyber Forensics and Information Security (Double Major)','Bachelor of Commerce in Finance and Business Law','Bachelor of Commerce in International Business and Hospitality & Tourism Management (Double Major)') 
                    AND (Cast(op.createdDate as date) BETWEEN '5/28/2019' AND '6/15/2022') 
                    AND op.Contact_me__c = 'True' 
                    AND op.StageName IN('Open','Contacted','Appointment','Applied') 
                    AND (op.Opt_out_Email__c = 'False' OR op.Opt_out_Mobile__c = 'False')]]>
            </QueryText>
            <TargetType>DE</TargetType>
            <DataExtensionTarget>
                <PartnerKey xsi:nil="true"></PartnerKey>
                <ObjectID xsi:nil="true"></ObjectID>
                <CustomerKey>FEC22DF1-C7E8-4E38</CustomerKey>
                <Name>Blast_DE</Name>
            </DataExtensionTarget>
            <TargetUpdateType>Overwrite</TargetUpdateType>
        </Objects>
    </UpdateRequest>
    </s:Body>
</s:Envelope>
Related Topic