4 Replies Latest reply: Nov 1, 2013 4:17 AM by Boneist RSS

    Adding multiple different nodes from one xmltype into another

    Boneist

      Hi,

       

      Given the following two bits of XML:

       

      <root>
        <xmlnode>
          <subnode1>val1</subnode1>
          <subnode2>val2</subnode2>
        </xmlnode>
        <xmlnode>
          <subnode1>val3</subnode1>
          <subnode2>val4</subnode2>
        </xmlnode>
      </root>

       

      <a>
        <b>valb</b>
        <c>valc</c>
        <d>
          <d1>vald1</d1>
          <d2>vald2</d2>
        </d>
        <e>vale</e>
        <f>valf</f>
        <g>
          <g1>valg1</g1>
          <g2>valg2</g2>
        </g>
        <h>
          <h1>valh1</h1>
          <h2>valh2</h2>
        </h>
      </a>

       

      I need to insert some of the nodes from the second xml into a new node at the top of the first xml, like so:

       

      <root>

        <extrainfo>

          <b>valb</b>

          <d>

            <d1>vald1</d1>

            <d2>vald2</d2>

          </d>

          <f>valf</f>

          <h>

            <h1>valh1</h1>

            <h2>valh2</h2>

          </h>

        </extrainfo>

        <xmlnode>

          <subnode1>val1</subnode1>

          <subnode2>val2</subnode2>

        </xmlnode>

        <xmlnode>

          <subnode1>val3</subnode1>

          <subnode2>val4</subnode2>

        </xmlnode>

      </root>

       

      Back when I thought I only had to add a couple of nodes (eg. b and f), I used:

       

      with sample_data as (select xmltype('<root>

                                             <xmlnode>

                                               <subnode1>val1</subnode1>

                                               <subnode2>val2</subnode2>

                                             </xmlnode>

                                             <xmlnode>

                                               <subnode1>val3</subnode1>

                                               <subnode2>val4</subnode2>

                                             </xmlnode>

                                           </root>') xml_to_update,

                                  xmltype('<a>

                                             <b>valb</b>

                                             <c>valc</c>

                                             <d>

                                               <d1>vald1</d1>

                                               <d2>vald2</d2>

                                             </d>

                                             <e>vale</e>

                                             <f>valf</f>

                                             <g>

                                               <g1>valg1</g1>

                                               <g2>valg2</g2>

                                             </g>

                                             <h>

                                               <h1>valh1</h1>

                                               <h2>valh2</h2>

                                             </h>

                                           </a>') xml_to_extract_from

                            from   dual)

      select sd.xml_to_update,

             sd.xml_to_extract_from,

             insertXMLbefore(sd.xml_to_update,

                             '/root/xmlnode[1]',

                             xmltype('<extrainfo>'||

                                       '<b>'||

                                         extractvalue(sd.xml_to_extract_from, '/a/b/text()')||

                                       '</b><f>'||

                                         extractvalue(sd.xml_to_extract_from, '/a/f/text()')||

                                       '</f>'||

                                     '</extrainfo>')) updated_xml

      from   sample_data sd;

       

      which did work, but as I now need to add several other nodes, I don't think this is the wisest way to keep using. I've tried googling and searching the forums but my search-fu must be weak today, as I haven't found any similar examples that I can copy base my solution on. I'm pretty sure there's a funky way of doing it using xquery or something, but I have to admit to not having a clue as to where to even start with xquery!

       

      We're on 11.2.0.3, and the xmltype columns are stored as clobs (sorry Marco Gralike!).

       

      Can anyone help to point me in the right direction, please?

        • 1. Re: Adding multiple different nodes from one xmltype into another
          odie_63

          Hi,

           

          Using XQuery Update :

          SQL> with sample_data as (

            2    select xmltype('<root>

            3                      <xmlnode>

            4                        <subnode1>val1</subnode1>

            5                        <subnode2>val2</subnode2>

            6                      </xmlnode>

            7                      <xmlnode>

            8                        <subnode1>val3</subnode1>

            9                        <subnode2>val4</subnode2>

          10                      </xmlnode>

          11                    </root>') xml_to_update,

          12           xmltype('<a>

          13                      <b>valb</b>

          14                      <c>valc</c>

          15                      <d>

          16                        <d1>vald1</d1>

          17                        <d2>vald2</d2>

          18                      </d>

          19                      <e>vale</e>

          20                      <f>valf</f>

          21                      <g>

          22                        <g1>valg1</g1>

          23                        <g2>valg2</g2>

          24                      </g>

          25                      <h>

          26                        <h1>valh1</h1>

          27                        <h2>valh2</h2>

          28                      </h>

          29                    </a>') xml_to_extract_from

          30    from   dual

          31  )

          32  select xmlserialize(document

          33           xmlquery(

          34             'copy $d := $old

          35              modify (

          36                insert node element extrainfo {

          37                  $new/a/b

          38                , $new/a/d

          39                , $new/a/f

          40                , $new/a/h

          41                } as first into $d/root

          42              )

          43              return $d'

          44             passing sd.xml_to_update as "old"

          45                   , sd.xml_to_extract_from as "new"

          46             returning content

          47           )

          48           indent

          49         )

          50  from sample_data sd ;

           

          XMLSERIALIZE(DOCUMENTXMLQUERY(

          --------------------------------------------------------------------------------

          <root>

            <extrainfo>

              <b>valb</b>

              <d>

                <d1>vald1</d1>

                <d2>vald2</d2>

              </d>

              <f>valf</f>

              <h>

                <h1>valh1</h1>

                <h2>valh2</h2>

              </h>

            </extrainfo>

            <xmlnode>

              <subnode1>val1</subnode1>

              <subnode2>val2</subnode2>

            </xmlnode>

            <xmlnode>

              <subnode1>val3</subnode1>

              <subnode2>val4</subnode2>

            </xmlnode>

          </root>

           

          • 2. Re: Adding multiple different nodes from one xmltype into another
            Boneist

            Once again, odie_63 rides to the rescue! Many, *many* thanks! There's no way in a month of Sundays that I could have come up with that!

             

            Just out of curiosity now (please don't spend any time working on an example - I don't think I'll need to do this in anger... at least, not today!), what would I need to do if one of the nodes in the xml_to_extract_from needed to be renamed when it was merged into the xml_to_update? Is it possible to do that as part of the same xquery, or do I have to do it in a separate step?

            • 3. Re: Adding multiple different nodes from one xmltype into another
              odie_63

              what would I need to do if one of the nodes in the xml_to_extract_from needed to be renamed when it was merged into the xml_to_update? Is it possible to do that as part of the same xquery, or do I have to do it in a separate step?

               

              Sure, you can do it in the same XQuery, just add another element constructor.

              For example, to change <b> for <b2> :

              copy $d := $old

              modify (

                insert node element extrainfo {

                  element b2 { data($new/a/b) }

                , $new/a/d

                , $new/a/f

                , $new/a/h

                } as first into $d/root

              )

              return $d

              • 4. Re: Adding multiple different nodes from one xmltype into another
                Boneist

                Huh, that was even easier than I thought it would be! Thanks again *{:-D