Import XML Data to SQL Using DTS

[Marryp]

I have troubles importing XML to SQL.

I tried using OpenXML but I get an error this error: "XML parsing error: Invalid at the top level of the document"

What could probably cause the error?

Here's the sample XML:
--------------------
<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="

http://www.theIMO.com/docrefs/stylesheet/DispUnconsHOEP_HTML_t1-1.xsl"

?><IMODocument docID="DispUnconsHOEP" xmlns="

http://www.theIMO.com/schema"

xmlns:xsi="

http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="

http://www.theIMO.com/schema

http://www.theIMO.com/docrefs/schema/DispUnconsHOEP_r1.xsd">

<IMODocHeader>
<DocTitle>Hourly Ontario Energy Price (HOEP) Report</DocTitle>
<DocRevision>1</DocRevision>
<DocConfidentiality>
<DocConfClass>PUB</DocConfClass>
</DocConfidentiality>
<CreatedAt>2005-06-21T11:04:04</CreatedAt>
</IMODocHeader>
<IMODocBody>
<Date>2005-06-21</Date>
<HOEPs>
<HOEP>
<Hour>1</Hour>
<Price>29.36</Price>
<DataSource>DSO_RD</DataSource>
</HOEP>
<HOEP>
<Hour>2</Hour>
<Price>25.79</Price>
<DataSource>DSO_RD</DataSource>
</HOEP>
<HOEP>
<Hour>3</Hour>
<Price>9.85</Price>
<DataSource>DSO_RD</DataSource>
</HOEP>
<HOEP>
<Hour>4</Hour>
<Price>19.14</Price>
<DataSource>DSO_RD</DataSource>
</HOEP>
<HOEP>
<Hour>5</Hour>
<Price>15.13</Price>
<DataSource>DSO_RD</DataSource>
</HOEP>
<HOEP>
<Hour>6</Hour>
<Price>30.88</Price>
<DataSource>DSO_RD</DataSource>
</HOEP>
<HOEP>
<Hour>7</Hour>
<Price>40.41</Price>
<DataSource>DSO_RD</DataSource>
</HOEP>
<HOEP>
<Hour>8</Hour>
<Price>44.03</Price>
<DataSource>DSO_RD</DataSource>
</HOEP>
<HOEP>
<Hour>9</Hour>
<Price>41.55</Price>
<DataSource>DSO_RD</DataSource>
</HOEP>
<HOEP>
<Hour>10</Hour>
<Price>48.12</Price>
<DataSource>DSO_RD</DataSource>
</HOEP>
<HOEP>
<Hour>11</Hour>
<Price>60.24</Price>
<DataSource>DSO_RD</DataSource>
</HOEP>
</HOEPs>
</IMODocBody>
</IMODocument>


-------------
I use this SP code to parse it:

Declare @strXML varchar(8000)
DECLARE @iDoc int

Set @strXML = 'C:\IESO\HOEP.xml'

-- load XML document into memory
-- called to obtain a document handle that is then passed to the OPENXML statement

EXECUTE sp_xml_preparedocument @iDoc OUTPUT, @strXML

-- Insert row into table for each Transaction element on the XML file
INSERT INTO dbo.tmp_IESO_HOEP (HOEP_Date, HOEP_Hour,HOEP_Price, HOEP_Datasource)

-- process XML document with OPENXML statement
-- 2 indicates an element-centric mapping (instead of by attributes)
(SELECT * FROM OpenXML(@iDoc, 'IMODocument/IMODocHeader/IMODocBody', 2)
WITH (
[Date] Varchar(50) 'Date',
[Hour] Varchar(50) '/HOEPs/HOEP/Hour',
Price Varchar(53) '/HOEPs/HOEP/Price',
DataSource Varchar(50) '/HOEPs/HOEP/DataSource')
)
-- Clear memory of XML document
EXECUTE sp_xml_removedocument @iDoc