loaidng xml file into table

hi team,

i want load following xml file. please give suggestion on this

  <?xml version="1.0" ?> 

- <enrolment date="2011-02-10t21:45:00.3811504z" xmlns:fx="http://schemas.flexfinancialsolutions.com/enrolment">

  <account>7862</account> 

- <tpa>

  <name>aceg services</name> 

  <affinity>the insurance services</affinity> 

  </tpa>

- <product>

  <name>g6103 a&d</name> 

- <plan>

  <name>default plan</name> 

  <policynumber>g6103</policynumber> 

  </plan>

  </product>

- <field>

  <name>producturl</name> 

  <value>http://www.google.com</value> 

  </field>

- <field>

  <name>first name</name> 

  <value>peter</value> 

  </field>

- <field>

  <name>middle initial</name> 

  <value>p</value> 

  </field>

- <field>

  <name>last name</name> 

  <value>smith</value> 

  </field>

- <field>

  <name>member id</name> 

  <value>55555</value> 

  </field>

- <field>

  <name>address</name> 

  <value>555 street</value> 

  </field>

- <field>

  <name>city</name> 

  <value>any city</value> 

  </field>

- <field>

  <name>state</name> 

  <value>nj</value> 

  </field>

- <field>

  <name>zip</name> 

  <value>01888</value> 

  </field>

</enrolment>

thanks,

eshwar

 

use tempdb
create table t (intcol int, xmlcol xml)
go

insert t(xmlcol)
select * openrowset(
   bulk 'c:\temp\test.xml',
   single_blob) x
go

select * t
go

 

i think work....!

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

if post useful , can able solve problem please mark post answered..

 

SQL Server  >  SQL Server Integration Services