Link to home
Start Free TrialLog in
Avatar of Swadhin Ray
Swadhin RayFlag for United States of America

asked on

Read XML values

Hello Experts,

I have a following XML file inside a table.

My table structure:

create table sr_test 
(
col1 number ,
xml_data xmltype, 
comments varchar2(4000)
);

Open in new window


the column xml_data contains :

Sample_XML_FILE


Now I want to read the data as like below for other tags too:


            SELECT report_attachment, rpt_ra_fk
              FROM XMLTABLE (
                      xmlnamespaces (
                         'http://www.ABCD.com/appstudio/msa' AS "msa"),
                      'msa:datapacket/msa:data/msa:rpt/msa:row'
                      PASSING (SELECT xml_data g_xml_type FROM sr_test)
                      COLUMNS report_attachment VARCHAR2 (4000)
                                 PATH 'msa:report_attachment',
                              rpt_ra_fk VARCHAR2 (4000) PATH 'msa:rpt_ra_fk')
             WHERE rpt_ra_fk IS NOT NULL; 

Open in new window


from the above SQL i am reading the values from the tag "msa:rpt/msa:row"  but in the same SQL i want to read the other values from other tags like "msa:irq" rows data.
Avatar of Sean Stuber
Sean Stuber

what are your expected results?

With nothing to go on as to what you are trying to achieve,  I'll just take a guess.
Union the values you want


SELECT report_attachment, rpt_ra_fk
  FROM XMLTABLE(
           xmlnamespaces('http://www.ABCD.com/appstudio/msa' AS "msa"),
           'msa:datapacket/msa:data/msa:rpt/msa:row'
           PASSING (SELECT xml_data g_xml_type
                      FROM sr_test)
           COLUMNS report_attachment VARCHAR2(4000) PATH 'msa:report_attachment',
                   rpt_ra_fk VARCHAR2(4000) PATH 'msa:rpt_ra_fk'
       )
 WHERE rpt_ra_fk IS NOT NULL
UNION ALL
SELECT irq_attachment, irq_spi_fk
  FROM XMLTABLE(
           xmlnamespaces('http://www.ABCD.com/appstudio/msa' AS "msa"),
           'msa:datapacket/msa:data/msa:irq/msa:row'
           PASSING (SELECT xml_data g_xml_type
                      FROM sr_test)
           COLUMNS irq_attachment VARCHAR2(4000) PATH 'msa:irq_attachment',
                   irq_spi_fk VARCHAR2(4000) PATH 'msa:irq_spi_fk'
       )
 WHERE irq_spi_fk IS NOT NULL;


I'll take another guess that my guess is NOT what you want.  You'll have to be more specific in what you are trying to achieve.
I believe the solution will be the same as it was in your other question.

You'll need to nest the XMLTABLEs.

https://www.experts-exchange.com/questions/28455704/loading-xml-data-dynamically-to-oracle-table.html?anchorAnswerId=40140033#a40140033
Avatar of Swadhin Ray

ASKER

was trying similar method but failing, getting "ORA-19011: Character string buffer too small"

select supplier_code,
    rpt_pk
from (select  xml_data xml FROM sr_test )mydata, 
    xmltable(
        xmlnamespaces(
            'http://www.ABCD.com/appstudio/msa' as "msa",
            'http://www.w3.org/1999/xhtml' as "html"
        ),
        '/msa:datapacket'
        passing xmltype(regexp_replace(mydata.xml,'<\?xml .*\?>'))
        columns
            supplier_code varchar2(4000) path '/msa:datapacket/msa:data/msa:nonmultirow/msa:supplier_code'
            ,rpt xmltype path 'msa:datapacket/msa:data/msa:rpt' 
        ) xmltab,
        xmltable (
            xmlnamespaces(
                'http://www.ABCD.com/appstudio/msa' as "msa"
            ),
        '/msa:rpt/msa:row'
        passing xmltab.rpt
        columns
            rpt_pk varchar2(4000) path '/msa:row/msa:rpt_pk'
        ) rptxml;
        
        

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks this solves what i was looking for..
How does the accepted solution answer:
i want to read the other values from other tags like "msa:irq" rows data.
@slightwv: I used something like below, if you think we can use more better way please suggest, looking for your response and I hope sdstuber will also be fine with another answer and if I split it. :

SELECT             (select email_address from si_users_t where user_id = use_id) Emailadd,
                    irq_ra_id as risk_assessment_id,
                    supplier_code,
                    rpt_object_id,
                    rpt_pk,
                    use_id,
                    rle_id,
                    irq_rss_ra_id,
                    report_attachment,
                    email_to,
                    irq_no,
                    rpt_ra_fk,
                    rpt_id,
                    multirow_region_id,
                    instance_rec_num
               FROM XMLTABLE (
                       xmlnamespaces (
                          'http://www.ABCD.com/appstudio/msa' AS "msa",
                          'http://www.w3.org/1999/xhtml' AS "html"),
                       '/msa:datapacket/msa:data'
                       PASSING (SELECT xml_data FROM sr_test)
                       COLUMNS supplier_code VARCHAR2 (4000)
                                  PATH 'msa:nonmultirow/msa:supplier_code',
                               rpt XMLTYPE PATH 'msa:rpt',
                               irq XMLTYPE PATH 'msa:irq') xmltab,
                    XMLTABLE (
                       xmlnamespaces (
                          'http://www.ABCD.com/appstudio/msa' AS "msa"),
                       '/msa:rpt/msa:row'
                       PASSING xmltab.rpt
                       COLUMNS rpt_object_id VARCHAR2 (4000) PATH 'msa:rpt_object_id',
                               rpt_pk VARCHAR2 (4000) PATH 'msa:rpt_pk',
                               use_id VARCHAR2 (4000) PATH 'msa:use_id',
                               rle_id VARCHAR2 (4000) PATH 'msa:rle_id',
                               irq_rss_ra_id VARCHAR2 (4000) PATH 'msa:irq_rss_ra_id',
                               report_attachment VARCHAR2 (4000)
                                  PATH 'msa:report_attachment',
                               email_to VARCHAR2 (4000) PATH 'msa:email_to',
                               irq_no VARCHAR2 (4000) PATH 'msa:irq_no',
                               rpt_ra_fk VARCHAR2 (4000) PATH 'msa:rpt_ra_fk',
                               rpt_id VARCHAR2 (4000) PATH 'msa:rpt_id',
                               multirow_region_id VARCHAR2 (4000)
                                  PATH 'msa:multirow_region_id',
                               instance_rec_num VARCHAR2 (4000)
                                  PATH 'msa:instance_rec_num') rptxml,
                                  XMLTABLE (
                       xmlnamespaces (
                          'http://www.ABCD.com/appstudio/msa' AS "msa"),
                       '/msa:irq/msa:row'
                       PASSING xmltab.irq
                       COLUMNS irq_ra_id VARCHAR2 (4000) PATH 'msa:irq_ra_id'
                       ) irqxml 
                       WHERE rptxml.rpt_ra_fk IS NOT NULL;

Open in new window

this should be a new question

be sure to include your new xml input data and your expected output in the new question