March 15, 2013 at 9:03 am
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?
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy