how to update xml data in sql server

• polo.csit

  SSCrazy

  Points: 2574

  More actions

  October 17, 2018 at 12:21 am

  #416157

  <searchResult searchEngine="58" keyword="bake off recipes" dateTime="2018-10-16T17:56:47Z">
  <section col="main">

  above xml update the datetime

  please help mee

• Eirikur Eiriksson

  SSC Guru

  Points: 182911

  More actions

  October 17, 2018 at 2:52 am

  #2009995

  polo.csit - Wednesday, October 17, 2018 12:21 AM

  <searchResult searchEngine="58" keyword="bake off recipes" dateTime="2018-10-16T17:56:47Z">
  <section col="main">

  above xml update the datetime

  please help mee

  Here are two examples, should get you passed this hurdle.
  😎

  Simple date and time timezone update
  
USE TEEST;
GO

  DECLARE @TZ_ID     INT     = 1;
DECLARE @H_DIFF_MINUTE  INT     = -60;
DECLARE @TZ_NAME    VARCHAR(128)  = 'America/Chicago';
DECLARE @NEW_TIME    TIME(0);
DECLARE @NEW_DATE    DATE;
DECLARE @SXML     XML     = N'<scheduleitem>
<schedule>
  <frequency>
  <weekly weeklyInterval="1">
   <WED />
  </weekly>
  </frequency>
  <startDate>2008-08-05</startDate>
  <time>00:30:00</time>
  <timezoneId>0</timezoneId>
  <timezone>America/New_York</timezone>
</schedule>
</scheduleitem>';

  /*
  Retrieve the date and time values from the xml,
  combine the values into a datetime2 and add the
  timezone offset.
*/
;WITH BDATA AS
(
  SELECT TOP (1)
   ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS XRID
   ,DATEADD( 
       MINUTE
       ,@H_DIFF_MINUTE
       ,DATEADD(
           MINUTE
           ,DATEDIFF(
               MINUTE
               ,CAST(SCH.EDULE.value('(startDate/text())[1]','DATETIME2(0)') AS TIME)
               ,CAST(SCH.EDULE.value('(time/text())[1]','DATETIME2(0)') AS TIME)
              )
           ,SCH.EDULE.value('(startDate/text())[1]','DATETIME2(0)')
         )
     
     ) AS TRTIME
  FROM   @SXML.nodes('scheduleitem') AS SCI(TEM)
  OUTER APPLY SCI.TEM.nodes('schedule') AS SCH(EDULE)
)

  /*
  Split the new datetime value into date and time
*/
SELECT
  @NEW_DATE = CAST(BD.TRTIME AS DATE) 
 ,@NEW_TIME = CAST(BD.TRTIME AS TIME(0))
FROM  BDATA BD;

  /*
  Update the xml
*/
SET @SXML.modify('replace value of (/scheduleitem/schedule/time[1]/text())[1]   with sql:variable("@NEW_TIME")');
SET @SXML.modify('replace value of (/scheduleitem/schedule/startDate[1]/text())[1] with sql:variable("@NEW_DATE")');
SET @SXML.modify('replace value of (/scheduleitem/schedule/timezoneId[1]/text())[1] with sql:variable("@TZ_ID")' );
SET @SXML.modify('replace value of (/scheduleitem/schedule/timezone[1]/text())[1] with sql:variable("@TZ_NAME")' );

  /*
  View the changes
*/
SELECT @SXML;  


  Update from a table column
  
USE TEEST;
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
 MU.XU_ID
,MU.XU_VAL
,MU.XU_XML
FROM @XMLUPDATE MU;