Hello,
I am new to XML and have been trying this for few days and get no luck ...
First, I have an Excel file, which contains a list of bills for customers. It is a sample file to test my application.
In the future, my application will receive data of XML-datatype, so my job here is to import this Excel file into my Oracle database 11gr2, then transform it to XML datatype one.
I did import it by using Oracle SQL Developer and nicely stored it in a table called so_lieu.
After that, I run the following code to convert the table data to XML datatype:
CREATE TABLE xmlDoc(temp CLOB);
DECLARE
qryCtx DBMS_XMLGEN.ctxHandle;
temp CLOB;
BEGIN
qryCtx := DBMS_XMLGEN.newContext('SELECT * FROM huy.so_lieu');
-- Set the row header to be bill
DBMS_XMLGEN.setRowTag(qryCtx, 'bill');
-- Get the result
temp := DBMS_XMLGEN.getXML(qryCtx);
INSERT INTO xmlDoc VALUES(temp);
--Close context
DBMS_XMLGEN.closeContext(qryCtx);
END;
/
It is a success:
table XMLDOC created.
anonymous block completed
When I run: SELECT * from xmlDoc; I can get a data of XML-datatype as following:
<?xml version="1.0"?>
<ROWSET>
<bill>
<MA_CQTT>5335</MA_CQTT>
<ACCOUNT>P482668</ACCOUNT>
<TEN_CQTT>Công ty TNHH FrieslandCampina Việt Nam</TEN_CQTT>
<DIACHI>phường Bình Hòa, Thị xã Thuận An, Bình Dương</DIACHI>
<MS_THUE>3700229344- -</MS_THUE>
<SDT>754422 ext 333</SDT>
<LOAI_DV>48</LOAI_DV>
<TEN>Mail - SMD</TEN>
<CUOC_DV>0</CUOC_DV>
<DC>0</DC>
<CUOC_TBSD>0</CUOC_TBSD>
<TRUY_THU>0</TRUY_THU>
<TONG_TIEN>22254545</TONG_TIEN>
<VAT>2225455</VAT>
</bill>
...
<bill>
<MA_CQTT>90255</MA_CQTT>
<ACCOUNT>P0421100</ACCOUNT>
<TEN_CQTT>VPĐD HOME MERIDIAN INTERNATIONAL,INC TạI TP.HCM</TEN_CQTT>
<DIACHI>Tầng trệt, Block C, Cao ốc An Khang, P. An Phú, Q.2, TP.HCM</DIACHI>
<MS_THUE>0302199864- -</MS_THUE>
<SDT>838228511</SDT>
<FAX>838293764</FAX>
<LOAI_DV>7</LOAI_DV>
<TEN>Internet trực tiếp</TEN>
<CUOC_DV>0</CUOC_DV>
<DC>0</DC>
<CUOC_TBSD>35446000</CUOC_TBSD>
<TRUY_THU>0</TRUY_THU>
<TONG_TIEN>35446000</TONG_TIEN>
<VAT>3544600</VAT>
</bill>
</ROWSET>
Then I try to select/query the XML elements inside that XML data (to parse them into another tables) but cannot. I tried:
SELECT XMLQuery('
for $x in ora:view("xmlDoc")/ROW
return $x/TEMP/ROWSET/bill/MA_CQTT
' RETURNING CONTENT) FROM DUAL;
It gave errors
ORA-19276: XPST0005 - XPath step specifies an invalid element/attribute name: (ROWSET)
19276. 00000 - "XP0005 - XPath step specifies an invalid element/attribute name: (%s)"
*Cause: The XPath step specified invalid element or attribute name that did not match any nodes according to the input XML schema or structure.
*Action: Correct the element or attribute name as the name may be mis-spelled.
I read several threads and think that maybe my ROWSET element is not XML anymore. Thus, cannot select using XPath. How can I fix it?
Thank you in advance,
Huy Do.
PS: I tried using XMLTABLE as below:
SELECT x2.*
from XMLDOC h,
XMLTABLE(
'//ROWSET/bill'
PASSING xmltype(h.temp)
COLUMNS ma_cqtt varchar(20) path 'ma_cqtt'
) x2
;
The query gave me a table of 2566 row of "ma_cqtt" but their value are all "null" (excel file has 2566 rows of record)
Message was edited by: 2716502