Updating XML column in a sql table with new attribute

Question

I'm using t-sql to dismantle some XML, then produce a new attribute and value which I hope to add back in via xquery/xpath. Please note I've only looked at this from scratch for a couple of days....

So I'm iterating through a table which contains an xml column, working 1 row at a time and if there is a value in IndAllocID I want to add that back in to the XML for that row as completely new.

Whenever I try though I receive the following error.... can anyone please help?

XQuery [dbo.OTC_FIXML_Data.FIXML_Data.modify()]: The target of 'insert' must be a single node, found 'element(CustomUcs,xdt:untyped) *'

This is the code

declare @indallocid nvarchar(100)
select  @indallocid =   (
        select  IndAllocID
        from    dbo.OTC_FIXML_Data
        where   MessageRef = @Id
        )           

update  dbo.OTC_FIXML_Data
set     FIXML_Data.modify('declare namespace ns="http://www.w3.org/2001/XMLSchema";insert attribute CustomEventLink {sql:variable("@indallocid")}
into ns:FIXML[1]//AllocInstrctn[1]/Instrmt[1]/CustomTag[1]/CustomUcs[1]')
where   MessageRef  =@Id

From researching the error it seems that I'm trying to add to a structure that may exist more than once in the file? If so can I add to all occurrences?

Just adding how I would like the final xml to look.... New addition is the very last attribute

<?xml version="1.0" encoding="UTF-8"?>
-<FIXML xmlns="http://www.fixprotocol.org/FIXML-4-4" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">-    <AllocInstrctn PosEfct="C" NetMny="0000000" BkngTyp="0" SettlDt="2014-04-24" TxnTm="2014-04-23T13:54:36+01:00" TrdDt="2014-04-23" Ccy="GBP" AvgPx="0" PxTyp="9" OrignDt="2014-04-23" LastMkt="UNEX" QtyTyp="0" Qty="34700000"Side="1" NoOrdsTyp="1" LinkTyp="1" LinkID="0000000" ID2="2088767" Typ="1" TransTyp="0" ID="00000000FL">
<Hdr TID="" SID=""/>
<CustomTag CustomOtherCcy="GBP" CustomSettlementMethod="NORMAL" CustomRefRate="000.664516" CustomAuthDt="2014-04-23T12:56:16.68" CustomTrdTm="2014-04-23T13:52:28.41" CustomComments="abc def ghi jkl mnop" CustomTradeStatus="Confirmed" CustomFirstQuotedDt="2014-04-23T13:52:35" CustomFilledDt="2014-04-23T13:54:36.53" CustomWorkingDt="2014-04-23T13:54:36.53" CustomOrdTyp="Market" CustomFillType="N" CustomFillCnt="0" CustomDirectedComm="N" CustomSoftComm="N"/>
<OrdAlloc ClOrdID2="2088767" ClOrdID="MANUAL"/>
-<Instrmt ID="ABC0000099XX" IntAcrl="2013-09-24" Desc="GBP UKRPI  @ " Issr="Inflation Swap" Exch="UNEX" CpnRt="0" Mult="100" IssuCtry="GB" Fctr="1" CpnPmt="2063-09-24" MatDt="2063-09-24" SubTyp="Inflation Swap" SecTyp="Swap" CFI="" Src="101" Sym="Inflation Swap-FLOAT">
-<CustomTag CustomFixingReference="UKRPI" CustomCouponModifier="Following" CustomAccrualConvention="ACT/365" CustomPayFreq="0" CustomAssetSubType="Inflation Swap" CustomAssetType="Swap" CustomAssetSubClass="SWAP" CustomAssetClass="SWAPFL" CustomIssuerCode="HSB" CustomInstrumentStatus="1" CustomRiskCcy="GBP" CustomPriceCcy="GBP" CustomSharesOutstanding="0" CustomSettlDays="3" CustomFactorIndexation="1" CustomFactorAmortisation="1" CustomPrice="0.03713" CustomFixedFloat="2" CustomPaymentType="1" CustomIndexationLag="2" CustomDebtType="<Unassigned>" CustomResetFreq="0" CustomTenor="600" CustomFunded="Y" CustomUnadjFirstCpn="2063-09-24T00:00:00">
<CustomUCs Desc="Cash/Physical" Id="604" UC="<Unassigned>" CustomEventLink = "XXXXXX"/>

Mikael Eriksson · Accepted Answer · 2014-05-23 13:06:56Z

2

SQL Server does not realize that the expression you use in the into clause identifies one node only.

Try this instead.

into (FIXML//AllocInstrctn/Instrmt/CustomTag/CustomUcs)[1]

with xmlnamespaces(default 'http://www.fixprotocol.org/FIXML-4-4')
update dbo.OTC_FIXML_Data
set FIXML_Data.modify('insert attribute CustomEventLink {sql:variable("@indallocid")} 
                       into (FIXML//AllocInstrctn/Instrmt/CustomTag/CustomUCs)[1]');

edited May 23, 2014 at 13:06

answered May 23, 2014 at 11:16

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mikael Eriksson Over a year ago

@user3008705 If you post a sample of your XML and what you would like it to look like after the modification I might be able to help you with that.

user3008705 Over a year ago

Edited original post with desired XML.

Mikael Eriksson Over a year ago

@user3008705 The XML you have in the question is not valid. But I guessed a bit about what you want and you are using the wrong namespace.

Collectives™ on Stack Overflow

Updating XML column in a sql table with new attribute

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related