Issue Extracting Data from Oracle 19 CLOB column-CodePudding

I am having issues extracting data from a CLOB column in Oracle 19c.

My query is:

select TRANS_SETTINGS, x.Value
  cross join xmltable(
  xmlnamespaces(default 'http://tempuri.org/GenericSettingsDataSet.xsd')
  '/GenericSettingsDataSet/ConfigSettings'
  passing xmltype(TRANS_SETTINGS)
  columns Value CLOB path 'Value'
  ) x
from FS_STD_CONT_TRANS
  WHERE TRANS_SETTINGS LIKE '%\\Instem\%'
;

Result:

TRANS_SETTINGS|VALUE|
-------------- -----

Table=FS_STD_CONT_TRANS
Column=TRANS_SETTINGS
Column Type=CLOB

The value I need is this:

Value>\\Instem\alcon\PQ\Submit\ABC Labs</Value>

Below is the CLOB xml

<GenericSettingsDataSet xmlns="http://tempuri.org/GenericSettingsDataSet.xsd">
  <ConfigSettings>
    <Key>ActionToggle_FilterFiles</Key>
    <SubKey />
    <Value>True</Value>
    <Required>true</Required>
    <Description>Indicates if the list of files and folders involved in the transfer is to be filtered using the substitution tokens and regex matching settings in the transfer template.</Description>
    <DefaultValue>True</DefaultValue>
    <EditorType>6</EditorType>
    <SettingType>1</SettingType>
    <DisplayName>Filter File List</DisplayName>
    <IsVisible>false</IsVisible>
    <CategoryKey>TransferActions</CategoryKey>
  </ConfigSettings>
  <ConfigSettings>
    <Key>ActionToggle_TransferCompressedFiles</Key>
    <SubKey />
    <Value>False</Value>
    <Required>true</Required>
    <Description>Transfer compressed versions of files.</Description>
    <DefaultValue>False</DefaultValue>
    <EditorType>6</EditorType>
    <SettingType>1</SettingType>
    <DisplayName>Transfer Compressed Files</DisplayName>
    <IsVisible>true</IsVisible>
    <CategoryKey>TransferActions</CategoryKey>
  </ConfigSettings>
  <ConfigSettings>
    <Key>ActionToggle_OverwriteFiles</Key>
    <SubKey />
    <Value>False</Value>
    <Required>true</Required>
    <Description>Overwrite destination files on transfer.</Description>
    <DefaultValue>False</DefaultValue>
    <EditorType>6</EditorType>
    <SettingType>1</SettingType>
    <DisplayName>Overwrite Files</DisplayName>
    <IsVisible>true</IsVisible>
    <CategoryKey>TransferActions</CategoryKey>
  </ConfigSettings>
  <ConfigSettings>
    <Key>ActionToggle_DeleteDestination</Key>
    <SubKey />
    <Value>False</Value>
    <Required>true</Required>
    <Description>Delete all existing files from destination before transfer.</Description>
    <DefaultValue>False</DefaultValue>
    <EditorType>6</EditorType>
    <SettingType>1</SettingType>
    <DisplayName>Delete Existing Destination Files</DisplayName>
    <IsVisible>true</IsVisible>
    <CategoryKey>TransferActions</CategoryKey>
  </ConfigSettings>
  <ConfigSettings>
    <Key>ActionToggle_RemoveFolderStructure</Key>
    <SubKey />
    <Value>False</Value>
    <Required>true</Required>
    <Description>If enabled, will remove the folder structure when exporting to the external location.</Description>
    <DefaultValue>False</DefaultValue>
    <EditorType>6</EditorType>
    <SettingType>1</SettingType>
    <DisplayName>Remove Folder Structure</DisplayName>
    <IsVisible>true</IsVisible>
    <CategoryKey>TransferActions</CategoryKey>
  </ConfigSettings>
  <ConfigSettings>
    <Key>NetworkLocationKey</Key>
    <SubKey />
    <Value>\\Instem\alcon\PQ\Submit\ABC Labs</Value>
    <Required>true</Required>
    <Description>Network location for the file transfer.
The following placeholders are accepted: %STUDY_ID% (Study ID), %SPON_ID% (Sponsor ID) and %CONTRIB_ID% (Contributor ID).</Description>
    <DefaultValue />
    <EditorType>5</EditorType>
    <SettingType>1</SettingType>
    <DisplayName>Network Location</DisplayName>
    <IsVisible>true</IsVisible>
    <CategoryKey>ExternalLocation</CategoryKey>
  </ConfigSettings>
  <Categories>
    <Key>TransferActions</Key>
    <Required>false</Required>
    <Description>Actions that can be performed at various stages as part of the transfer process.</Description>
    <DisplayName>Transfer Actions</DisplayName>
    <IsVisible>true</IsVisible>
  </Categories>
  <Categories>
    <Key>FileSelection</Key>
    <Required>false</Required>
    <Description>Settings that control the files that are included in the transfer.</Description>
    <DisplayName>File Selection</DisplayName>
    <IsVisible>true</IsVisible>
  </Categories>
  <Categories>
    <Key>ExternalLocation</Key>
    <Required>false</Required>
    <Description>Settings that configure the remote target address or any other information required for the transfer.</Description>
    <DisplayName>External Location</DisplayName>
    <IsVisible>true</IsVisible>
  </Categories>
  <Categories>
    <Key>SENDActions</Key>
    <Required>false</Required>
    <Description>submit Workflow actions that can be performed on the incoming data.</Description>
    <DisplayName>SEND Processing</DisplayName>
    <IsVisible>true</IsVisible>
  </Categories>
</GenericSettingsDataSet>

CodePudding user response：

Your FROM is in the wrong place, and you're missing a comma after the xmlnamespaces clause. But with those fixed your query returns:

select x.Value
from FS_STD_CONT_TRANS
  cross join xmltable(
  xmlnamespaces(default 'http://tempuri.org/GenericSettingsDataSet.xsd'),
  '/GenericSettingsDataSet/ConfigSettings'
  passing xmltype(TRANS_SETTINGS)
  columns Value CLOB path 'Value'
  ) x
  WHERE TRANS_SETTINGS LIKE '%\\Instem\%'
;

VALUE
---------------------------------
True
False
False
False
False
\\Instem\alcon\PQ\Submit\ABC Labs

because you are filtering on the entire CLOB, not the specific value. You could filter on the value instead, as in @dr's answer:

select x.Value
from FS_STD_CONT_TRANS
  cross join xmltable(
  xmlnamespaces(default 'http://tempuri.org/GenericSettingsDataSet.xsd'),
  '/GenericSettingsDataSet/ConfigSettings'
  passing xmltype(TRANS_SETTINGS)
  columns Value CLOB path 'Value'
  ) x
  WHERE value LIKE '%\\Instem\%'
;

VALUE
---------------------------------
\\Instem\alcon\PQ\Submit\ABC Labs

but you probably actually want to look for the value corresponding to the NetWorkLocationKey whether that starts with Instem or not:

select x.Value
from FS_STD_CONT_TRANS
  cross join xmltable(
  xmlnamespaces(default 'http://tempuri.org/GenericSettingsDataSet.xsd'),
  '/GenericSettingsDataSet/ConfigSettings[Key="NetworkLocationKey"]'
  passing xmltype(TRANS_SETTINGS)
  columns Value CLOB path 'Value'
  ) x
;

VALUE
---------------------------------
\\Instem\alcon\PQ\Submit\ABC Labs

db<>fiddle

As @OldProgrammer commented, extracting that value as a CLOB seems strange - the value looks like it would be constrained to be much smaller than 4k so could be handled as a properly-sixed varchar2. Perhaps you were having to deal with values for other keys that could be longer - which wouldn't be necessary if you filter on the key.

CodePudding user response：

There are some sintax mistakes in your code. Try this:

Select 
    x.A_VALUE
From 
    FS_STD_CONT_TRANS,
    xmltable(
        xmlnamespaces(default 'http://tempuri.org/GenericSettingsDataSet.xsd'),
                '/GenericSettingsDataSet/ConfigSettings'
        passing xmltype(TRANS_SETTINGS)
                columns A_VALUE CLOB path 'Value'
    ) x
WHERE x.A_VALUE LIKE '%\\Instem\%';