DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Merging XML Document

  1. #1
    Join Date
    Sep 2005
    Posts
    278

    Merging XML Document

    Hi everyone,

    I have a table with following structure

    SrcCode Char(5),
    DestCode Char(5),
    XMLDoc XMLType.

    I have a requirement to merge all the XML documents with same destCode under one parent tag.

    The contents of XMLDoc column is:

    HTML Code:
             <XMLDoc>
                    <Node1> </Node1>
                    <Node2> </Node2>
             </XMLDoc>
    I want the result in following format

    HTML Code:
    <Documents>
           <XMLDoc></XMLDoc>
           <XMLDoc></XMLDoc>
    </Documents>
    I try the following query but it does not provide me required results

    Select XMLElement("ParentNode", XMLDoc)
    from XMLDocs;

    but the query returns like
    HTML Code:
    <Documents> <XMLDoc></XMLDoc> </Documents>
    <Documents> <XMLDoc></XMLDoc> </Documents>
    Last edited by tabreaz; 07-22-2006 at 08:56 AM.

  2. #2
    Join Date
    May 2006
    Posts
    12
    The logic to follow is:
    - Extract < XMLDoc > nodes that have the same destCode
    - Aggregate the nodes into a single one
    - Add the parent node < Documents >

    Consider the following sample data:
    HTML Code:
    SQL> create table xml_temp
      2  (SrcCode Char(5),
      3  DestCode Char(5),
      4  XMLDoc XMLType);
    
    Table created.
    
    SQL> insert into xml_temp values
      2  ('SC1', 'DC1', '<XMLDoc>
      3                  <Node1>1-DC1-A</Node1>
      4                  <Node2>1-DC1-B</Node2>
      5           </XMLDoc>');
    
    1 row created.
    
    SQL> insert into xml_temp values
      2  ('SC1', 'DC2', '<XMLDoc>
      3                  <Node1>1-DC2-A</Node1>
      4                  <Node2>1-DC2-B</Node2>
      5           </XMLDoc>');
    
    1 row created.
    
    SQL> insert into xml_temp values
      2  ('SC1', 'DC1', '<XMLDoc>
      3                  <Node1>1-DC1-C</Node1>
      4                  <Node2>1-DC1-D</Node2>
      5           </XMLDoc>');
    
    1 row created.
    
    SQL> select x.XMLDoc.EXTRACT('/*') xml from xml_temp x;
    
    XML
    ----------------------------------------------------
    <XMLDoc>
      <Node1>1-DC1-A</Node1>
      <Node2>1-DC1-B</Node2>
    </XMLDoc>
    
    <XMLDoc>
      <Node1>1-DC2-A</Node1>
      <Node2>1-DC2-B</Node2>
    </XMLDoc>
    
    <XMLDoc>
      <Node1>1-DC1-C</Node1>
      <Node2>1-DC1-D</Node2>
    </XMLDoc>
    The query you need:
    HTML Code:
    SQL> SELECT XMLELEMENT("Documents",
      2                    XMLAGG(
      3                      EXTRACT(x.XMLDoc, '/XMLDoc')
      4                          )
      5                   ).EXTRACT('/*') xml
      6  FROM   xml_temp x
      7  WHERE  x.DestCode = '&InputDestCode';
    Enter value for inputdestcode: DC1
    old   7: WHERE  x.DestCode = '&InputDestCode'
    new   7: WHERE  x.DestCode = 'DC1'
    
    XML
    -----------------------------------------------
    <Documents>
      <XMLDoc>
        <Node1>1-DC1-A</Node1>
        <Node2>1-DC1-B</Node2>
      </XMLDoc>
      <XMLDoc>
        <Node1>1-DC1-C</Node1>
        <Node2>1-DC1-D</Node2>
      </XMLDoc>
    </Documents>

  3. #3
    Join Date
    Sep 2005
    Posts
    278
    Thanks a lot

Posting Permissions

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


Click Here to Expand Forum to Full Width