CODEWITHSUNDEEP
sql-serverxmlt-sqlxquery
BrackleyMan
BrackleyMan

Reputation: 19

Update to an XML Column

Trying to update an XML column for Sales Managers on our customers table on SQL.

Format of Column and name of it are like this:

<CustomColumnsCollection>
   <CustomColumn>
      <Name>ReceiveRebate</Name>
      <DataType>5</DataType>
      <Value>False</Value>
   </CustomColumn>
   <CustomColumn>
      <Name>Sales Manager</Name>
      <DataType>0</DataType>
      <Value>Ben Dover</Value>
   </CustomColumn>
   <CustomColumn>
      <Name>SurveyDate</Name>
      <DataType>1</DataType>
      <Value />
   </CustomColumn>
</CustomColumnsCollection>

I am trying to update the Sales Manager value column, so Instead of Ben Dover it would Gary Shaw for example.

So Far I have this:

UPDATE Customers
SET CustomColumns.modify('replace value of 
(/CustomColumnsCollection/CustomColumn[@Name=("Sales Manager ")]/Value/text())[1] with ("Gary Shaw")
')
WHERE CustomerID = 'aptcustomerid'

It runs successfully but It seemingly does nothing, any clues would be greatly appriciated.

Upvotes: 0

Views: 49

Answers (1)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22311

Here is how to do it.

(1) The XPath expression was off. There are no attributes in the XML. <Name> is an element.

(2) I added an SQL variable for flexibility to find a proper XML fragment.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, CustomColumns XML);
INSERT INTO @tbl ( CustomColumns) VALUES
(N'<CustomColumnsCollection>
   <CustomColumn>
      <Name>ReceiveRebate</Name>
      <DataType>5</DataType>
      <Value>False</Value>
   </CustomColumn>
   <CustomColumn>
      <Name>Sales Manager</Name>
      <DataType>0</DataType>
      <Value>Ben Dover</Value>
   </CustomColumn>
   <CustomColumn>
      <Name>SurveyDate</Name>
      <DataType>1</DataType>
      <Value />
   </CustomColumn>
</CustomColumnsCollection>');
-- DDL and sample data population, end

-- before
SELECT * FROM @tbl;

DECLARE @nameElement VARCHAR(30) = 'Sales Manager';

UPDATE @tbl
SET CustomColumns.modify('replace value of 
(/CustomColumnsCollection/CustomColumn[Name=sql:variable("@nameElement")]/Value/text())[1] 
with ("Gary Shaw")
');
--WHERE CustomerID = 'aptcustomerid'

-- after
SELECT * FROM @tbl;

Upvotes: 1

Related Questions