Execute multiple SQL Queries through Cisco AXL

[Zaher Juha]

Hi,

Trying to execute multiple SQL update query using Postman, but it only takes the first and ignore the rest

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.cisco.com/AXL/API/14.0">
<soapenv:Header/>
<soapenv:Body>
<ns:executeSQLUpdate>
<sql>
update device set fkenduser='77303ed5-eea4-4d68-b6ed-a5c32edbf5b1' where name like'%8C941F2A3661%'
</sql>

<sql>
update device set fkenduser='77303ed5-eea4-4d68-b6ed-a5c32edbf5b1' where name like'%8C941FFF4934%'
</sql>

<sql>
update device set fkenduser='77303ed5-eea4-4d68-b6ed-a5c32edbf5b1' where name like'%8C941FFF3EDE%'
</sql>
</ns:executeSQLUpdate>
</soapenv:Body>
</soapenv:Envelope>

 

I know I'm doing something wrong, but couldn't figure it out.

Thanks

[Zaher Juha]

It works with OR but still by using one SQL update query, what I'm looking for is using multiple SQL queries at once

<sql>update device set fkenduser='77303ed5-eea4-4d68-b6ed-a5c32edbf5b1' where name like'%8C941F2A3661%' OR name like'%8C941F496177%' OR name like'%8C941FFF4B05%' OR name like'%8C941FFF4AA9%' OR name like'%8C941F2A3661%' OR name like'%8C941FFF4A66%' OR name like'%8C941FFF4593%' OR name like'%8C941F49615D%' OR name like'%8C941FFF4943%' OR name like'%8C941FFF4C44%' OR name like'%8C941FFF4934%' OR name like'%8C941FC792DA%' OR name like'%8C941FE1D37E%' OR name like'%8C941FFF493A%' OR name like'%8C941FFF3EDE%' OR name like'%8C941FFF48C3%' OR name like'%8C941FFF48D1%' OR name like'%8C941FFF4CE9%' OR name like'%8C941FFF4BC4%' OR name like'%8C941FFF4CA1%'</sql>

[dstaudt · Accepted solution]

Only one <sql> element can be provided in the request.  However, it is possible to include multiple SQL statements the "SQL" way, by separating them by semi-colon:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:ns="http://www.cisco.com/AXL/API/14.0">
    <soapenv:Header />
    <soapenv:Body>
        <ns:executeSQLUpdate>
            <sql>
                update device set fkenduser='77303ed5-eea4-4d68-b6ed-a5c32edbf5b1' where name like'%8C941F2A3661%';
                update device set fkenduser='77303ed5-eea4-4d68-b6ed-a5c32edbf5b1' where name like'%8C941FFF4934%';
                update device set fkenduser='77303ed5-eea4-4d68-b6ed-a5c32edbf5b1' where name like'%8C941FFF3EDE%';
            </sql>
        </ns:executeSQLUpdate>
    </soapenv:Body>
</soapenv:Envelope>

 If you can avoid the wildcards, you could use the 'IN' operator, like this:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:ns="http://www.cisco.com/AXL/API/14.0">
    <soapenv:Header />
    <soapenv:Body>
        <ns:executeSQLUpdate>
            <sql>UPDATE DEVICE SET fkenduser='77303ed5-eea4-4d68-b6ed-a5c32edbf5b1' WHERE name IN
                ('SEP8C941F2A3661','SEP8C941FFF4934','SEP8C941FFF3EDE')
            </sql>
        </ns:executeSQLUpdate>
    </soapenv:Body>
</soapenv:Envelope>

 which will likely be faster.
Note, as AXL SQL commands are wrapped in a SQL transaction behind the scenes, when executing multiple commands, if one fails then they should all get rolled back...

[Zaher Juha]

Thanks dstaudt,

Yes that could also help, but also good to know that only one SQL element could be done at a time