Hello!
I'm currently trying to post a request with multiple rows to the Tessitura API and store the rows in table, I've registered in the TR_DATASERVICE_TABLES. The data is in the form of XML and the request is something like:
<root>
<row>
<id></id>
<customer_id></customer_id>
<event></event>
</row>
</root>
The endpoint is something like Custom/SaveActivityToTable. I’m able to only save the first row, but I need to multiple/bulk upload rows. I’ve also tried to create a Stored Procedure, which fetches the XML request.
INSERT INTO dbo.LT_MaillingLetterEvents (id,customer_no,event)
SELECT
XCol.value('(id)[1]', 'int'),
XCol.value('(customer_no)[1]', 'int'),
XCol.value('(event)[1]', 'varchar(120)')
FROM
@Input.nodes('/root/row') AS XTbl(XCol)
Which I try to call with Custom/Execute/SaveActivityStoredProcedure but I get the following error.
{
"Message": "An error has occurred."
}
Do you have any suggestions on how to get the stored procedure to work or how I save multiple rows directly into the table?
The stored procedure does work within SSMS:
DECLARE @xml XML = '<root> <row> <id>5</id> <customer_no>1112</customer_no> <event>Event1</event> </row> <row> <id>6</id> <customer_no>1112</customer_no> <event>Event2</event> </row></root>'
EXECUTE dbo.[IinsertSP] @xml
I have a couple of functioning dataservices, but I put them together so long ago it would take me a while to figure it out again. However, I will suggest that maybe you want to try it with JSON instead of XML? I've always found XML to be more temperamental.
Update: one thing I don't remember is whether dataservices even take multiple row inputs, or if you are limited to one "row" per post. I feel like there was a lot of discussion about this a few years ago because the overhead on individual requests was high enough to make "bulk" data uploads untenable, but that improvements in the overhead have corrected that somewhat.