Solved

XML-Select Result

Posted on 2006-06-14
16
829 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
 
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
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 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

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.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now