Re: Inserting xml data into an XMLTYPE table

  3      xmltable(
  4         '/XxxsSum/xxxSum'
  5         passing
  6           xmltype('
  7                <XxxsSum>
  8                  <xxxSum><A>a1</A><B>b1</B>...</xxxSum>
  9                  <xxxSum><A>a2</A><B>b2</B>...</xxxSum>
 10                </XxxsSum>'
 11                )
 12         columns
 13            a varchar2(30) path 'A'
 14           ,b varchar2(30) path 'B'
 15         );

A                              B
------------------------------ ------------------------------
a1                             b1
a2                             b2

2 rows selected.

On Thu, Aug 30, 2012 at 5:51 PM, Schauss, Peter (ESS) <peter.schauss_at_ngc.com> wrote:
> Sayan,
>
> Thanks. That worked like a charm.
>
> I would like to write queries against the table showing xml fields as though they were database columns. Is there an easy way to do that?
>
> -Peter Schauss
>
> -----Original Message-----
> From: Sayan Malakshinov [mailto:xt.and.r_at_gmail.com]
> Sent: Wednesday, August 29, 2012 4:55 PM
> To: Schauss, Peter (ESS)
> Subject: EXT :Re: Inserting xml data into an XMLTYPE table
>
> insert into <mytable>
> select *
> from xmltable('/XxxsSum/xxxSum'
> passing xmltype(bfilename('XML_FILES','myfile.xml',
> nls_charset_id('AL32UTF8'))
> )
>
>
> On Wed, Aug 29, 2012 at 11:45 PM, Schauss, Peter (ESS) <peter.schauss_at_ngc.com> wrote:
>> I have an xml file which has the format:
>>
>> <XxxsSum>
>> <xxxSum>
>> <A>...</A><B>...</B>...
>> </xxxSum>
>> <xxxSum>
>> <A>...</A><B>...</B>...
>> </xxxSum>
>> ...
>> </xxxsSum>
>>
>> I have defined an XML_FILES directory and created a table (create table mytable of xmltype; ).
>>
>> If I use a statement like
>> Insert into <mytable> values
>> (xmltype(bfilename('XML_FILES','myfile.xml',
>> nls_charset_id('AL32UTF8')));
>>
>> it goes in as a single row. How can I load this into a table so that each of the <xxxSum>...</xxxSum> blocks is in a separate row?
>>
>> Thanks,
>> Peter Schauss
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>
>
> --
> Best regards,
> Sayan Malakshinov
> Senior perfomance tuning engineer

-- 
Best regards,
Sayan Malakshinov
Senior oracle developer
PromSvayzBank
--
http://www.freelists.org/webpage/oracle-l