从甲骨文中的复杂XML中提取数据

Question

请我需要帮助将复杂的XML数据提取到Oracle的行中。我尝试了ExtractValue，但它给出了错误：

ORA-31011 XML解析失败

ORA-19202 XML处理中发生了错误

在输出xml下方：

<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://www.w3.org/2005/08/addressing" xmlns:u="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
   <s:Body>
      <GetEmploymentStatusMultipleResponse xmlns="http://MSB.Services.Integration.External.ServicesContracts.GOSI.IGOSIService">
         <GetEmploymentStatusMultipleResult xmlns:b="http://MSB.Services.Integration.External.DataContracts.GOSI.MultipleEmploymentStatusStructure" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
            <b:EmploymentStatusStructure xmlns:c="http://MSB.Services.Integration.External.DataContracts.GOSI.EmploymentStatusStructure">
               <c:EmploymentStatusStructure>
                  <c:Contributor xmlns:d="http://MSB.Services.Integration.External.DataContracts.GOSI.ContributorStructure">
                     <d:ContributorID xmlns:e="http://MSB.Services.Integration.External.DataContracts.GOSI.NationalIdentifierSummaryStructure">
                        <e:NationalID>1086828181</e:NationalID>
                     </d:ContributorID>
                  </c:Contributor>
                  <c:ContributorName xmlns:d="http://MSB.Services.Integration.External.DataContracts.GOSI.PersonNameDetailsStructure">
                     <d:Item xmlns:e="http://MSB.Services.Integration.External.DataContracts.GOSI.PersonNameBodyStructure">
                        <e:Title i:nil="true" xmlns:f="http://schemas.microsoft.com/2003/10/Serialization/Arrays"/>
                        <e:Prefix i:nil="true" xmlns:f="http://schemas.microsoft.com/2003/10/Serialization/Arrays"/>
                        <e:FirstName>test1</e:FirstName>
                        <e:SecondName>test2</e:SecondName>
                        <e:ThirdName>test3</e:ThirdName>
                        <e:FourthName i:nil="true"/>
                        <e:LastName>test4</e:LastName>
                        <e:Suffix i:nil="true" xmlns:f="http://schemas.microsoft.com/2003/10/Serialization/Arrays"/>
                     </d:Item>
                     <d:Language>AR</d:Language>
                  </c:ContributorName>
                  <c:ContributorStatus>2</c:ContributorStatus>
               </c:EmploymentStatusStructure>
            </b:EmploymentStatusStructure>
         </GetEmploymentStatusMultipleResult>
      </GetEmploymentStatusMultipleResponse>
   </s:Body>
 </s:Envelope>

Answer 1

使用utl_file或简单地选择to_char（dbms_xmlgen.getxml（'select* from ur_table'））为xml_data，来自dual;