8 Replies Latest reply on Jun 3, 2013 6:21 AM by ynandi

    Clob Column to XML

    INRi
      HI All,
      I have table like-
      CREATE TABLE C2X_TEST(NAME VARCHAR2(100),XML_CLOB CLOB,XML_XML XMLType);

      here in XML_CLOB column xml files are stored.
      Now i want to update XML_XML column with corresponding XML_CLOB value .

      How to update or insert?
        • 1. Re: Clob Column to XML
          odie_63
          INRi wrote:
          here in XML_CLOB column xml files are stored.
          Now i want to update XML_XML column with corresponding XML_CLOB value .
          UPDATE c2x_test
          SET xml_xml = xmlparse(document xml_clob)
          WHERE ...
          ;
          Now the obvious question : why storing the same data twice in different columns ? Why not loading the XMLType column in the first place ?
          Is it a one-shot migration operation ?
          • 2. Re: Clob Column to XML
            INRi
            Yes you can say one shot of migration.
            I have already one table which has one clob column,now i want it to update to xmlType column.
            How i will do this??
            • 3. Re: Clob Column to XML
              odie_63
              INRi wrote:
              Yes you can say one shot of migration.
              I have already one table which has one clob column,now i want it to update to xmlType column.
              How i will do this??
              Seems like I already gave you the answer, what's the problem?

              Once you've run the update on the whole table, you can drop the CLOB column, if it's what you want to do ultimately.
              • 4. Re: Clob Column to XML
                INRi
                Here i have thousands of records.I want to update in a single process.
                • 5. Re: Clob Column to XML
                  odie_63
                  INRi wrote:
                  Here i have thousands of records.I want to update in a single process.
                  Fine.
                  Let me ask again one last time : What's the problem? Is the update not working ?
                  UPDATE c2x_test
                  SET xml_xml = xmlparse(document xml_clob)
                  WHERE ...
                  ;
                  1 person found this helpful
                  • 6. Re: Clob Column to XML
                    ynandi
                    Hi Odie:

                    Can I do the same thing for blob column to XML ?

                    Also, what is the advantage of converting to an XML type column ?

                    Thanks - Yesh
                    • 7. Re: Clob Column to XML
                      odie_63
                      Can I do the same thing for blob column to XML ?
                      Yes. Use the XMLType constructor in this case :

                      http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/t_xml.htm#i1009842
                        constructor function XMLType(xmlData IN blob, csid IN number,
                                                     schema IN varchar2 := NULL,
                                      validated IN number := 0, wellformed IN number := 0)
                          return self as result deterministic parallel_enable
                      Also, what is the advantage of converting to an XML type column ?
                      Start reading here :
                      http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb03usg.htm#BABEHIJG
                      • 8. Re: Clob Column to XML
                        ynandi
                        Thanks Odie. Will be trying it this week. We have many documents (Excel, Word) stored as BLOB columns in the Oracle EBS - R12 Procurement suite. Our general plan is to convert them using the following steps:

                        STEP 1: Convert BLOB to XMLtype (using the technique you have suggested )

                        STEP 2: Use XQuery to search data ..


                        I have been reading on the XML docs as well as your blog and I think the number of options here can get very confusing .....when to use what (CLOB, BLOB, XMLType, etc) ....

                        I am going to try and stick with the following policy:

                        (a) Use SQL for querying and updating Structured data
                        (b) Use XQuery for querying and updating XML data

                        The other options such as the below do not make sense to me ...

                        (a) Use SQL for XML data
                        (b) Use XML for Structured data

                        Thanks - Yesh