当前位置： 文江博客话题详情

从XML数组中提取特定值（其中fieldName = x）

发布于 2025-02-10 19:12:37 字数 13952 浏览 1 评论 0原文

Oracle 18c：

测试＃1：

以下查询按预期工作。它从XML中选择值（XML是通过视图的clob列）。

查询：

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>

来自应用程序的屏幕截图。

测试＃2：：

涉及查询中的第三列和第四列：

在第一个测试中，＆lt; fieldInfos ...＆gt;数组仅具有一个值：activity> activity < /代码>。

但是，在下一个测试中，＆lt; fieldInfos ...＆gt;数组具有多个值：活动和stragity。因此，当我运行相同的查询时，它将失败，因为它无法返回单列的多个值。

查询：

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'

错误：

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>

来自应用程序的屏幕截图。

问题：问题：

我如何仅从＆lt; fieldInfos ...＆gt;数组返回单个值：fieldname = activity的值？

原文

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?

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（1）

苏大泽ㄣ 2025-02-17 19:12:38

deftractValue被弃用。使用xmltable与XPATH /detableInfo/subtypes/subtype/fieldinfos/subtypefieldinfo [fieldName =“ active”]仅获取活动>活动值，然后返回层次结构以获取子类型代码和名称：

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'

对于示例数据，输出：

subtype_codesubtype_namefield_namedomain_name
0主动运输活动activity_atn_1
1大地控制调查纪念碑活动activity_gcsm
2市政结构（桥梁）活动activity_ms

db＆db＆lt;小提琴= BFBC0C628E3E6159E952E20D73E6855F“ rel =“ nofollow noreferrer”>在这里

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_CODESUBTYPE_NAMEFIELD_NAMEDOMAIN_NAME
0ACTIVE TRANSPORTATIONACTIVITYACTIVITY_ATN_1
1GEODETIC CONTROL SURVEY MONUMENTACTIVITYACTIVITY_GCSM
2MUNICIPAL STRUCTURES (BRIDGES)ACTIVITYACTIVITY_MS

db<>fiddle here

回复 原文
~没有更多了~

关于作者

情未る

暂无简介

文章
评论
28 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

alipaysp_snBf0MSZIv

文章 0 评论 0

梦断已成空

文章 0 评论 0

瞎闹

文章 0 评论 0

凯凯我们等你回来

文章 0 评论 0

寄意

文章 0 评论 0

似梦非梦

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文