9 Replies Latest reply: May 3, 2012 3:59 PM by 933216 RSS

    UpdateXML using another table (newbie question :) )

    933216
      Hi,
      I am struggling to find a way to do this....
      I have table A (O-R XML Type..tied to the schema and everything). Table A acts as my staging table.
      There is another table B..exactly like A but this is the production table. Both A and B have just one column of type XMLType.

      I want to merge each of the row from A to the one single row in B such that if B's XML contains the element then it is just updated (identified by a key) else added to the B XML.

      updateXML doesnt allow me to pass two OBJECT VALUES...
      UPDATE B SET OBJECT_VALUE = updateXML(B.OBJECT_VALUE,'/parent/chilld' <-- How do I specify here that if A contains this child then update else add?) How do I even bring in A here?
      can someone help?
      thanks in advance,
      -v-
        • 1. Re: UpdateXML using another table (newbie question :) )
          damorgan
          I see no value in your staging table based on what you've written but assuming you keep it ... perhaps you could use APPENDCHILDXML

          http://www.morganslibrary.org/reference/xml_functions.html
          • 2. Re: UpdateXML using another table (newbie question :) )
            933216
            Thanks for the reply.
            Wtih APPENDCHILDXML I still get the same problem..how do I refer to the XML from table A.

            UPDATE warehouses
            SET warehouse_spec = APPENDCHILDXML(warehouse_spec,
            'Warehouse/Building', XMLType('<Owner>Grandco</Owner>'))
            WHERE EXTRACTVALUE(warehouse_spec, '/Warehouse/Building') = 'Rented'; But what if I wan to refer to another table and update Warehouse/building only if say building/@id matches from the other table.

            Why do you say there is no value to staging table?
            • 3. Re: UpdateXML using another table (newbie question :) )
              damorgan
              Define the problem because I fail to see either (A) why you need a staging table or (B) why having put something into it you can not figure out how to get it back out.

              A clear explanation would be very helpful.

              Start with the following question: What is the source of the XML. How is it getting into the database in the first place? Because if you can get it into the staging table I see no reason you can not just put it where it belongs as part of the same process.
              • 4. Re: UpdateXML using another table (newbie question :) )
                933216
                My apologies for sounding so confusing.

                Why I need the staging table?
                The use case that I am trying to solve - My application will get several incoming XMLs one after the other at regular intervals. All conform to a particular schema. Now the application needs to be have one XML (master XML/Master schedule). This master XML gets updated with every new incoming XML if the incoming XML adds more element or changes existing element. So as a quick solution I thought of putting all these incoming XMLs to a table of type XMLType (OR table). Then picking these XMLs one after the other and merging to the main master XML stored in table B - my production table. That way B will always have one row which contains the final merged XML.

                Now, lets say the XML is of the structure <library><book><book-id>1</book-id><name>blah</name></book>.....</library>
                When a new XML comes in I need to do two things - if the given book-id is already present in the master XML then just update the <book> element in the master with this new book element (thus updating the name) from the incoming XML from the staging table. Else if the book-id is not present then insert the node for this book into the Master XML.

                I suppose the UpdateXML, insertXML.. etc functions would do the trick. But those function only refer to one context/object_value. How do I say updateXML prodcution with new node from staging already present else add the new node.
                I saw some examples on the net using XQuery update functions i.e. writing an Xquery which runs insert/replace etc. (XQUF function) and then calling SQL update set ...
                But I cannot find more documentation on it.
                Thanks for all your help so far.
                Sorry for being so verbose.
                • 5. Re: UpdateXML using another table (newbie question :) )
                  933216
                  So here is something that I tried but it didn't work,


                  update DATA_PROD
                  set object_value =
                  xmlquery('declare default element namespace "http://schemas.openxmlformats.org/spreadsheetml/2006/main"; (::)
                  copy $d := $prod
                       copy $e := $stage
                  modify (
                                 let $prodhead := $d/HEAD/row
                                 for $h in $e/HEAD/row
                                 return (
                                 if ($h/HEAD_ID = $prodhead/HEAD_ID) (: already exists :)
                                 then replace node $prodhead with $h
                                 else insert node $h as last into $prodhead
                                 )
                  )
                  return $d'
                  passing DATA_PROD.object_value as "prod", DATA_STAGE.object_value as "stage"
                  returning content)
                  ;

                  gives - SQL Error: ORA-00904: "DATA_STAGE"."OBJECT_VALUE": invalid identifier
                  00904. 00000 - "%s: invalid identifier"

                  Which I understand is because DATA_STAGE is not mentioned in the update table. But then how else can I refer to DATA_STAGE.
                  Both DATA_STATE and DATA_PROD are identical O-R tables conforming to the same schema.
                  • 6. Re: UpdateXML using another table (newbie question :) )
                    odie_63
                    Please give your database version, all digits from : SELECT * FROM v$version
                    XQUF is available starting with 11.2.0.3.

                    Could you post a test case, ie some incoming documents from the staging table, the doc from the prod table, and the expected result in the prod table?
                    • 7. Re: UpdateXML using another table (newbie question :) )
                      933216
                      select * from v$version;
                      BANNER
                      --------------------------------------------------------------------------------
                      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
                      PL/SQL Release 11.2.0.3.0 - Production
                      CORE 11.2.0.3.0 Production
                      TNS for Linux: Version 11.2.0.3.0 - Production
                      NLSRTL Version 11.2.0.3.0 - Production

                      Test Case - I have to make up an XML since I am not allowed to share the actual XMLs (company's policy)
                      Incoming documents from staging table -
                      <Library><Book><ID>1</ID><Name>Some name 1</Name><Cost>5</Cost></Book><Book><ID>2</ID><Name>Some other name</Name><Cost>10</Cost></Book><Library>
                      The above XML gets inserted to my staging table since my schema says, xdb:defaultTable="DATA_STAGING" and the above XML mentions that schema. Similarly if I FTP other XMLs like above they all get inserted as a row in the DATA_STAGING table.
                      Now I want to go through each of these rows and merge it to my production table into a single consolidated XML such that if new Book IDs come in that book is added else the book node is updated in the production table.
                      The production table will only have one entry of XMLType which has the final merged XML.

                      Thanks for your help.
                      -v-
                      • 8. Re: UpdateXML using another table (newbie question :) )
                        odie_63
                        I've run a few simulations.

                        Test case :
                        begin
                          dbms_xmlschema.registerSchema(
                            schemaURL => 'Library.xsd'
                          , schemaDoc => '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" 
                          xmlns:xdb="http://xmlns.oracle.com/xdb"
                          xmlns="http://xmlns.example.com/Library"
                          targetNamespace="http://xmlns.example.com/Library"
                          elementFormDefault="qualified">
                          <xs:complexType name="BookType" xdb:SQLType="BOOK_TYPE" xdb:maintainDOM="false">
                            <xs:sequence>
                              <xs:element name="ID" type="xs:integer"/>
                              <xs:element name="Name" type="xs:string"/>
                              <xs:element name="Cost" type="xs:integer"/>
                            </xs:sequence>
                          </xs:complexType>
                          <xs:complexType name="LibraryType" xdb:SQLType="LIBRARY_TYPE" xdb:maintainDOM="false">
                            <xs:sequence>
                              <xs:element name="Book" type="BookType" maxOccurs="unbounded" xdb:SQLCollType="BOOK_COLL"/>
                            </xs:sequence>
                          </xs:complexType>
                          <xs:element name="Library" type="LibraryType"/>
                         </xs:schema>'
                          , local => true
                          , genTypes => true
                          , genTables => false
                          , enableHierarchy => dbms_xmlschema.ENABLE_HIERARCHY_NONE
                          );
                        end;
                        /
                        
                        
                        create table data_prod of xmltype
                        xmltype store as object relational
                        xmlschema "Library.xsd" element "Library"
                        ;
                        
                        
                        create table data_stage of xmltype
                        xmltype store as object relational
                        xmlschema "Library.xsd" element "Library"
                        ;
                        
                        
                        begin
                          dbms_xmlstorage_manage.renameCollectionTable(
                            tab_name              => 'DATA_STAGE'
                          , xpath                 => '"XMLDATA"."Book"'
                          , collection_table_name => 'DATA_STAGE_BOOK_TABLE'
                          );
                        end;
                        /
                        
                        
                        begin
                          dbms_xmlstorage_manage.renameCollectionTable(
                            tab_name              => 'DATA_PROD'
                          , xpath                 => '"XMLDATA"."Book"'
                          , collection_table_name => 'DATA_PROD_BOOK_TABLE'
                          );
                        end;
                        /
                        
                        
                        create unique index data_prod_book_id_uix on data_prod_book_table ("ID");
                        
                        -- creates the prod document : 
                        insert into data_prod
                        select xmlelement("Library", 
                                 xmlattributes(
                                   'http://xmlns.example.com/Library' as "xmlns"
                                 , 'http://www.w3.org/2001/XMLSchema-instance' as "xmlns:xsi"
                                 , 'http://xmlns.example.com/Library Library.xsd' as "xsi:schemaLocation"
                                 )
                               , xmlagg(
                                   xmlelement("Book",
                                     xmlforest(
                                       object_id as "ID"
                                     , object_name as "Name"
                                     , nvl(data_object_id, 0) as "Cost"
                                     )
                                   )
                                 )
                               )
                        from all_objects
                        where owner = 'SYS'
                        ;
                        
                        -- creates a stage document (new IDs + existing IDs with new Names)
                        insert into data_stage
                        select xmlelement("Library", 
                                 xmlattributes(
                                   'http://xmlns.example.com/Library' as "xmlns"
                                 , 'http://www.w3.org/2001/XMLSchema-instance' as "xmlns:xsi"
                                 , 'http://xmlns.example.com/Library Library.xsd' as "xsi:schemaLocation"
                                 )
                               , xmlagg(
                                   xmlelement("Book",
                                     xmlforest("ID", "Name", "Cost")
                                   )
                                 )
                               )
                        from (
                          select object_id as "ID"
                               , object_name as "Name"
                               , nvl(data_object_id, 0) as "Cost"
                          from all_objects
                          where owner = 'HR'
                          union all
                          select object_id as "ID"
                               , owner || '_' || object_name as "Name"
                               , nvl(data_object_id, 0) as "Cost"
                          from all_objects
                          where owner = 'SYS'
                          and rownum <= 1000
                        );
                        1) Attempt to use a single UPDATE statement with XQUF :
                        update data_prod 
                        set object_value = 
                        xmlquery(
                        'declare default element namespace "http://xmlns.example.com/Library"; (::)
                         copy $prod := .
                         modify (
                           for $i in fn:collection("oradb:/DEV/DATA_STAGE")/Library/Book
                           let $book := $prod/Library/Book[ID=$i/ID]
                           return 
                             if (exists($book))
                               then ( replace value of node $book/Name with $i/Name
                                    , replace value of node $book/Cost with $i/Cost )
                               else insert node $i as last into $prod/Library
                         )
                         return $prod'
                         passing object_value
                         returning content
                        );
                        That works but it's dead slow, so not viable "as is".


                        2) Two UPDATEs - one for existing IDs, another for new ones :
                        update data_prod 
                        set object_value =  
                        xmlquery(
                        'declare default element namespace "http://xmlns.example.com/Library"; (::)
                         copy $prod := .
                         modify (
                           for $i in fn:collection("oradb:/DEV/DATA_STAGE")/Library/Book
                             , $j in $prod/Library/Book
                           where $i/ID = $j/ID
                           return replace value of node $j/Name with $i/Name
                         )
                         return $prod'
                        passing object_value
                        returning content
                        );
                        update data_prod 
                        set object_value = 
                        xmlquery(
                        'declare default element namespace "http://xmlns.example.com/Library"; (::)
                         copy $prod := .
                         modify (
                           for $i in fn:collection("oradb:/DEV/DATA_STAGE")/Library/Book
                           where not(exists($prod/Library/Book[ID=$i/ID]))
                           return insert node $i as last into $prod/Library
                         )
                         return $prod'
                         passing object_value
                         returning content
                        );
                        Overall performance is much better when the two update primitives are invoked separately.


                        3) Rebuilding the document entirely from a FULL OUTER JOINed resultset :
                        with staging as (
                          select xs.* 
                          from data_stage s
                             , xmltable(
                                 xmlnamespaces(default 'http://xmlns.example.com/Library')
                               , '/Library/Book' 
                                 passing s.object_value
                                 columns ID   number         path 'ID'
                                       , Name varchar2(4000) path 'Name'
                                       , Cost number         path 'Cost' 
                               ) xs
                        )
                        select xmlelement("Library",
                                 xmlattributes('http://xmlns.example.com/Library' as "xmlns")
                               , xmlagg(
                                   xmlelement("Book",
                                     case when xp.id = s.id or xp.id is null
                                        then xmlforest(s.ID as "ID", s.Name as "Name", s.Cost as "Cost") 
                                        else xmlforest(xp.ID as "ID", xp.Name as "Name", xp.Cost as "Cost")
                                     end
                                   )
                                 )
                               )
                        from data_prod p
                           , xmltable(
                               xmlnamespaces(default 'http://xmlns.example.com/Library')
                             , '/Library/Book' 
                               passing p.object_value
                               columns ID   number         path 'ID'
                                     , Name varchar2(4000) path 'Name'
                                     , Cost number         path 'Cost' ) xp 
                        full outer join staging s on xp.id = s.id
                        ;
                        • 9. Re: UpdateXML using another table (newbie question :) )
                          933216
                          Thanks a ton!! for taking time and providing that excellent example.
                          I am trying it out..and also trying to modify it to make it work with my schema and XML.
                          Thanks a lot again.
                          -v-