This content has been marked as final. Show 3 replies
I'm afraid you're on the wrong track with DBMS_XMLQUERY.
It may be possible by involving some object types or XSLT but it'll require some efforts.
I think SQL/XML functions will serve you better.
What's the db version? (SELECT * FROM v$version)
Can you post some sample data from the base table(s)?
Thanks for reply .
Version is "Oracle Database 11g Enterprise Edition Release 22.214.171.124.0 - 64bit Production".
First I tried with DBMS_XMLGEN ,, it doesnt work properly.
Then tried with XMLAGG and XMLELEMENT but giving me output in 1 row. I need proper XML format.
Following is some data, For which I had created view of 2 tables.
ID STORE_ID EMPLOYEE_ID ACTUAL_ARRIVAL_DATE CARTON_ID SHIPMENT_ID QUANTITY_RECEIVED UNIT_COST RECEIPT_DOC_ID RECEIPT_Date
3772 12340001 PALS001 16-Feb-11 2A632 29 28.000 118.50000 1234 16-Feb-11
3775 12340001 PALS001 16-Feb-11 1A633 30 120.000 218.50000 7866 16-Feb-11
3776 12340001 PALS001 16-Feb-11 2A634 30 75.000 345.75000 7876 16-Feb-11
3773 12340001 PALS001 16-Feb-11 3A632 30 45.000 200.10000 7869 16-Feb-11
3774 12340001 PALS001 16-Feb-11 3A632 30 10.000 450.45000 7869 16-Feb-11
Please suggest better way..
I don't see how most of that data in the sample you provided maps to your XML. Please provide an actual CREATE TABLE and INSERT statements to avoid any confusion/issues.
Then tried with XMLAGG and XMLELEMENT but giving me output in 1 row. I need proper XML format.That was valid XML format. There is no such thing as "proper XML format". I think what you are referring to is human-readable (line returns and indentions). Without them, the XML is smaller actually. A computer system does not care about whether the XML is human readable or not. If it does, that is a bug within that system.
If you are still looking for making human readable XML for some reason, then look at [url http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions239.htm#SQLRF06231]XMLSerialize You would want something like
SELECT XMLSERIALIZE(DOCUMENT XMLElement(...) AS CLOB indent size = 2) FROM ...