3

I have an oracle table which has a column that stores XML in a CLOB format. The XML has the following syntax:

<?xml version="1.0" encoding="ISO-8859-1" ?> 
 <BaseXML Version="2009.4">
 <InvoiceCanvasDetails>
     <Grouping>
       <ParentGroup Name=”Parent group 1” ID=”100”>
        <ParentGroupLineItems>
         <Item ID="461616" Name=”Item 1”>
           <Papers Quantity=10000>
                          <Paper Name="UNCOATED GROUNDWOOD SCA+ (25X38)"    
                                      ID="126287" Weight="1268" Type=”A4” /> 
                          <Paper Name="COATED GROUNDWOOD SCA+ (25X38)"    
                                      ID="126288" Weight="1290" Type=”A4” />
                     </Papers>
              </Item>
       </ParentGroupLineItems>
    </ParentGroup>
  </Grouping>
 </InvoiceCanvasDetails>
</BaseXML>

Now, I want to retrieve only the Paper information corresponding to each item. ie, Given an item ID, retrieve all the papers associated to it using a query. Please guide me which is the best way to do this.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Pradeep
  • 299
  • 2
  • 8
  • 15

2 Answers2

8

You can use a combination of extract and extractvalue:

SQL> SELECT extractvalue(column_value, 'Paper/@Name') NAME,
  2         extractvalue(column_value, 'Paper/@ID') ID,
  3         extractvalue(column_value, 'Paper/@Weight') Weight,
  4         extractvalue(column_value, 'Paper/@Type') TYPE
  5    FROM TABLE (SELECT xmlsequence(XMLTYPE(a).extract('BaseXML/' ||
  6                                             'InvoiceCanvasDetails/' ||
  7                                             'Grouping/ParentGroup/' ||
  8                                             'ParentGroupLineItems/' ||
  9                                             'Item/Papers/Paper'))
 10                   FROM t);

NAME                                     ID         WEIGHT     TYPE
---------------------------------------- ---------- ---------- -----
UNCOATED GROUNDWOOD SCA+ (25X38)         126287     1268       A4
COATED GROUNDWOOD SCA+ (25X38)           126288     1290       A4

If you're looking for a specific ID, you can filter data directly in the extract function:

SQL> SELECT extract(XMLTYPE(a),
  2                  'BaseXML/InvoiceCanvasDetails/Grouping/' ||
  3                  'ParentGroup/ParentGroupLineItems/' ||
  4                  'Item/Papers/Paper[@ID="126287"]') ext
  5    FROM t;

EXT
--------------------------------------------------------------------------------
<Paper Name="UNCOATED GROUNDWOOD SCA+ (25X38)" ID="126287" Weight="1268" Type="A
4"/>
Vincent Malgrat
  • 66,725
  • 9
  • 119
  • 171
  • In 11g [use of extractvalue is deprecated](http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/functions061.htm#i1131042). Can you also give an example using the recommended functions, please ? – user272735 May 27 '11 at 06:36
  • @user272735: see [xml to oracle DB table](http://stackoverflow.com/questions/1231981/xml-to-oracle-db-table-encountering-problems/1232209#1232209) or [Oracle: loading a large xml file?](http://stackoverflow.com/questions/998055/oracle-loading-a-large-xml-file/1000331#1000331) for example of XMLTABLE use – Vincent Malgrat May 27 '11 at 08:40
0

Take a look at using XPATH expressions in Oracle.

Pablo Santa Cruz
  • 176,835
  • 32
  • 241
  • 292