Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2010
    Posts
    33

    Unanswered: How to update all XML attribute?

    Hello
    I have table with XML data as below:
    I need a little help to update the XML attribute.

    Code:
    |         CUSTOMER       |
    ------------------------
    <customer gen="male">
      <name>Bob</name>
    </customer>
    ------------------------
    <customer gen="male">
      <name>Ken</name>
    </customer>
    ------------------------
    <customer gen="male">
      <name>John</name>
    </customer>
    ----------------------

    I want to update the 'gen' to 'gender' for all XML data not one-by-one..
    So the result will look like this

    Code:
    |         CUSTOMER       |
    ------------------------
    <customer gender="male">
      <name>Bob</name>
    </customer>
    ------------------------
    <customer gender="male">
      <name>Ken</name>
    </customer>
    ------------------------
    <customer gender="male">
      <name>John</name>
    </customer>
    ------------------------
    Thanks in advanced

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You cannot change the name of an attribute; you would need to delete the gen attribute and add the gender attribute, e.g. using the XQuery transform expression as shown here: Use of updating expressions in a transform expression (DB2 XQuery)

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Another alternative is to treat the XML document as string, do a string search/replace and then convert the string back to an XML document. But this is rather fragile in case "gen" can occur elsewhere in the stringified document. And you'd also need to write functions that replace all occurrences of "gen" with "gender" by iterating over the string and then using CONCAT and SUBSTR a lot. Nick's suggestion is the much better approach.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Very simple example using Stolze's idea.

    I might be off the point. Because, I know a little about XML and XML support of DB2.

    Create test table and populate it:
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE test_xml
    ( id          SMALLINT NOT NULL
                  GENERATED ALWAYS AS IDENTITY
    , customer    XML
    , PRIMARY KEY (id)
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO test_xml(customer)
    VALUES
      XMLPARSE( DOCUMENT '
    <customer gen="male">
      <name>Bob</name>
    </customer>')
    , XMLPARSE( DOCUMENT '
    <customer gen="male">
      <name>Ken</name>
    </customer>')
    , XMLPARSE( DOCUMENT '
    <customer gen="male">
      <name>John</name>
    </customer>')
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    Before update:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT id
         , XMLSERIALIZE(customer AS VARCHAR(100) ) AS customer
     FROM  test_xml
    ;
    ------------------------------------------------------------------------------
    
    ID     CUSTOMER                                                                                            
    ------ ----------------------------------------------------------------------------------------------------
         1 <customer gen="male"><name>Bob</name></customer>                                                    
         2 <customer gen="male"><name>Ken</name></customer>                                                    
         3 <customer gen="male"><name>John</name></customer>                                                   
    
      3 record(s) selected.
    Update:
    Code:
    ------------------------------ Commands Entered ------------------------------
    UPDATE test_xml
       SET customer
         = XMLPARSE( DOCUMENT
              REPLACE(
                 XMLSERIALIZE(customer AS VARCHAR(100) )
               , ' gen="'
               , ' gender="'
              )
           )
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    After update:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT id
         , XMLSERIALIZE(customer AS VARCHAR(100) ) AS customer
     FROM  test_xml
    ;
    ------------------------------------------------------------------------------
    
    ID     CUSTOMER                                                                                            
    ------ ----------------------------------------------------------------------------------------------------
         1 <customer gender="male"><name>Bob</name></customer>                                                 
         2 <customer gender="male"><name>Ken</name></customer>                                                 
         3 <customer gender="male"><name>John</name></customer>                                                
    
      3 record(s) selected.

  5. #5
    Join Date
    Oct 2010
    Posts
    33
    Yes, Thank you very much tonkuma. You're the man.
    You're method works like charm.
    Last edited by tempe; 06-20-12 at 21:12.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •