4 Replies Latest reply: May 27, 2014 8:04 AM by e51eb1d8-84ec-4c26-b00f-c57dddd26db9 RSS

    SQL To Parse SOAP Response into Columns

    e51eb1d8-84ec-4c26-b00f-c57dddd26db9

      Would someone be able to help me use SQL to parse this response into a table? Oracle 11g.

       

      <s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">

         <s:Body>

            <GetWaitTimesResponse xmlns="urn:Epic-com:Specialty.2010.Services.EmergencyDepartment">

               <GetWaitTimesResult xmlns:i="http://www.w3.org/2001/XMLSchema-instance">

                  <Events>

                     <Event>

                        <Description>Arrival to First Provider Contact</Description>

                        <EndID>3100050</EndID>

                        <EndName>ED PHYSICIAN EVALUATION START </EndName>

                        <StartID>50</StartID>

                        <StartName>ED ARRIVED</StartName>

                        <WaitTimes>

                           <WaitTime>

                              <PatientCount>0</PatientCount>

                              <TimeFrameInHours>1</TimeFrameInHours>

                              <WaitTimeInMinutes>0</WaitTimeInMinutes>

                           </WaitTime>

                           <WaitTime>

                              <PatientCount>0</PatientCount>

                              <TimeFrameInHours>2</TimeFrameInHours>

                              <WaitTimeInMinutes>0</WaitTimeInMinutes>

                           </WaitTime>

                           <WaitTime>

                              <PatientCount>0</PatientCount>

                              <TimeFrameInHours>3</TimeFrameInHours>

                              <WaitTimeInMinutes>0</WaitTimeInMinutes>

                           </WaitTime>

                        </WaitTimes>

                     </Event>

                     <Event>

                        <Description>Arrival to Roomed</Description>

                        <EndID>55</EndID>

                        <EndName>ED ROOMED</EndName>

                        <StartID>50</StartID>

                        <StartName>ED ARRIVED</StartName>

                        <WaitTimes>

                           <WaitTime>

                              <PatientCount>0</PatientCount>

                              <TimeFrameInHours>1</TimeFrameInHours>

                              <WaitTimeInMinutes>0</WaitTimeInMinutes>

                           </WaitTime>

                           <WaitTime>

                              <PatientCount>0</PatientCount>

                              <TimeFrameInHours>2</TimeFrameInHours>

                              <WaitTimeInMinutes>0</WaitTimeInMinutes>

                           </WaitTime>

                           <WaitTime>

                              <PatientCount>0</PatientCount>

                              <TimeFrameInHours>3</TimeFrameInHours>

                              <WaitTimeInMinutes>0</WaitTimeInMinutes>

                           </WaitTime>

                        </WaitTimes>

                     </Event>

                     <Event>

                        <Description>Arrival to Full Registration Complete</Description>

                        <EndID>3000202</EndID>

                        <EndName>ED FULL REGISTRATION COMPLETE </EndName>

                        <StartID>50</StartID>

                        <StartName>ED ARRIVED</StartName>

                        <WaitTimes>

                           <WaitTime>

                              <PatientCount>0</PatientCount>

                              <TimeFrameInHours>1</TimeFrameInHours>

                              <WaitTimeInMinutes>0</WaitTimeInMinutes>

                           </WaitTime>

                           <WaitTime>

                              <PatientCount>0</PatientCount>

                              <TimeFrameInHours>2</TimeFrameInHours>

                              <WaitTimeInMinutes>0</WaitTimeInMinutes>

                           </WaitTime>

                           <WaitTime>

                              <PatientCount>0</PatientCount>

                              <TimeFrameInHours>3</TimeFrameInHours>

                              <WaitTimeInMinutes>0</WaitTimeInMinutes>

                           </WaitTime>

                        </WaitTimes>

                     </Event>

                     <Event>

                        <Description>Roomed to Attending</Description>

                        <EndID>3000150</EndID>

                        <EndName>ED FIRST PROVIDER CONTACT </EndName>

                        <StartID>55</StartID>

                        <StartName>ED ROOMED</StartName>

                        <WaitTimes>

                           <WaitTime>

                              <PatientCount>0</PatientCount>

                              <TimeFrameInHours>1</TimeFrameInHours>

                              <WaitTimeInMinutes>0</WaitTimeInMinutes>

                           </WaitTime>

                           <WaitTime>

                              <PatientCount>0</PatientCount>

                              <TimeFrameInHours>2</TimeFrameInHours>

                              <WaitTimeInMinutes>0</WaitTimeInMinutes>

                           </WaitTime>

                           <WaitTime>

                              <PatientCount>0</PatientCount>

                              <TimeFrameInHours>3</TimeFrameInHours>

                              <WaitTimeInMinutes>0</WaitTimeInMinutes>

                           </WaitTime>

                        </WaitTimes>

                     </Event>

       

                  </Events>

                  <LastUpdatedAt>5/23/2014  1:35 PM</LastUpdatedAt>

               </GetWaitTimesResult>

            </GetWaitTimesResponse>

         </s:Body>

      </s:Envelope>

       

       

       

      I've been googling around and here's where I sit current - getting "PL/SQL: numeric or value error"

       

      select r.*

      from xml_table_test t

         , xmltable(

             xmlnamespaces(

               'http://schemas.xmlsoap.org/soap/envelope/' as "env"

             , 'urn:Epic-com:Specialty.2010.Services.EmergencyDepartment' as "ns1"

             )

           , '/env:Envelope/env:Body/ns1:GetWaitTimesResponse'

             passing t.xml_column_1

             columns xmlbody xmltype path 'GetWaitTimesResult/text()'

           ) x

         , xmltable(

             '/Events/Event'

             passing xmltype(dbms_xmlgen.convert(x.xmlbody.getclobval(), 1))

             columns Description varchar2(250) path 'Description'

       

           ) r

      ;

       

       

      Thanks for any help anyone can provide

        • 1. Re: SQL To Parse SOAP Response into Columns
          odie_63

          passing xmltype(dbms_xmlgen.convert(x.xmlbody.getclobval(), 1))

          What's the purpose of this part?

          You can use that when the soap envelope contains an XML payload in its escaped form (i.e. embedded in a single text node), but it doesn't appear so in your sample.

           

          How about :

          SQL> select x.*

            2  from xml_table_test t

            3     , xmltable(

            4         xmlnamespaces(

            5           'http://schemas.xmlsoap.org/soap/envelope/' as "env"

            6         , default 'urn:Epic-com:Specialty.2010.Services.EmergencyDepartment'

            7         )

            8       , '/env:Envelope/env:Body/GetWaitTimesResponse/GetWaitTimesResult/Events/Event'

            9         passing t.xml_column_1

          10         columns Description varchar2(250) path 'Description'

          11               , EndID       number        path 'EndID'

          12               , EndName     varchar2(250) path 'EndName'

          13               , StartID     number        path 'StartID'

          14               , StartName   varchar2(250) path 'StartName'

          15       ) x

          16  ;

           

          DESCRIPTION                                   ENDID ENDNAME                            STARTID STARTNAME

          ---------------------------------------- ---------- ------------------------------- ---------- ------------

          Arrival to First Provider Contact           3100050 ED PHYSICIAN EVALUATION START           50 ED ARRIVED

          Arrival to Roomed                                55 ED ROOMED                               50 ED ARRIVED

          Arrival to Full Registration Complete       3000202 ED FULL REGISTRATION COMPLETE           50 ED ARRIVED

          Roomed to Attending                         3000150 ED FIRST PROVIDER CONTACT               55 ED ROOMED

           

          ?

           

          You can then chain another XMLTABLE to further extract the WaitTime nodes. This will result in a flat format.

           

          How many target relational tables do you have to fill?

          Such an XML structure would require two tables. It's also possible to load them using a single multitable insert statement.

          • 2. Re: SQL To Parse SOAP Response into Columns
            e51eb1d8-84ec-4c26-b00f-c57dddd26db9

            Thanks this is very helpful. I can fill two tables with the results, but how would I generate the key that would relate the waittimes to the events?

            • 3. Re: SQL To Parse SOAP Response into Columns
              odie_63

              For example :

               

              create table event_table (

                event_id integer

              , description varchar2(256)

              , end_id       number

              , end_name     varchar2(30)

              , start_id     number

              , start_name   varchar2(30)

              );

               

              create table event_wt_table (

                event_wt_id integer

              , event_id    integer

              , patient_count number

              , time_frame_hrs number

              , wait_time_min  number

              );

               

              insert all

              when EventWtID = 1 then

                  into event_table (event_id, description, end_id, end_name, start_id, start_name)

                  values (EventID, Description, EndID, EndName, StartID, StartName)

              when 1 = 1 then

                  into event_wt_table (event_wt_id, event_id, patient_count, time_frame_hrs, wait_time_min)

                  values (EventWtID, EventID, PatientCount, TimeFrameInHours, WaitTimeInMinutes)

              select x1.EventID

                   , x1.Description

                   , x1.EndID

                   , x1.EndName

                   , x1.StartID

                   , x1.StartName

                   , x2.EventWtID

                   , x2.PatientCount

                   , x2.TimeFrameInHours

                   , x2.WaitTimeInMinutes

              from xml_table_test t

                 , xmltable(

                     xmlnamespaces(

                       'http://schemas.xmlsoap.org/soap/envelope/' as "env"

                     , default 'urn:Epic-com:Specialty.2010.Services.EmergencyDepartment'

                     )

                   , '/env:Envelope/env:Body/GetWaitTimesResponse/GetWaitTimesResult/Events/Event'

                     passing t.xml_column_1

                     columns EventID     for ordinality

                           , Description varchar2(256) path 'Description'

                           , EndID       number        path 'EndID'

                           , EndName     varchar2(30)  path 'EndName'

                           , StartID     number        path 'StartID'

                           , StartName   varchar2(30)  path 'StartName'

                           , WaitTimes   xmltype       path 'WaitTimes'

                   ) x1

                 , xmltable(

                     xmlnamespaces(

                       default 'urn:Epic-com:Specialty.2010.Services.EmergencyDepartment'

                     )

                   , '/WaitTimes/WaitTime'

                     passing x1.WaitTimes

                     columns EventWtID         for ordinality

                           , PatientCount      varchar2(250) path 'PatientCount'

                           , TimeFrameInHours  number        path 'TimeFrameInHours'

                           , WaitTimeInMinutes varchar2(250) path 'WaitTimeInMinutes'

                   ) x2

              ;

              • 4. Re: SQL To Parse SOAP Response into Columns
                e51eb1d8-84ec-4c26-b00f-c57dddd26db9

                This is perfect - thanks for all your help!