TechSapphire Wednesday, 2018-10-17, 12:17 PM
Site menu
Login form
News
Play Games
  • Deep Freeze
  • Ice Slide
  • Gyroball
  • Fat Fish
  • Bush Royal Rampage
  • Updating multiple attribute in XML using "replace value of" is challenge as, we are constraint to update 1 value at once. To achieve this we need to loop for each attribute.

    Below is script used in lesson:

    DECLARE @T TABLE
      (
         xmltext XML
      )

    INSERT INTO @T
    VALUES     ('<Configuration>   <OptIn>     <CodeId IncludeChildren="True">2548578015</CodeId>     <ReadCode>93C0%</ReadCode>   </OptIn>   <OptOut>     <CodeId IncludeChildren="True">2533649013</CodeId>     <CodeId IncludeChildren="True">2548920016</CodeId>   </OptOut> </Configuration>'),
                ('<Configuration>   <OptIn>     <CodeId IncludeChildren="True">2548578015</CodeId>     <ReadCode>93C0%</ReadCode>   </OptIn>   <OptOut>     <CodeId IncludeChildren="True">2533649013</CodeId>     <CodeId IncludeChildren="True">2548920016</CodeId> <CodeId IncludeChildren="True">2548920016</CodeId>   </OptOut> </Configuration>'),
                ('<Configuration>   <OptIn>     <CodeId IncludeChildren="True">2548578015</CodeId>     <ReadCode>93C0%</ReadCode>   </OptIn>   <OptOut>     <CodeId IncludeChildren="True">2533649013</CodeId>     <CodeId IncludeChildren="True">2548920016</CodeId>   </OptOut> </Configuration>')

    SELECT *
    FROM   @T

    WHILE EXISTS (SELECT 1
                  FROM   @t
                  WHERE
    xmltext.exist('/Configuration/OptOut/CodeId[ contains(@IncludeChildren , "True")]') = 1)
      BEGIN
          UPDATE m
          SET
    xmltext.modify('replace value of (/Configuration/OptOut/CodeId[ contains(@IncludeChildren , "True")]/@IncludeChildren)[1] with "false" ')
    FROM   (SELECT xmltext,
    Replace(t.u.value('./@IncludeChildren[1]', 'varchar(100)'), 'True', 'False') AS
    replaceval
     FROM   @t p
            CROSS apply
    p.xmltext.nodes('/Configuration/OptOut/CodeId[ contains(@IncludeChildren , "True")]')t(u))m
    END

    SELECT *
    FROM   @T 

    Categories
    Programming [27]
    Tips for programming
    Security [2]
    Security Tips
    Google [1]
    Use google faster then ever you use
    Project [14]
    HTML [2]
    Electronics [0]
    Data Structure [0]
    Database [16]
    SQL SERVER
    SSRS [1]
    Sql Server Reporting Services
    Copyright MyCorp © 2018