xquery

• chatwithkrishan

  Ten Centuries

  Points: 1056

  More actions

  May 4, 2009 at 6:40 am

  #155650

  Hi, i can update the content for specific position.

  by replacing the line 10

  [

  SET @newXML.modify('replace value of (row/rownew/@*[local-name() =sql:variable("@attrName")])[1] with sql:variable("@attrValue")')

  ] but i want modify by looping..???

  1) DECLARE @newXML xml;

  2) declare @count int;

  3)DECLARE @attrName nvarchar(100), @attrValue nvarchar(200);

  4)set @attrValue='B'

  5)SET @newXML = '';

  6)SET @attrName = 'text';

  7)set @count=1

  8)WHILE(@count <= 2)
  9)BEGIN
  10)SET @newXML.modify('replace value of (row/rownew/@*[local-name() =sql:variable("@attrName")])[position()=sql:variable("@count")] with sql:variable("@attrValue")')
  11)set @count=@count+1
  12)end
  13)select @newXML

  Any body can tell me how to do this ?

• LutzM

  SSC Guru

  Points: 107049

  More actions

  May 4, 2009 at 10:23 am

  #988185

  Hi,

  I assume your getting the following error message:

  Msg 2337, Level 16, State 1, Line xx

  XQuery [modify()]: The target of 'replace' must be at most one node, found 'attribute(*,xdt:untypedAtomic) *'

  The reason is you have to reference the sequence number of the attribute within the element you're changing.

  Instead of

  SET @newXML.modify('replace value of (row/rownew/@*[local-name() =sql:variable("@attrName")])[position()=sql:variable("@count")] with sql:variable("@attrValue")')

  you should use

  SET @newXML.modify('replace value of (row/rownew/@*[local-name() =sql:variable("@attrName")])[position()=sql:variable("@count")][1] with sql:variable("@attrValue")')

  to refer to the first occurence of @attrName.

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]