0

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')

1 Answer 1

2

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>
1
  • Thank you, Macca for your time and response -The CDATA section worked for me :) Jun 15 at 14:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.