Extracting Xml data from XML using PL/SQL

I am trying to extract the name tag in the Recipe-List from xml and put it as a seperate column in excel this is the xml script:

<?xml version="1.0" encoding="ISO-8859-1"?>
    <EXPOPT_EQUIPMENT_SETUP VarType="Complex" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="EXPOPT_EQUIPMENT_SETUP.xsd">
      <MES_STEP VarType="A">1502.P8-2G625.2060.2.VEE37003</MES_STEP>
      <EQP_NAME VarType="A">L2306</EQP_NAME>
      <PRODUCT VarType="A">35389083</PRODUCT>
      <SET_USED VarType="BL">true</SET_USED>
      <REPLACE VarType="BL">false</REPLACE>
      <RECIPE VarType="A"><![CDATA[<?xml version="1.0" encoding="ISO-8859-1" standalone="yes"?>
    <RECIPE-LIST>
      <ESMInfo>
        <Sequence>
          <Name>XCM_GaN_GT_47_PM3</Name>
        </Sequence>
      </ESMInfo>
    </RECIPE-LIST>]]></RECIPE>
      <DEVIATIONS NodeType="Array" VarType="Complex">
        <_n VarType="Complex">
          <PARAMETER VarType="A">/ESMInfos/ESMInfo[1]/Sequence[1]/Name[1]</PARAMETER>
          <VALUE VarType="A">XCM_GaN_GT_47_PM3</VALUE>
        </_n>
      </DEVIATIONS>
      <RESOURCES NodeType="Array" VarType="Complex">
        <_n VarType="Complex">
          <RESOURCE_ID VarType="A">L2306-A</RESOURCE_ID>
          <IS_SELECTED VarType="BL">true</IS_SELECTED>
        </_n>
        <_n VarType="Complex">
          <RESOURCE_ID VarType="A">L2306-AL1</RESOURCE_ID>
          <IS_SELECTED VarType="BL">true</IS_SELECTED>
        </_n>
        <_n VarType="Complex">
          <RESOURCE_ID VarType="A">L2306-AL2</RESOURCE_ID>
          <IS_SELECTED VarType="BL">true</IS_SELECTED>
        </_n>
        <_n VarType="Complex">
          <RESOURCE_ID VarType="A">L2306-LLR</RESOURCE_ID>
          <IS_SELECTED VarType="BL">true</IS_SELECTED>
        </_n>
        <_n VarType="Complex">
          <RESOURCE_ID VarType="A">L2306-C</RESOURCE_ID>
          <IS_SELECTED VarType="BL">true</IS_SELECTED>
        </_n>
        <_n VarType="Complex">
          <RESOURCE_ID VarType="A">L2306-LLC</RESOURCE_ID>
          <IS_SELECTED VarType="BL">true</IS_SELECTED>
        </_n>
        <_n VarType="Complex">
          <RESOURCE_ID VarType="A">L2306-LLL</RESOURCE_ID>
          <IS_SELECTED VarType="BL">true</IS_SELECTED>
        </_n>
        <_n VarType="Complex">
          <RESOURCE_ID VarType="A">L2306-B</RESOURCE_ID>
          <IS_SELECTED VarType="BL">true</IS_SELECTED>
        </_n>
      </RESOURCES>
    </EXPOPT_EQUIPMENT_SETUP>

the xml script is located in a column called O.Option_Data and i want to put the Recipe-List name in a separate column I have already tried this:

  SELECT EXTRACTVALUE(XMLTYPE(O.Option_Data), '/EXPOPT_EQUIPMENT_SETUP/RECIPE/RECIPE-
  LIST/ESMInfo/Sequence/Name') AS RecipeListName
  FROM my_table;

Solution

Please try the following solution.

It is a two step process:

Retrieving CData section as a CLOB data type.
Converting CLOB into XMLType data type.

The rest is a standard XML shredding.

SQL

with tbl as
(
    select
        XMLType(
        '<?xml version="1.0" encoding="ISO-8859-1"?>
        <EXPOPT_EQUIPMENT_SETUP VarType="Complex"
                                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                                xsi:noNamespaceSchemaLocation="EXPOPT_EQUIPMENT_SETUP.xsd">
            <MES_STEP VarType="A">1502.P8-2G625.2060.2.VEE37003</MES_STEP>
            <EQP_NAME VarType="A">L2306</EQP_NAME>
            <PRODUCT VarType="A">35389083</PRODUCT>
            <SET_USED VarType="BL">true</SET_USED>
            <REPLACE VarType="BL">false</REPLACE>
            <RECIPE VarType="A"><![CDATA[<?xml version="1.0" encoding="ISO-8859-1" standalone="yes"?>
            <RECIPE-LIST>
              <ESMInfo>
                <Sequence>
                  <Name>XCM_GaN_GT_47_PM3</Name>
                </Sequence>
              </ESMInfo>
            </RECIPE-LIST>]]>
            </RECIPE>
            <DEVIATIONS NodeType="Array" VarType="Complex">
                <_n VarType="Complex">
                    <PARAMETER VarType="A">/ESMInfos/ESMInfo[1]/Sequence[1]/Name[1]</PARAMETER>
                    <VALUE VarType="A">XCM_GaN_GT_47_PM3</VALUE>
                </_n>
            </DEVIATIONS>
            <RESOURCES NodeType="Array" VarType="Complex">
                <_n VarType="Complex">
                    <RESOURCE_ID VarType="A">L2306-A</RESOURCE_ID>
                    <IS_SELECTED VarType="BL">true</IS_SELECTED>
                </_n>
                <_n VarType="Complex">
                    <RESOURCE_ID VarType="A">L2306-AL1</RESOURCE_ID>
                    <IS_SELECTED VarType="BL">true</IS_SELECTED>
                </_n>
                <_n VarType="Complex">
                    <RESOURCE_ID VarType="A">L2306-AL2</RESOURCE_ID>
                    <IS_SELECTED VarType="BL">true</IS_SELECTED>
                </_n>
                <_n VarType="Complex">
                    <RESOURCE_ID VarType="A">L2306-LLR</RESOURCE_ID>
                    <IS_SELECTED VarType="BL">true</IS_SELECTED>
                </_n>
                <_n VarType="Complex">
                    <RESOURCE_ID VarType="A">L2306-C</RESOURCE_ID>
                    <IS_SELECTED VarType="BL">true</IS_SELECTED>
                </_n>
                <_n VarType="Complex">
                    <RESOURCE_ID VarType="A">L2306-LLC</RESOURCE_ID>
                    <IS_SELECTED VarType="BL">true</IS_SELECTED>
                </_n>
                <_n VarType="Complex">
                    <RESOURCE_ID VarType="A">L2306-LLL</RESOURCE_ID>
                    <IS_SELECTED VarType="BL">true</IS_SELECTED>
                </_n>
                <_n VarType="Complex">
                    <RESOURCE_ID VarType="A">L2306-B</RESOURCE_ID>
                    <IS_SELECTED VarType="BL">true</IS_SELECTED>
                </_n>
            </RESOURCES>
        </EXPOPT_EQUIPMENT_SETUP>'
        ) xmldata
    from dual
)
select t1.RECIPE, t2.name
from tbl
cross apply XMLTable('/EXPOPT_EQUIPMENT_SETUP'
         passing tbl.xmldata 
         columns RECIPE CLOB PATH 'RECIPE/text()[1]') t1
outer apply xmltable('/*'
    passing XMLType(t1.RECIPE)
    columns name varchar2(100) path '/RECIPE-LIST/ESMInfo/Sequence/Name'
) t2

Output

name
XCM_GaN_GT_47_PM3