Posted by KEN on 06/28/07 14:44

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

[Back to original message]