我存储在一个表中的CLOB列以下XML。 我想要从这个XML一个值 - ReplacementPersonId。
我尝试使用下面的查询,但它不能正常工作。 它只返回空行,但我知道有数据领域。
SELECT EXTRACTVALUE(xmltype.createxml(transaction_document), '/Transaction/TransCtx/ReplacementPersonId')
FROM hr_api_transactions where LENGTHB(TO_CHAR(SUBSTR(transaction_document,1,4000)))<>0;
XML数据:
<Transaction>
<TransCtx>
<TransactionGroup>ABSENCE_MGMT</TransactionGroup>
<PrsnJobName>SE.Software Engineer</PrsnJobName>
<PrsnAsgFlag>Y</PrsnAsgFlag>
<LoginPrsnNpwFlag>A</LoginPrsnNpwFlag>
<PrsnBgId>202</PrsnBgId>
<EmployeeGeneration>A</EmployeeGeneration>
<PrsnNpwFlag>A</PrsnNpwFlag>
<ItemType>HRSSA</ItemType>
<PrsnJobId>35934</PrsnJobId>
<AsgStartDate dataType="d">2013-11-13</AsgStartDate>
<PrsnGradeId>22</PrsnGradeId>
<TransactionRefId dataType="n">36498</TransactionRefId>
<LoginPrsnLegCode>US</LoginPrsnLegCode>
<PrsnMgrId>31421</PrsnMgrId>
<PrsnAssignmentId>32077</PrsnAssignmentId>
<PrsnLegCode>US</PrsnLegCode>
<LoginPrsnId>31422</LoginPrsnId>
<LoginPrsnContextSet dataType="b">true</LoginPrsnContextSet>
<pNtfSubMsg>HR_ABS_NTF_SUB_MSG</pNtfSubMsg>
<ProductCode>PER</ProductCode>
<EffectiveDate dataType="d">2013-12-19</EffectiveDate>
<LoginPrsnEmpFlag>A</LoginPrsnEmpFlag>
<LoginPrsnType>E</LoginPrsnType>
<pCalledId dataType="n">1010344</pCalledId>
<ReviewTemplateRNAttr>HR_ABS_NTF_SS</ReviewTemplateRNAttr>
<TransactionId>130479</TransactionId>
<PrsnLocationId>20795</PrsnLocationId>
<PrsnKflexStructCode>PEOPLE_GROUP_FLEXFIELD</PrsnKflexStructCode>
<LoginPrsnMgrName>Ramachandran, Abhijit</LoginPrsnMgrName>
<PrsnContextSet dataType="b">true</PrsnContextSet>
<PrsnPositionName>199.Software Engineer</PrsnPositionName>
<PrsnEmpFlag>A</PrsnEmpFlag>
<PrsnMgrName>Ramachandran, Abhijit</PrsnMgrName>
<PrsnOrganizationId>204</PrsnOrganizationId>
<PrsnPositionId>58470</PrsnPositionId>
<TxnStatus>W</TxnStatus>
<LoginWorkerNumber>2110</LoginWorkerNumber>
<PrsnBgCurrencyCode>USD</PrsnBgCurrencyCode>
<HeaderType>PER_HEADER</HeaderType>
<SSHR_WF_BASED dataType="b">true</SSHR_WF_BASED>
<PrsnSecurityGroupId>0</PrsnSecurityGroupId>
<LoginPrsnMgrId>31421</LoginPrsnMgrId>
<PrsnType>E</PrsnType>
<PerzOrganizationId>202</PerzOrganizationId>
<LoginPrsnName>Ramachandran, Anoop</LoginPrsnName>
<CreatorPrsnId dataType="n">31422</CreatorPrsnId>
<PrsnId>31422</PrsnId>
<TransactionType>WF</TransactionType>
<NtfAttachAttr>FND:entity=PQH_SS_ATTACHMENT&pk1name=TransactionId&pk1value=130479</NtfAttachAttr>
<pApprovalReqd>YD</pApprovalReqd>
<pAMETranType>SSHRMS</pAMETranType>
<RelaunchFunction>HR_ABS_ENTRY_PAGE_SS</RelaunchFunction>
<PerzLocalizationCode>US</PerzLocalizationCode>
<TransactionRefTable>PER_ABSENCE_ATTENDANCES</TransactionRefTable>
<TransactionIdentifier>ABSENCES</TransactionIdentifier>
<pCalledFrom>HR_LOA_SS</pCalledFrom>
<LoginPrsnBgId>202</LoginPrsnBgId>
<PerzFunctionName>HR_LOA_SS</PerzFunctionName>
<ProcessName>HR_GENERIC_APPROVAL_PRC</ProcessName>
<PrsnPayrollId>153</PrsnPayrollId>
<PrsnName>Ramachandran, Anoop</PrsnName>
<CNode name="AbsenceParams" type="Ht">
<AbsenceAttdId>36498</AbsenceAttdId>
<AbsenceAction>CreateMode</AbsenceAction>
</CNode>
<pAMEAppId>800</pAMEAppId>
<SSHR_REVIEW_FLOW_MODE>WF_REVIEW_PAGE</SSHR_REVIEW_FLOW_MODE>
</TransCtx>
<EoApiMap>
<EO Name="oracle.apps.per.schema.server.PerAbsenceAttendancesEO">HR_PERSON_ABSENCE_SWI.PROCESS_API</EO>
</EoApiMap>
<TransCache>
<AM MomVer="1044362310593">
<cd/>
<TXN Def="0" New="0" Lok="2" pcid="128">
<EO Name="oracle.apps.per.schema.server.PerAbsenceAttendancesEO">
<![CDATA[000100000004C3044163]]>
<PerAbsenceAttendancesEORow PS="0" PK="Y">
<AbsenceAttendanceId>36498</AbsenceAttendanceId>
<BusinessGroupId>202</BusinessGroupId>
<AbsenceAttendanceTypeId>6</AbsenceAttendanceTypeId>
<PersonId>31422</PersonId>
<ReplacementPersonId>28</ReplacementPersonId>
<AbsenceDays>2</AbsenceDays>
<DateEnd>2013-12-26</DateEnd>
<DateNotification>2013-12-19 10:31:20.0</DateNotification>
<DateProjectedEnd null="true"/>
<DateProjectedStart null="true"/>
<DateStart>2013-12-25</DateStart>
<TimeProjectedEnd null="true"/>
<TimeProjectedStart null="true"/>
<LastUpdateDate>2013-12-19 10:30:56.0</LastUpdateDate>
<LastUpdatedBy>1013437</LastUpdatedBy>
<LastUpdateLogin>5574319</LastUpdateLogin>
<CreatedBy>1013437</CreatedBy>
<CreationDate>2013-12-19 10:30:56.0</CreationDate>
<ObjectVersionNumber null="true"/>
</PerAbsenceAttendancesEORow>
</EO>
<EO Name="oracle.apps.per.schema.server.TransactionsEO">
<![CDATA[000100000004C30E0550]]>
<TransactionsEORow PS="0"/>
</EO>
</TXN>
</AM>
</TransCache>
</Transaction>