Forum Stats

  • 3,825,053 Users
  • 2,260,461 Discussions
  • 7,896,399 Comments

Discussions

XMLTABLE - how to get given result?

User_ITSY5
User_ITSY5 Member Posts: 2 Green Ribbon
edited Dec 9, 2021 12:15AM in XML DB

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

  • cormaco
    cormaco Member Posts: 1,940 Silver Crown
    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

  • cormaco
    cormaco Member Posts: 1,940 Silver Crown

    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>      
    
    
  • cormaco
    cormaco Member Posts: 1,940 Silver Crown
    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>'
        )