Update from one field in a table to another section of XML code

0
votes

I have created a new custom column in SQL, this has been created as part of a XML field,

I need to take the data from another field called "Comments", and put it into the XML code that creates my custom column. I could do this manually, but with 10,s of thousands of lines to update, im hoping this can be achieved.

Thank you in advance.

Current column called Comments is just a plain text field, i need to take this field and update part of a XML field that looks like this:

<CustomColumns>
    <CustomColumn>
       <Name>My New Column</Name>
       <DataType>0</DataType>
       <Value>The data to be updated</Value>
    </CustomColumn>
 </CustomColumns>

So i was thinking i need to do an UPDATE statement, to pull the current data from "Comments", and insert it into the Section of this XML code.

2
sql-serverxmlsql-server-2014
can you share values of your comment column and with output you required? – Hardik Leuwa
Hi, thanks, yes comments is also a plain text field, so comments would be something along the line of "Insert 5 * units", so i would like the update to change the <Value> Insert 5 Units </Value> if possible. thank you – Nick Palmer

2 Answers

0
votes

You can use SQL Server's XML.modify( 'replace value of' ... ) to do this. Here is an example that you can run in SSMS that should get you moving in the right direction.

-- SAMPLE DATA --
-- ADDED AN EXTRA CUSTOM COLUMN FOR THIS EXAMPLE --

DECLARE @Data TABLE ( id INT PRIMARY KEY IDENTITY (1,1), Comment VARCHAR(255), MyXmlCol XML );
INSERT INTO @Data ( Comment, MyXmlCol ) 
VALUES ( 
    'I need to update this comment to Value for "My New Column".', 
    '<CustomColumns>
      <CustomColumn>
        <Name>My New Column</Name>
        <DataType>0</DataType>
        <Value>The data to be updated</Value>
      </CustomColumn>
      <CustomColumn>
        <Name>My Other New Column</Name>
        <DataType>0</DataType>
        <Value>This value will not change.</Value>
      </CustomColumn>
    </CustomColumns>' 
);

SELECT id, Comment, MyXmlCol FROM @Data;

This initial SELECT returns:

+----+-------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id |                           Comment                           |                                                                                                                                    MyXmlCol                                                                                                                                    |
+----+-------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | I need to update this comment to Value for "My New Column". | <CustomColumns><CustomColumn><Name>My New Column</Name><DataType>0</DataType><Value>The data to be updated</Value></CustomColumn><CustomColumn><Name>My Other New Column</Name><DataType>0</DataType><Value>This value will not change.</Value></CustomColumn></CustomColumns> |
+----+-------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

The current XML is:

<CustomColumns>
  <CustomColumn>
    <Name>My New Column</Name>
    <DataType>0</DataType>
    <Value>The data to be updated</Value>
  </CustomColumn>
  <CustomColumn>
    <Name>My Other New Column</Name>
    <DataType>0</DataType>
    <Value>This value will not change.</Value>
  </CustomColumn>
</CustomColumns>

Next up, update "Comment" to "Value" for "My New Column":

UPDATE @Data
SET
    MyXmlCol.modify( 'replace value of (/CustomColumns/CustomColumn[Name="My New Column"]/Value/text())[1] with (sql:column("Comment"))' )

SELECT id, Comment, MyXmlCol FROM @Data;

This new SELECT returns:

+----+-------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id |                           Comment                           |                                                                                                                                                      MyXmlCol                                                                                                                                                       |
+----+-------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | I need to update this comment to Value for "My New Column". | <CustomColumns><CustomColumn><Name>My New Column</Name><DataType>0</DataType><Value>I need to update this comment to Value for "My New Column".</Value></CustomColumn><CustomColumn><Name>My Other New Column</Name><DataType>0</DataType><Value>This value will not change.</Value></CustomColumn></CustomColumns> |
+----+-------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

And here is the updated XML:

<CustomColumns>
  <CustomColumn>
    <Name>My New Column</Name>
    <DataType>0</DataType>
    <Value>I need to update this comment to Value for "My New Column".</Value>
  </CustomColumn>
  <CustomColumn>
    <Name>My Other New Column</Name>
    <DataType>0</DataType>
    <Value>This value will not change.</Value>
  </CustomColumn>
</CustomColumns>

Couple of things to note about:

replace value of (/CustomColumns/CustomColumn[Name="My New Column"]/Value/text())[1]
  • Use "text()" to set the text value of a node.
  • The [1] is required as you can only target 1 node using'replace'.
  • Note the use of CustomColumn[Name="My New Column"] to restrict the update.

Also note:

Using sql:column("Comment") allows you to reference another column (in this case [Comment]) in your table without breaking SQL's "string literal" requirement with XML.

0
votes

Your question is not all clear to me, but it might be something along

--Careful with real data. The XML is recreated from scratch

UPDATE YourTable SET CustomColumns=
(
    SELECT 'My new column name' AS [Name]
           ,0 AS [DataType]
           ,Comment AS [Value]
    FOR XML PATH('CustomColumn'),ROOT('CustomColumns'),TYPE  
);

This should create a XML per row, where the row's column Comment is placed within the element <Value>, while the two other elements are hardcoded.

You can test it like this:

DECLARE @tbl TABLE(ID INT IDENTITY, Comment VARCHAR(100),CustomColumns XML);
INSERT INTO @tbl(Comment) VALUES('blah1'),('blah2');
UPDATE @tbl SET CustomColumns=
(
    SELECT 'My new column name' AS [Name]
           ,0 AS [DataType]
           ,Comment AS [Value]
    FOR XML PATH('CustomColumn'),ROOT('CustomColumns'),TYPE  
);

SELECT * FROM @tbl