Skip to Main Content
  • Questions

  • How to extract portion of an xmltype column

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Cheryl.

Asked: November 06, 2023 - 1:17 pm UTC

Last updated: November 07, 2023 - 1:57 pm UTC

Version: Cloud

Viewed 1000+ times

You Asked

XML looks like this:

<TaxRuleConditionVO>
<TaxRuleConditionVORow>
<ConditionGroupCode>SU CC 37,69</ConditionGroupCode>
<TaxParameterCode>ACCOUNT</TaxParameterCode>
<DataTypeCode>ALPHANUMERIC</DataTypeCode>
<DeterminingFactorClassCode>ACCOUNTING_FLEXFIELD</DeterminingFactorClassCode>
<DeterminingFactorCqCode null="true"/>
<OperatorCode>=</OperatorCode>
<RecordTypeCode>USER_DEFINED</RecordTypeCode>
<IgnoreFlag>N</IgnoreFlag>
<DateValue null="true"/>
<AlphanumericValue null="true"/>
<ValueLow null="true"/>
<ValueHigh null="true"/>
<CreationDate>2023-07-06 16:06:08.604</CreationDate>
<LastUpdateDate>2023-07-06 17:04:14.235</LastUpdateDate>
<CreatedBy>stuartd</CreatedBy>
<LastUpdatedBy>stuartd</LastUpdatedBy>
<LastUpdateLogin>FFC4CEC7678DF531E0534D60410A1325</LastUpdateLogin>
<ProgramAppName null="true"/>
<ProgramName null="true"/>
<DeterminingFactorCode>LINE_ACCOUNT</DeterminingFactorCode>
<NumericValueTrans null="true"/>
<FlexFilterValue><?xml version="1.0" encoding="UTF-8"?> <FndFilter xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://www.oracle.com/apps/fnd/applcore/filter/FndFilter.xsd"> <KeyFlexFilter> <keyFlexfieldCode>GL#</keyFlexfieldCode> <structureInstanceCode>SUN</structureInstanceCode> <applicationShortName>GL</applicationShortName> <filterCriteriaRow> <filterCriteriaItem> <attributeName>sunCostcentre</attributeName> <columnName>SEGMENT2</columnName> <operator>EQUALTO</operator> <conjunction>AND</conjunction> <valueDataType>STRING</valueDataType> <value>85502</value> </filterCriteriaItem> <conjunction>AND</conjunction> </filterCriteriaRow> </KeyFlexFilter> </FndFilter> </FlexFilterValue>
<TaxRuleConditionDetails null="true"/>
</TaxRuleConditionVORow>


The column I need lies between <value> and </value>, but this lies inside an xmltype column called Flexfiltervalue.

How do I write the sql to extract that value and it's related ConditionGroupCode

I have tried various options

select t.CONDITION_GROUP_CODE as ConditionGroupCode, instr((t.FLEX_FILTER_VALUE).getClobVal(),''), instr((t.FLEX_FILTER_VALUE).getClobVal(),''))as clobval from ZX_CONDITIONS t where t.condition_group_code = 'SU CC 50 1'
THIS RETURNED THE WRONG VALUE

SELECT t.FLEX_FILTER_VALUE.getStringVal() FROM ZX_CONDITIONS t
THIS PRODUCED AN ERROR: ORA-19011: Character string buffer too small ORA-06512: at "SYS.XMLTYPE", line 169

select extract(t.FLEX_FILTER_VALUE, '/').getClobVal(), <'value>' from ZX_CONDITIONS t
THIS GIVES ME MISSING EXPRESISON ERROR

and Chris said...

You can use the XMLQuery function to search inside an XML document. Pass it the path your searching for.

The example XML document is invalid, so here's a slightly adapted version:

with rws as (
  select xmltype ('    
<TaxRuleConditionVORow>
    <ConditionGroupCode>SU CC 37,69</ConditionGroupCode>
    <TaxParameterCode>ACCOUNT</TaxParameterCode>
    <DataTypeCode>ALPHANUMERIC</DataTypeCode>
    <DeterminingFactorClassCode>ACCOUNTING_FLEXFIELD</DeterminingFactorClassCode>
    <DeterminingFactorCqCode null="true"/>
    <OperatorCode>=</OperatorCode>
    <RecordTypeCode>USER_DEFINED</RecordTypeCode>
    <IgnoreFlag>N</IgnoreFlag>
    <DateValue null="true"/>
    <AlphanumericValue null="true"/>
    <ValueLow null="true"/>
    <ValueHigh null="true"/>
    <CreationDate>2023-07-06 16:06:08.604</CreationDate>
    <LastUpdateDate>2023-07-06 17:04:14.235</LastUpdateDate>
    <CreatedBy>stuartd</CreatedBy>
    <LastUpdatedBy>stuartd</LastUpdatedBy>
    <LastUpdateLogin>FFC4CEC7678DF531E0534D60410A1325</LastUpdateLogin>
    <ProgramAppName null="true"/>
    <ProgramName null="true"/>
    <DeterminingFactorCode>LINE_ACCOUNT</DeterminingFactorCode>
    <NumericValueTrans null="true"/>
    <FlexFilterValue>
        <FndFilter>
            <KeyFlexFilter>
                <keyFlexfieldCode>GL#</keyFlexfieldCode>
                <structureInstanceCode>SUN</structureInstanceCode>
                <applicationShortName>GL</applicationShortName>
                <filterCriteriaRow>
                    <filterCriteriaItem>
                        <attributeName>sunCostcentre</attributeName>
                        <columnName>SEGMENT2</columnName>
                        <operator>EQUALTO</operator>
                        <conjunction>AND</conjunction>
                        <valueDataType>STRING</valueDataType>
                        <value>85502</value>
                    </filterCriteriaItem>
                    <conjunction>AND</conjunction>
                </filterCriteriaRow>
            </KeyFlexFilter>
        </FndFilter>
    </FlexFilterValue>
    <TaxRuleConditionDetails null="true"/>
</TaxRuleConditionVORow>') x from dual
)
  select xmlquery ( 
    '/TaxRuleConditionVORow/FlexFilterValue/FndFilter/KeyFlexFilter/filterCriteriaRow/filterCriteriaItem/value' 
    passing x returning content 
  ).getClobVal() val
  from rws;
  
VAL                     
<value>85502</value>

Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

using xmltable()

Rajeshwaran Jeyabal, November 07, 2023 - 1:51 am UTC 

demo@PDB1> select t.x.getclobval() from t t;

T.X.GETCLOBVAL()
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<TaxRuleConditionVO>
  <TaxRuleConditionVORow>
    <ConditionGroupCode>SU CC 37,69</ConditionGroupCode>
    <TaxParameterCode>ACCOUNT</TaxParameterCode>
    <DataTypeCode>ALPHANUMERIC</DataTypeCode>
    <DeterminingFactorClassCode>ACCOUNTING_FLEXFIELD</DeterminingFactorClassCode>
    <DeterminingFactorCqCode null="true"/>
    <OperatorCode>=</OperatorCode>
    <RecordTypeCode>USER_DEFINED</RecordTypeCode>
    <IgnoreFlag>N</IgnoreFlag>
    <DateValue null="true"/>
    <AlphanumericValue null="true"/>
    <ValueLow null="true"/>
    <ValueHigh null="true"/>
    <CreationDate>2023-07-06 16:06:08.604</CreationDate>
    <LastUpdateDate>2023-07-06 17:04:14.235</LastUpdateDate>
    <CreatedBy>stuartd</CreatedBy>
    <LastUpdatedBy>stuartd</LastUpdatedBy>
    <LastUpdateLogin>FFC4CEC7678DF531E0534D60410A1325</LastUpdateLogin>
    <ProgramAppName null="true"/>
    <ProgramName null="true"/>
    <DeterminingFactorCode>LINE_ACCOUNT</DeterminingFactorCode>
    <NumericValueTrans null="true"/>
    <FlexFilterValue>
      <FndFilter xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://www.oracle.com/apps/fnd/applcore/filter/FndFilter.xsd">
        <KeyFlexFilter>
          <keyFlexfieldCode>GL#</keyFlexfieldCode>
          <structureInstanceCode>SUN</structureInstanceCode>
          <applicationShortName>GL</applicationShortName>
          <filterCriteriaRow>
            <filterCriteriaItem>
              <attributeName>sunCostcentre</attributeName>
              <columnName>SEGMENT2</columnName>
              <operator>EQUALTO</operator>
              <conjunction>AND</conjunction>
              <valueDataType>STRING</valueDataType>
              <value>85502</value>
            </filterCriteriaItem>
            <conjunction>AND</conjunction>
          </filterCriteriaRow>
        </KeyFlexFilter>
      </FndFilter>
    </FlexFilterValue>
    <TaxRuleConditionDetails null="true"/>
  </TaxRuleConditionVORow>
</TaxRuleConditionVO>

demo@PDB1> select t2.*
  2  from t, xmltable('$p/TaxRuleConditionVO/TaxRuleConditionVORow' passing t.x as "p"
  3      columns
  4          ConditionGroupCode varchar2(30) path 'ConditionGroupCode' ,
  5          filter_value number path 'FlexFilterValue/FndFilter/KeyFlexFilter/filterCriteriaRow/filterCriteriaItem/value'
  6          )  t2
  7  /

CONDITIONGROUPCODE             FILTER_VALUE
------------------------------ ------------
SU CC 37,69                           85502

demo@PDB1>

Query to Chris

Cheryl, November 07, 2023 - 5:57 am UTC 

select xmlquery (     '/TaxRuleConditionVORow/FlexFilterValue/FndFilter/KeyFlexFilter/filterCriteriaRow/filterCriteriaItem/value' 
    passing x returning content 
  ).getClobVal() val
  from ZX_CONDITIONS x


Chris I adapted your query to above as the data resides in an Oracle table ZX_CONDITIONS
I did not use the section of 'with rws as (
select xmltype (' as my data resides in the Oracle table. Hope that makes sense.

When I run this adapted query I get an error 'X invalid identifier'

What am I doing wrong?
Chris Saxon
November 07, 2023 - 1:57 pm UTC

I'm not sure. So we can help you further, please post an example:

- Create table (to store the XML)
- Insert into (of the XML)
- The statement that raises an error

Query to Rajeshwaran

Cheryl, November 07, 2023 - 6:33 am UTC

Thank you for your response. I tried your code but replaced the table values with the actual Oracle table as the data lies in that Oracle table. I dont want to still create a table. The column FLEX_FILTER_VALUE in the ZX_CONDITIONS table is where the data sits.

select t2.*
   from ZX_CONDITIONS t, xmltable('$p/TaxRuleConditionSetVO/TaxRuleConditionSetVORow/TaxRuleConditionVO/TaxRuleConditionVORow' passing t.x as "p"
  columns
           ConditionGroupCode varchar2(30) path 'ConditionGroupCode' ,
            filter_value number path 'FlexFilterValue/FndFilter/KeyFlexFilter/filterCriteriaRow/filterCriteriaItem/value'
            )  t2


Get an error on this one: T.X invalid identifier which kinda makes sense, but dont know how to fix.

Also tried:
select t2.*
   from ZX_CONDITIONS t, xmltable('$p/TaxRuleConditionSetVO/TaxRuleConditionSetVORow/TaxRuleConditionVO/TaxRuleConditionVORow' passing t.* as "p"
  columns
           ConditionGroupCode varchar2(30) path 'ConditionGroupCode' ,
            filter_value number path 'FlexFilterValue/FndFilter/KeyFlexFilter/filterCriteriaRow/filterCriteriaItem/value'
            )  t2


But this to produced an error.
ORA-01747: invalid user.table.column, table.column, or column specification

Not sure what else to try?

to the above response...

Rajeshwaran Jeyabal, November 07, 2023 - 12:42 pm UTC

...I dont want to still create a table....
yes, you can still go without creating a table.

..passing t.x as "p"...
when you say passing "t.x" means the column X from table T

so in your case it should be the column holding the xml data in the table "ZX_CONDITIONS"

once you understand the above logic, you can play with xmltable stuff - all are documented here

https://docs.oracle.com/en/database/oracle/oracle-database/21/adxdb/relational-views-over-XML-data.html#GUID-E550E7D4-0D19-47CA-B86F-76825EE54CE1 

demo@PDB1> variable x clob
demo@PDB1> begin
  2     :x := '<TaxRuleConditionVO>
  3    <TaxRuleConditionVORow>
  4      <ConditionGroupCode>SU CC 37,69</ConditionGroupCode>
  5      <TaxParameterCode>ACCOUNT</TaxParameterCode>
  6      <DataTypeCode>ALPHANUMERIC</DataTypeCode>
  7      <DeterminingFactorClassCode>ACCOUNTING_FLEXFIELD</DeterminingFactorClassCode>
  8      <DeterminingFactorCqCode null="true"/>
  9      <OperatorCode>=</OperatorCode>
 10      <RecordTypeCode>USER_DEFINED</RecordTypeCode>
 11      <IgnoreFlag>N</IgnoreFlag>
 12      <DateValue null="true"/>
 13      <AlphanumericValue null="true"/>
 14      <ValueLow null="true"/>
 15      <ValueHigh null="true"/>
 16      <CreationDate>2023-07-06 16:06:08.604</CreationDate>
 17      <LastUpdateDate>2023-07-06 17:04:14.235</LastUpdateDate>
 18      <CreatedBy>stuartd</CreatedBy>
 19      <LastUpdatedBy>stuartd</LastUpdatedBy>
 20      <LastUpdateLogin>FFC4CEC7678DF531E0534D60410A1325</LastUpdateLogin>
 21      <ProgramAppName null="true"/>
 22      <ProgramName null="true"/>
 23      <DeterminingFactorCode>LINE_ACCOUNT</DeterminingFactorCode>
 24      <NumericValueTrans null="true"/>
 25      <FlexFilterValue>
 26        <FndFilter xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://www.oracle.com/apps/fnd/applcore/filter/FndFilter.xsd">
 27          <KeyFlexFilter>
 28            <keyFlexfieldCode>GL#</keyFlexfieldCode>
 29            <structureInstanceCode>SUN</structureInstanceCode>
 30            <applicationShortName>GL</applicationShortName>
 31            <filterCriteriaRow>
 32              <filterCriteriaItem>
 33                <attributeName>sunCostcentre</attributeName>
 34                <columnName>SEGMENT2</columnName>
 35                <operator>EQUALTO</operator>
 36                <conjunction>AND</conjunction>
 37                <valueDataType>STRING</valueDataType>
 38                <value>85502</value>
 39              </filterCriteriaItem>
 40              <conjunction>AND</conjunction>
 41            </filterCriteriaRow>
 42          </KeyFlexFilter>
 43        </FndFilter>
 44      </FlexFilterValue>
 45      <TaxRuleConditionDetails null="true"/>
 46    </TaxRuleConditionVORow>
 47  </TaxRuleConditionVO> ';
 48  end;
 49  /

PL/SQL procedure successfully completed.

demo@PDB1> print x

X
--------------------------------------------------------------------------------------------------------------------------------------------
<TaxRuleConditionVO>
  <TaxRuleConditionVORow>
    <ConditionGroupCode>SU CC 37,69</ConditionGroupCode>
    <TaxParameterCode>ACCOUNT</TaxParameterCode>
    <DataTypeCode>ALPHANUMERIC</DataTypeCode>
    <DeterminingFactorClassCode>ACCOUNTING_FLEXFIELD</DeterminingFactorClassCode>
    <DeterminingFactorCqCode null="true"/>
    <OperatorCode>=</OperatorCode>
    <RecordTypeCode>USER_DEFINED</RecordTypeCode>
    <IgnoreFlag>N</IgnoreFlag>
    <DateValue null="true"/>
    <AlphanumericValue null="true"/>
    <ValueLow null="true"/>
    <ValueHigh null="true"/>
    <CreationDate>2023-07-06 16:06:08.604</CreationDate>
    <LastUpdateDate>2023-07-06 17:04:14.235</LastUpdateDate>
    <CreatedBy>stuartd</CreatedBy>
    <LastUpdatedBy>stuartd</LastUpdatedBy>
    <LastUpdateLogin>FFC4CEC7678DF531E0534D60410A1325</LastUpdateLogin>
    <ProgramAppName null="true"/>
    <ProgramName null="true"/>
    <DeterminingFactorCode>LINE_ACCOUNT</DeterminingFactorCode>
    <NumericValueTrans null="true"/>
    <FlexFilterValue>
      <FndFilter xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://www.oracle.com/apps/fnd/applcor
e/filter/FndFilter.xsd">
        <KeyFlexFilter>
          <keyFlexfieldCode>GL#</keyFlexfieldCode>
          <structureInstanceCode>SUN</structureInstanceCode>
          <applicationShortName>GL</applicationShortName>
          <filterCriteriaRow>
            <filterCriteriaItem>
              <attributeName>sunCostcentre</attributeName>
              <columnName>SEGMENT2</columnName>
              <operator>EQUALTO</operator>
              <conjunction>AND</conjunction>
              <valueDataType>STRING</valueDataType>
              <value>85502</value>
            </filterCriteriaItem>
            <conjunction>AND</conjunction>
          </filterCriteriaRow>
        </KeyFlexFilter>
      </FndFilter>
    </FlexFilterValue>
    <TaxRuleConditionDetails null="true"/>
  </TaxRuleConditionVORow>
</TaxRuleConditionVO>


demo@PDB1> select t2.*
  2  from xmltable('$p/TaxRuleConditionVO/TaxRuleConditionVORow' passing xmltype(:x) as "p"
  3     columns
  4             ConditionGroupCode varchar2(30) path 'ConditionGroupCode' ,
  5             filter_value number path 'FlexFilterValue/FndFilter/KeyFlexFilter/filterCriteriaRow/filterCriteriaItem/value'
  6             )  t2
  7  /

CONDITIONGROUPCODE             FILTER_VALUE
------------------------------ ------------
SU CC 37,69                           85502

demo@PDB1>

Query to Chris

Cheryl, November 08, 2023 - 6:26 am UTC

Thank you for your response. However please note that I dont want to create a table. There is already a table in Oracle called ZX_CONDITIONS of which only one of the columns 'FLEX_FILTER_VALUE' is of an xmltype.
It is within this column that data resides that I am trying to pull out via an select statement.
When I run the statement select * from ZX_CONDITIONS it pushes out data for each column. The data lying in 'FLEX_FILTER_VALUE' is not like the layout you showed. It is not an xml document that was loaded, but is part of configuration for Tax purposes.

is this xmltable stuff is not helping you?

Rajeshwaran Jeyabal, November 09, 2023 - 4:20 am UTC 

demo@PDB1> select t2.*
  2     from ZX_CONDITIONS, xmltable('$p/TaxRuleConditionVO/TaxRuleConditionVORow' passing FLEX_FILTER_VALUE as "p"
  3        columns
  4                ConditionGroupCode varchar2(30) path 'ConditionGroupCode' ,
  5                filter_value number path 'FlexFilterValue/FndFilter/KeyFlexFilter/filterCriteriaRow/filterCriteriaItem/value'
  6                )  t2
  7  /

CONDITIONGROUPCODE             FILTER_VALUE
------------------------------ ------------
SU CC 37,69                           85502

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here