Tech Off Post

Single Post Permalink

View Thread: Tough SQL Server XML Challenge
  • User profile image
    rmessier

    Okay, I have a particulary ugly block of xml that I need to parse out and stick into sql server.
    The challenge is getting this thing into the correct format since it has both element and attribute
    peppered in it. I was just going to use a dataset and read the xml into it. But there are some problems
    with that because the repeating <geo_co> tag in mail and residence address blocks gum up the works.
    Anyone point me in the right direction? I have started to sort it out, but the voting regions return a
    very strange row, this one has me baffled, I spent the better part of a day futzing with it. Any help would
    be appreciated...


    ------------[ Cut Here ]--------------------
    BEGIN
    DECLARE @IDOC INT
    DECLARE @DOC VARCHAR(4000)

    SET @doc = '<?xml version="1.0" encoding="UTF-8"?>
     <boe_vr_init>
     <tran_id>10703111500002423</tran_id>
     <boe_server_id>192.168.1.1</boe_server_id>
     <boe_voter_id>234123523</boe_voter_id>
     <voter>
     <lastname>Gravenstein</lastname>
     <firstname>Rumpi</firstname>
     <middlename>Ohiostate</middlename>
     <name_suffix>Jr.</name_suffix>
     <birth_da>1967-08-13</birth_da>
     <registration_da>2001-08-13</registration_da>
     <registration_type>MAILIN</registration_type>
     <voter_status>ACTIVE</voter_status>
     <private_address_fl>N</private_address_fl>
     <bmv_id>AD132455</bmv_id>
     <ssn>300994430</ssn>
     <ssn4>4430</ssn4>
     <phone>(000) 000-0000</phone>
     <birth_city>Akron</birth_city>
     <birth_state>Ohio</birth_state>
     <birth_country>USA</birth_country>
     </voter>
     <party_affiliation>R</party_affiliation>
     <residence_address>
     <house_number>2020</house_number>
     <secondary_indicator/>
     <street_name>Front</street_name>
     <street_description>ST</street_description>
     <pre_street_direction/>
     <post_street_direction/>
     <secondary_address>Falls Towne Center, Suite 104-8</secondary_address>
     <city>Cuyahoga Falls</city>
     <state>OHIO</state>
     <zip_co>44221</zip_co>
     <geo_co>
     <latitude>3.1415926535897932384626433832795</latitude>
     <longitude>3.1415926535897932384626433832795</longitude>
     </geo_co>
     </residence_address>
     <mail_address>
     <street_name>MAIN</street_name>
     <city>AKRON</city>
     <state>OHIO</state>
     <zip_co>44221</zip_co>
     <geo_co>
     <latitude>3.1415926535897932384626433832795</latitude>
     <longitude>3.1415926535897932384626433832795</longitude>
     </geo_co>
     </mail_address>
     <voting_regions>
     <district_code district_type="CONGDIST">23</district_code>
     <district_code district_type="STATHOUS">34</district_code>
     <district_code district_type="STATSENA">12</district_code>
     </voting_regions>
     <edit_flag>Y</edit_flag>
     </boe_vr_init>
    '


     EXEC sp_xml_preparedocument @IDOC OUTPUT, @DOC
     SELECT *
      FROM OPENXML(@idoc,'/')
      WITH (
        tran_id   varchar(020) '/boe_vr_init'
       ,district_code varchar(20)  '/boe_vr_init/voting_regions'
       ,district_type varchar(20)  '/boe_vr_init/voting_regions/district_type'
       ,latitude  varchar(20)  '/boe_vr_init/mail_address/geo_co'
       ,longitude  varchar(20)  '/boe_vr_init/mail_address/geo_co'
      )
     EXEC sp_xml_removedocument @IDOC
     
    END
    GO