Extract specific value from XML array (where FieldName = x)-CodePudding

Oracle 18c:

Test #1:

The following query works as expected. It selects values from XML (the XML is a clob column via a view).

Query:

select
    cast(substr(extractvalue(a.column_value,'/Subtype/SubtypeCode'),1,255) as number(9,0)) as subtype_code,
    substr(extractvalue(a.column_value,'/Subtype/SubtypeName'),1,255) as subtype_name,
    substr(extractvalue(a.column_value,'/Subtype/FieldInfos/SubtypeFieldInfo/FieldName'),1,255) as field_name,
    substr(extractvalue(a.column_value,'/Subtype/FieldInfos/SubtypeFieldInfo/DomainName'),1,255) as domain_name
from
    sde.gdb_items_vw
cross join
    xmlsequence(xmltype(definition).extract('/DETableInfo/Subtypes/Subtype')) a     --https://community.oracle.com/tech/developers/discussion/4499629/at-what-version-did-the-table-keyword-for-table-collection-expressions-become-optional
where
    name = 'INFRASTR.EVENTS_ONLY_1_ARRAY_VALUE'

SUBTYPE_CODE   SUBTYPE_DESCRIPTION               FIELD_NAME  DOMAIN_NAME
------------   --------------------------------  ----------  --------------
           0   ACTIVE TRANSPORTATION             ACTIVITY    ACTIVITY_ATN_1
           1   GEODETIC CONTROL SURVEY MONUMENT  ACTIVITY    ACTIVITY_GCSM
           2   MUNICIPAL STRUCTURES (BRIDGES)    ACTIVITY    ACTIVITY_MS

XML:

<?xml version="1.0" encoding="UTF-8"?>
<DETableInfo xmlns:typens="http://www.esri.com/schemas/ArcGIS/10.7" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="typens:DETableInfo">
   <CatalogPath>\INFRASTR.BC_EVENTS</CatalogPath>
   <Name>INFRASTR.BC_EVENTS</Name>
   <ChildrenExpanded>false</ChildrenExpanded>
   <DatasetType>esriDTTable</DatasetType>
   <DSID>60337</DSID>
   <Versioned>false</Versioned>
   <CanVersion>true</CanVersion>
   <ConfigurationKeyword />
   <RequiredGeodatabaseClientVersion>10.0</RequiredGeodatabaseClientVersion>
   <HasOID>true</HasOID>
   <OIDFieldName>OBJECTID</OIDFieldName>
   <GPFieldInfoExs xsi:type="typens:ArrayOfGPFieldInfoEx">
      <GPFieldInfoEx xsi:type="typens:GPFieldInfoEx">
         <Name>OBJECTID</Name>
         <AliasName>OBJECTID</AliasName>
         <ModelName>OBJECTID</ModelName>
         <FieldType>esriFieldTypeOID</FieldType>
         <IsNullable>false</IsNullable>
         <DomainFixed>true</DomainFixed>
         <Required>true</Required>
         <Editable>false</Editable>
      </GPFieldInfoEx>
      <GPFieldInfoEx xsi:type="typens:GPFieldInfoEx">
         <Name>ASSET_CLASS</Name>
         <AliasName>ASSET_CLASS</AliasName>
         <ModelName>ASSET_CLASS</ModelName>
         <FieldType>esriFieldTypeSmallInteger</FieldType>
         <DefaultValueNumeric>2</DefaultValueNumeric>
         <IsNullable>true</IsNullable>
      </GPFieldInfoEx>
      <GPFieldInfoEx xsi:type="typens:GPFieldInfoEx">
         <Name>ACTIVITY</Name>
         <AliasName>ACTIVITY</AliasName>
         <ModelName>ACTIVITY</ModelName>
         <FieldType>esriFieldTypeString</FieldType>
         <IsNullable>true</IsNullable>
      </GPFieldInfoEx>
   </GPFieldInfoExs>
   <CLSID>{7A566981-C114-11D2-8A28-006097AFF44E}</CLSID>
   <EXTCLSID />
   <RelationshipClassNames xsi:type="typens:Names" />
   <AliasName />
   <ModelName />
   <HasGlobalID>false</HasGlobalID>
   <GlobalIDFieldName />
   <RasterFieldName />
   <ExtensionProperties xsi:type="typens:PropertySet">
      <PropertyArray xsi:type="typens:ArrayOfPropertySetProperty" />
   </ExtensionProperties>
   <SubtypeFieldName>ASSET_CLASS</SubtypeFieldName>
   <DefaultSubtypeCode>2</DefaultSubtypeCode>
   <Subtypes xsi:type="typens:ArrayOfSubtype">
      <Subtype xsi:type="typens:Subtype">
         <SubtypeName>ACTIVE TRANSPORTATION</SubtypeName>
         <SubtypeCode>0</SubtypeCode>
         <FieldInfos xsi:type="typens:ArrayOfSubtypeFieldInfo">
            <SubtypeFieldInfo xsi:type="typens:SubtypeFieldInfo">
               <FieldName>ACTIVITY</FieldName>
               <DomainName>ACTIVITY_ATN_1</DomainName>
            </SubtypeFieldInfo>
         </FieldInfos>
      </Subtype>
      <Subtype xsi:type="typens:Subtype">
         <SubtypeName>GEODETIC CONTROL SURVEY MONUMENT</SubtypeName>
         <SubtypeCode>1</SubtypeCode>
         <FieldInfos xsi:type="typens:ArrayOfSubtypeFieldInfo">
            <SubtypeFieldInfo xsi:type="typens:SubtypeFieldInfo">
               <FieldName>ACTIVITY</FieldName>
               <DomainName>ACTIVITY_GCSM</DomainName>
            </SubtypeFieldInfo>
         </FieldInfos>
      </Subtype>
      <Subtype xsi:type="typens:Subtype">
         <SubtypeName>MUNICIPAL STRUCTURES (BRIDGES)</SubtypeName>
         <SubtypeCode>2</SubtypeCode>
         <FieldInfos xsi:type="typens:ArrayOfSubtypeFieldInfo">
            <SubtypeFieldInfo xsi:type="typens:SubtypeFieldInfo">
               <FieldName>ACTIVITY</FieldName>
               <DomainName>ACTIVITY_MS</DomainName>
            </SubtypeFieldInfo>
         </FieldInfos>
      </Subtype>
   </Subtypes>
   <ControllerMemberships xsi:type="typens:ArrayOfControllerMembership" />
   <EditorTrackingEnabled>false</EditorTrackingEnabled>
   <CreatorFieldName />
   <CreatedAtFieldName />
   <EditorFieldName />
   <EditedAtFieldName />
   <IsTimeInUTC>false</IsTimeInUTC>
   <ChangeTracked>false</ChangeTracked>
   <FieldFilteringEnabled>false</FieldFilteringEnabled>
   <FilteredFieldNames xsi:type="typens:Names" />
</DETableInfo>

Screenshot from the application.

Test #2:

Regarding the 3rd and 4th columns in the query:

In the first test, the <FieldInfos...> array only had a single value: ACTIVITY.

However, in this next test, the <FieldInfos...> array has multiple values: ACTIVITY and STRATEGY. So when I run the same query, it will fail because it can't return multiple values for a single column.

Query:

select
    cast(substr(extractvalue(a.column_value,'/Subtype/SubtypeCode'),1,255) as number(9,0)) as subtype_code,
    substr(extractvalue(a.column_value,'/Subtype/SubtypeName'),1,255) as subtype_name,
    substr(extractvalue(a.column_value,'/Subtype/FieldInfos/SubtypeFieldInfo/FieldName'),1,255) as field_name,
    substr(extractvalue(a.column_value,'/Subtype/FieldInfos/SubtypeFieldInfo/DomainName'),1,255) as domain_name
from
    sde.gdb_items_vw
cross join
    xmlsequence(xmltype(definition).extract('/DETableInfo/Subtypes/Subtype')) a
where
    name = 'INFRASTR.EVENTS_MULTIPLE_ARRAY_VALUES'

Error:

ORA-19025: EXTRACTVALUE returns value of only one node
19025. 00000 -  "EXTRACTVALUE returns value of only one node"
*Cause:    Given XPath points to more than one node.
*Action:   Rewrite the query so that exactly one node is returned.

XML:

<?xml version="1.0" encoding="UTF-8"?>
<DETableInfo xmlns:typens="http://www.esri.com/schemas/ArcGIS/10.7" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="typens:DETableInfo">
   <CatalogPath>\INFRASTR.BC_EVENTS</CatalogPath>
   <Name>INFRASTR.BC_EVENTS</Name>
   <ChildrenExpanded>false</ChildrenExpanded>
   <DatasetType>esriDTTable</DatasetType>
   <DSID>60337</DSID>
   <Versioned>false</Versioned>
   <CanVersion>true</CanVersion>
   <ConfigurationKeyword />
   <RequiredGeodatabaseClientVersion>10.0</RequiredGeodatabaseClientVersion>
   <HasOID>true</HasOID>
   <OIDFieldName>OBJECTID</OIDFieldName>
   <GPFieldInfoExs xsi:type="typens:ArrayOfGPFieldInfoEx">
      <GPFieldInfoEx xsi:type="typens:GPFieldInfoEx">
         <Name>OBJECTID</Name>
         <AliasName>OBJECTID</AliasName>
         <ModelName>OBJECTID</ModelName>
         <FieldType>esriFieldTypeOID</FieldType>
         <IsNullable>false</IsNullable>
         <DomainFixed>true</DomainFixed>
         <Required>true</Required>
         <Editable>false</Editable>
      </GPFieldInfoEx>
      <GPFieldInfoEx xsi:type="typens:GPFieldInfoEx">
         <Name>ASSET_CLASS</Name>
         <AliasName>ASSET_CLASS</AliasName>
         <ModelName>ASSET_CLASS</ModelName>
         <FieldType>esriFieldTypeSmallInteger</FieldType>
         <DefaultValueNumeric>2</DefaultValueNumeric>
         <IsNullable>true</IsNullable>
      </GPFieldInfoEx>
      <GPFieldInfoEx xsi:type="typens:GPFieldInfoEx">
         <Name>ACTIVITY</Name>
         <AliasName>ACTIVITY</AliasName>
         <ModelName>ACTIVITY</ModelName>
         <FieldType>esriFieldTypeString</FieldType>
         <IsNullable>true</IsNullable>
      </GPFieldInfoEx>
      <GPFieldInfoEx xsi:type="typens:GPFieldInfoEx">
         <Name>STRATEGY</Name>
         <AliasName>TEST_TEXT_FIELD</AliasName>
         <ModelName>STRATEGY</ModelName>
         <FieldType>esriFieldTypeString</FieldType>
         <IsNullable>true</IsNullable>
      </GPFieldInfoEx>
   </GPFieldInfoExs>
   <CLSID>{7A566981-C114-11D2-8A28-006097AFF44E}</CLSID>
   <EXTCLSID />
   <RelationshipClassNames xsi:type="typens:Names" />
   <AliasName />
   <ModelName />
   <HasGlobalID>false</HasGlobalID>
   <GlobalIDFieldName />
   <RasterFieldName />
   <ExtensionProperties xsi:type="typens:PropertySet">
      <PropertyArray xsi:type="typens:ArrayOfPropertySetProperty" />
   </ExtensionProperties>
   <SubtypeFieldName>ASSET_CLASS</SubtypeFieldName>
   <DefaultSubtypeCode>2</DefaultSubtypeCode>
   <Subtypes xsi:type="typens:ArrayOfSubtype">
      <Subtype xsi:type="typens:Subtype">
         <SubtypeName>ACTIVE TRANSPORTATION</SubtypeName>
         <SubtypeCode>0</SubtypeCode>
         <FieldInfos xsi:type="typens:ArrayOfSubtypeFieldInfo">
            <SubtypeFieldInfo xsi:type="typens:SubtypeFieldInfo">
               <FieldName>ACTIVITY</FieldName>
               <DomainName>ACTIVITY_ATN_1</DomainName>
            </SubtypeFieldInfo>
            <SubtypeFieldInfo xsi:type="typens:SubtypeFieldInfo">
               <FieldName>STRATEGY</FieldName>
               <DomainName>STRATEGY</DomainName>
            </SubtypeFieldInfo>
         </FieldInfos>
      </Subtype>
      <Subtype xsi:type="typens:Subtype">
         <SubtypeName>GEODETIC CONTROL SURVEY MONUMENT</SubtypeName>
         <SubtypeCode>1</SubtypeCode>
         <FieldInfos xsi:type="typens:ArrayOfSubtypeFieldInfo">
            <SubtypeFieldInfo xsi:type="typens:SubtypeFieldInfo">
               <FieldName>ACTIVITY</FieldName>
               <DomainName>ACTIVITY_GCSM</DomainName>
            </SubtypeFieldInfo>
            <SubtypeFieldInfo xsi:type="typens:SubtypeFieldInfo">
               <FieldName>STRATEGY</FieldName>
               <DomainName>STRATEGY</DomainName>
            </SubtypeFieldInfo>
         </FieldInfos>
      </Subtype>
      <Subtype xsi:type="typens:Subtype">
         <SubtypeName>MUNICIPAL STRUCTURES (BRIDGES)</SubtypeName>
         <SubtypeCode>2</SubtypeCode>
         <FieldInfos xsi:type="typens:ArrayOfSubtypeFieldInfo">
            <SubtypeFieldInfo xsi:type="typens:SubtypeFieldInfo">
               <FieldName>ACTIVITY</FieldName>
               <DomainName>ACTIVITY_MS</DomainName>
            </SubtypeFieldInfo>
            <SubtypeFieldInfo xsi:type="typens:SubtypeFieldInfo">
               <FieldName>STRATEGY</FieldName>
               <DomainName>STRATEGY</DomainName>
            </SubtypeFieldInfo>
         </FieldInfos>
      </Subtype>
   </Subtypes>
   <ControllerMemberships xsi:type="typens:ArrayOfControllerMembership" />
   <EditorTrackingEnabled>false</EditorTrackingEnabled>
   <CreatorFieldName />
   <CreatedAtFieldName />
   <EditorFieldName />
   <EditedAtFieldName />
   <IsTimeInUTC>false</IsTimeInUTC>
   <ChangeTracked>false</ChangeTracked>
   <FieldFilteringEnabled>false</FieldFilteringEnabled>
   <FilteredFieldNames xsi:type="typens:Names" />
</DETableInfo>

Screenshot from the application.

Question:

How can I only return a single value from the the <FieldInfos...> array: the value where FieldName = ACTIVITY?

CodePudding user response：

EXTRACTVALUE is deprecated. Use XMLTABLE with the XPATH /DETableInfo/Subtypes/Subtype/FieldInfos/SubtypeFieldInfo[FieldName="ACTIVITY"] to filter to only get the ACTIVITY values and then go back up the hierarchy to get the sub-type code and name:

SELECT x.*
FROM   /*sde.*/gdb_items_vw i
       CROSS APPLY XMLTABLE(
         '/DETableInfo/Subtypes/Subtype/FieldInfos/SubtypeFieldInfo[FieldName="ACTIVITY"]'
         PASSING XMLTYPE(i.definition)
         COLUMNS
           SubType_Code NUMBER        PATH './../../SubtypeCode',
           SubType_Name VARCHAR2(255) PATH './../../SubtypeName',
           Field_Name   VARCHAR2(255) PATH './FieldName',
           Domain_Name  VARCHAR2(255) PATH './DomainName'
       ) x
WHERE  i.name = 'INFRASTR.EVENTS_ONLY_1_ARRAY_VALUE'

Which, for the sample data, outputs:

SUBTYPE_CODE SUBTYPE_NAME FIELD_NAME DOMAIN_NAME

0 ACTIVE TRANSPORTATION ACTIVITY ACTIVITY_ATN_1

1 GEODETIC CONTROL SURVEY MONUMENT ACTIVITY ACTIVITY_GCSM

2 MUNICIPAL STRUCTURES (BRIDGES) ACTIVITY ACTIVITY_MS

SUBTYPE_CODE	SUBTYPE_NAME	FIELD_NAME	DOMAIN_NAME
0	ACTIVE TRANSPORTATION	ACTIVITY	ACTIVITY_ATN_1
1	GEODETIC CONTROL SURVEY MONUMENT	ACTIVITY	ACTIVITY_GCSM
2	MUNICIPAL STRUCTURES (BRIDGES)	ACTIVITY	ACTIVITY_MS

db<>fiddle here