2 Replies Latest reply: Apr 14, 2012 10:22 AM by WoodyInAK RSS

    Updatexml to update multinode data

    WoodyInAK
      I've been working on a project for about a week and have utilized the forum for absolutely everything! Not sure what we would do without it. Here is my problem:

      I have the following XML file (sample) in an 11G database as an XMLTYPE table.


      <?xml version="1.0" encoding="utf-8"?>
      <Record>
      <People>
      <Person>
      <ID>643920</ID>
      <HomePhone>907-563-2919</HomePhone>
      <CellPhone>907-442-2231</CellPhone>
      </Person>
      <Person>
      <ID>643926</ID>
      <HomePhone>907-452-5633</HomePhone>
      <CellPhone>907-840-6562</CellPhone>
      </Person>
      <Person>
      <ID>643939</ID>
      <HomePhone>907-333-4915</HomePhone>
      <CellPhone>907-278-9557</CellPhone>
      </Person>
      </People>
      </Record>

      I am trying to update the HomePhone or CellPhone based upon the ID but when I did it it updated all of them.

      update xml_table
      set xml_column = updatexml(xml_column, '/Record/People/Person/HomePhone/text()','907-456-5918')
      where existsNode(xml_column, '/Record/People/Person[ID="643926"]') = 1;

      Any help would be appreciated. I need to be able to update these based upon a variable passed in as the ID.

      Thank you!
        • 1. Re: Updatexml to update multinode data
          odie_63
          Hi,

          The function existsNode() filters rows from the base table, not logical record in the XML itself.

          To update a specific person in the XML document, you must use the XPath predicate in updateXML function :
          SQL> create table xml_table (xml_column xmltype);
           
          Table created
           
          SQL> 
          SQL> insert into xml_table values(xmltype(
            2  '<?xml version="1.0" encoding="utf-8"?>
            3  <Record>
            4  <People>
            5  <Person>
            6  <ID>643920</ID>
            7  <HomePhone>907-563-2919</HomePhone>
            8  <CellPhone>907-442-2231</CellPhone>
            9  </Person>
           10  <Person>
           11  <ID>643926</ID>
           12  <HomePhone>907-452-5633</HomePhone>
           13  <CellPhone>907-840-6562</CellPhone>
           14  </Person>
           15  <Person>
           16  <ID>643939</ID>
           17  <HomePhone>907-333-4915</HomePhone>
           18  <CellPhone>907-278-9557</CellPhone>
           19  </Person>
           20  </People>
           21  </Record>'
           22  ));
           
          1 row inserted
           
          SQL> set long 1000
          SQL> select xml_column from xml_table;
           
          XML_COLUMN
          --------------------------------------------------------------------------------
          <?xml version="1.0" encoding="UTF-8"?>
          <Record>
            <People>
              <Person>
                <ID>643920</ID>
                <HomePhone>907-563-2919</HomePhone>
                <CellPhone>907-442-2231</CellPhone>
              </Person>
              <Person>
                <ID>643926</ID>
                <HomePhone>907-452-5633</HomePhone>
                <CellPhone>907-840-6562</CellPhone>
              </Person>
              <Person>
                <ID>643939</ID>
                <HomePhone>907-333-4915</HomePhone>
                <CellPhone>907-278-9557</CellPhone>
              </Person>
            </People>
          </Record>
           
          SQL> 
          SQL> update xml_table
            2  set xml_column =
            3      updatexml( xml_column
            4               , '/Record/People/Person[ID="643926"]/HomePhone/text()', '907-456-5918'
            5               , '/Record/People/Person[ID="643926"]/CellPhone/text()', '907-456-5919'
            6               )
            7  where existsNode(xml_column, '/Record/People/Person[ID="643926"]') = 1
            8  ;
           
          1 row updated
           
          SQL> select xml_column from xml_table;
           
          XML_COLUMN
          --------------------------------------------------------------------------------
          <?xml version="1.0" encoding="UTF-8"?>
          <Record>
            <People>
              <Person>
                <ID>643920</ID>
                <HomePhone>907-563-2919</HomePhone>
                <CellPhone>907-442-2231</CellPhone>
              </Person>
              <Person>
                <ID>643926</ID>
                <HomePhone>907-456-5918</HomePhone>
                <CellPhone>907-456-5919</CellPhone>
              </Person>
              <Person>
                <ID>643939</ID>
                <HomePhone>907-333-4915</HomePhone>
                <CellPhone>907-278-9557</CellPhone>
              </Person>
            </People>
          </Record>
           
          • 2. Re: Updatexml to update multinode data
            WoodyInAK
            Odie,

            That was definately the issue. I now fully understand how to traverse up and down.

            Thank you so much for the quick and accurate response. I

            sure do love this forum!