Importing XML file into SQL 2008R2...nearly there

• jmarkwesterman91

  Valued Member

  Points: 67

  More actions

  August 20, 2013 at 7:29 am

  #297125

  Hi,

  Trying to import a file with the structure:

  <PetrolPrices>

  <Fuel type="Super Unleaded">

  <Average units="p">145.83</Average>

  </Fuel>

  <Fuel type="Unleaded">

  <Average units="p">137.42</Average>

  </Fuel>

  <Fuel type="Premium Diesel">

  <Average units="p">151.63</Average>

  </Fuel>

  <Fuel type="Diesel">

  <Average units="p">141.64</Average>

  </Fuel>

  <Fuel type="LPG">

  <Average units="p">70.57</Average>

  </Fuel>

  </PetrolPrices>

  Using:

  select

  c3.value('Fuel[1]','Varchar(30)') as [Fuel Type],

  c3.value('Average[1]','decimal(18,3)') as [Average Units]

  from

  (

  select

  cast(c1 as xml)

  from

  OPENROWSET (BULK 'C:\FuelCosts\UK averages_20130819.xml',SINGLE_BLOB) as T1(c1)

  )as T2(c2)

  cross apply c2.nodes('/PetrolPrices/Fuel') T3(c3)

  Which gives me:

  Fuel TypeAverage Units

  NULL145.830

  NULL137.420

  NULL151.630

  NULL141.640

  NULL70.570

  Can someone help me out with what I am doing wrong for the Fuel Type please.

  many thanks

  Mark

• drew.allen

  SSC Guru

  Points: 77014

  More actions

  August 20, 2013 at 8:11 am

  #1643087

  Fuel type is stored in an attribute of the Fuel element, so you need to give the path to that attribute rather than referencing the element itself.

  c3.value('@type', 'varchar(30)') AS [Fuel Type]

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• jmarkwesterman91

  Valued Member

  Points: 67

  More actions

  August 20, 2013 at 8:18 am

  #1643091

  Thank you sir.

  You're a gent.

  Cheers

  Mark