Oracle 包含嵌套XML元素的XML到JSON

嗨，

对于下面的查询，我能够提取头，但需要行数组在与json输出相同的sql中。

数据库版本： 12.2.0.1.0

SELECT JSON_OBJECT('hdr_id' VALUE hdr_id
          ,'prno' VALUE prno
          )
FROM  XMLTABLE(
     '/hdr'
     PASSING XMLTYPE('
             2238770
             64922
             2021-09-01
             in process
             
              
                2618885
                1
                Test1
                each
                400
                1
              
              
                2618886
                2
                Test2
                each
                555
                1
              
            
           ')
     COLUMNS 
      hdr_id  VARCHAR2(20) PATH 'hdr_id',
      prno  VARCHAR2(20) PATH 'prno'
    );

所需输出：

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

通过将XMLTable调用链接在一起，可以为每个嵌套行创建一行。

这看起来有点像：

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

一旦将每一行排成一行，您就可以使用JSON_rerageagg将它们组合回到每个标题行的一个文档中。

下面是一个示例，帮助您开始：

with header as (
  select *
  from  xmltable ( 
    '/hdr' passing xmltype ( '
         2238770
         64922
         2021-09-01
         in process
         
          
            2618885
            1
            Test1
            each
            400
            1
          
          
            2618886
            2
            Test2
            each
            555
            1
          
        
       '
  ) 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]   
*/