7 Replies Latest reply on Sep 21, 2018 5:26 AM by cormaco

    convert xml to relational data.

    M Prasad-Oracle

      I have below xml and wanted to convert it in relational data.

      <?xml version='1.0' encoding='UTF-8' standalone='yes'?><methodResponse><params><param><value><array><data><value><struct><member><name>id</name><value><string>4</string></value></member><member><name>network_id</name><value><string>Management</string></value></member><member><name>port</name><value><string>1514</string></value></member><member><name>enabled</name><value><string>true</string></value></member></struct></value><value><struct><member><name>id</name><value><string>5</string></value></member><member><name>network_id</name><value><string>Management</string></value></member><member><name>port</name><value><string>1415</string></value></member><member><name>enabled</name><value><string>false</string></value></member></struct></value></data></array></value></param></params></methodResponse>

       

       

      Result should as below.

       

       

      ID NETWORK_ID PORT ENABLED

      ================================

      4 Management 1514 true

      5 Management 1415 false

       

       

      Please provide pointer.

        • 2. Re: convert xml to relational data.
          mNem
          with t (xmldoc) as (
          select xmltype(
          '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
          <methodResponse>
              <params>
                  <param>
                      <value>
                          <array>
                              <data>
                                  <value>
                                      <struct>
                                          <member>
                                              <name>id</name>
                                              <value>
                                                  <string>4</string>
                                              </value>
                                          </member>
                                          <member>
                                              <name>network_id</name>
                                              <value>
                                                  <string>Management</string>
                                              </value>
                                          </member>
                                          <member>
                                              <name>port</name>
                                              <value>
                                                  <string>1514</string>
                                              </value>
                                          </member>
                                          <member>
                                              <name>enabled</name>
                                              <value>
                                                  <string>true</string>
                                              </value>
                                          </member>
                                      </struct>
                                  </value>
                                  <value>
                                      <struct>
                                          <member>
                                              <name>id</name>
                                              <value>
                                                  <string>5</string>
                                              </value>
                                          </member>
                                          <member>
                                              <name>network_id</name>
                                              <value>
                                                  <string>Management</string>
                                              </value>
                                          </member>
                                          <member>
                                              <name>port</name>
                                              <value>
                                                  <string>1415</string>
                                              </value>
                                          </member>
                                          <member>
                                              <name>enabled</name>
                                              <value>
                                                  <string>false</string>
                                              </value>
                                          </member>
                                      </struct>
                                  </value>
                              </data>
                          </array>
                      </value>
                  </param>
              </params>
          </methodResponse>') from dual
          )
          select x.* from t, xmltable (
                                '/methodResponse/params/param/value/array/data/value'
                                passing t.xmldoc
                                columns
                                  id          varchar2(20) path 'struct/member[name[.="id"]]/value/string'
                                  ,network_id varchar2(20) path 'struct/member[name[.="network_id"]]/value/string'
                                  ,port       varchar2(20) path 'struct/member[name[.="port"]]/value/string'
                                  ,enabled    varchar2(20) path 'struct/member[name[.="enabled"]]/value/string'
                            ) x
          ;
          ID                   NETWORK_ID           PORT                 ENABLED            
          -------------------- -------------------- -------------------- --------------------
          4                    Management           1514                 true                 
          5                    Management           1415                 false
          
          • 3. Re: convert xml to relational data.
            cormaco

            A bit overcomplicated, this works as well:

             

            SELECT x.* FROM T, XMLTABLE (  
                                  '/methodResponse/params/param/value/array/data/value'  
                                  PASSING t.xmldoc  
                                  COLUMNS  
                                    ID          varchar2(20) PATH 'struct/member[name="id"]/value/string'  
                                    ,network_id varchar2(20) PATH 'struct/member[name="network_id"]/value/string'  
                                    ,port       varchar2(20) PATH 'struct/member[name="port"]/value/string'  
                                    ,enabled    varchar2(20) PATH 'struct/member[name="enabled"]/value/string'  
                              ) x  
            
            1 person found this helpful
            • 4. Re: convert xml to relational data.
              mNem

              Thanks.

              • 5. Re: convert xml to relational data.
                M Prasad-Oracle

                Thanks mNem. It worked.

                • 6. Re: convert xml to relational data.
                  M Prasad-Oracle

                  Thank you so much Cormaco, Your answer also gives correct result. But I can mark only one answer as correct. Please let me know if there is way to mark both the answers as correct.

                   

                  Thanks

                   

                  Re: convert xml to relational data.
                  • 7. Re: convert xml to relational data.
                    cormaco

                    Never mind, this is quite alright to me.