XML INTO SQL

I need to open a newly created xml doc (in the same format) each time
a new one is sent to our web service. I would like to use a stored
procedure and call to it the problem is I keep getting a

XML parsing error: Invalid at the top level of the document.

See code

What I really need is a way preferably in t-sql to get a xml from a
path open it and input the data


SET NOCOUNT ON

-- 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'

DECLARE @strXMLText nvarchar(4000)

-- 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



DROP TABLE #tmpFileLines

-- Preparing for calling OPENXML
DECLARE @hDoc INT
EXEC sp_xml_preparedocument @hDoc OUTPUT, @strXMLText

-- Inserting using OPENXML
INSERT INTO _LOAN_NOTIFICATIONS (MESSAGE_DATE)
SELECT *
FROM OPENXML(@hDoc, '/dds_messages/dds_message', 2)
WITH
(
MESSAGE_DATE nvarchar(50)
)

EXEC sp_xml_removedocument @hDoc
GO

SELECT * FROM _LOAN_NOTIFICATIONS
GO

SET NOCOUNT OFF


This is the procedure I put it into it gets the path as @strXML

CREATE PROCEDURE sp_Insert_Books_Openxml

@strXML text OUTPUT

AS

DECLARE @iDoc int



EXECUTE sp_xml_preparedocument @iDoc OUTPUT, @strXML



--INSERT INTO _LOAN_NOTIFICATIONS (FIELD, FIELD, FIELD)

SELECT * FROM OpenXML(@iDoc, '/dds_messages/dds_message', 2)




--EXECUTE sp_xml_removedocument @iDoc

GO

Re: XML INTO SQL

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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

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 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/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx

Re: XML INTO SQL

KEN (kenkopicky@gmail.com) writes:
> 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.

Did I even reference a link? My VBscript abilities are not fantastic, so
I will have to decline. I just pointed out the possibility.

But when you ask "when the files hits the server" I have to ask: how you
detect that this occurs? Surely not through a stored procedure? Why cannot
that process simply read the file and pass the XML document? When I said
Agent job, it was because I thought you had an agent job already. That was
the only reason I could envision you would use a stored procedure for
the wrong job.




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx