Xml Modify - insert

Transact-SQL
1

I am trying to insert data to an XML file

UPDATE @WORKINGXML SET XMLTEXT.modify('insert <TransactionSet IdKey="{sql:variable("@Lookup_TranssetId")}" into (/BACSDocument/Data/ARUDD/Advice/OriginatingAccountRecords/OriginatingAccountRecord/ReturnedDebitItem)[' + CAST(@tID AS VARCHAR(100)) + ']')

But I keep getting an error The argument 1 of the XML data type method "modify" must be a string literal.

2

Without being able to run the query - because I don't have your table and data, it is hard to say. Here is an example, if that will help. If that does not help, post your create table and insert statements, so someone can copy and paste your data and run it to see the sample data.

DECLARE @x XML = '
<BACSDocument>
<Data>
	<TransactionSet>
	</TransactionSet>
</Data>
</BACSDocument>';       

declare @Lookup_TranssetId nvarchar(50) = 'SomeName'
SET @x.modify('insert <IdKey>{sql:variable("@Lookup_TranssetId")}</IdKey>
into (/BACSDocument/Data/TransactionSet)[1]') 

SELECT @x;
3

Hi,

Thanks for the response, I have added the edited XML file i am using and what i am trying to get to . The SQL code in on a while loop.
original XML

DECLARE @x XML = '



























'

New XML

<BACSDocument >

Using the suggestion you made, the only the first ReturnedDebitItem is being updated / created. I have tried to parm in the loop count where the [1] is but i keep getting the same error message back about a string literal. The xml file can contain any number of the ReturnedDebitItems. The only way I can make it work with this file is by
(/BACSDocument/Data/TransactionSet)[1]')
(/BACSDocument/Data/TransactionSet)[2]') etc

4

I can't see the XML you posted. Not your fault, this site eats the XML when you try to post it. So what I am posting below is a guess as to what you are trying to do. If that does not work, send me an e-mail with the sample XML fragment you have, and the result that you are trying to get.

In this example, I am trying to insert something into the third TransactionSet node.

DECLARE @x XML = '
<BACSDocument>
<Data>
	<TransactionSet>
	</TransactionSet>
	<TransactionSet>
	</TransactionSet>
	<TransactionSet>
	</TransactionSet>
</Data>
</BACSDocument>';     

declare @Lookup_TranssetId nvarchar(50) = 'SomeName'
DECLARE @tID INT = 3;
SET @x.modify('insert <IdKey>{sql:variable("@Lookup_TranssetId")}</IdKey>
into (/BACSDocument/Data/TransactionSet[sql:variable("@tID")])[1]') 

SELECT @x;