How can I tell which XML file data has come from?
437568Feb 2 2005 — edited Feb 23 2005Hi
I am loading various XML files into XML DB via a registered schema, and querying the data using the following view.
CREATE OR REPLACE VIEW vw_xml_tracks AS
select extractvalue(value(x),'/rowset/@supplier') SUPPLIER
, extractvalue(value(x),'/rowset/@territory') TERRITORY
, extractvalue(value(z),'/track/@isrc') ISRC
, extractvalue(value(z),'/track/@trancount') SALES
, extractvalue(value(z),'/track/@custcount') CUSTCOUNT
, extractvalue(value(z),'/track/@internalid') INTERNALID
, extractvalue(value(z),'/track/@artistname') ARTISTNAME
, extractvalue(value(z),'/track/@title') TITLE
, extractvalue(value(z),'/track/@promo') PROMO
, extractvalue(value(y),'/activity/@postcode') POSTCODE
, extractvalue(value(y),'/activity/@tranDate') TRANDATE
, extractvalue(value(y),'/activity/@tranType') TRANTYPE
from xml_sales_load xml_sales_load
,table(xmlsequence(extract(value(xml_sales_load),'/rowset'))) x
,table(xmlsequence(extract(value(xml_sales_load),'/rowset/activity'))) y
,table(xmlsequence(extract(value(y),'/activity/track'))) z
/
I also want to add the original file that each row of data was originally loaded from. I know I can use resource_view to see these files, but can't for the life of me work out how to tie the sgredded data back to the source file. I'm on 9iR2...