Skip to Main Content
  • Questions
  • XML to JSON with nested XML elements

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: September 08, 2021 - 9:42 am UTC

Last updated: September 08, 2021 - 10:33 am UTC

Version: 12.2.0.1.0

Viewed 100+ times

You Asked

Hi,

For below query, i am able to extract the header, but need the lines array in the same sql as json output.

DB Version: 12.2.0.1.0

SELECT JSON_OBJECT('hdr_id' VALUE hdr_id
          ,'prno' VALUE prno
          )
FROM  XMLTABLE(
     '/hdr'
     PASSING XMLTYPE('<hdr>
             <hdr_id>2238770</hdr_id>
             <prno>64922</prno>
             <creation_date>2021-09-01</creation_date>
             <status>in process</status>
             <lines>
              <line>
                <line_id>2618885</line_id>
                <line_num>1</line_num>
                <item_description>Test1</item_description>
                <uom>each</uom>
                <unit_price>400</unit_price>
                <quantity>1</quantity>
              </line>
              <line>
                <line_id>2618886</line_id>
                <line_num>2</line_num>
                <item_description>Test2</item_description>
                <uom>each</uom>
                <unit_price>555</unit_price>
                <quantity>1</quantity>
              </line>
            </lines>
           </hdr>')
     COLUMNS 
      hdr_id  VARCHAR2(20) PATH 'hdr_id',
      prno  VARCHAR2(20) PATH 'prno'
    );


Desired Output:

{
    "hdr_id": "2238770",
    "prno": "64922",
    "lines": [
        {
            "line_num": 1,
            "item_description": "Test1"
        },
        {
            "line_num": 2,
            "item_description": "Test2"
        }
    ]
}

and we said...

You can create a row for each nested line by chaining together XMLTable calls.

This looks something like:

with header as (
  select *
  from  xmltable ( 
    '...' passing xmltype ( ..  ) 
    columns 
      ...
      nested_element xmltype path '...'
  )
)
  select *
  from   header h
  left join xmltable (
    '...' passing h.nested_element 
    columns 
      ...
  ) xt
  on  1 = 1


Once you've each line into a row, you can use JSON_arrayagg to combine them back into one document for each header row.

Here's an example to get you started:

with header as (
  select *
  from  xmltable ( 
    '/hdr' passing xmltype ( '<hdr>
         <hdr_id>2238770</hdr_id>
         <prno>64922</prno>
         <creation_date>2021-09-01</creation_date>
         <status>in process</status>
         <lines>
          <line>
            <line_id>2618885</line_id>
            <line_num>1</line_num>
            <item_description>Test1</item_description>
            <uom>each</uom>
            <unit_price>400</unit_price>
            <quantity>1</quantity>
          </line>
          <line>
            <line_id>2618886</line_id>
            <line_num>2</line_num>
            <item_description>Test2</item_description>
            <uom>each</uom>
            <unit_price>555</unit_price>
            <quantity>1</quantity>
          </line>
        </lines>
       </hdr>'
  ) columns 
    hdr_id  varchar2(20) path 'hdr_id',
    prno    varchar2(20) path 'prno', 
    lines   xmltype path 'lines'
  )
)
  select hdr_id, prno, json_arrayagg ( line_id )
  from   header h
  left join xmltable (
    '/lines/line' passing h.lines
    columns 
      line_id int path 'line_id'
  ) xt
  on  1 = 1
  group  by hdr_id, prno;
/*
HDR_ID     PRNO     JSON_ARRAYAGG(LINE_ID)   
2238770    64922    [2618885,2618886]   
*/

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.