|
|
Posted by Erland Sommarskog on 06/28/07 20:44
KEN (kenkopicky@gmail.com) writes:
> What I really need is a way preferably in t-sql to get a xml from a
> path open it and input the data
Since you use OPENXML, I assume that you are on SQL2000, in which
case your chances to get it working are not that bright. If the
XML documents are small, maybe.
> -- Let's now first read the XML file into a temporary table
> -- Create temporary table first
> CREATE TABLE #tmpFileLines (rowID int IDENTITY, lineData
> nvarchar(255))
>
> -- Insert lines from files into temp table (using xp_cmdshell)
> INSERT #tmpFileLines EXEC master.dbo.xp_cmdshell 'TEXT Z:\services
> \emds'
Can you safely assume that no line has more than 255 characters?
And can you assume that rowID is really assigned in the order the rows
are returned from xp_cmdshell? Maybe, but it's a little iffy.
> DECLARE @strXMLText nvarchar(4000)
And can you safely assume that no XML document is more than 4000 chars.
> -- Reading the XML data from the table into a string variable
> -- This string variable is used with OPENXML
> SELECT @strXMLText =
> CASE rowID WHEN 1 THEN
> ISNULL(RTRIM(lineData), '')
> ELSE
> @strXMLText + ISNULL(RTRIM(lineData), '')
> END
> FROM #tmpFileLines ORDER BY rowID ASC
And here's one thing you cannot rely on at all. The correct result of
SELECT @x = @x + col FROM tbl
and its variations is undefined. You may get what you expect (and often
you do), or you may get something else.
This particular problem could be addressed byh the use of a cursor, but
combined with the limitations of the XML document, I would not deem this
as a suitable solution.
If you want to run this on the SQL Server box, write an agent job in
VBscript that reads the file and connects to SQL Server. (Agent jobs
can be ActiveX tasks.)
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
[Back to original message]
|