none
Update multple XML nodes under some specific root node RRS feed

  • Question

  • I want to update all the status field value which are under Order id = 2. I am able to update status value for absolute path but cannot update all of them at once.

    Here is the example XML.

    <

     

    Orders>

    <

     

    Order1 status="2" id ="1">

    <

     

    Product status="0" />

    <

     

    Product status ="1" />

    </

     

    Order1>

    </

     

    Orders>

    Monday, December 13, 2010 10:02 AM

Answers

  • I've altered the XPath slightly for your new example. 

    DECLARE @xml XML 
    SET @xml = '<Orders>
     <Order1 status="2" id="1">
     <Product status="0" />
     <Product status="1" />
     </Order1>
     <Order1 status="2" id="2">
     <Product status="0" />
     <Product status="1" />
     </Order1>
    </Orders>'
    
    SELECT 'before', DATALENGTH(@xml), @xml
    
    DECLARE @v_id INT 
    SET @v_id = 0
    
    WHILE @xml.exist( '/Orders/Order1[@id = "2"]/*[@status != "99"]') = 1
    BEGIN
    SET @xml.modify( 'replace value of (/Orders/Order1[@id = "2"]//@status[.!="99"])[1] with "99"' )
    SET @v_id = @v_id + 1 
    
    IF @v_id > 99 BEGIN RAISERROR( 'Too many loops! %i', 16, 1, @v_id ) BREAK END
    
    END
    SELECT @v_id loops
    SELECT 'after', DATALENGTH(@xml), @xml
    
    

    Please learn to use the 'Insert Code Block' button which looks like this </>

     

    • Marked as answer by goldenagent Monday, December 13, 2010 2:53 PM
    Monday, December 13, 2010 12:44 PM

All replies

  • Sorry, i'm not quite clear on your requirement- can you give an example of the expected output and also what queries you have tried already
    every day is a school day
    Monday, December 13, 2010 10:47 AM
    Moderator
  • The target for an XML replace must always be one node, so you have to do it in a loop.  Try something like this:

    DECLARE @xml XML
    
    SET @xml = '<Orders>
     <Order1 status="2" id ="1">
      <Product status="0"/>
      <Product status ="1" />
     </Order1>
    </Orders>'
    
    
    SELECT 'before', DATALENGTH(@xml), @xml
    
    DECLARE @id INT
    
    SET @id = 0
    
    WHILE @xml.exist( '/Orders/Order1[@id = "1"]/*[@status != "99"]') = 1
    BEGIN
    
    	SET @xml.modify( 'replace value of (/Orders[Order1[@id = "1"]]//*[@status != "99"]/@status)[1] with "99"' )
    
    	SET @id = @id + 1
    	
    	IF @id > 99 BEGIN RAISERROR( 'Too many loops! %i', 16, 1, @id ) BREAK END
    
    END
    
    SELECT @id loops
    
    SELECT 'after', DATALENGTH(@xml), @xml
    
    Monday, December 13, 2010 10:54 AM
  • I want to update the status for order with id 2 the output will be like this

    <

     

    Orders>

    <

     

    Order status="2" id ="1">

    <

     

    Product status="0" id ="123"/>

    <

     

    Product status ="1" id ="456" />

    </

     

    Order>

    <

     

    Order status="3" id ="2">

    <

     

    Product status="3" id="856"/>

    <

     

    Product status ="3" id ="123"/>

    </

     

    Order>

    </

     

    Orders>

    update query

    update xmlcolumn

    set

    xmlcolumn.modify('replace value of (//@status)[1] with xs:string("3")')

    where

    xmlcolumn.value('(/Orders/Order/product/@id)[1]','char(6)') = 2


    saleem
    Monday, December 13, 2010 10:55 AM
  • If your XML is in a table then the UPDATE is slightly different:

    DECLARE @query TABLE ( xmlcolumn XML )
    
    INSERT INTO @query
    VALUES ('<Orders>
     <Order status="2" id ="1">
      <Product status="0" id ="123"/>
      <Product status ="1" id ="456" />
     </Order>
     <Order status="3" id ="2">
      <Product status="3" id="856"/>
      <Product status ="3" id ="123"/>
     </Order>
    </Orders>')
    
    
    SELECT 'before', DATALENGTH(xmlcolumn), xmlcolumn
    FROM @query
    
    DECLARE @id INT
    
    SET @id = 0
    
    WHILE EXISTS ( SELECT * FROM @query WHERE xmlcolumn.exist( '/Orders/Order[@id = "2"]/*[@status != "99"]') = 1 )
    BEGIN
    
    	UPDATE @query
    	SET xmlcolumn.modify( 'replace value of (/Orders[Order[@id = "2"]]//*[@status != "99"]/@status)[1] with "99"' )
    
    	SET @id = @id + 1
    	
    	IF @id > 99 BEGIN RAISERROR( 'Too many loops! %i', 16, 1, @id ) BREAK END
    
    END
    
    SELECT @id loops
    
    SELECT 'after', DATALENGTH(xmlcolumn), xmlcolumn
    FROM @query
    
    • Proposed as answer by wBob Monday, December 13, 2010 12:06 PM
    Monday, December 13, 2010 12:03 PM
  • Thanks BOB....But while trying to update the status of order id = 2 i am getting some error...please have a look

    DECLARE

     

    @xml XML

    SET

     

    @xml = '<Orders>

    <Order1 status="2" id ="1">

    <Product status="0"/>

    <Product status ="1" />

    </Order1>

    <Order1 status="2" id ="2">

    <Product status="0"/>

    <Product status ="1" />

    </Order1>

    </Orders>'

     

    SELECT

     

    'before', DATALENGTH(@xml), @xml

    DECLARE

     

    @v_id INT

    SET

     

    @v_id = 0

    WHILE

     

    @xml.exist( '/Orders/Order1[@id = "2"]/*[@status != "3"]') = 1

    BEGIN

     

    SET @xml.modify( 'replace value of (/Orders[Order1[@id = "2"]]//*[@status != "3"]/@status)[1] with "3"' )

     

    SET @v_id = @v_id + 1

     

     

    IF @v_id > 3 BEGIN RAISERROR( 'Too many loops! %i', 16, 1, @v_id ) BREAK END

    END

    SELECT

     

    @v_id loops

    SELECT

     

    'after', DATALENGTH(@xml), @xml


    saleem
    Monday, December 13, 2010 12:24 PM
  • I've altered the XPath slightly for your new example. 

    DECLARE @xml XML 
    SET @xml = '<Orders>
     <Order1 status="2" id="1">
     <Product status="0" />
     <Product status="1" />
     </Order1>
     <Order1 status="2" id="2">
     <Product status="0" />
     <Product status="1" />
     </Order1>
    </Orders>'
    
    SELECT 'before', DATALENGTH(@xml), @xml
    
    DECLARE @v_id INT 
    SET @v_id = 0
    
    WHILE @xml.exist( '/Orders/Order1[@id = "2"]/*[@status != "99"]') = 1
    BEGIN
    SET @xml.modify( 'replace value of (/Orders/Order1[@id = "2"]//@status[.!="99"])[1] with "99"' )
    SET @v_id = @v_id + 1 
    
    IF @v_id > 99 BEGIN RAISERROR( 'Too many loops! %i', 16, 1, @v_id ) BREAK END
    
    END
    SELECT @v_id loops
    SELECT 'after', DATALENGTH(@xml), @xml
    
    

    Please learn to use the 'Insert Code Block' button which looks like this </>

     

    • Marked as answer by goldenagent Monday, December 13, 2010 2:53 PM
    Monday, December 13, 2010 12:44 PM
  • Sorry Peso, I think you've misread my post and misunderstood the OP question.

    "The target for an XML replace must always be one node..."

    This is true.  Multiple attribute values are to be replaced, therefore this must be done in a loop. 

    Monday, December 13, 2010 2:43 PM
  • I am sure Peso misunderstood your statement. You are right, the target being updated (or replaced) must be, at most, one node. See the definition under the argument Expression1.

    replace value of (XML DML)


    AMB

    Some guidelines for posting questions...

    Monday, December 13, 2010 7:38 PM
    Moderator
  • Yes there is always more than one way to do things! : )

    Tuesday, December 14, 2010 10:07 AM