Parsing XML in an Oracle function

Question

I have a very simple XML document that is stored as a VARCHAR2 in an Oracle 10g table. Below is an example of the XML. I want to take the XML and insert it into a global temp table as a single record. I've seen a couple other questions related to this topic but they were a little mor complex than I need. Anyone show me how to grab the data out of this XML? Thanks

<Document>
  <A1>
    <D1>dzzz</D1>
    <P1>pzzz</P1>
    <AA1>abcd</AA1>
    <PP1>TEMP</PP1>
    <Desc>TEMP DESC</Desc>
    <Price>1.81568</Price>
    <Qty>278</Qty>
    <Location>E</Location>
  </A1>
</Document>

Answer 1

You can define the column in your temp table as an XMLType column.

CREATE TABLE EXAMPLE_XML_TABLE
(
    XML_DATA XMLType
);

Once you have the column you can add the string to it (white space added for readability).

INSERT INTO EXAMPLE_XML_TABLE VALUES(
   XMLType('<Document>
               <A1>
                  <D1>dzzz</D1>
                  <P1>pzzz</P1>
                  <AA1>abcd</AA1>
                  <PP1>TEMP</PP1>
                  <Desc>TEMP DESC</Desc>
                  <Price>1.81568</Price>
                  <Qty>278</Qty>
                  <Location>E</Location>
               </A1>
            </Document>')
   );

You can then use SQL to query the data in the XML.

SELECT EXTRACT(XML_DATA, '/Document/A1/D1') D1,
       EXTRACT(XML_DATA, '/Document/A1/P1') P1,
       EXTRACT(XML_DATA, '/Document/A1/AA1') AA1,
       EXTRACT(XML_DATA, '/Document/A1/PP1') PP1,
       EXTRACT(XML_DATA, '/Document/A1/Desc') DESC,
       EXTRACT(XML_DATA, '/Document/A1/Price') PRICE,
       EXTRACT(XML_DATA, '/Document/A1/Qty') QTY,
       EXTRACT(XML_DATA, '/Document/A1/Location') LOCATION,
FROM EXAMPLE_XML_TABLE;

Answer 2

By

I want to take the XML and insert it into a global temp table as a single record

I'm assuming you want to insert the elements on the A1 node as a single row of data. If so, then you can accomplish is with something like this:

insert into temp_table_name
select extractvalue(T.COLUMN_VALUE, '/A1/D1'),
         extractvalue(T.COLUMN_VALUE, '/A1/P1'),
         extractvalue(T.COLUMN_VALUE, '/A1/AA1'),
         extractvalue(T.COLUMN_VALUE, '/A1/PP1'),
         extractvalue(T.COLUMN_VALUE, '/A1/Desc'),
         extractvalue(T.COLUMN_VALUE, '/A1/Price'),
         extractvalue(T.COLUMN_VALUE, '/A1/Qty'),
         extractvalue(T.COLUMN_VALUE, '/A1/Location')
from
table(xmlsequence(extract(xmltype('<Document> 
  <A1> 
    <D1>dzzz</D1> 
    <P1>pzzz</P1> 
    <AA1>abcd</AA1> 
    <PP1>TEMP</PP1> 
    <Desc>TEMP DESC</Desc> 
    <Price>1.81568</Price> 
    <Qty>278</Qty> 
    <Location>E</Location> 
  </A1> 
</Document>'), '/Document/A1'))) T

Optionally, to extract to Price and Qty as numbers you can do:

extract(T.COLUMN_VALUE, '/A1/Price/text()').getNumberVal()
extract(T.COLUMN_VALUE, '/A1/Qty/text()').getNumberVal()