我想从Oracle中的XML中提取同一标记的多个值

Question

我有以下xml：

  <?xml version="1.0" encoding="UTF-8"?>
    <SyncConsumer xmlns="http://www.ford.com/oagis" xmlns:oa="http://www.openapplications.org/oagis" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" environment="Test" revision="1.7" xsi:schemaLocation="http://www.ford.com/oagis C:\VCC_Workspace\gca_D14.07\gca_xsd\src\main\resources\Consumer\Ford\BODs\SyncConsumer.xsd">
    <oa:ApplicationArea>
    <oa:Sender>
    <oa:LogicalId>Volvo</oa:LogicalId>
    <oa:Component>Vista</oa:Component>
    <oa:Task>SyncConsumer</oa:Task>
    <oa:AuthorizationId>MQTEST27</oa:AuthorizationId>
    </oa:Sender>
    <oa:CreationDateTime>2016-07-05T12:23:47Z</oa:CreationDateTime>
    <oa:BODId>mq-test-027</oa:BODId>
    </oa:ApplicationArea>
    <DataArea>
    <oa:Sync confirm="Always">
    <oa:SyncCriteria>
    <oa:SyncExpression action="Add"/>
    </oa:SyncCriteria>
    </oa:Sync>
    <Consumer>
    <ConsumerHeader>
    <ResponseTemplate Name="VistaSyncConsumer">
    </ResponseTemplate>
    <Source>AU</Source>
    </ConsumerHeader>
    <ConsumerDetails Category="Person">
    <PersonDetails>
           <FirstName>Test_data_027</FirstName>
               <Surname>testdata_027</Surname>
     <Gender>MM</Gender>
    </PersonDetails>
    <EmailAddress Active="true" Usage="Home">no_email@volvocars.com</EmailAddress>
    <EmailAddress Active="true" Usage="Home">no_email_1@volvocars.com</EmailAddress>
    <EmailAddress Active="true" Usage="Home">no_email_2@volvocars.com</EmailAddress>
    <ContactNumber Active="true" Usage="Home">6666666</ContactNumber>
    <ContactNumber Active="true" Usage="Home">77777777</ContactNumber>
    <ContactNumber Active="true" Usage="Home">888888888</ContactNumber>
    </ConsumerDetails>
    </Consumer>
    </DataArea>
    </SyncConsumer>

the xml is stored as a clob in orig messsage in xml_message_table.
Now i want all the email address and contactno from the above xml.

我尝试了下面的代码：

Select Contact_no.EmailAddress as EmailAddress
  , Contact_no.ContactNumber as ContactNumber
 from xml_message_335
    , xmltable(xmlnamespaces(default 'http://www.ford.com/oagis'),'SyncConsumer/DataArea/Consumer/ConsumerDetails'  
           passing xmltype(xml_message_335.orig_message)
           columns 
               EmailAddress  varchar2(30) path 'EmailAddress'
              ,ContactNumber  varchar2(30) path 'ContactNumber'
          ) Contact_no;

上下文：

此查询仅在xml只有一个emailaddress和contactnumber时运行。如果该值超过，我的查询将不起作用。

我想要所有的值的电子邮件地址和联系人号码与n的出现次数在此xml。

它不会停留在一个以上的值上。

Answer 1

假设您希望每个电子邮件或电话号码占一行，您可以这样做：

• 对xmltable有两个单独的调用；一个是提取电话号码，另一个是emails
• 将一个for ordinality列添加到每个调用中，以便将行号分配给提取的元素
• 在序数列上将这些元素的输出合并在一起。

我猜每个文档中电子邮件地址和电话号码的数量可能不同，它们的数量也不总是相等的(例如，1个电子邮件地址和2个电话号码，反之亦然)。

所以我使用了一个full outer join来组合这些表。如果每次都有三个或相同数量的连接，则可以使用内部连接。

看起来是这样的：

with rws as (
  select '<?xml version="1.0" encoding="UTF-8"?>
<SyncConsumer xmlns="http://www.ford.com/oagis" xmlns:oa="http://www.openapplications.org/oagis" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" environment="Test" revision="1.7" xsi:schemaLocation="http://www.ford.com/oagis C:\VCC_Workspace\gca_D14.07\gca_xsd\src\main\resources\Consumer\Ford\BODs\SyncConsumer.xsd">
  <oa:ApplicationArea>
    <oa:Sender>
      <oa:LogicalId>Volvo</oa:LogicalId>
      <oa:Component>Vista</oa:Component>
      <oa:Task>SyncConsumer</oa:Task>
      <oa:AuthorizationId>MQTEST27</oa:AuthorizationId>
    </oa:Sender>
    <oa:CreationDateTime>2016-07-05T12:23:47Z</oa:CreationDateTime>
    <oa:BODId>mq-test-027</oa:BODId>
  </oa:ApplicationArea>
  <DataArea>
    <oa:Sync confirm="Always">
      <oa:SyncCriteria>
        <oa:SyncExpression action="Add" />
      </oa:SyncCriteria>
    </oa:Sync>
    <Consumer>
      <ConsumerHeader>
        <ResponseTemplate Name="VistaSyncConsumer" />
        <Source>AU</Source>
      </ConsumerHeader>
      <ConsumerDetails Category="Person">
        <PersonDetails>
          <FirstName>Test_data_027</FirstName>
          <Surname>testdata_027</Surname>
          <Gender>MM</Gender>
        </PersonDetails>
        <EmailAddress Active="true" Usage="Home">no_email@volvocars.com</EmailAddress>
        <EmailAddress Active="true" Usage="Home">no_email_1@volvocars.com</EmailAddress>
        <EmailAddress Active="true" Usage="Home">no_email_2@volvocars.com</EmailAddress>
        <ContactNumber Active="true" Usage="Home">6666666</ContactNumber>
        <ContactNumber Active="true" Usage="Home">77777777</ContactNumber>
        <ContactNumber Active="true" Usage="Home">888888888</ContactNumber>
      </ConsumerDetails>
    </Consumer>
  </DataArea>
</SyncConsumer>' x from dual
), emails as (
  select x.*
  from   rws, 
    xmltable (
      xmlnamespaces (default 'http://www.ford.com/oagis'),
      'SyncConsumer/DataArea/Consumer/ConsumerDetails/EmailAddress'  
         passing xmltype(rws.x)
         columns 
           seq for ordinality,
           email_address varchar2(320) path '.'
    ) x 
), phones as (
  select x.*
  from   rws, xmltable (
      xmlnamespaces (default 'http://www.ford.com/oagis'),
      'SyncConsumer/DataArea/Consumer/ConsumerDetails/ContactNumber'  
         passing xmltype(rws.x)
         columns 
           seq for ordinality,
           contact_number varchar2(320) path '.'
    ) x
)
  select * 
  from   emails 
  full outer join phones
  using  ( seq ) ;
  
SEQ    EMAIL_ADDRESS               CONTACT_NUMBER   
     1 no_email@volvocars.com      6666666           
     2 no_email_1@volvocars.com    77777777          
     3 no_email_2@volvocars.com    888888888