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