September 26, 2003 at 12:18 pm
I'm using OpenXML to import an XML document into SQL server table, but the problem I'm running into is my XML document is larger than a varchar or nvarchar can hold.
Is there a "Character" datatype that can hold more than 8000 bytes???
here's my code
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 'TYPE \\jepeters\1.xml'
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
PRINT '-------------------------------'
PRINT 'Bytes read from the file:'
PRINT DATALENGTH(@strXMLText)
PRINT @strXMLText
PRINT '-------------------------------'
DROP TABLE #tmpFileLines
--Preparing for calling OPENXML
DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @strXMLText
--Inserting using OPENXML
INSERT INTO PoHeader
SELECT *
FROM OPENXML(@hDOC, '/aXML/Header', 2)
WITH
(
Title nvarchar(255),
Publisher nvarchar(255),
DateOfPurchase datetime
)
EXEC sp_xml_removedocument @hdoc
GO
SELECT * FROM PoHeader
GO
SET NOCOUNT OFF
September 26, 2003 at 1:32 pm
have you tries text columns?.
September 26, 2003 at 1:54 pm
yup
the message I get is
The text, ntext, and image data types are invalid for local variables.
September 27, 2003 at 7:11 am
You'll have to load the file outside of the proc and pass it in using a text variable.
Andy
September 29, 2003 at 6:47 am
Sounds like it should be simple.
Do you have an example of how this is done??
September 29, 2003 at 7:19 am
Not handy. The fun part is because you can't use text as a local var type, there is no way to load it that I know of except outside TSQL. You'll have to open the doc with DTS or whatever, then call the proc, passing the XML doc in.
Andy
September 29, 2003 at 7:45 am
Maybe I can load the file into a VBscript variable then use isql or osql to execute the stored procedure, passing the variable as the parameter?
Sounds like it should work...in theory
September 29, 2003 at 2:41 pm
create procedure spTest
@txt text
as
select @txt
go
Try this for the variable of the OpenXML part.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
October 1, 2003 at 11:11 am
Well,
I got the thing working in the Query Analyzer, but I'm getting an error in my Vbscript whne using the Wscript.Shell Object. I hope the Run command doesn't have a length restriction on it, but it sure does look that way. My script works fine when I invoke isql from the command line without the parameter...but when I add it, it gives me an error...
Still debugging
October 7, 2003 at 5:20 am
I do a lot of imports this way working with xml files which are several mb...
I have a VBS file that opens the xml document into a local variable...
--------------------- SNIP ---------------------
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set theFile = objFSO.OpenTextFile(xmlFileName,1)
theXML = theFile.ReadAll
theFile.close
--------------------- SNIP ---------------------
And then pass it through to a SP as a TEXT type...
--------------------- SNIP ---------------------
adoCmd.CommandText = "xml_import"
adoCmd.Parameters.Refresh
adoCmd.Parameters.Item(1).Value = theXML
adoCmd.Parameters.Item(2).Value = "BASICS"
Set adoRS = adoCmd.Execute()
--------------------- SNIP ---------------------
The SP is basically...
--------------------- SNIP ---------------------
CREATE Procedure xml_import
(
@xmlDocntext,
@whichSectionvarchar(50)
)
As
set nocount on
declare
@ReturnCodeint,
@iDocint
execute sp_xml_preparedocument @iDoc OUTPUT, @XMLDoc
if @whichSection = 'BASICS'
begin
-- do my processig here with OPENXML based on @iDoc
end
-- other code here....
-- always close th document...
execute sp_xml_removedocument @iDoc
--------------------- SNIP ---------------------
October 28, 2003 at 9:42 pm
what is the problem in passing the entire xml to a sp as an input parameter of datatype text?
October 29, 2003 at 1:19 am
That is what i have suggested, the above code passes the XML as ntext (Unicode Text).
Dan
November 1, 2003 at 8:16 pm
You might want to use Microsoft SQLXML to bulkload the XML file into your SQL table. I have been using it and it has worked fine.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply