Home » Developer & Programmer » JDeveloper, Java & XML » Extracting table in XML in Oracle row to a flat table
Extracting table in XML in Oracle row to a flat table [message #334610] Thu, 17 July 2008 06:32
georgez.uy
Messages: 1
Registered: July 2008
Junior Member
I've got this table with an XML in a CLOB field.
This is an example of that XML.

<?xml version="1.0" encoding="UTF-8"?>
<ItemParameters type="VIPDR Dyn QOS H2">
		<Attribute name="Item ID">
			<Value value="79560698"/>
		</Attribute>
		<Attribute name="Minimum Charge">
			<Value value="0.0"/>
		</Attribute>
		<Attribute name="QoS Sensitivity">
			<Value value="I"/>
		</Attribute>
		<Attribute name="Rate quantity">
			<Value value="1">
				<DimensionKey dimensionName="Period" value="0"/>
				<DimensionKey dimensionName="Rated QoS" value="100"/>
			</Value>
			<Value value="1">
				<DimensionKey dimensionName="Period" value="0"/>
				<DimensionKey dimensionName="Rated QoS" value="200"/>
			</Value>
			<Value value="1">
				<DimensionKey dimensionName="Period" value="1"/>
				<DimensionKey dimensionName="Rated QoS" value="100"/>
			</Value>
			<Value value="1">
				<DimensionKey dimensionName="Period" value="1"/>
				<DimensionKey dimensionName="Rated QoS" value="200"/>
			</Value>
			<Value value="1">
				<DimensionKey dimensionName="Period" value="2"/>
				<DimensionKey dimensionName="Rated QoS" value="100"/>
			</Value>
			<Value value="1">
				<DimensionKey dimensionName="Period" value="2"/>
				<DimensionKey dimensionName="Rated QoS" value="200"/>
			</Value>
		</Attribute>
		<Attribute name="Rate rate">
			<Value value="0.62334">
				<DimensionKey dimensionName="Period" value="0"/>
				<DimensionKey dimensionName="Rated QoS" value="100"/>
			</Value>
			<Value value="0.62334">
				<DimensionKey dimensionName="Period" value="0"/>
				<DimensionKey dimensionName="Rated QoS" value="200"/>
			</Value>
			<Value value="0.62334">
				<DimensionKey dimensionName="Period" value="1"/>
				<DimensionKey dimensionName="Rated QoS" value="100"/>
			</Value>
			<Value value="0.62334">
				<DimensionKey dimensionName="Period" value="1"/>
				<DimensionKey dimensionName="Rated QoS" value="200"/>
			</Value>
			<Value value="0.62334">
				<DimensionKey dimensionName="Period" value="2"/>
				<DimensionKey dimensionName="Rated QoS" value="100"/>
			</Value>
			<Value value="0.62334">
				<DimensionKey dimensionName="Period" value="2"/>
				<DimensionKey dimensionName="Rated QoS" value="200"/>
			</Value>
		</Attribute>
	</ItemParameters>

The following query brings me most of the attributes in a flat format, but the values for attribute 'Rate quantity' and 'Rate rate' all come together, concatenated as a string in one field.
I need them to be put each in one record, with the other fields repeating at the beginning, or in a separate table, with a link thru the id field.
Any ideas?

SELECT 0 offer_id, RPAD(' ',20) offer_name, 0 pkg_id, rpad(' ', 40) pkg_name, x.id,
       x.xml.extract('//ItemParameters/@type').getStringVal() rate_type,
       x.xml.extract('//ItemParameters/Attribute[@name="QoS Sensitivity"]/Value/@value').getStringVal() qos_sensitivity,
       to_number(x.xml.extract('//ItemParameters/Attribute[@name="Minimum Charge"]/Value/@value').getStringVal()) min_charge, 
	   'Kb  ' AS "UOM", 
       x.xml.extract('//ItemParameters/Attribute[@name="Rate quantity"]/Value/@value').getStringVal() AS qty,
       x.xml.extract('//ItemParameters/Attribute[@name="Rate rate"]/Value/@value').getStringVal() AS rate,
       x.xml.extract('//ItemParameters/Attribute[@name="Rate rate"]/Value/DimensionKey[@dimensionName="Period"]/@value').getStringVal() period,
       x.xml.extract('//ItemParameters/Attribute[@name="Rate rate"]/Value/DimensionKey[@dimensionName="Rated QoS"]/@value').getStringVal() qos,
       0 as priority, RPAD(' ',9) soc
       FROM   (
       SELECT id,
              XMLTYPE(dynamic_parameters) AS xml
       FROM   item_parameters_xml x
       WHERE  version = (SELECT MAX(version) FROM item_parameters_xml)
       ) x
WHERE  x.xml.extract('//ItemParameters/@type').getStringVal() LIKE '%VIPDR Dyn QOS%' or
       x.xml.extract('//ItemParameters/@type').getStringVal() LIKE '%VIPDR WLAN%'
	   and rownum=1[/COLOR]


This is the source table, you can create a single row with a numeric id and put the xml above in the clob field
CREATE TABLE ITEM_PARAMETERS_XML
(
  ID                  NUMBER(9) CONSTRAINT IPXML_ID_NN NOT NULL,
  DYNAMIC_PARAMETERS  CLOB,
  VERSION             VARCHAR2(9 BYTE) CONSTRAINT IPXML_VERSION_NN NOT NULL,
  PROCESS_IND         CHAR(1 BYTE)
)
Previous Topic: XML page break
Next Topic: empty result
Goto Forum:
  


Current Time: Fri Dec 09 00:28:22 CST 2016

Total time taken to generate the page: 0.15819 seconds