Solved

XML-Select Result

Posted on 2006-06-14
16
836 Views
Last Modified: 2012-05-05
lets say i have such XML-Doc stored in a xmltype-column in oracle:

<TEST>
  <ORDER>
    <ORDER_NO>1234</ORDER_NO>
    <POSITIONS>
       <POSITION>
          <POSITION_NO>1</POSITION_NO>
          <ARTIKEL_NO>7890</ARTIKEL_NO>
          <AMOUNT>1</AMOUNT>
       </POSITION>
       <POSITION>
          <POSITION_NO>2</POSITION_NO>
          <ARTIKEL_NO>0987</ARTIKEL_NO>
          <AMOUNT>1</AMOUNT>
       </POSITION>
    </POSITIONS>
  </ORDER>
  <ORDER>    
    <ORDER_NO>2547</ORDER_NO>
    <POSITIONS>
       <POSITION>
          <POSITION_NO>1</POSITION_NO>
          <ARTIKEL_NO>1245</ARTIKEL_NO>
          <AMOUNT>1</AMOUNT>
       </POSITION>
    </POSITIONS>
  </ORDER>
</TEST>    

howto select from this to have such a result:

ORDER_NO      POSITION_NO      ARTIKEL_NO      AMOUNT

1234            1            7890            1
1234            2            0987            1
2547            1            1245            1

Possible, if yes, how

please no links, preferable without additional creating objects in oracle

meikl ;-)
0
Comment
Question by:kretzschmar
  • 8
  • 7
16 Comments
 
LVL 16

Expert Comment

by:MohanKNair
ID: 16900563
If the data is stored in CLOB data type it is possible to parse using dbms_xmlsave package. The XML data can also be stored in a temporary LOB and passed to dbms_xmlsave.insertxml procedure. Data from xmltype datatype can be converted to clob data in a temporary variable in PL/SQL.

BEGIN
l_ctx   dbms_xmlsave.ctxType;
l_rows  number;
l_clob CLOB;
BEGIN
l_ctx := dbms_xmlsave.newContext('MYEMP');
l_rows := dbms_xmlsave.insertxml(l_ctx,l_clob);
dbms_xmlsave.closeContext(l_ctx);
END;
/

Also see these links

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:4061080732051

http://www.oracle.com/technology/oramag/oracle/03-jul/o43xml.html
0
 
LVL 27

Author Comment

by:kretzschmar
ID: 16900948
thanks MohanKNair,
looks good so far in theory

it will take some time (some days) for implementing/evaluating
so please be patient

meikl ;-)
0
 
LVL 16

Accepted Solution

by:
MohanKNair earned 500 total points
ID: 16901090
In the sample code l_xmlt holds the XML data. The tablename is passed to dbms_xmlsave.newContext. This table will have the parsed data.

Run the SQL script to create dbms_xmlsave package
$ORACLE_HOME/rdbms/admin/dbmsxml.sql

declare
l_clob CLOB;
l_ctx   dbms_xmlsave.ctxType;
l_rows  number;
l_xmlt  xmltype;
BEGIN
l_clob := xmltype.getclobval(l_xmlt);
l_ctx := dbms_xmlsave.newContext('MYTAB');
l_rows := dbms_xmlsave.insertxml(l_ctx,l_clob);
dbms_xmlsave.closeContext(l_ctx);
END;
/

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 19

Expert Comment

by:actonwang
ID: 16902612
if you store it as xmltype in the column, you might just apply styleshhet and use

xmltype.transform()

to do it.

0
 
LVL 27

Author Comment

by:kretzschmar
ID: 16918249
sorry for delay (because yesterday was a holiday)

to actonwang,

will this also applyable if the xmltype-column may used for various xml-docs?
if so, could you give a sample?

trying now MohanKNair suggestion . . .

meikl ;-)
 
0
 
LVL 27

Author Comment

by:kretzschmar
ID: 16918802
hmm, MohanKNair,

it works partial

my testcase (based on the xml-doc i posted in question):

DECLARE
  l_clob  CLOB := '<TEST><ORDER><ORDER_NO>1234</ORDER_NO><POSITIONS><POSITION>'||
                  '<POSITION_NO>1</POSITION_NO><ARTIKEL_NO>7890</ARTIKEL_NO>'||
                  '<AMOUNT>1</AMOUNT></POSITION><POSITION><POSITION_NO>2'||
                  '</POSITION_NO><ARTIKEL_NO>0987</ARTIKEL_NO><AMOUNT>1</AMOUNT>'||
                  '</POSITION></POSITIONS></ORDER><ORDER><ORDER_NO>2547</ORDER_NO>'||
                  '<POSITIONS><POSITION><POSITION_NO>1</POSITION_NO><ARTIKEL_NO>'||
                  '1245</ARTIKEL_NO><AMOUNT>1</AMOUNT></POSITION></POSITIONS>'||
                  '</ORDER></TEST>';
 
  l_ctx   dbms_xmlsave.ctxType;
  l_rows  NUMBER;
BEGIN
  l_ctx := dbms_xmlsave.newContext('MK_XML_TEST');
  ****<--- there is a line i describe later
  l_rows := dbms_xmlsave.insertxml(l_ctx,l_clob);
  dbms_xmlsave.closeContext(l_ctx);
  DBMS_OUTPUT.PUT_LINE(l_rows || ' rows inserted...');
END;

i have created a simple table for this

CREATE TABLE mk_xml_test
(
  ORDER_NO     NUMBER,
  POSITION_NO  NUMBER,
  ARTIKEL_NO   NUMBER,
  AMOUNT       NUMBER
);

-----------------------------------------------------------
if i use for
  ****
  the line:
  dbms_xmlsave.setrowtag(l_ctx,'ORDER');

then the result is (only order_no is filled)

ORDER_NO      POSITION_NO      ARTIKEL_NO      AMOUNT

1234                  
2547                  
-----------------------------------------------------------
if i use for
  ****
  the line:
  dbms_xmlsave.setrowtag(l_ctx,'POSITION');

then the result is (all except order_no is filled)

ORDER_NO      POSITION_NO      ARTIKEL_NO      AMOUNT

NULL      1      7890      1
NULL      2      987      1
NULL      1      1245      1
------------------------------------------------------------

so how to fix this, to get the recommended result (see question)

appendix: there is no chance to change the xml-structure!


meikl ;-)
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 16919020
Try this code

DECLARE
  l_clob  CLOB := '<TEST><ORDER><ORDER_NO>1234</ORDER_NO><POSITIONS><POSITION>'||
                  '<POSITION_NO>1</POSITION_NO><ARTIKEL_NO>7890</ARTIKEL_NO>'||
                  '<AMOUNT>1</AMOUNT></POSITION><POSITION><POSITION_NO>2'||
                  '</POSITION_NO><ARTIKEL_NO>0987</ARTIKEL_NO><AMOUNT>1</AMOUNT>'||
                  '</POSITION></POSITIONS></ORDER><ORDER><ORDER_NO>2547</ORDER_NO>'||
                  '<POSITIONS><POSITION><POSITION_NO>1</POSITION_NO><ARTIKEL_NO>'||
                  '1245</ARTIKEL_NO><AMOUNT>1</AMOUNT></POSITION></POSITIONS>'||
                  '</ORDER></TEST>';
  l_ctx   dbms_xmlsave.ctxType;
  l_rows  NUMBER;
BEGIN
  l_ctx := dbms_xmlsave.newContext('MK_XML_TEST');
dbms_xmlsave.setrowtag(l_ctx,'ARTIKEL_NO');
dbms_xmlsave.setrowtag(l_ctx,'AMOUNT');
dbms_xmlsave.setrowtag(l_ctx,'POSITION');
dbms_xmlsave.setrowtag(l_ctx,'ORDER');
  l_rows := dbms_xmlsave.insertxml(l_ctx,l_clob);
  dbms_xmlsave.closeContext(l_ctx);
  DBMS_OUTPUT.PUT_LINE(l_rows || ' rows inserted...');
END;
/

SQL> select * from MK_XML_TEST;

  ORDER_NO POSITION_NO ARTIKEL_NO     AMOUNT
---------- ----------- ---------- ----------
      1234
      2547


Change the order

dbms_xmlsave.setrowtag(l_ctx,'ORDER');
dbms_xmlsave.setrowtag(l_ctx,'ARTIKEL_NO');
dbms_xmlsave.setrowtag(l_ctx,'AMOUNT');
dbms_xmlsave.setrowtag(l_ctx,'POSITION');

  ORDER_NO POSITION_NO ARTIKEL_NO     AMOUNT
---------- ----------- ---------- ----------
                     1       7890          1
                     2        987          1
                     1       1245          1

0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 16919023
It is required to use the procedure dbms_xmlsave.setrowtag according to the hierarchy
0
 
LVL 27

Author Comment

by:kretzschmar
ID: 16919069
???

i would guess only the last call of
dbms_xmlsave.setrowtag(...);
is valid, all before may overwritten by the last one

meikl ;-)
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 16919170
I changed the XML data slightly. Removed <POSITION> and <POSITIONS> tag and also </POSITION> and </POSITIONS>

DECLARE
  l_clob  CLOB := '<TEST><ORDER><ORDER_NO>1234</ORDER_NO>'||
                  '<POSITION_NO>1</POSITION_NO><ARTIKEL_NO>7890</ARTIKEL_NO>'||
                  '<AMOUNT>1</AMOUNT><POSITION_NO>2'||
                  '</POSITION_NO><ARTIKEL_NO>0987</ARTIKEL_NO><AMOUNT>1</AMOUNT>'||
                  '</ORDER><ORDER><ORDER_NO>2547</ORDER_NO>'||
                  '<POSITION_NO>1</POSITION_NO><ARTIKEL_NO>'||
                  '1245</ARTIKEL_NO><AMOUNT>1</AMOUNT>'||
                  '</ORDER></TEST>';
  l_ctx   dbms_xmlsave.ctxType;
  l_rows  NUMBER;
BEGIN
l_ctx := dbms_xmlsave.newContext('MK_XML_TEST');
dbms_xmlsave.setrowtag(l_ctx,'ORDER');
l_rows := dbms_xmlsave.insertxml(l_ctx,l_clob);
dbms_xmlsave.closeContext(l_ctx);
DBMS_OUTPUT.PUT_LINE(l_rows || ' rows inserted...');
END;
/

SQL> select * from MK_XML_TEST;
      1234           2        987          1
      2547           1       1245          1
0
 
LVL 27

Author Comment

by:kretzschmar
ID: 16919240
well well,

if i could change the structure -> no problem but
>appendix: there is no chance to change the xml-structure!

meikl ;-)
0
 
LVL 27

Author Comment

by:kretzschmar
ID: 16919530
well, seems
that i cannot produce the recommended result
using dbms_xmlsave.

looks like this is only available for one-level xml-docs
(this one in question is a two-level xml-doc),
and i cannot change the xml-structure
as MohanKNair last commented

so any further suggestion is welcome

so, actonwang,
how could a stylesheet look like for the doc in question?
how can i apply it?
where will be the result?
(sorry, no experience with xslt-sheets)

meikl ;-)
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 16932065
Use dbms_xmlparser and dbms_xmldom packages to parse the XML document and get fields withing the DOM hierarchy.

XDB has to be installed as sysdba. See the link below for installing XML DB

Installing and Configuring XML DB - http://www.idevelopment.info/data/Oracle/DBA_tips/xmldb/XMLDB_3.shtml

DECLARE
l_parser  dbms_xmlparser.Parser;
curn number;
l_doc     dbms_xmldom.DOMDocument;
l_nl      dbms_xmldom.DOMNodeList;
l_n       dbms_xmldom.DOMNode;
eno number;
l_clob CLOB := '<TEST><ORDER><ORDER_NO>1234</ORDER_NO><POSITIONS><POSITION>'||
'<POSITION_NO>1</POSITION_NO><ARTIKEL_NO>7890</ARTIKEL_NO>'||
'<AMOUNT>1</AMOUNT></POSITION><POSITION><POSITION_NO>2'||
'</POSITION_NO><ARTIKEL_NO>0987</ARTIKEL_NO><AMOUNT>1</AMOUNT>'||
'</POSITION></POSITIONS></ORDER><ORDER><ORDER_NO>2547</ORDER_NO>'||
'<POSITIONS><POSITION><POSITION_NO>1</POSITION_NO><ARTIKEL_NO>'||
'1245</ARTIKEL_NO><AMOUNT>1</AMOUNT></POSITION></POSITIONS>'||
'</ORDER></TEST>';
 l_ctx dbms_xmlsave.ctxType;
 l_rows NUMBER;
BEGIN
l_parser := dbms_xmlparser.newParser;
dbms_xmlparser.parseClob(l_parser, l_clob);
l_doc := dbms_xmlparser.getDocument(l_parser);
dbms_xmlparser.freeParser(l_parser);

--    l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'');
--   l_n := dbms_xmldom.item(l_nl, curn);
--   dbms_xslprocessor.valueOf(l_n,'ORDER/text()',eno);
end;
/

0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 16932074
Also see this link about Parsing XML Documents
http://www.oracle-base.com/articles/9i/ParseXMLDocuments9i.php
0
 
LVL 27

Author Comment

by:kretzschmar
ID: 16940177
sorry,
didn't got the time to evaluate your last suggestion,
will do it asap

meikl ;-)
0
 
LVL 27

Author Comment

by:kretzschmar
ID: 16950957
i graded this answer,
because the nested levels are containing fields in the real doc ihave (above was a like sample)
which allows me to reference to the first level of the doc

so this works for my case this way with a bit refernce-action
after transform/saving the levels in question

your last suggestion may a possibilty, too,
but is very expensive for coding,
because you have allways to type the full xpath
for extract one value.

currently i'm missing the benefit of XML,
every kind of flatfile seems to be easier to handle
as long as the structure is published

so i go with the comment i read in link given here
-> XML is overhyped

thanks for your help

meikl ;-)
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ORA-01008: not all variables bound. 6 56
help on oracle query 5 43
'G_F01' is not a procedure or is undefined 3 24
pl/sql - query very slow 26 58
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

805 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question