Database Software

Database: 11.1.0.6.0

Below is a sample of an XML file. I have this loaded as an xmltype column in a table called mytable1.

<?xml version="1.0" encoding="UTF-8"?>
<ApplicationProfile UniqueID="KWID_AP" xmlns="http://namespaces.globalplatform.org/systems-profiles/1.1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://namespaces.globalplatform.org/systems-profiles/1.1.0 GP.systems.profiles.1.1.0.ApplicationProfile.xsd" ProfileVersion="1.1.0">
	<Description>MyDescription</Description>
	<DataElement Name="AdminPinMinLength" External="true" Update="false" ReadWrite="false" FixedLength="true" Length="1" Type="ByteString" Encoding="HEX"/>
	<DataElement Name="AdminPinMaxLength" External="true" Update="false" ReadWrite="false" FixedLength="true" Length="1" Type="ByteString" Encoding="HEX"/>
</ApplicationProfile>

Now I can read Description quite easily using

SELECT extractvalue(xml_column, '/ApplicationProfile/Description','xmlns="http://namespaces.globalplatform.org/systems-profiles/1.1.0"')
  FROM mytable1;

However, where I am really struggling is reading the individual DataElements.
As I understand it, using extractvalue can only return a scalar (ie only one value can be returned).

If I use

  SELECT extractvalue(xml_column, 'ApplicationProfile/DataElement','xmlns="http://namespaces.globalplatform.org/systems-profiles/1.1.0"')
  FROM mytable1;

I get ORA19025. ExtractValue returns value of only one Node.

So, as I understand it, I need to specify which element number and value I need to get.

Below is one of the numerous examples I have tried which returns Null.

  SELECT extractvalue(xml_column, 'ApplicationProfile/DataElement/Name[1]','xmlns="http://namespaces.globalplatform.org/systems-profiles/1.1.0"')
  FROM mytable1;

How can I extract an individual array element, preferable using extractvalue but an example of using xquery would be useful too?



And to extend this a bit further, what I'm hoping to achieve is to be able to add/remove dataelements (along with their attributes).

eg I may want to add/modify values/or remove dataelements via sql.

	<DataElement Name="AdminPinValue" External="true" Update="false" ReadWrite="false" Type="ByteString" Encoding="ASCII"/>

to the above xml (within the DB).

Is this possible and if so how?

Added database version
Message was edited by:
Keith Jamieson