Re: XML INTO SQL

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...@xxxxxxxxxxxxx> wrote:
KEN (kenkopi...@xxxxxxxxx) 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...@xxxxxxxxxxxxx

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



.

Relevant Pages