Create JSON from XML - JSON_AGG OUTPUT PROBLEM

Question

I have a problem with converting XML content to JSON format (with plain oracle select statement), where more then 1 sub level of data is present in the original XML - with my code the result of level 2+ is presented as string and not as JSON_OBJECT. Please, could someone tell me, where is fault in my code or what I'm doing wrong:

source:

<envelope>
  <sender>
    <name>IZS</name>
    <country>SU</country>
    <address>LOCATION 10B</address>
    <address>1000 CITY</address>
    <sender_identifier>SU46794093</sender_identifier>
    <sender_address>
      <sender_agent>SKWWSI20XXX</sender_agent>
      <sender_mailbox>SI56031098765414228</sender_mailbox>
    </sender_address>
  </sender>
</envelope>

transformation select statement:

WITH SAMPLE AS (SELECT XMLTYPE ('
<envelope>
  <sender>
    <name>IZS</name>
    <country>SU</country>
    <address>LOCATION 10B</address>
    <address>1000 CITY</address>
    <sender_identifier>SU46794093</sender_identifier>
    <sender_address>
      <sender_agent>SKWWSI20XXX</sender_agent>
      <sender_mailbox>SI56031098765414228</sender_mailbox>
    </sender_address>
  </sender>
</envelope>') XMLDOC FROM DUAL)
         SELECT JSON_SERIALIZE (
                    JSON_OBJECT (
                        KEY 'envelope' VALUE
                            JSON_OBJECTAGG (
                                KEY ID_LEVEL1 VALUE
                                    CASE ID_LEVEL1
                                        WHEN 'sender' THEN
                                            (      SELECT JSON_OBJECTAGG (
                                                              KEY ID_LEVEL2 VALUE
                                                                  CASE ID_LEVEL2
                                                                      WHEN 'sender_address' THEN
                                                                          (      SELECT JSON_OBJECTagg (KEY ID_LEVEL22 VALUE TEXT_LEVEL22)
                                                                                   FROM    XMLTABLE ('/sender/sender_address/*' 
                                                                                           PASSING  XML_LEVEL2 
                                                                                           COLUMNS  ID_LEVEL22 VARCHAR2 (128) PATH './name()', 
                                                                                                    TEXT_LEVEL22 VARCHAR2 (128) PATH './text()'
                                                                                                    )
                                                                          )
                                                                      ELSE
                                                                          TEXT_LEVEL2
                                                                  END)
                                                     FROM   XMLTABLE    ('/sender/*' 
                                                            PASSING     XML_LEVEL2 
                                                            COLUMNS     ID_LEVEL2 VARCHAR2 (1024) PATH './name()', 
                                                                        TEXT_LEVEL2 VARCHAR2 (1024) PATH './text()'
                                                                        )
                                            )
                                        ELSE
                                            '"' || TEXT_LEVEL1 || '"'
                                    END FORMAT JSON)
                                ) PRETTY
                               )JSON_DOC
           FROM SAMPLE, XMLTABLE ('/envelope/*' 
                        PASSING XMLDOC 
                        COLUMNS ID_LEVEL1 VARCHAR2 (1024) PATH './name()', 
                                TEXT_LEVEL1 VARCHAR2 (1024) PATH './text()', 
                                XML_LEVEL2 XMLTYPE PATH '.'
                                );

wrong result:

{
  "envelope" :
  {
    "sender" :
    {
      "name" : "IZS",
      "country" : "SU",
      "address" : "LOCATION 10B",
      "address" : "1000 CITY",
      "sender_identifier" : "SU46794093",
      "sender_address" : "{\"sender_agent\":\"SKWWSI20XXX\",\"sender_mailbox\":\"SI56031098765414228\"}"
    }
  }
}

wrong part:

      ***"sender_address" : "{\"sender_agent\":\"SKWWSI20XXX\",\"sender_mailbox\":\"SI56031098765414228\"}"***

Alex Poole · Accepted Answer · 2022-10-06 11:39:24Z

1

For the level 1 text you're wrapping the value in double-quotes and specifying format json; you aren't doing that for level 2. If you change:

ELSE
  TEXT_LEVEL2
END

to:

ELSE
  '"' || TEXT_LEVEL2 || '"'
END FORMAT JSON)

then the result is:

{
  "envelope" :
  {
    "sender" :
    {
      "name" : "IZS",
      "country" : "SU",
      "address" : "LOCATION 10B",
      "address" : "1000 CITY",
      "sender_identifier" : "SU46794093",
      "sender_address" :
      {
        "sender_agent" : "SKWWSI20XXX",
        "sender_mailbox" : "SI56031098765414228"
      }
    }
  }
}

fiddle

answered Oct 6, 2022 at 11:39

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

p3consulting · Accepted Answer · 2022-10-06 11:27:50Z

1

The problem is that you need kind of conditional "FORMAT JSON" in the "SELECT JSON_OBJECTAGG ( KEY ID_LEVEL2 VALUECASE ID_LEVEL2": when the ID_LEVEL2 is 'sender_address' but not in the ELSE part, but the syntax requires you put after the END of CASE, and of course this fails for the "ELSE TEXT_LEVEL2" part.

answered Oct 6, 2022 at 11:27

p3consulting

5,4312 gold badges14 silver badges15 bronze badges

Collectives™ on Stack Overflow

Create JSON from XML - JSON_AGG OUTPUT PROBLEM

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related