Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table activity (xml) as
select xmltype('<Activity
    xmlns="urn:astrazeneca:na:Activity:domain:3" RestrictionGroup="NONE"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="DiscussionActivityType" TransactionCode="I" CreatedOnDate="2019-11-21T16:04:29" UpdatedOnDate="2019-11-21T16:04:32" SourceCreatedByID="20303090" SourceCreatedByFirstName="Brandy" SourceCreatedByLastName="Nirider" SourceCreatedByRole="" SourceUpdatedByID="20303090" SourceUpdatedByFirstName="Brandy" SourceUpdatedByLastName="Nirider" SourceSystemCode="FSA" SystemOfRecordCode="FSA" RecordCompanyCode="AZN" SourceCountry="USA" SourceRegion="USA" SourceSystemGroup="VNA" SystemOfRecordGroup="VNA">
    <ActivityId>
        <ns2:ID
            xmlns:ns2="urn:astrazeneca:na:CommonTypes:domain:2" SystemCode="VNA">a080P00001YZM7WQAX
        </ns2:ID>
    </ActivityId>
    <Agent>
        <EmployeeId>
            <ns2:ID
                xmlns:ns2="urn:astrazeneca:na:CommonTypes:domain:2" SystemCode="EMPLID">20303090
            </ns2:ID>
        </EmployeeId>
        <Territory>
            <ns2:TerritoryId
                xmlns:ns2="urn:astrazeneca:na:CommonTypes:domain:2">
                <ns2:ID SystemCode="AZ">20070009</ns2:ID>
            </ns2:TerritoryId>
            <ns2:TerritoryCode
                xmlns:ns2="urn:astrazeneca:na:CommonTypes:domain:2">20070009
            </ns2:TerritoryCode>
            <ns2:Role
                xmlns:ns2="urn:astrazeneca:na:CommonTypes:domain:2">PSS
            </ns2:Role>
            <ns2:Description
                xmlns:ns2="urn:astrazeneca:na:CommonTypes:domain:2">Indianapolis C IN 2
            </ns2:Description>
            <ns2:SalesTeamId
                xmlns:ns2="urn:astrazeneca:na:CommonTypes:domain:2">
                <ns2:ID SystemCode="AZ">10669</ns2:ID>
            </ns2:SalesTeamId>
            <ns2:SalesTeamCode
                xmlns:ns2="urn:astrazeneca:na:CommonTypes:domain:2">D_ALPHA
            </ns2:SalesTeamCode>
        </Territory>
    </Agent>
    <Customer>
        <AZCustomerMasterID SystemCode="AZ">58586509</AZCustomerMasterID>
        <SourceCustomerID SystemCode="NAVVA">001U000000pMTySIAW</SourceCustomerID>
        <CustomerType>HCP</CustomerType>
        <CustomerActivityRole>participant</CustomerActivityRole>
    </Customer>
    <ReferenceContent>
        <ReferenceName>Reported Adverse Reaction</ReferenceName>
        <ReferenceType>action</ReferenceType>
        <ReferenceId SystemCode="VNA">Reported Adverse Reaction</ReferenceId>
    </ReferenceContent>
    <StartDate>2019-11-21T16:03:00</StartDate>
    <EndDate>2019-11-21T16:03:00</EndDate>
    <Topic>
        <Name>BYDUREON BCISE</Name>
        <Product>
            <AZBrandId SystemCode="AZ">881574</AZBrandId>
            <AZProductId SystemCode="AZ">881574</AZProductId>
            <SourceProductId SystemCode=""/>
            <ReferenceProductId SystemCode=""/>
            <ProductName>BYDUREON BCISE</ProductName>
        </Product>
    </Topic>
    <ActivityAction>discussion</ActivityAction>
    <ActivityStatus>completed</ActivityStatus>
    <ActivityInitiatedBy>AstraZeneca agent</ActivityInitiatedBy>
    <ActivityOutcome>success with identified customer</ActivityOutcome>
    <CommunicationMode>in person</CommunicationMode>
    <LocationSetting>Selling</LocationSetting>
    <SourceValues>
        <Status>Submitted</Status>
        <SubStatus>Completed</SubStatus>
        <Applet>Actions</Applet>
        <Activity>Detail Only</Activity>
        <InteractionCategory>A_Selling (PSS)</InteractionCategory>
        <Type>Selling</Type>
    </SourceValues>
    <Priority>2</Priority>
    <DiscussionTool>N</DiscussionTool>
    <ActivityTypeCheck>DiscussionActivityType</ActivityTypeCheck>
</Activity>')
from dual;
1 rows affected

SELECT * from activity
where ExtractValue(xml,
  '/Activity/Agent/Territory/ns2:SalesTeamId/ns2:ID/text()',
  'xmlns="urn:astrazeneca:na:Activity:domain:3" xmlns:ns2="urn:astrazeneca:na:CommonTypes:domain:2"'
  ) = 10669;


XML


<Activity xmlns="urn:astrazeneca:na:Activity:domain:3" RestrictionGroup="NONE" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="DiscussionActivityType" TransactionCode="I" CreatedOnDate="2019-11-21T16:04:29" UpdatedOnDate="2019-11-21T16:04:32" SourceCreatedByID="20303090" SourceCreatedByFirstName="Brandy" SourceCreatedByLastName="Nirider" SourceCreatedByRole="" SourceUpdatedByID="20303090" SourceUpdatedByFirstName="Brandy" SourceUpdatedByLastName="Nirider" SourceSystemCode="FSA" SystemOfRecordCode="FSA" RecordCompanyCode="AZN" SourceCountry="USA" SourceRegion="USA" SourceSystemGroup="VNA" SystemOfRecordGroup="VNA">
  <ActivityId>
    <ns2:ID xmlns:ns2="urn:astrazeneca:na:CommonTypes:domain:2" SystemCode="VNA">a080P00001YZM7WQAX
        </ns2:ID>
  </ActivityId>
  <Agent>
    <EmployeeId>
      <ns2:ID xmlns:ns2="urn:astrazeneca:na:CommonTypes:domain:2" SystemCode="EMPLID">20303090
            </ns2:ID>
    </EmployeeId>
    <Territory>
      <ns2:TerritoryId xmlns:ns2="urn:astrazeneca:na:CommonTypes:domain:2">
        <ns2:ID SystemCode="AZ">20070009</ns2:ID>
      </ns2:TerritoryId>
      <ns2:TerritoryCode xmlns:ns2="urn:astrazeneca:na:CommonTypes:domain:2">20070009
            </ns2:TerritoryCode>
      <ns2:Role xmlns:ns2="urn:astrazeneca:na:CommonTypes:domain:2">PSS
            </ns2:Role>
      <ns2:Description xmlns:ns2="urn:astrazeneca:na:CommonTypes:domain:2">Indianapolis C IN 2
            </ns2:Description>
      <ns2:SalesTeamId xmlns:ns2="urn:astrazeneca:na:CommonTypes:domain:2">
        <ns2:ID SystemCode="AZ">10669</ns2:ID>
      </ns2:SalesTeamId>
      <ns2:SalesTeamCode xmlns:ns2="urn:astrazeneca:na:CommonTypes:domain:2">D_ALPHA
            </ns2:SalesTeamCode>
    </Territory>
  </Agent>
  <Customer>
    <AZCustomerMasterID SystemCode="AZ">58586509</AZCustomerMasterID>
    <SourceCustomerID SystemCode="NAVVA">001U000000pMTySIAW</SourceCustomerID>
    <CustomerType>HCP</CustomerType>
    <CustomerActivityRole>participant</CustomerActivityRole>
  </Customer>
  <ReferenceContent>
    <ReferenceName>Reported Adverse Reaction</ReferenceName>
    <ReferenceType>action</ReferenceType>
    <ReferenceId SystemCode="VNA">Reported Adverse Reaction</ReferenceId>
  </ReferenceContent>
  <StartDate>2019-11-21T16:03:00</StartDate>
  <EndDate>2019-11-21T16:03:00</EndDate>
  <Topic>
    <Name>BYDUREON BCISE</Name>
    <Product>
      <AZBrandId SystemCode="AZ">881574</AZBrandId>
      <AZProductId SystemCode="AZ">881574</AZProductId>
      <SourceProductId SystemCode=""/>
      <ReferenceProductId SystemCode=""/>
      <ProductName>BYDUREON BCISE</ProductName>
    </Product>
  </Topic>
  <ActivityAction>discussion</ActivityAction>
  <ActivityStatus>completed</ActivityStatus>
  <ActivityInitiatedBy>AstraZeneca agent</ActivityInitiatedBy>
  <ActivityOutcome>success with identified customer</ActivityOutcome>
  <CommunicationMode>in person</CommunicationMode>
  <LocationSetting>Selling</LocationSetting>
  <SourceValues>
    <Status>Submitted</Status>
    <SubStatus>Completed</SubStatus>
    <Applet>Actions</Applet>
    <Activity>Detail Only</Activity>
    <InteractionCategory>A_Selling (PSS)</InteractionCategory>
    <Type>Selling</Type>
  </SourceValues>
  <Priority>2</Priority>
  <DiscussionTool>N</DiscussionTool>
  <ActivityTypeCheck>DiscussionActivityType</ActivityTypeCheck>
</Activity>



SELECT * from activity
where XMLExists('
    declare default element namespace "urn:astrazeneca:na:Activity:domain:3";
    declare namespace ns2="urn:astrazeneca:na:CommonTypes:domain:2";
    /Activity/Agent/Territory/ns2:SalesTeamId/ns2:ID[text()=10669]
  '
  passing xml
);


XML


<Activity xmlns="urn:astrazeneca:na:Activity:domain:3" RestrictionGroup="NONE" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="DiscussionActivityType" TransactionCode="I" CreatedOnDate="2019-11-21T16:04:29" UpdatedOnDate="2019-11-21T16:04:32" SourceCreatedByID="20303090" SourceCreatedByFirstName="Brandy" SourceCreatedByLastName="Nirider" SourceCreatedByRole="" SourceUpdatedByID="20303090" SourceUpdatedByFirstName="Brandy" SourceUpdatedByLastName="Nirider" SourceSystemCode="FSA" SystemOfRecordCode="FSA" RecordCompanyCode="AZN" SourceCountry="USA" SourceRegion="USA" SourceSystemGroup="VNA" SystemOfRecordGroup="VNA">
  <ActivityId>
    <ns2:ID xmlns:ns2="urn:astrazeneca:na:CommonTypes:domain:2" SystemCode="VNA">a080P00001YZM7WQAX
        </ns2:ID>
  </ActivityId>
  <Agent>
    <EmployeeId>
      <ns2:ID xmlns:ns2="urn:astrazeneca:na:CommonTypes:domain:2" SystemCode="EMPLID">20303090
            </ns2:ID>
    </EmployeeId>
    <Territory>
      <ns2:TerritoryId xmlns:ns2="urn:astrazeneca:na:CommonTypes:domain:2">
        <ns2:ID SystemCode="AZ">20070009</ns2:ID>
      </ns2:TerritoryId>
      <ns2:TerritoryCode xmlns:ns2="urn:astrazeneca:na:CommonTypes:domain:2">20070009
            </ns2:TerritoryCode>
      <ns2:Role xmlns:ns2="urn:astrazeneca:na:CommonTypes:domain:2">PSS
            </ns2:Role>
      <ns2:Description xmlns:ns2="urn:astrazeneca:na:CommonTypes:domain:2">Indianapolis C IN 2
            </ns2:Description>
      <ns2:SalesTeamId xmlns:ns2="urn:astrazeneca:na:CommonTypes:domain:2">
        <ns2:ID SystemCode="AZ">10669</ns2:ID>
      </ns2:SalesTeamId>
      <ns2:SalesTeamCode xmlns:ns2="urn:astrazeneca:na:CommonTypes:domain:2">D_ALPHA
            </ns2:SalesTeamCode>
    </Territory>
  </Agent>
  <Customer>
    <AZCustomerMasterID SystemCode="AZ">58586509</AZCustomerMasterID>
    <SourceCustomerID SystemCode="NAVVA">001U000000pMTySIAW</SourceCustomerID>
    <CustomerType>HCP</CustomerType>
    <CustomerActivityRole>participant</CustomerActivityRole>
  </Customer>
  <ReferenceContent>
    <ReferenceName>Reported Adverse Reaction</ReferenceName>
    <ReferenceType>action</ReferenceType>
    <ReferenceId SystemCode="VNA">Reported Adverse Reaction</ReferenceId>
  </ReferenceContent>
  <StartDate>2019-11-21T16:03:00</StartDate>
  <EndDate>2019-11-21T16:03:00</EndDate>
  <Topic>
    <Name>BYDUREON BCISE</Name>
    <Product>
      <AZBrandId SystemCode="AZ">881574</AZBrandId>
      <AZProductId SystemCode="AZ">881574</AZProductId>
      <SourceProductId SystemCode=""/>
      <ReferenceProductId SystemCode=""/>
      <ProductName>BYDUREON BCISE</ProductName>
    </Product>
  </Topic>
  <ActivityAction>discussion</ActivityAction>
  <ActivityStatus>completed</ActivityStatus>
  <ActivityInitiatedBy>AstraZeneca agent</ActivityInitiatedBy>
  <ActivityOutcome>success with identified customer</ActivityOutcome>
  <CommunicationMode>in person</CommunicationMode>
  <LocationSetting>Selling</LocationSetting>
  <SourceValues>
    <Status>Submitted</Status>
    <SubStatus>Completed</SubStatus>
    <Applet>Actions</Applet>
    <Activity>Detail Only</Activity>
    <InteractionCategory>A_Selling (PSS)</InteractionCategory>
    <Type>Selling</Type>
  </SourceValues>
  <Priority>2</Priority>
  <DiscussionTool>N</DiscussionTool>
  <ActivityTypeCheck>DiscussionActivityType</ActivityTypeCheck>
</Activity>



SELECT * from activity
where XMLExists('
    declare default element namespace "urn:astrazeneca:na:Activity:domain:3";
    declare namespace ns2="urn:astrazeneca:na:CommonTypes:domain:2";
    /Activity/Agent/Territory/ns2:SalesTeamId/ns2:ID[text()=$id]
  '
  passing xml, 10669 as "id"
);


XML


<Activity xmlns="urn:astrazeneca:na:Activity:domain:3" RestrictionGroup="NONE" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="DiscussionActivityType" TransactionCode="I" CreatedOnDate="2019-11-21T16:04:29" UpdatedOnDate="2019-11-21T16:04:32" SourceCreatedByID="20303090" SourceCreatedByFirstName="Brandy" SourceCreatedByLastName="Nirider" SourceCreatedByRole="" SourceUpdatedByID="20303090" SourceUpdatedByFirstName="Brandy" SourceUpdatedByLastName="Nirider" SourceSystemCode="FSA" SystemOfRecordCode="FSA" RecordCompanyCode="AZN" SourceCountry="USA" SourceRegion="USA" SourceSystemGroup="VNA" SystemOfRecordGroup="VNA">
  <ActivityId>
    <ns2:ID xmlns:ns2="urn:astrazeneca:na:CommonTypes:domain:2" SystemCode="VNA">a080P00001YZM7WQAX
        </ns2:ID>
  </ActivityId>
  <Agent>
    <EmployeeId>
      <ns2:ID xmlns:ns2="urn:astrazeneca:na:CommonTypes:domain:2" SystemCode="EMPLID">20303090
            </ns2:ID>
    </EmployeeId>
    <Territory>
      <ns2:TerritoryId xmlns:ns2="urn:astrazeneca:na:CommonTypes:domain:2">
        <ns2:ID SystemCode="AZ">20070009</ns2:ID>
      </ns2:TerritoryId>
      <ns2:TerritoryCode xmlns:ns2="urn:astrazeneca:na:CommonTypes:domain:2">20070009
            </ns2:TerritoryCode>
      <ns2:Role xmlns:ns2="urn:astrazeneca:na:CommonTypes:domain:2">PSS
            </ns2:Role>
      <ns2:Description xmlns:ns2="urn:astrazeneca:na:CommonTypes:domain:2">Indianapolis C IN 2
            </ns2:Description>
      <ns2:SalesTeamId xmlns:ns2="urn:astrazeneca:na:CommonTypes:domain:2">
        <ns2:ID SystemCode="AZ">10669</ns2:ID>
      </ns2:SalesTeamId>
      <ns2:SalesTeamCode xmlns:ns2="urn:astrazeneca:na:CommonTypes:domain:2">D_ALPHA
            </ns2:SalesTeamCode>
    </Territory>
  </Agent>
  <Customer>
    <AZCustomerMasterID SystemCode="AZ">58586509</AZCustomerMasterID>
    <SourceCustomerID SystemCode="NAVVA">001U000000pMTySIAW</SourceCustomerID>
    <CustomerType>HCP</CustomerType>
    <CustomerActivityRole>participant</CustomerActivityRole>
  </Customer>
  <ReferenceContent>
    <ReferenceName>Reported Adverse Reaction</ReferenceName>
    <ReferenceType>action</ReferenceType>
    <ReferenceId SystemCode="VNA">Reported Adverse Reaction</ReferenceId>
  </ReferenceContent>
  <StartDate>2019-11-21T16:03:00</StartDate>
  <EndDate>2019-11-21T16:03:00</EndDate>
  <Topic>
    <Name>BYDUREON BCISE</Name>
    <Product>
      <AZBrandId SystemCode="AZ">881574</AZBrandId>
      <AZProductId SystemCode="AZ">881574</AZProductId>
      <SourceProductId SystemCode=""/>
      <ReferenceProductId SystemCode=""/>
      <ProductName>BYDUREON BCISE</ProductName>
    </Product>
  </Topic>
  <ActivityAction>discussion</ActivityAction>
  <ActivityStatus>completed</ActivityStatus>
  <ActivityInitiatedBy>AstraZeneca agent</ActivityInitiatedBy>
  <ActivityOutcome>success with identified customer</ActivityOutcome>
  <CommunicationMode>in person</CommunicationMode>
  <LocationSetting>Selling</LocationSetting>
  <SourceValues>
    <Status>Submitted</Status>
    <SubStatus>Completed</SubStatus>
    <Applet>Actions</Applet>
    <Activity>Detail Only</Activity>
    <InteractionCategory>A_Selling (PSS)</InteractionCategory>
    <Type>Selling</Type>
  </SourceValues>
  <Priority>2</Priority>
  <DiscussionTool>N</DiscussionTool>
  <ActivityTypeCheck>DiscussionActivityType</ActivityTypeCheck>
</Activity>