Accessing XML document FROM T-SQL

Getting ERROR:

Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Line 5
XML parsing error: Invalid at the top level of the document.

Here is what i executed:

declare @doc varchar(250)
select @doc='G:\emp.XML'

declare @testdoc int
exec sp_xml_preparedocument @testdoc OUTPUT, @doc

select * from
openxml(@testdoc,N'/emp/employee')
WITH(
name varchar(20) './name',
phone varchar(20) './phone',
email varchar(20) './email'
)

exec sp_xml_removedocument @testdoc


emp.XML :

<emp>
      <employee>
            <name> Margus Lehiste </name>
            <phone> 718-333-3333 </phone>
            <email>margusL@mpow.com</email>
      </employee>
      <employee>
            <name>Nelson</name>
            <phone>409-3777</phone>
            <email/>
      </employee>
      <employee>
            <name>Judy</name>
            <phone></phone>
            <email>JudyP@mpow.com</email>
      </employee>
</emp>

Unfortunately you cannot use a file path in the xmltext paramenter, it must be the Xml document itself. From BOL

<quote>
Is the original XML document. The MSXML parser parses this XML document.  xmltext is a text (char, nchar, varchar, nvarchar, text, or ntext) parameter. The default value is NULL, in which case an internal representation of an empty XML document is created.
</quote>

so how would i be able to accomplish what i want - have an XML document and retrieve a SQL result ?

Reading the Xml document and passing it into SQL Server is best accomplished using a front end language like VB or C#.  That is not to say it cannot be done, it is just not a good idea.

So do you want to make this a T-SQL only solution, even though it is not recommended?

Yes - I would like to make it T-SQL only solution
(it's for theoretical reasons only - i do not use XML @ development - but I teach SQL server design and it would be xtra cool if you could read XML documents straight from T-SQL)

You could bcp the xml into a single field temporary table using '</emp>' as the row delimiter - this would give you one row. Select this to get your XML variable - not particularly elegant but would work in pure t-sql...

Dave

Using the sp_OA* functions with the File System object is no better (or faster) than the solution I posted.  If you really want to go this route than use the sp_OA functions with the MSXML object and save your self the trouble of first reading it into a File System Stream object.

Just my 2 cents worth...

very true. they should work very similar to each other. I included this url because it does don't have an 8K character limit.
You bring up an interesting point... How could one use MSXML with OPENXML?

acperkins,
Thanks for the info. As usual your insight is keen an bright. (i gotta wear shades !;) )

Sorry but - Ill try to close it when I have figured out the solution in here
 (at the moment Im too occupied with other things)