Database Software

Hi,

I am trying to write a function that receives an XML as input parameters and return a modified XML as output

- nodes are deleted from the input XML

- one node must be inserted, and the value of the node must be 1 or 0 based on an XPATH expression

I found out how to delete the nodes, I found out how to insert a new node, but I can't set the value of the inserted node conditionally on an XPATH expression.

Here is my current procedure and below a sample of an input XML. I am using Oracle 12c.

CREATE OR REPLACE FUNCTION STRIP_XML
(
  IN_XML IN SYS.XMLTYPE
) RETURN SYS.XMLTYPE AS
p_result XMLType;
BEGIN
  select
      xmlquery(
         'declare default element namespace "http://mad.evs.com/search"; (: :)
          copy $d := .
          modify (
  delete node $d//MainCategory/@logId,
           delete node $d/MainCategory/@id,
-- a lot more of those delete node
 -- insert a node, but the value 1 is conditional
           insert node <DMZ>1</DMZ> after $d/MainCategory/SDataSection/EventDate)
          return $d'
        passing in_xml
        returning content
        ) into p_result
        from dual;


  RETURN p_result;
END STRIP_XML;

The condition for the value of is

HASPATH(//DigitalAssets/DigitalAsset[@available="true" and @videoFormatId="11"]/VideoLocations/VideoLocation[@typeId="8"]) then 1 else 0

It is complex because DigitalAssets/DigitalAsset is a collection. Here is an example of an input XML

<MainCategory xmlns="http://mad.evs.com/search" id="9" logId="3349" logType="3">
  <Name>Sport</Name>
  <Serie id="163" externalId="557">
  <TitleAKA>UCL 2006/07</TitleAKA>
  <DigitalAssets available="true" som="20:28:49:05" dur="00:02:46:04" videoDurationMinutes="3">
     <DigitalAsset available="true" som="20:28:49:05" dur="00:02:46:04" videoDurationMinutes="3" videoFormatId="3">
        <VideoLocations>
            <VideoLocation id="3" path="003349MA.mxf" typeId="1" locationId="1" priority="0"/>
            <VideoLocation id="3" path="003349MA.mxf" typeId="2" locationId="1" priority="0"/>
            <VideoLocation id="3" path="003349MA.mxf" typeId="5" locationId="1" priority="0"/>
         </VideoLocations>
      </DigitalAsset>
      <DigitalAsset available="true" som="20:28:49:05" dur="00:02:46:04" videoDurationMinutes="3" videoFormatId="11">
         <VideoLocations>
            <VideoLocation id="101" path="003349MA.mpg" typeId="1" locationId="1" priority="0"/>
            <VideoLocation id="101" path="003349MA.mpg" typeId="2" locationId="1" priority="0"/>
            <VideoLocation id="101" path="003349MA.mpg" typeId="8" locationId="1" priority="0"/>
         </VideoLocations>
      </DigitalAsset>
  </DigitalAssets>
  <SDataSection xmlns="http://mad.evs.com/search">
    <EventDate>2006-08-09</EventDate>
    <LogType>3</LogType>
  </SDataSection>
</MainCategory>

In this case, because the HASPATH expression would evaluate to true, the value for the added node should be 1.

Any help or hints how I should tackle that would be appreciated. I have no experience with XML and XQuery, I have build my function via trial and error based on the doc.

Thanks and regards, Pierre