Re: Inserting xml data into an XMLTYPE table
- From: Sayan Malakshinov <xt.and.r@xxxxxxxxx>
- To: "Schauss, Peter (ESS)" <peter.schauss@xxxxxxx>
- Date: Thu, 30 Aug 2012 19:14:04 +0400
Yes, simple example:
>> select *
2 from
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@xxxxxxx> 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@xxxxxxxxx]
> 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@xxxxxxx> 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
>> --
>> //www.freelists.org/webpage/oracle-l
>>
>>
>
>
> --
> Best regards,
> Sayan Malakshinov
> Senior perfomance tuning engineer
--
Best regards,
Sayan Malakshinov
Senior oracle developer
PromSvayzBank
--
//www.freelists.org/webpage/oracle-l
Other related posts:
