Re: XML INTO SQL

KEN (kenkopicky@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, esquel@xxxxxxxxxxxxx

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
.

Relevant Pages

  • Re: Simulieren einer Informix - RowID
    ... Diese Adresse stellt zwar gleiches da wie eine ROWID. ... wieviel sie Oracle nachgeahmt haben - das verliess mich bei C-ISAM ... Da sich der SQL Server beim Defragmentieren oder Teilen von Seiten ...
    (microsoft.public.de.sqlserver)
  • Re: Equivalent to rowid in Oracle...
    ... I actually meant to refer to rownum, not rowid - just haven't ... > the SQL Server Timestamp data type. ... RowID actually IS the internal Oracle pointer ... > Server Timestamp is, as was implicit in my first posting, that the SQL ...
    (microsoft.public.sqlserver.server)
  • Re: Converting an Oracle Trigger Script into SQL Server
    ... Wayne Snyder, MCDBA, SQL Server MVP ... STTIME + DURATION from inserted ... > This is assuming that ROWID is a column in the table GCDR and that there ...
    (microsoft.public.sqlserver.programming)
  • Re: Loading XML to SLQ Server
    ... "In SQL Server 2000 you have two main options - using the OPENXML technology ... BulkLoad runs on the client and requires no T-SQL but you need to generate ...
    (microsoft.public.sqlserver.xml)
  • Re: Have Insert statement, need equivalent Update.
    ... All the relevent openxml is there I just couldn't figure out how to ... INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID, ... FROM tblScan, tblScanAttribute JOIN #temp ON ... SQL Server, ...
    (comp.databases.ms-sqlserver)