OraFAQ Forum: JDeveloper, Java & XML

Home » Developer & Programmer » JDeveloper, Java & XML » Remove XML node

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Remove XML node [message #112150]

Wed, 23 March 2005 05:44

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.

Report message to a moderator

Re: Remove XML node [message #112238 is a reply to message #112150]

Wed, 23 March 2005 18:11

Barbara Boehmer
Messages: 9100
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>

Report message to a moderator

Re: Remove XML node [message #112313 is a reply to message #112238]

Thu, 24 March 2005 06:39

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

Barbara Boehmer
Messages: 9100
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>

Report message to a moderator

Re: Remove XML node [message #112436 is a reply to message #112412]

Fri, 25 March 2005 07:01

milind_sri
Messages: 70
Registered: February 2005
Location: Pune

Member

Thanks a million Barbara for your response. You have helped me a lot.

Milind.

Report message to a moderator

Previous Topic:	System error 1114
Next Topic:	Query for XML node

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 10:14:47 CST 2024