6 Replies Latest reply on Mar 23, 2012 3:39 PM by Marco Gralike

    clob xml

    francy77
      Hi all,
      I have a table that contain clob,

      like this:

      id_flusso number;
      ds_flusso clob

      inside the field ds_flusso there are an xml, and inside the xml there is a code.

      I need to write a query that allow me to retriew the id_flusso and a code in the xml stored in the file, how i can accomplish this.

      thanks
      Francesco
        • 1. Re: clob xml
          odie_63
          Hi,

          Given your previous posts here in the XML DB forum, you should already know how to do it.

          Here's an example :
          SQL> create table documents (id number, xmldoc clob);
           
          Table created
           
          SQL> insert into documents
            2  values (1, '<root><item num="1"><code>TEST</code></item></root>');
           
          1 row inserted
           
          SQL> select id
            2       , xmlcast(
            3           xmlquery('/root/item[@num="1"]/code'
            4                    passing xmlparse(document xmldoc)
            5                    returning content)
            6           as varchar2(30)
            7         ) code
            8  from documents t
            9  ;
           
                  ID CODE
          ---------- ------------------------------
                   1 TEST
           
          1 person found this helpful
          • 2. Re: clob xml
            francy77
            THIS solution works on 11g, but not on 10g

            Is there a solution on 10G also?

            Thanks
            Francesco
            • 3. Re: clob xml
              odie_63
              Always give your database version in the first place (select * from v$version).
              It'll help providing relevant answers.

              On 10g :
              select id
                   , extractvalue(xmltype(xmldoc), '/root/item[@num="1"]/code') code
              from documents
              ;
              • 4. Re: clob xml
                MGralike
                As far as I can see, it should also work on 10.2.x if you replace xmlcast with cast
                • 5. Re: clob xml
                  odie_63
                  As far as I can see, it should also work on 10.2.x if you replace xmlcast with cast
                  Yes, with the following small change :
                  SQL> select * from v$version;
                   
                  BANNER
                  ----------------------------------------------------------------
                  Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
                  PL/SQL Release 10.2.0.5.0 - Production
                  CORE     10.2.0.5.0     Production
                  TNS for 64-bit Windows: Version 10.2.0.5.0 - Production
                  NLSRTL Version 10.2.0.5.0 - Production
                   
                  SQL> create table documents (id number, xmldoc clob);
                   
                  Table created
                   
                  SQL> insert into documents
                    2  values (1, '<root><item num="1"><code>TEST</code></item></root>');
                   
                  1 row inserted
                   
                  SQL> select id
                    2       , cast(
                    3           xmlquery('/root/item[@num="1"]/code/text()'
                    4                    passing xmlparse(document xmldoc)
                    5                    returning content)
                    6           as varchar2(30)
                    7         ) code
                    8  from documents t
                    9  ;
                   
                          ID CODE
                  ---------- ------------------------------
                           1 TEST
                   
                  1 person found this helpful
                  • 6. Re: clob xml
                    Marco Gralike
                    As you said a short while ago...stupid SSO (live watching/waiting on a prio 1 SR call in a different browser), didn't notice SSO was said on a different account. CAST will work in 10.2 for most XMLCAST alternatives in 11.x