Dear Experts,
My problem is simple, but I am having difficulty solving it. Simply, I have an XML file with multiple records in it, with the tag <Row> identifying each record. I am attempting to retrieve each of the values in the leaf nodes within the <Row> parent, where the XML data is stored in a table 'XXIBO_XML_STAGE_TABLE', in an XMLTYPE field. I'm attempting to retrieve each <Row> within a cursor, but I have tried to write this cursor and keep getting errors implying that multiple records can not be retrieved.
Here is an example of the data:
<Table>
<Row no="1">
<INVOICE_ID>11690</INVOICE_ID>
<EVENT_NAME>test cc</EVENT_NAME>
<EVENT_NAME_SHORT>test cc</EVENT_NAME_SHORT>
<EVENT_ID>2388</EVENT_ID>
<EVENT_CITY>nyon</EVENT_CITY>
<EVENT_COUNTRY>CH</EVENT_COUNTRY>
<EVENT_TYPE></EVENT_TYPE>
<DESC>1x 1 GBP</DESC>
<LAST_NAME>Reewrtererter</LAST_NAME>
<FIRST_NAME>Reewrtererter</FIRST_NAME>
<COST>1.00</COST>
<CURRENCY_ID>GBP</CURRENCY_ID>
<PAYMENT_TYPE_ID>CC</PAYMENT_TYPE_ID>
</Row>
<Row no="2">
<INVOICE_ID>13530</INVOICE_ID>
etc....
So I was looking at passing in the <Row no= number, which is incremented on each Fetch loop. Here are some of my attempts:
CURSOR lcn_get_xml_data(p_row IN NUMBER) IS
SELECT XST.eventname EVENT_NAME
, XST.eventtype EVENT_TYPE
, XST.descrip INVOICE_DESC
, XST.lastname LAST_NAME
, XST.firstname FIRST_NAME
, XST.invcost INVOICE_COST
, XST.currencyid CURRENCY_ID
, XST.paymenttypeid PAYMENT_TYPE_ID
FROM XXIBO_XML_STAGE_TABLE
, XMLTABLE('/Table'
PASSING XXIBO_XML_STAGE_TABLE.xml_invoice_data
COLUMNS
eventname VARCHAR2(240) PATH '/Table/Row[@no="' || p_row || '"]/EVENT_NAME',
eventtype VARCHAR2(240) PATH '/Table/Row[@no="' || p_row || '"]/EVENT_TYPE',
lastname VARCHAR2(240) PATH '/Table/Row[@no="' || p_row || '"]/LAST_NAME',
firstname VARCHAR2(240) PATH '/Table/Row[@no="' || p_row || '"]/FIRST_NAME',
) XST
This one errors errors as it seems you can't use a string placeholder in the PATH string (if I was to put a value in, it works e.g. '/Table/Row[@no="2"]/LAST_NAME')
If I use something like:
CURSOR lcn_get_xml_data(p_row IN NUMBER) IS
SELECT extractvalue( XST.xml_invoice_data, '/Table/Row/EVENT_NAME/text()') E
, extractvalue( XST.xml_invoice_data, '/Table/Row/EVENT_TYPE/text()')EVENT_TYPE
FROM XXIBO_XML_STAGE_TABLE XST
WHERE existsNode(XST.xml_invoice_data, '/Table/Row[@no="' || p_row || '"= 1
It will not work, as I have many <Row> records in the file, extractValue can return only one node, even though I am just restricting to the one <Row> record.
I could use:
CURSOR lcn_get_xml_data(p_row IN NUMBER) IS
select extractValue(value(x), '.')
from XXIBO_XML_STAGE_TABLE XST,
table(XMLSequence(
extract( XST.xml_invoice_data, '/Table/Row/EVENT_NAME') )) x
WHERE existsNode(XST.xml_invoice_data, '/Table/Row[@no="' || p_row || '"= 1;
But is is only possible to extract one leaf node value, as in above, if I used more extract() lines, it errors.
Therefore, I have used many dirrerent ways of extracting the data and its not been possible for me to loop through many <Row> records, incrementing the "x" value in the tag <Row no="x">, extracting the leaf node values within each <Row>, for a single XML file.
Any help would be much appreciated!!
Thanks,
Mark