甲骨文 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:
關于查詢中的第 3 列和第 4 列:
在第一個測驗中,<FieldInfos...>陣列只有一個值:ACTIVITY.
但是,在下一個測驗中,<FieldInfos...>陣列有多個值:ACTIVITY和STRATEGY。因此,當我運行相同的查詢時,它會失敗,因為它不能為單個列回傳多個值。
詢問:
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>
應用程式的螢屏截圖。
問題:
<FieldInfos...>我怎樣才能從陣列中只回傳一個值:值 where FieldName = ACTIVITY?
uj5u.com熱心網友回復:
EXTRACTVALUE已棄用。XMLTABLE與 XPATH一起使用/DETableInfo/Subtypes/Subtype/FieldInfos/SubtypeFieldInfo[FieldName="ACTIVITY"]過濾以僅獲ACTIVITY取值,然后回傳層次結構以獲取子型別代碼和名稱:
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_CODE SUBTYPE_NAME FIELD_NAME DOMAIN_NAME 0 主動運輸 活動 ACTIVITY_ATN_1 1 大地測量控制測量紀念碑 活動 活動_GCSM 2 市政結構(橋梁) 活動 ACTIVITY_MS
db<>在這里擺弄
轉載請註明出處,本文鏈接:https://www.uj5u.com/ruanti/495838.html
