Skip to Main Content
Forums

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

XMLTABLE - how to get given result?

User_ITSY5Dec 9 2021 — edited Dec 9 2021

Hi,
Let's assume I have following XML:
<root>
<header>
<header_attr1>attr1</header_attr1>
</header>
<item>
<item_attr1>attr11</item_attr1>
<item_attr2>attr21</item_attr2>
</item>
<item>
<item_attr1>attr12</item_attr1>
<item_attr2>attr22</item_attr2>
</item>
<item>
<item_attr1>attr13</item_attr1>
<item_attr2>attr23</item_attr2>
</item>
</root>

I need to write query that would use XMLTABLE function and get following result:
HEADER_ATTR1 | ITEM_ATTR1 | ITEM_ATTR2 | ITEM_XML (without <item_attr2)
----------------------------------------------------------------------------------------------
attr1 | attr11 | attr21 | <item><item_attr1>attr11</item_attr1></item>
attr1 | attr12 | attr22 | <item><item_attr1>attr12</item_attr1></item>
attr1 | attr13 | attr23 | <item><item_attr1>attr13</item_attr1></item>

How to do that?
This post has been answered by cormaco on Dec 9 2021
Jump to Answer
Comments
Post Details
Added on Dec 9 2021
#database, #performance-availability, #sql, #xml, #xml-db
2 comments
272 views