Reading XML into a DB

• rsaxer@cox.net

  SSC Veteran

  Points: 213

  More actions

  January 31, 2006 at 10:12 am

  #167289

  I am new to using XML. I have been given the following structure and

  cannot figure out how to read the elements. I can get the edge table

  but not the data in the format I am looking for. I believe there

  should be 4 elements:

   service_request_num, activity_code, outcome_code, details

  I've been beating my head against the wall and the flat spots are

  starting to show!

  Can someone help?

  TIA,

  Ron

  DECLARE @idoc int

  DECLARE @doc varchar(8000)

  SET @doc =

  '<?xml version="1.0" encoding="UTF-8"?>

  <aaa:aaa-msg

      xmlns:aaa="http://www.company.com/Schema/aaa"

      xmlns:bbb="http://www.company.com/Schema/bbb">

   

   <aaa:header>

           <aaa:eid/>

           <aaa:reference_id>1234</aaa:reference_id>

           <aaa:event_date>JAN 10, 2003 02:07:21

  PM</aaa:event_date>

           <aaa:sr_type_code>DIRTYWAT</aaa:sr_type_code>

           <aaa:event_type_code>REQUEST</aaa:event_type_code>

           <aaa:eai_event_code>XAPPSR</aaa:eai_event_code>

           <aaa:source_code>SIDEWALK</aaa:source_code>

           <aaa:target_code>PRIMAPP</aaa:target_code>

           <aaa:status_code />

           <aaa:details/>

   </aaa:header>

   

   <aaa:body>

    <bbb:document>

     <bbb:header>

      <bbb:system>bbb</bbb:system>

      <bbb:doc_name>NA</bbb:doc_name>

      <bbb:version>3.9.0</bbb:version>

      <bbb:dml_event/>

      <bbb:comment/>

     </bbb:header>

   

     <bbb:service_request>

      <bbb:service_request_num>

   <bbb:value>03-00002269</bbb:value>

      </bbb:service_request_num>

                                  <bbb:sr_activities>

                                          <bbb:sr_activity>

                                                  <bbb:activity_code>

   <bbb:value>ACTIVIT3</bbb:value>

                                                  </bbb:activity_code>

                                                  <bbb:outcome_code>

   <bbb:value>LINKSR</bbb:value>

                                                  </bbb:outcome_code>

                                                  <bbb:details>

   <bbb:value>Details go here</bbb:value>

                                                  </bbb:details>

                                          </bbb:sr_activity>

                                  </bbb:sr_activities>

     </bbb:service_request>

    </bbb:document>

   </aaa:body>

  </aaa:aaa-msg>'

  EXEC sp_xml_preparedocument @idoc OUTPUT, @doc  , '<aaa

  xmlns:bbb="'">http://www.company.com/Schema/bbb"/>'

  SELECT    *

  FROM       OPENXML (@idoc, '/',2)

• Ray M

  SSC-Insane

  Points: 21093

  More actions

  January 31, 2006 at 12:21 pm

  #617991

  Here Ya Go

  DECLARE @idoc int

  DECLARE @doc varchar(8000)

  SET @doc =

  '<?xml version="1.0" encoding="UTF-8"?>

  <aaa:aaa-msg

      xmlns:aaa="http://www.company.com/Schema/aaa"

      xmlns:bbb="http://www.company.com/Schema/bbb">

   

   <aaa:header>

           <aaa:eid/>

           <aaa:reference_id>1234</aaa:reference_id>

           <aaa:event_date>JAN 10, 2003 02:07:21

  PM</aaa:event_date>

           <aaa:sr_type_code>DIRTYWAT</aaa:sr_type_code>

           <aaa:event_type_code>REQUEST</aaa:event_type_code>

           <aaa:eai_event_code>XAPPSR</aaa:eai_event_code>

           <aaa:source_code>SIDEWALK</aaa:source_code>

           <aaa:target_code>PRIMAPP</aaa:target_code>

           <aaa:status_code />

           <aaa:details/>

   </aaa:header>

   

   <aaa:body>

    <bbb:document>

     <bbb:header>

      <bbb:system>bbb</bbb:system>

      <bbb:doc_name>NA</bbb:doc_name>

      <bbb:version>3.9.0</bbb:version>

      <bbb:dml_event/>

      <bbb:comment/>

     </bbb:header>

   

     <bbb:service_request>

      <bbb:service_request_num>

    <bbb:value>03-00002269</bbb:value>

      </bbb:service_request_num>

      <bbb:sr_activities>

    <bbb:sr_activity>

     <bbb:activity_code>

      <bbb:value>ACTIVIT3</bbb:value>

     </bbb:activity_code>

     <bbb:outcome_code>

      <bbb:value>LINKSR</bbb:value>

     </bbb:outcome_code>

              <bbb:details>

      <bbb:value>Details go here</bbb:value>

              </bbb:details>

          </bbb:sr_activity>

      </bbb:sr_activities>

     </bbb:service_request>

    </bbb:document>

   </aaa:body>

  </aaa:aaa-msg>'

  EXEC sp_xml_preparedocument @idoc OUTPUT, @doc  , '<aaa

  xmlns:bbb="'">http://www.company.com/Schema/bbb"/>'

  SELECT    *

  FROM       OPENXML (@idoc, '/',2)

               WITH (service_request_num varchar(12) '//bbb:service_request/bbb:service_request_num/bbb:value'

  ,                  activity_code varchar(10) '//bbb:sr_activities/bbb:sr_activity/bbb:activity_code/bbb:value'

  ,                  outcome_code varchar(10) '//bbb:sr_activities/bbb:sr_activity/bbb:outcome_code/bbb:value'

  ,                  details varchar(50) '//bbb:sr_activities/bbb:sr_activity/bbb:details/bbb:value'

                     )

  EXEC sp_xml_removedocument @idoc

   

• rsaxer@cox.net

  SSC Veteran

  Points: 213

  More actions

  January 31, 2006 at 3:58 pm

  #618025

  Thanks Ray,

  Worked like a charm!