May 20, 2014 at 1:27 pm
I have a table with some standard fields and one xml field. i need to update one of the xml fields with a value in the standard fields for each respective row. How would I go about doing this?
i.e.
UPDATE [MyTable]
SET Xmlfield.modify('replace value of (/MyParentNode/MyNode)[1] with sql:column("MyTable.MyField")')
WHERE MyField2 = 0
May 21, 2014 at 2:47 am
That is the first step to get the answer you need:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
If you follow the above, you most likely will get relevant help quite promptly...
June 5, 2014 at 11:16 pm
This is straight forward, the main pitfall is that the node value must either be typed (XSD) or referenced with text().
😎
USE tempdb;
GO
/* Create a table (variable) */
DECLARE @XMLUPDATE TABLE
(
XU_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL
,XU_VAL INT NOT NULL
,XU_XML XML NOT NULL
);
/* Populate with few samples */
INSERT INTO @XMLUPDATE
(
XU_VAL
,XU_XML
)
SELECT
S.object_id + 10 AS XU_VAL
,(SELECT
SO.name AS 'MyNode/@name'
,SO.type_desc AS 'MyNode/@type_desc'
,SO.create_date AS 'MyNode/@create_date'
,SO.object_id AS 'MyNode/@object_id'
,SO.object_id AS 'MyNode'
FROM sys.objects SO
WHERE S.object_id = SO.object_id
FOR XML PATH('MyParentNode'),TYPE) AS XU_XML
FROM sys.objects S
/* filter if needed */
--WHERE YEAR(S.create_date) = 2009;
/* run the update */
UPDATE MU
SET XU_XML.modify('replace value of (/MyParentNode/MyNode/text())[1] with sql:column("MU.XU_VAL")')
FROM @XMLUPDATE MU
/* filter if needed */
--WHERE MU.XU_ID < 40
/* The node value is now equal to 10 + object_id attribute */
SELECT
*
FROM @XMLUPDATE
Results (first)
<MyParentNode>
<MyNode name="sysrscols" type_desc="SYSTEM_TABLE" create_date="2014-02-20T20:48:35.270" object_id="3">13</MyNode>
</MyParentNode>
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply