Xquery with XML column in Xml.Modify in SQL Server not working

Question

I am trying to modify the node in the XML that it gets a new id after inserting in the main XML this xmlfields where groupid = @groupid element and the qubefieldId = @i

Can someone tell me why this is not working?

Update @temp
Set xmlField.modify('replace value of (/ArrayOfQubeField/QubeField[QubeFieldID = sql:variable("@i")]/text())[1] with sql:variable  ("@newID")')
Where XmlField.exist('/ArrayOfQubeField/QubeField[GroupID = sql:variable ("@groupID")][QubeFieldID = sql:variable("@i")]') = 1;

Here is the xml

<ArrayOfQubeField xmlns:xsi="http://www.w3.org/2001/XMLSchema-   instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <QubeField>
      <QubeFieldID>1</QubeFieldID>
      <GroupID>1</GroupID>
      <QubeFieldName>ID</QubeFieldName>
      <QubeFieldSelected>true</QubeFieldSelected>
      <Order>0</Order>
  </QubeField>
 <QubeField>
 <QubeFieldID>1</QubeFieldID>
     <GroupID>4</GroupID>
     <QubeFieldName>Name</QubeFieldName>
     <QubeFieldSelected>true</QubeFieldSelected>
     <Order>1</Order>
 </QubeField>
 <QubeField>
    <QubeFieldID>2</QubeFieldID>
    <GroupID>3</GroupID>
    <QubeFieldName>Town</QubeFieldName>
    <QubeFieldSelected>true</QubeFieldSelected>
    <Order>2</Order>
 </QubeField>
 <QubeField>
    <QubeFieldID>1</QubeFieldID>
    <GroupID>3</GroupID>
    <QubeFieldName> Name</QubeFieldName>
    <QubeFieldSelected>true</QubeFieldSelected>
    <Order>3</Order>
 </QubeField>
 <QubeField>
    <QubeFieldID>13</QubeFieldID>
    <GroupID>1</GroupID>
    <QubeFieldName>Migrated</QubeFieldName>
    <QubeFieldSelected>true</QubeFieldSelected>
    <Order>4</Order>
 </QubeField>
 <QubeField>
   <QubeFieldID>16</QubeFieldID>
   <GroupID>1</GroupID>
   <QubeFieldName>Date</QubeFieldName>
   <QubeFieldSelected>true</QubeFieldSelected>
   <Order>5</Order>
 </QubeField>
</ArrayOfQubeField>

I only tried to clean up your post a bit - I DID NOT downvote you! — marc_s
– marc_s, Commented Mar 26, 2015 at 16:04
If I'd have to guess I would say you got a downvote because your question is quite unclear. "Not working" is not a error description - What does not work, what message do you get? Also, screaming "URGENT" at as in the title will not make any of us answer faster (in our free time; for free) — dirkk
– dirkk, Commented Mar 26, 2015 at 18:07
I only put urgent on it because it is, The xpath does not update the id as I put in the description the piece of code should by logic up date the value of the node text. — user551445
– user551445, Commented Mar 26, 2015 at 20:18

roman · Accepted Answer · 2015-03-27 13:05:06Z

1

You're missing QubeFieldID in the XPath, try something like this:

update @temp set
    xmlField.modify('
        replace value of
        (/ArrayOfQubeField/QubeField[GroupID = sql:variable ("@groupID")]/QubeFieldID[. = sql:variable("@i")]/text())[1]
        with sql:variable("@newID")
    ')

sql fiddle demo

answered Mar 27, 2015 at 13:05

roman

118k30 gold badges205 silver badges209 bronze badges

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

1 Comment

user551445 Over a year ago

Thank you I had not done what you did but mine was bit fiddler by using the position and another loop by using a count. I will change my code to yours its much neater.

Collectives™ on Stack Overflow

Xquery with XML column in Xml.Modify in SQL Server not working

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related