Hi Tom,
I am not an XML expert. I have a flexible attribute based XML stored into a table in XMLTYPE column TBTest.C1(XMLTYPE) which is a message from upstream queue. The XPATHs of message are like -
/*[name()='Message']
/*[name()='Message']/*[name()='Version']
/*[name()='Message']/*[name()='DateTime']
/*[name()='Message']/*[name()='Object']
/*[name()='Message']/*[name()='ObjectProperties']
/*[name()='Message']/*[name()='ObjectProperties']/*[name()='EventId']
/*[name()='Message']/*[name()='ObjectProperties']/*[name()='SourceSystem']
/*[name()='Message']/*[name()='ObjectProperties']/*[name()='EventType']
/*[name()='Message']/*[name()='AdditionalObjectProperties']
/*[name()='Message']/*[name()='AdditionalObjectProperties']/*[name()='ObjectElementValue']
/*[name()='Message']/*[name()='TestStatus']
/*[name()='Message']/*[name()='TestStatus']/*[name()='TestResult']
Sample data:
<Message xmlns="urn:message" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemalocation="urn:message message.xsd">
<Version>v2.0</Version>
<DateTime>2018-06-10T11:00:05Z</DateTime>
<Object>US Event</Object>
<ObjectProperties>
<EventId>0000121</EventId>
<SourceSystem>S1</SourceSystem>
<EventType>New</EventType>
</ObjectProperties>
<AdditionalObjectProperties type="GridTestCase1">
<ObjectElementValue name="TestGrid">42440</ObjectElementValue>
<ObjectElementValue name="AssetId">B1-42440</ObjectElementValue>
<ObjectElementValue name="GridType">B1</ObjectElementValue>
</AdditionalObjectProperties>
<AdditionalObjectProperties type="GridTestResult">
<ObjectElementValue name="EventBucket">New</ObjectElementValue>
</AdditionalObjectProperties>
<TestStatus ControlId="T1">
<TestResult TestId="BB-0001" ResultId="1121">Pass</TestResult>
<TestResult TestId="BB-0002" ResultId="1122">Fail</TestResult>
<TestResult TestId="BB-0003" ResultId="1123">NA</TestResult>
</TestStatus>
<TestStatus ControlId="T2">
<TestResult TestId="BT-0001" ResultId="1501">Fail</TestResult>
<TestResult TestId="BT-0002" ResultId="1502">Fail</TestResult>
<TestResult TestId="BT-0003" ResultId="1503">Fail</TestResult>
</TestStatus>
</Message>
I wish to extract this data into tabular form. I have been trying to use the XMLTABLE operator to get the data but couldn't succeed.
How can I achieve this, any quick SQL to solve this?
Thanks!
You can use XMLTable to do this. Here's few principles:
The XML has the namespace xmlns="urn:message". So you'll want to include:
xmlnamespaces ( default 'urn:message' )
At the start of all your XMLTable calls. So to get the values for elements, do:
select tests.*
from t, xmltable (
xmlnamespaces ( default 'urn:message' ),
'/' passing t.x
columns
DateTime path '/Message/DateTime',
event_id path '/Message/ObjectProperties/EventId'
) tests;
DATETIME EVENT_ID
2018-06-10T11:00:05Z 0000121 You have multiple elements with the same name. To extract these into separate rows, chain together XMLTable calls. Do this by defining the repeated element as an XMLType column. Then to turn repeated elements into rows, use the double slash prefix in the passing clause of the next XMLTable call:
select event_id, datetime, control_id
from t, xmltable (
xmlnamespaces ( default 'urn:message' ),
'/' passing t.x
columns
DateTime path '/Message/DateTime',
event_id path '/Message/ObjectProperties/EventId',
test_status xmltype path '/Message/TestStatus'
) tests, xmltable (
xmlnamespaces ( default 'urn:message' ),
'//TestStatus' passing tests.test_status
columns
control_id path '@ControlId'
) results;
EVENT_ID DATETIME CONTROL_ID
0000121 2018-06-10T11:00:05Z T1
0000121 2018-06-10T11:00:05Z T2 The @ prefix allows you access attribute values.
From here, keep chaining the calls as needed:
select event_id, datetime, control_id, test_id, res
from t, xmltable (
xmlnamespaces ( default 'urn:message' ),
'/' passing t.x
columns
DateTime path '/Message/DateTime',
event_id path '/Message/ObjectProperties/EventId',
test_status xmltype path '/Message/TestStatus'
) tests, xmltable (
xmlnamespaces ( default 'urn:message' ),
'//TestStatus' passing tests.test_status
columns
test_result xmltype path '/TestStatus/TestResult',
control_id path '@ControlId'
) results, xmltable (
xmlnamespaces ( default 'urn:message' ),
'//TestResult' passing results.test_result
columns
test_id path '@TestId',
res path 'text()'
);
EVENT_ID DATETIME CONTROL_ID TEST_ID RES
0000121 2018-06-10T11:00:05Z T1 BB-0001 Pass
0000121 2018-06-10T11:00:05Z T1 BB-0002 Fail
0000121 2018-06-10T11:00:05Z T1 BB-0003 NA
0000121 2018-06-10T11:00:05Z T2 BT-0001 Fail
0000121 2018-06-10T11:00:05Z T2 BT-0002 Fail
0000121 2018-06-10T11:00:05Z T2 BT-0003 Fail Table create and populate script:
create table t (
x xmltype
);
insert into t values (xmltype ('<Message xmlns="urn:message" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemalocation="urn:message message.xsd">
<Version>v2.0</Version>
<DateTime>2018-06-10T11:00:05Z</DateTime>
<Object>US Event</Object>
<ObjectProperties>
<EventId>0000121</EventId>
<SourceSystem>S1</SourceSystem>
<EventType>New</EventType>
</ObjectProperties>
<AdditionalObjectProperties type="GridTestCase1">
<ObjectElementValue name="TestGrid">42440</ObjectElementValue>
<ObjectElementValue name="AssetId">B1-42440</ObjectElementValue>
<ObjectElementValue name="GridType">B1</ObjectElementValue>
</AdditionalObjectProperties>
<AdditionalObjectProperties type="GridTestResult">
<ObjectElementValue name="EventBucket">New</ObjectElementValue>
</AdditionalObjectProperties>
<TestStatus ControlId="T1">
<TestResult TestId="BB-0001" ResultId="1121">Pass</TestResult>
<TestResult TestId="BB-0002" ResultId="1122">Fail</TestResult>
<TestResult TestId="BB-0003" ResultId="1123">NA</TestResult>
</TestStatus>
<TestStatus ControlId="T2">
<TestResult TestId="BT-0001" ResultId="1501">Fail</TestResult>
<TestResult TestId="BT-0002" ResultId="1502">Fail</TestResult>
<TestResult TestId="BT-0003" ResultId="1503">Fail</TestResult>
</TestStatus>
</Message>'));