Skip to Main Content

SQL & PL/SQL

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!

xml to json

User_8Q7DSSep 8 2021 — edited Sep 8 2021

Hi,
i have below query. we are able to extract the header, but we 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"
    }
  ]
}
This post has been answered by cormaco on Sep 8 2021
Jump to Answer
Comments
Post Details
Added on Sep 8 2021
9 comments
202 views