Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 63 Insurance
- 536.4K On-Premises Infrastructure
- 138.3K Analytics Software
- 38.6K Application Development Software
- 5.8K Cloud Platform
- 109.5K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71.1K Infrastructure Software
- 105.3K Integration
- 41.6K Security Software
XMLTABLE - how to get given result?

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?
Best Answer
-
In newer Oracle versions the query can be simplified like this:
select header_attr1, item_attr1, item_attr2, xmlserialize(document item_xml as varchar2(200) no indent) as item_xml from example, xmltable( '/root/item' passing xmldata columns header_attr1 varchar2(10) path './../header/header_attr1', item_attr1 varchar2(10) path 'item_attr1', item_attr2 varchar2(10) path 'item_attr2', item_xml xmltype path 'for $i in item_attr1 return <item>{$i}</item>' )
Answers
-
Here is one way, you can leave out the call to XMLSERIALIZE, this is only for better display of the xml fragment.
with example(xmldata) as ( select xmltype( '<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>') from dual) select header_attr1, item_attr1, item_attr2, xmlserialize(document item_xml as varchar2(50) no indent) as item_xml from example, xmltable( '/root' passing xmldata columns header_attr1 varchar2(10) path 'header/header_attr1', items xmltype path 'item' ), xmltable( 'item' passing items columns item_attr1 varchar2(10) path 'item_attr1', item_attr2 varchar2(10) path 'item_attr2', item_xml xmltype path 'for $i in item_attr1 return <item>{$i}</item>' ) HEADER_ATTR1 ITEM_ATTR1 ITEM_ATTR2 ITEM_XML ------------ ---------- ---------- -------------------------------------------------- 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>
-
In newer Oracle versions the query can be simplified like this:
select header_attr1, item_attr1, item_attr2, xmlserialize(document item_xml as varchar2(200) no indent) as item_xml from example, xmltable( '/root/item' passing xmldata columns header_attr1 varchar2(10) path './../header/header_attr1', item_attr1 varchar2(10) path 'item_attr1', item_attr2 varchar2(10) path 'item_attr2', item_xml xmltype path 'for $i in item_attr1 return <item>{$i}</item>' )