Can't import Excel xml format

• aj 7234

  SSC Rookie

  Points: 29

  More actions

  March 15, 2013 at 9:03 am

  #271662

  I'm trying to select a Excel XML but my code doesn't work.

  The file i'm trying to import looks like this:

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

  <?xml version="1.0"?>

  <?mso-application progid="Excel.Sheet"?>

  <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"

  xmlns:o="urn:schemas-microsoft-com:office:office"

  xmlns:x="urn:schemas-microsoft-com:office:excel"

  xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"

  xmlns:html="http://www.w3.org/TR/REC-html40">

  <Worksheet ss:Name="view_0">

  <Table ss:ExpandedColumnCount="50" ss:ExpandedRowCount="1022" x:FullColumns="1"

  x:FullRows="1" ss:DefaultRowHeight="15">

  <Column ss:Index="26" ss:Width="219.75"/>

  <Column ss:Width="252"/>

  <Column ss:Index="38" ss:StyleID="s16" ss:AutoFitWidth="0" ss:Span="12"/>

  <Row>

  <Cell><Data ss:Type="String">1</Data></Cell>

  <Cell><Data ss:Type="String">Unit 1</Data></Cell>

  </Row>

  <Row>

  <Cell><Data ss:Type="String">2</Data></Cell>

  <Cell><Data ss:Type="String">Unit 2</Data></Cell>

  </Row>

  <Row>

  <Cell><Data ss:Type="String">3</Data></Cell>

  <Cell><Data ss:Type="String">Unit 3</Data></Cell>

  </Row>

  </Table>

  </Worksheet>

  </Workbook>

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

  My code looks like this

  select @xmlData = cast(c1 as xml)

  from

  OPENROWSET (BULK 'C:\Test2.xml',SINGLE_BLOB) as T1(c1)

  SELECT

  ref.value('Cell[1]', 'nvarchar(255)') AS UnitID,

  ref.value('Cell[2]', 'nvarchar(255)') AS UnitName

  FROM @xmlData.nodes('/Workbook/Worksheet/Table/Row')

  xmlData( ref )

  I works fine if I strip it down to this:

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

  <Workbook >

  <Worksheet>

  <Table>

  <Row>

  <Cell><Data>1</Data></Cell>

  <Cell><Data>Unit 1</Data></Cell>

  </Row>

  <Row>

  <Cell><Data>2</Data></Cell>

  <Cell><Data>Unit 2</Data></Cell>

  </Row>

  <Row>

  <Cell><Data>3</Data></Cell>

  <Cell><Data>Unit 3</Data></Cell>

  </Row>

  </Table>

  </Worksheet>

  </Workbook>

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

  Is there any way to get it to work or is it because Excel's XML format is invalid?