Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 858
  • Last Modified:

XML-Select Result

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
kretzschmar
Asked:
kretzschmar
  • 8
  • 7
1 Solution
 
MohanKNairCommented:
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
 
kretzschmarAuthor Commented:
thanks MohanKNair,
looks good so far in theory

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

meikl ;-)
0
 
MohanKNairCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
actonwangCommented:
if you store it as xmltype in the column, you might just apply styleshhet and use

xmltype.transform()

to do it.

0
 
kretzschmarAuthor Commented:
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
 
kretzschmarAuthor Commented:
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
 
MohanKNairCommented:
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
 
MohanKNairCommented:
It is required to use the procedure dbms_xmlsave.setrowtag according to the hierarchy
0
 
kretzschmarAuthor Commented:
???

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

meikl ;-)
0
 
MohanKNairCommented:
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
 
kretzschmarAuthor Commented:
well well,

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

meikl ;-)
0
 
kretzschmarAuthor Commented:
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
 
MohanKNairCommented:
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
 
MohanKNairCommented:
Also see this link about Parsing XML Documents
http://www.oracle-base.com/articles/9i/ParseXMLDocuments9i.php
0
 
kretzschmarAuthor Commented:
sorry,
didn't got the time to evaluate your last suggestion,
will do it asap

meikl ;-)
0
 
kretzschmarAuthor Commented:
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now