table from file xml

• antonela

  Ten Centuries

  Points: 1007

  More actions

  February 13, 2015 at 4:38 am

  #147196

  Hello,

  I have a xml file (myfile.xml):

  " <dataSegment>

  <cube xmlns="http://www.a.it/" id="AM2_C0000102" lastObsId="0">

  <obs>

  <dim name="DATA_CONTABILE" value="20140331" />

  <dim name="ENTE_SEGN" value="E02" />

  <dim name="C_PROF" value="8" />

  <dim name="MISURA" value="4099" />

  <obs>

  </cube>

  <cube xmlns="http://www.a.it/" id="AM2_C0000102" lastObsId="0">

  <obs>

  <dim name="DATA_CONTABILE" value="20140331" />

  <dim name="ENTE_SEGN" value="E02" />

  <dim name="C_PROF" value="6" />

  <dim name="MISURA" value="54862" />

  </obs>

  </cube>

  </dataSegment>"

  I need to create a table with 4 fields (DATA_CONTABILE,ENTE_SEGN,C_PROF,MISURA) and 2 records like this:

  20140331E0284099

  20140331E02654862

  I imported it in sql server

  SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() as data

  into table

  FROM OPENROWSET(BULK 'c:\myfile.xml', SINGLE_BLOB) AS x

  I don't know how to catch the name and value from the dim tag.

  Anybody could help me?Thanks