Hello All,
Question is in continuation with the previous query Parsing an xml and storing the details into hierarchical tables where I oversimplified my scenario and hence, posting a new query. My XML Structure looks as:
<Root>
<ParentTagname="JobName"attrib1="Text"attrib2="SomeOtherText">
<ChildTagchildAttrib1="SomeValue1"childAttrib2="SomeValue2"/>
<ChildTagchildAttrib1="SomeValue3"childAttrib2="SomeValue4"/>
<ChildTagchildAttrib1="SomeValue5"childAttrib2="SomeValue6"/>
<ChildTag2 child2Attrib1="Child2SomeValue1" />
<ChildTag2 child2Attrib1="Child2SomeValue2" />
<ChildTag2 child2Attrib1="Child2SomeValue3" />
<ChildTag3 child3Attrib1="Child3SomeValue1" />
<ChildTag3 child3Attrib1="Child3SomeValue2" />
<ChildTag3 child3Attrib1="Child3SomeValue3" />
<OtherChildTagchildAttrib1="SomeValue1"childAttrib2="SomeValue2"/>
</ParentTag>
- <ParentTag name="JobName2" attrib1="TextX" attrib2="SomeOtherText">
- <ChildTag childAttrib1="SomeValue6" childAttrib2="SomeValue8" />
- <ChildTag childAttrib1="SomeValue7" childAttrib2="SomeValue9" />
- <OtherChildTag childAttrib1="SomeValue1" childAttrib2="SomeValue2" />
- </ParentTag>
</Root>
There will be Other Child Tags with attributes as well, however, to keep it simple, I am only extending the sample tag, that I think should cover my other scenarios. The query being used is doing a cartesian join between siblings [ChildTag, ChildTag2 and ChildTag3]. Is there a way to avoid this?
My current SQL is:
with t(xml) as (select xmltype('<Root>
<ParentTag name="JobName" attrib1="Text" attrib2="SomeOtherText">
<ChildTag childAttrib1="SomeValue1" childAttrib2="SomeValue2"/>
<ChildTag childAttrib1="SomeValue3" childAttrib2="SomeValue4"/>
<ChildTag childAttrib1="SomeValue5" childAttrib2="SomeValue6"/>
<ChildTag2 child2Attrib1="Child2SomeValue1" />
<ChildTag2 child2Attrib1="Child2SomeValue2" />
<ChildTag2 child2Attrib1="Child2SomeValue3" />
<ChildTag3 child3Attrib1="Child3SomeValue1" />
<ChildTag3 child3Attrib1="Child3SomeValue2" />
<ChildTag3 child3Attrib1="Child3SomeValue3" />
<OtherChildTag childAttrib1="SomeValue1" childAttrib2="SomeValue2"/>
</ParentTag>
<ParentTag name="JobName2" attrib1="TextX" attrib2="SomeOtherText">
<ChildTag childAttrib1="SomeValue6" childAttrib2="SomeValue8" />
<ChildTag childAttrib1="SomeValue7" childAttrib2="SomeValue9" />
<OtherChildTag childAttrib1="SomeValue1" childAttrib2="SomeValue2" />
</ParentTag>
</Root>') from dual)
--
-- end of test data
--
select x.p
,x.name, x.attrib1, x.attrib2
,y.c
,y.childattrib1, y.childattrib2
,z.child2Attrib1
,aa.child3Attrib1
from t
,xmltable('/Root/ParentTag'
passing t.xml
columns p for ordinality
,name varchar2(10) path './@name'
,attrib1 varchar2(10) path './@attrib1'
,attrib2 varchar2(10) path './@attrib2'
,children xmltype path '.'
,child2 xmltype path '.'
,child3 xmltype path '.'
) x
,xmltable('/ParentTag/ChildTag'
passing x.children
columns c for ordinality
,childattrib1 varchar2(20) path './@childAttrib1'
,childattrib2 varchar2(20) path './@childAttrib2'
,childattrib3 varchar2(20) path './@childAttrib3'
) y
,xmltable('/ParentTag/ChildTag2'
passing x.child2
columns
child2attrib1 varchar2(20) path './@child2Attrib1'
) z
,xmltable('/ParentTag/ChildTag3'
passing x.child3
columns
child3attrib1 varchar2(20) path './@child3Attrib1'
) aa;
Expected Result:
| NAME |
ATTRIB1 |
ATTRIB2 |
CHILDATTRIB1 |
CHILDATTRIB2 |
CHILDATTRIB3 |
CHILDATTRIB4 |
|
---------- ---------- ---------- -------------------- -------------------- -------------------- --------------------
| JobName |
Text |
SomeOtherText SomeValue1 |
SomeValue2 |
Child2SomeValue1 |
Child3SomeValue1 |
| JobName |
Text |
SomeOtherText SomeValue3 |
SomeValue4 |
Child2SomeValue2 |
Child3SomeValue2 |
| JobName |
Text |
SomeOtherText SomeValue5 |
SomeValue6 |
Child2SomeValue3 |
Child3SomeValue3 |
| JobName2 TextX |
SomeOtherText SomeValue6 |
SomeValue8 |
|
| JobName2 TextX |
SomeOtherText SomeValue7 |
SomeValue9 |
|
Oracle Version :- 11.2.0.4
Any help in this regard is highly appreciated.
Regards,
Purvesh Kulkarni.
Message was edited by: Purvesh K Added ParentTag with name="JobName2".