Home » Developer & Programmer » JDeveloper, Java & XML » Remove XML node
Remove XML node [message #112150] Wed, 23 March 2005 05:44 Go to next message
milind_sri
Messages: 70
Registered: February 2005
Location: Pune
Member
Hello Experts,

I have this XML structure in my database. Now, the problem I am facing is I want to remove one node from the database table.
I am newbie to XML and cant find a solution for this. I need help from you people.

This is the sample XML structure:-

- <DI xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://www.cch-lis.com/di.xsd">
<DIId>85292</DIId>
<DIType>SOP</DIType>
<IsConditional>true</IsConditional>
<Created>1995-01-16T10:04:56</Created>
<LastModifiedDate>1995-01-16T10:04:56</LastModifiedDate>
<LastModifiedBy>CONV04</LastModifiedBy>
- <Instructions>
- <Instruction>
<Tag>67515F07C51046E380B99B26025D7913</Tag>
- <Conditions>
- <SuitTypeCond>
- <SuitTypes>
<SuitType>35001</SuitType>
</SuitTypes>
</SuitTypeCond>
</Conditions>
- <Actions>
- <Action>
<Tag>5D4D410C6A314078A7FAD95CF0451275</Tag>
<ActionType>DeliverPapers</ActionType>
<What>SOPPapers</What>
<How>FedEx</How>
<Who>24449</Who>
<FedExService>FedEx2Day</FedExService>
- <RecipientInfo>
<ParticipantId>24449</ParticipantId>
<CustomerId>1182</CustomerId>
<ParticipantName>Norman G Orodenker</ParticipantName>
<CustomerName>Tillinghast, Licht, Perkins, Smith & Cohen, LLP</CustomerName>
<AddrLine1>10 Weybosset Street</AddrLine1>
<AddrLine2 />
<City>Providence</City>
<State>RI</State>
<Zip>02903</Zip>
<Country>US</Country>
<Email />
<Phone />
<Fax />
</RecipientInfo>
</Action>
</Actions>
</Instruction>
- <Instruction>
<Tag>0FC6BE1918A24859A420D92BE78609D5</Tag>
- <Conditions>
- <SuitTypeCond>
- <SuitTypes>
<SuitType>35001</SuitType>
</SuitTypes>
</SuitTypeCond>
- <JurisdictionCond>
- <Jurisdictions>
<Juris>70001</Juris>
</Jurisdictions>
</JurisdictionCond>
</Conditions>
- <Actions>
- <Action>
<Tag>C68F96E5CF7C40798BB4897FC0898CED</Tag>
<ActionType>DeliverPapers</ActionType>
<What>SOPPapers</What>
<How>FedEx</How>
<Who>68967</Who>
<FedExService>FedEx2Day</FedExService>
- <RecipientInfo>
<ParticipantId>68967</ParticipantId>
<CustomerId>10223</CustomerId>
<ParticipantName>Sandy Hopkins</ParticipantName>
<CustomerName>Teknor Apex Company</CustomerName>
<AddrLine1>505 Central Avenue</AddrLine1>
<AddrLine2 />
<City>Pawtucket</City>
<State>RI</State>
<Zip>02862</Zip>
<Country>US</Country>
<Email>crecords@cch-lis.com</Email>
<Phone>401-725-8000</Phone>
<Fax>212-894-8930</Fax>
</RecipientInfo>
</Action>
</Actions>
</Instruction>
</Instructions>
</DI>

I want to remove the Juris node from this XML structure. Can anybody give me a query to delete this Juris node entirely from the table. Version is Oracle 9.2.0.5.

Pls. help me on this.
Any help is appreciated.

Milind.
Re: Remove XML node [message #112238 is a reply to message #112150] Wed, 23 March 2005 18:11 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You have not provided your table structure, so I do not know whether you are storing your xml data in a column of xmltype or clob or what. If you are storing it in a clob or some such thing, then you can create a function that uses instr and substr to parse out the data, then use that function in an upate statement. Please see the following example, in which I have used a loop within the function to allow for multiple occurences of a node. I haven't done much with XML, so there may be a better way, using some sort of XML funtion, but I thought I would post this since nobody else has responded yet.

-- table for testing:
scott@ORA92> DESCRIBE your_table
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 XML_COL                                                        CLOB

scott@ORA92> SELECT * FROM your_table
  2  /

XML_COL
--------------------------------------------------------------------------------
<DI xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaL
ocation="http://www.cch-lis.com/di.xsd">
<DIId>85292</DIId>
<DIType>SOP</DIType>
<IsConditional>true</IsConditional>
<Created>1995-01-16T10:04:56</Created>
<LastModifiedDate>1995-01-16T10:04:56</LastModifiedDate>
<LastModifiedBy>CONV04</LastModifiedBy>
- <Instructions>
- <Instruction>
<Tag>67515F07C51046E380B99B26025D7913</Tag>
- <Conditions>
- <SuitTypeCond>
- <SuitTypes>
<SuitType>35001</SuitType>
</SuitTypes>
</SuitTypeCond>
</Conditions>
- <Actions>
- <Action>
<Tag>5D4D410C6A314078A7FAD95CF0451275</Tag>
<ActionType>DeliverPapers</ActionType>
<What>SOPPapers</What>
<How>FedEx</How>
<Who>24449</Who>
<FedExService>FedEx2Day</FedExService>
- <RecipientInfo>
<ParticipantId>24449</ParticipantId>
<CustomerId>1182</CustomerId>
<ParticipantName>Norman G Orodenker</ParticipantName>
<CustomerName>Tillinghast, Licht, Perkins, Smith & Cohen, LLP</CustomerName>
<AddrLine1>10 Weybosset Street</AddrLine1>
<AddrLine2 />
<City>Providence</City>
<State>RI</State>
<Zip>02903</Zip>
<Country>US</Country>
<Email />
<Phone />
<Fax />
</RecipientInfo>
</Action>
</Actions>
</Instruction>
- <Instruction>
<Tag>0FC6BE1918A24859A420D92BE78609D5</Tag>
- <Conditions>
- <SuitTypeCond>
- <SuitTypes>
<SuitType>35001</SuitType>
</SuitTypes>
</SuitTypeCond>
- <JurisdictionCond>
- <Jurisdictions>
<Juris>70001</Juris>
</Jurisdictions>
</JurisdictionCond>
</Conditions>
- <Actions>
- <Action>
<Tag>C68F96E5CF7C40798BB4897FC0898CED</Tag>
<ActionType>DeliverPapers</ActionType>
<What>SOPPapers</What>
<How>FedEx</How>
<Who>68967</Who>
<FedExService>FedEx2Day</FedExService>
- <RecipientInfo>
<ParticipantId>68967</ParticipantId>
<CustomerId>10223</CustomerId>
<ParticipantName>Sandy Hopkins</ParticipantName>
<CustomerName>Teknor Apex Company</CustomerName>
<AddrLine1>505 Central Avenue</AddrLine1>
<AddrLine2 />
<City>Pawtucket</City>
<State>RI</State>
<Zip>02862</Zip>
<Country>US</Country>
<Email>crecords@cch-lis.com</Email>
<Phone>401-725-8000</Phone>
<Fax>212-894-8930</Fax>
</RecipientInfo>
</Action>
</Actions>
</Instruction>
</Instructions>
</DI>


-- function:
scott@ORA92> CREATE OR REPLACE FUNCTION remove_node
  2    (p_xml  IN CLOB,
  3  	p_node IN VARCHAR2)
  4    RETURN	  CLOB
  5  AS
  6    v_xml	  CLOB;
  7  BEGIN
  8    v_xml := p_xml;
  9    WHILE INSTR (v_xml, '<' || p_node || '>') > 0 LOOP
 10  	 v_xml	 := SUBSTR (v_xml, 1, INSTR (v_xml, '<' || p_node || '>') - 1)
 11  		 || SUBSTR (v_xml, INSTR (v_xml, '</' || p_node || '>')
 12  				   + LENGTH (p_node) + 3);
 13    END LOOP;
 14    RETURN v_xml;
 15  END remove_node;
 16  /

Function created.

scott@ORA92> SHOW ERRORS
No errors.


-- update:
scott@ORA92> UPDATE your_table
  2  SET    xml_col = remove_node (xml_col, 'Juris')
  3  /

1 row updated.


-- results:
scott@ORA92> SELECT * FROM your_table
  2  /

XML_COL
--------------------------------------------------------------------------------
<DI xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaL
ocation="http://www.cch-lis.com/di.xsd">
<DIId>85292</DIId>
<DIType>SOP</DIType>
<IsConditional>true</IsConditional>
<Created>1995-01-16T10:04:56</Created>
<LastModifiedDate>1995-01-16T10:04:56</LastModifiedDate>
<LastModifiedBy>CONV04</LastModifiedBy>
- <Instructions>
- <Instruction>
<Tag>67515F07C51046E380B99B26025D7913</Tag>
- <Conditions>
- <SuitTypeCond>
- <SuitTypes>
<SuitType>35001</SuitType>
</SuitTypes>
</SuitTypeCond>
</Conditions>
- <Actions>
- <Action>
<Tag>5D4D410C6A314078A7FAD95CF0451275</Tag>
<ActionType>DeliverPapers</ActionType>
<What>SOPPapers</What>
<How>FedEx</How>
<Who>24449</Who>
<FedExService>FedEx2Day</FedExService>
- <RecipientInfo>
<ParticipantId>24449</ParticipantId>
<CustomerId>1182</CustomerId>
<ParticipantName>Norman G Orodenker</ParticipantName>
<CustomerName>Tillinghast, Licht, Perkins, Smith & Cohen, LLP</CustomerName>
<AddrLine1>10 Weybosset Street</AddrLine1>
<AddrLine2 />
<City>Providence</City>
<State>RI</State>
<Zip>02903</Zip>
<Country>US</Country>
<Email />
<Phone />
<Fax />
</RecipientInfo>
</Action>
</Actions>
</Instruction>
- <Instruction>
<Tag>0FC6BE1918A24859A420D92BE78609D5</Tag>
- <Conditions>
- <SuitTypeCond>
- <SuitTypes>
<SuitType>35001</SuitType>
</SuitTypes>
</SuitTypeCond>
- <JurisdictionCond>
- <Jurisdictions>

</Jurisdictions>
</JurisdictionCond>
</Conditions>
- <Actions>
- <Action>
<Tag>C68F96E5CF7C40798BB4897FC0898CED</Tag>
<ActionType>DeliverPapers</ActionType>
<What>SOPPapers</What>
<How>FedEx</How>
<Who>68967</Who>
<FedExService>FedEx2Day</FedExService>
- <RecipientInfo>
<ParticipantId>68967</ParticipantId>
<CustomerId>10223</CustomerId>
<ParticipantName>Sandy Hopkins</ParticipantName>
<CustomerName>Teknor Apex Company</CustomerName>
<AddrLine1>505 Central Avenue</AddrLine1>
<AddrLine2 />
<City>Pawtucket</City>
<State>RI</State>
<Zip>02862</Zip>
<Country>US</Country>
<Email>crecords@cch-lis.com</Email>
<Phone>401-725-8000</Phone>
<Fax>212-894-8930</Fax>
</RecipientInfo>
</Action>
</Actions>
</Instruction>
</Instructions>
</DI>


scott@ORA92> 


Re: Remove XML node [message #112313 is a reply to message #112238] Thu, 24 March 2005 06:39 Go to previous messageGo to next message
milind_sri
Messages: 70
Registered: February 2005
Location: Pune
Member
Thanks a lot barbara. You are of great help.
I know i am troubling you but if I have to remove the Juris nodes only for particular DI_ID's given then how can I do it.
Suppose we need to delete <Juris>44</Juris> node from xml clob.

Now here are two possibilities:
1. only one <Juris> node with value 44
-----------------------------------------
Then your current solution will work perfect for this situation.

2.If multiple <Juris> nodes are there in which one node is having 44 as the value and also other values are present,for e.g.,

<Jurisdictions>
<Juris>44</Juris>
<Juris>45</Juris>
<Juris>46</Juris>
<Juris>47</Juris>
<Juris>48</Juris>
<Juris>49</Juris>
<Juris>50</Juris>
</Jurisdictions>
Then I want to delete only <Juris>44</Juris> node,however it is deleting all the Juris node.i have tried to modify function given by you,but was not able to do so.So can you please help me out in this matter.
I have not given the table structure previousely so I am providing you this time.

Table Structure:-
Name Null? Type
----------------------------------------- -------- ------------
DI_ID NOT NULL NUMBER(18)
DI_TYPE_CD NOT NULL NUMBER(9)
IS_CONDITIONAL NOT NULL CHAR(1)
IS_CONVERTED NOT NULL CHAR(1)
DI_INFO NOT NULL SYS.XMLTYPE
CREATED_BY NOT NULL VARCHAR2(25)
CREATED_DATE NOT NULL DATE
LAST_MODIFIED_BY NOT NULL VARCHAR2(25)
LAST_MODIFIED_DATE NOT NULL DATE

Awaiting for your precious response.

Milind.

[Updated on: Thu, 24 March 2005 07:51]

Report message to a moderator

Re: Remove XML node [message #112412 is a reply to message #112313] Thu, 24 March 2005 20:57 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
-- table and data for testing:
scott@ORA92> DESCRIBE arv_di
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 DI_ID                                                 NOT NULL NUMBER(18)
 DI_TYPE_CD                                            NOT NULL NUMBER(9)
 IS_CONDITIONAL                                        NOT NULL CHAR(1)
 IS_CONVERTED                                          NOT NULL CHAR(1)
 DI_INFO                                               NOT NULL SYS.XMLTYPE
 CREATED_BY                                            NOT NULL VARCHAR2(25)
 CREATED_DATE                                          NOT NULL DATE
 LAST_MODIFIED_BY                                      NOT NULL VARCHAR2(25)
 LAST_MODIFIED_DATE                                    NOT NULL DATE

scott@ORA92> SELECT * FROM arv_di
  2  /

     DI_ID DI_TYPE_CD I I
---------- ---------- - -
DI_INFO
----------------------------------------------------------------------------------------------------
CREATED_BY                CREATED_D LAST_MODIFIED_BY          LAST_MODI
------------------------- --------- ------------------------- ---------
   8457356      11004 N N
<DI>
  <DIId>8457359</DIId>
  <DIType>SOP</DIType>
  <IsConditional>true</IsConditional>
  <Created>2004-05-08T03:50:40</Created>
  <LastModifiedDate>2005-03-15T21:01:42.000000</LastModifiedDate>
  <LastModifiedBy>1911</LastModifiedBy>
  <Instructions>
    <Instruction>
      <Tag>3B581660C2BB409CB7E06B9E0FBD0AAC</Tag>
      <Conditions>
        <SuitTypeCond>
          <SuitTypes>
            <SuitType>35001</SuitType>
          </SuitTypes>
        </SuitTypeCond>
      </Conditions>
      <Actions>
        <Action>
          <Tag>D89AAB3D317A46E3929CB1A12D39BFD9</Tag>
          <ActionType>DeliverPapers</ActionType>
          <What>SOPPapers</What>
          <How>FedEx</How>
          <Who>814283</Who>
          <FedExService>FedEx2Day</FedExService>
        </Action>
      </Actions>
    </Instruction>
    <Instruction>
      <Tag>FA22906CE72143DDA7ADD6591B5F861E</Tag>
      <Conditions>
        <SuitTypeCond>
          <SuitTypes>
            <SuitType>35003</SuitType>
          </SuitTypes>
        </SuitTypeCond>
        <JurisdictionCond>
          <Jurisdictions>
            <Juris>44</Juris>
            <Juris>45</Juris>
            <Juris>46</Juris>
            <Juris>47</Juris>
            <Juris>48</Juris>
            <Juris>49</Juris>
            <Juris>50</Juris>
          </Jurisdictions>
        </JurisdictionCond>
      </Conditions>
      <Actions>
        <Action>
          <Tag>C2E892A22624439981689774C68B9EC2</Tag>
          <ActionType>DeliverPapers</ActionType>
          <What>SOPPapers</What>
          <How>FedEx</How>
          <Who>8888</Who>
          <FedExService>FedEx2Day</FedExService>
        </Action>
      </Actions>
    </Instruction>
  </Instructions>
</DI>
ME                        24-MAR-05 ME                        24-MAR-05


-- function:
scott@ORA92> CREATE OR REPLACE FUNCTION remove_node
  2    (p_xml	IN XMLTYPE,
  3  	p_node	IN VARCHAR2,
  4  	p_value IN VARCHAR2 DEFAULT NULL)
  5    RETURN	   XMLTYPE
  6  AS
  7    v_clob	   CLOB;
  8    v_string    VARCHAR2(32767);
  9  BEGIN
 10    v_clob := p_xml.getClobval();
 11    IF p_value IS NULL THEN
 12  	 WHILE INSTR (v_clob, '<' || p_node || '>') > 0 LOOP
 13  	   v_clob   := SUBSTR (v_clob, 1, INSTR (v_clob, '<' || p_node || '>') - 2)
 14  		   || SUBSTR (v_clob, INSTR (v_clob, '</' || p_node || '>')
 15  				     + LENGTH (p_node) + 3);
 16  	 END LOOP;
 17    ELSE
 18  	 v_string := '<' || p_node || '>' || p_value || '</' || p_node || '>';
 19  	 WHILE INSTR (v_clob, v_string) > 0 LOOP
 20  	   v_clob   := SUBSTR (v_clob, 1, INSTR (v_clob, v_string) - 2)
 21  		   || SUBSTR (v_clob, INSTR (v_clob, v_string) + LENGTH (v_string));
 22  	 END LOOP;
 23    END IF;
 24    RETURN XMLTYPE (v_clob);
 25  END remove_node;
 26  /

Function created.

scott@ORA92> SHOW ERRORS
No errors.


-- to update removing only 'Juris' nodes with value of '44':
scott@ORA92> UPDATE arv_di
  2  SET    di_info = remove_node (di_info, 'Juris', '44')
  3  /

1 row updated.

scott@ORA92> -- results:
scott@ORA92> SELECT * FROM arv_di
  2  /

     DI_ID DI_TYPE_CD I I
---------- ---------- - -
DI_INFO
----------------------------------------------------------------------------------------------------
CREATED_BY                CREATED_D LAST_MODIFIED_BY          LAST_MODI
------------------------- --------- ------------------------- ---------
   8457356      11004 N N
<DI>
  <DIId>8457359</DIId>
  <DIType>SOP</DIType>
  <IsConditional>true</IsConditional>
  <Created>2004-05-08T03:50:40</Created>
  <LastModifiedDate>2005-03-15T21:01:42.000000</LastModifiedDate>
  <LastModifiedBy>1911</LastModifiedBy>
  <Instructions>
    <Instruction>
      <Tag>3B581660C2BB409CB7E06B9E0FBD0AAC</Tag>
      <Conditions>
        <SuitTypeCond>
          <SuitTypes>
            <SuitType>35001</SuitType>
          </SuitTypes>
        </SuitTypeCond>
      </Conditions>
      <Actions>
        <Action>
          <Tag>D89AAB3D317A46E3929CB1A12D39BFD9</Tag>
          <ActionType>DeliverPapers</ActionType>
          <What>SOPPapers</What>
          <How>FedEx</How>
          <Who>814283</Who>
          <FedExService>FedEx2Day</FedExService>
        </Action>
      </Actions>
    </Instruction>
    <Instruction>
      <Tag>FA22906CE72143DDA7ADD6591B5F861E</Tag>
      <Conditions>
        <SuitTypeCond>
          <SuitTypes>
            <SuitType>35003</SuitType>
          </SuitTypes>
        </SuitTypeCond>
        <JurisdictionCond>
          <Jurisdictions>
            <Juris>45</Juris>
            <Juris>46</Juris>
            <Juris>47</Juris>
            <Juris>48</Juris>
            <Juris>49</Juris>
            <Juris>50</Juris>
          </Jurisdictions>
        </JurisdictionCond>
      </Conditions>
      <Actions>
        <Action>
          <Tag>C2E892A22624439981689774C68B9EC2</Tag>
          <ActionType>DeliverPapers</ActionType>
          <What>SOPPapers</What>
          <How>FedEx</How>
          <Who>8888</Who>
          <FedExService>FedEx2Day</FedExService>
        </Action>
      </Actions>
    </Instruction>
  </Instructions>
</DI>
ME                        24-MAR-05 ME                        24-MAR-05


-- to update removing all 'Juris' nodes:
scott@ORA92> UPDATE arv_di
  2  SET    di_info = remove_node (di_info, 'Juris')
  3  /

1 row updated.

scott@ORA92> -- results:
scott@ORA92> SELECT * FROM arv_di
  2  /

     DI_ID DI_TYPE_CD I I
---------- ---------- - -
DI_INFO
----------------------------------------------------------------------------------------------------
CREATED_BY                CREATED_D LAST_MODIFIED_BY          LAST_MODI
------------------------- --------- ------------------------- ---------
   8457356      11004 N N
<DI>
  <DIId>8457359</DIId>
  <DIType>SOP</DIType>
  <IsConditional>true</IsConditional>
  <Created>2004-05-08T03:50:40</Created>
  <LastModifiedDate>2005-03-15T21:01:42.000000</LastModifiedDate>
  <LastModifiedBy>1911</LastModifiedBy>
  <Instructions>
    <Instruction>
      <Tag>3B581660C2BB409CB7E06B9E0FBD0AAC</Tag>
      <Conditions>
        <SuitTypeCond>
          <SuitTypes>
            <SuitType>35001</SuitType>
          </SuitTypes>
        </SuitTypeCond>
      </Conditions>
      <Actions>
        <Action>
          <Tag>D89AAB3D317A46E3929CB1A12D39BFD9</Tag>
          <ActionType>DeliverPapers</ActionType>
          <What>SOPPapers</What>
          <How>FedEx</How>
          <Who>814283</Who>
          <FedExService>FedEx2Day</FedExService>
        </Action>
      </Actions>
    </Instruction>
    <Instruction>
      <Tag>FA22906CE72143DDA7ADD6591B5F861E</Tag>
      <Conditions>
        <SuitTypeCond>
          <SuitTypes>
            <SuitType>35003</SuitType>
          </SuitTypes>
        </SuitTypeCond>
        <JurisdictionCond>
          <Jurisdictions/>
        </JurisdictionCond>
      </Conditions>
      <Actions>
        <Action>
          <Tag>C2E892A22624439981689774C68B9EC2</Tag>
          <ActionType>DeliverPapers</ActionType>
          <What>SOPPapers</What>
          <How>FedEx</How>
          <Who>8888</Who>
          <FedExService>FedEx2Day</FedExService>
        </Action>
      </Actions>
    </Instruction>
  </Instructions>
</DI>
ME                        24-MAR-05 ME                        24-MAR-05


scott@ORA92> 


Re: Remove XML node [message #112436 is a reply to message #112412] Fri, 25 March 2005 07:01 Go to previous message
milind_sri
Messages: 70
Registered: February 2005
Location: Pune
Member
Thanks a million Barbara for your response. You have helped me a lot.

Milind.
Previous Topic: System error 1114
Next Topic: Query for XML node
Goto Forum:
  


Current Time: Mon Mar 04 05:22:00 CST 2024