Search code examples
sqlxmloracle-databasexmltype

Extract value from column (xmltype)

We are using an ERP system. I'm newbie in XML.

In our system, we have a column XML_DATA which TYPE is xmltype(2000)

Schema: 

<?xml version="1.0" ?> 
- <xs:schema xmlns:xs=" " attributeFormDefault="qualified"  elementFormDefault="qualified">
 - <xs:element name="XK6">
  - <xs:complexType>
   - <xs:choice maxOccurs="unbounded">

   - <xs:element name="Product">
    - <xs:complexType>
     - <xs:sequence>
      - <xs:element name="Product_row" minOccurs="0" maxOccurs="unbounded">
       - <xs:complexType>
        - <xs:sequence>

        <xs:element name="DETAIL" type="Product_DETAIL" minOccurs="0" /> 

          </xs:sequence>
         </xs:complexType>
        </xs:element>
       </xs:sequence>
      </xs:complexType>
     </xs:element>
    </xs:choice>
   </xs:complexType>
  </xs:element>

  - <xs:simpleType name="Product_DETAIL">
   - <xs:restriction base="xs:string">
    <xs:maxLength value="1000" /> 
   </xs:restriction>
  </xs:simpleType>
 </xs:schema>

EDIT:

The XML:

<?xml version="1.0" encoding="utf-8" ?> 
- <XP6>
  +<collapsed_node>
  +<collapsed_node>
  +<collapsed_node>
  - <Product>
    - <Product_row>
       <DETAIL>sometext </DETAIL> 
      </Product_row>
    </Product>
  </XP6>

How can i extract the column ?

Or need more data to do it ? I want to get the Product_Detail value.

Solution

  • Use the "XMLTABLE" function (see https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions228.htm):

    with d as (
select xmltype('
<XK6>
    <Product>
        <Product_row>
            <DETAIL>First product detail</DETAIL>
        </Product_row>
        <Product_row>
            <DETAIL>Second product detail</DETAIL>
        </Product_row>
    </Product>
</XK6>'
) as thexml from dual)
select detail from d, 
  xmltable('/XK6/Product/Product_row' 
    passing d.thexml 
    columns detail varchar2(100) path 'DETAIL')