I have an Oracle table which contains multiple XML files stored in an XMLTYPE. Although I can query and update the XML types, I'm trying to write a procedure that will take one XML and insert into the same table as two new XMLTYPES based on some condition(s).
As an example, if the below was stored in my XMLTYPE, I would like to create two messages, both with all the header information but one containing message lines where rank/position was upto 5 (would return two lines) and the other where rank/position was greater than 5 (would return three lines):
<DistrictMsg timestamp="2014-09-05T04:45:00">
<Publication xmlns:ns1="http://www.mynamespace.co.uk">
<TopicID>ENGLISH_DISTRICTS</TopicID>
</Publication>
<Message xmlns="http://www.mynamespace.co.uk">
<ns1:MessageHeader xmlns:ns1="http://www.mynamespace.co.uk">
<ns1:MessageType>
<ns1:ID>EnglishDistricts2014</ns1:ID>
</ns1:MessageType>
<ns1:DocType>
<ns1:InternalName>
<ns1:ID>DistrictByPopulation</ns1:ID>
</ns1:InternalName>
</ns1:DocType>
</ns1:MessageHeader>
<ns1:MessageLine xmlns:ns1="http://www.mynamespace.co.uk">
<ns1:District>
<ns1:Name>Birmingham</ns1:Name>
</ns1:District>
<ns1:Population measure="1000">1074.3</ns1:Population>
<DistRank>
<PopulationRank>
<Position>1</Position>
</PopulationRank>
</DistRank>
</ns1:MessageLine>
<ns1:MessageLine xmlns:ns1="http://www.mynamespace.co.uk">
<ns1:District>
<ns1:Name>Manchester</ns1:Name>
</ns1:District>
<ns1:Population measure="1000">502.9</ns1:Population>
<DistRank>
<PopulationRank>
<Position>7</Position>
</PopulationRank>
</DistRank>
</ns1:MessageLine>
<ns1:MessageLine xmlns:ns1="http://www.mynamespace.co.uk">
<ns1:District>
<ns1:Name>Liverpool</ns1:Name>
</ns1:District>
<ns1:Population measure="1000">465.7</ns1:Population>
<DistRank>
<PopulationRank>
<Position>9</Position>
</PopulationRank>
</DistRank>
</ns1:MessageLine>
<ns1:MessageLine xmlns:ns1="http://www.mynamespace.co.uk">
<ns1:District>
<ns1:Name>Doncaster</ns1:Name>
</ns1:District>
<ns1:Population measure="1000">302.5</ns1:Population>
<DistRank>
<PopulationRank>
<Position>34</Position>
</PopulationRank>
</DistRank>
</ns1:MessageLine>
<ns1:MessageLine xmlns:ns1="http://www.mynamespace.co.uk">
<ns1:District>
<ns1:Name>Cornwall</ns1:Name>
</ns1:District>
<ns1:Population measure="1000">533.8</ns1:Population>
<DistRank>
<PopulationRank>
<Position>4</Position>
</PopulationRank>
</DistRank>
</ns1:MessageLine>
</Message>
</DistrictMsg>
You can do this by removing the entries you dont want using deletexml. To return those entries up to 5, I've deleted all those greater than 5. Similarly to return those greater than 5 I've deleted the entries less than or equal to 5. Here's my solution, hope it helps:
with xml_data as(
select xmltype('
<DistrictMsg timestamp="2014-09-05T04:45:00">
<Publication xmlns:ns1="http://www.mynamespace.co.uk">
<TopicID>ENGLISH_DISTRICTS</TopicID>
</Publication>
<Message xmlns="http://www.mynamespace.co.uk">
<ns1:MessageHeader xmlns:ns1="http://www.mynamespace.co.uk">
<ns1:MessageType>
<ns1:ID>EnglishDistricts2014</ns1:ID>
</ns1:MessageType>
<ns1:DocType>
<ns1:InternalName>
<ns1:ID>DistrictByPopulation</ns1:ID>
</ns1:InternalName>
</ns1:DocType>
</ns1:MessageHeader>
<ns1:MessageLine xmlns:ns1="http://www.mynamespace.co.uk">
<ns1:District>
<ns1:Name>Birmingham</ns1:Name>
</ns1:District>
<ns1:Population measure="1000">1074.3</ns1:Population>
<DistRank>
<PopulationRank>
<Position>1</Position>
</PopulationRank>
</DistRank>
</ns1:MessageLine>
<ns1:MessageLine xmlns:ns1="http://www.mynamespace.co.uk">
<ns1:District>
<ns1:Name>Manchester</ns1:Name>
</ns1:District>
<ns1:Population measure="1000">502.9</ns1:Population>
<DistRank>
<PopulationRank>
<Position>7</Position>
</PopulationRank>
</DistRank>
</ns1:MessageLine>
<ns1:MessageLine xmlns:ns1="http://www.mynamespace.co.uk">
<ns1:District>
<ns1:Name>Liverpool</ns1:Name>
</ns1:District>
<ns1:Population measure="1000">465.7</ns1:Population>
<DistRank>
<PopulationRank>
<Position>9</Position>
</PopulationRank>
</DistRank>
</ns1:MessageLine>
<ns1:MessageLine xmlns:ns1="http://www.mynamespace.co.uk">
<ns1:District>
<ns1:Name>Doncaster</ns1:Name>
</ns1:District>
<ns1:Population measure="1000">302.5</ns1:Population>
<DistRank>
<PopulationRank>
<Position>34</Position>
</PopulationRank>
</DistRank>
</ns1:MessageLine>
<ns1:MessageLine xmlns:ns1="http://www.mynamespace.co.uk">
<ns1:District>
<ns1:Name>Cornwall</ns1:Name>
</ns1:District>
<ns1:Population measure="1000">533.8</ns1:Population>
<DistRank>
<PopulationRank>
<Position>4</Position>
</PopulationRank>
</DistRank>
</ns1:MessageLine>
</Message>
</DistrictMsg>') xtype
from dual)
select
xmlserialize(document
deletexml(xtype, '//ns1:MessageLine[ns1:DistRank/ns1:PopulationRank/ns1:Position[text()>5]]', 'xmlns:ns1="http://www.mynamespace.co.uk"')
as clob indent size = 2) up_to_five,
xmlserialize(document
deletexml(xtype, '//ns1:MessageLine[ns1:DistRank/ns1:PopulationRank/ns1:Position[text()<=5]]', 'xmlns:ns1="http://www.mynamespace.co.uk"')
as clob indent size = 2) greater_than_five
from xml_data;