Posted by KEN on 06/28/07 22:12

Thanks,

I Started by writing a ActiveX data transformation object I thought a
stored procedure might be better because I can call it when the xml
file hits our server rather than scheduling it because the file name
will change. Would you mind posting the code for a good Active X
solution the link you referenced got truncated.








On Jun 28, 2:44 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> KEN (kenkopi...@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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]