Swadhin Ray
asked on
Read XML values
Hello Experts,
I have a following XML file inside a table.
My table structure:
the column xml_data contains :
Sample_XML_FILE
Now I want to read the data as like below for other tags too:
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.
I have a following XML file inside a table.
My table structure:
create table sr_test
(
col1 number ,
xml_data xmltype,
comments varchar2(4000)
);
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;
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.
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
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
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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
i want to read the other values from other tags like "msa:irq" rows data.
ASKER
@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;
this should be a new question
be sure to include your new xml input data and your expected output in the new question
be sure to include your new xml input data and your expected output in the new question
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/m
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/m
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.