CODEWITHSUNDEEP
sqlsql-serverxmlxquerysqlxml
user551445
user551445

Reputation: 25

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

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>

Upvotes: 0

Views: 686

Answers (1)

roman
roman

Reputation: 117400

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

Upvotes: 1

Related Questions