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)
)
] 
Blog
Search
Help
Members
Register
Login
Home
