XML String Process

Question

Post reply

XML String Process

James Tech

Right there with Babe

Points: 762
More actions
March 5, 2009 at 3:02 am

#131431

I am encountering problem in processing long XML strings. For small amount of xml string, it will give me the correct output...probably it won't fit on the nvarchar(max). Any alternatives? or any better solution processing large XML?
Please see the sample SP below:
IF OBJECT_ID ( '[dbo].[SampleSP]', 'P' ) IS NOT NULL
DROP PROCEDURE [dbo].[SampleSP];
GO
CREATE PROCEDURE [dbo].[SampleSP]
@fileName varchar(100)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @bulkInsCmd varchar(500),
@xmlLine varchar(200),
@xmlDoc nvarchar(MAX),
@idoc int
SET @xmlDoc = '';
CREATE TABLE #tempXML(
xml_line nvarchar(MAX)
)
SET @bulkInsCmd = 'BULK INSERT #tempXML ' +
'FROM ''' + @fileName + ''''
EXEC (@bulkInsCmd)
DECLARE xmlCursor CURSOR
FOR SELECT * FROM #tempXML
-- create XML string in SQL SERVER memory
OPEN xmlCursor
FETCH NEXT FROM xmlCursor INTO @xmlLine
WHILE @@FETCH_STATUS = 0
BEGIN
SET @xmlDoc = @xmlDoc + ltrim(rtrim(@xmlLine))
FETCH NEXT FROM xmlCursor INTO @xmlLine
END
CLOSE xmlCursor
DEALLOCATE xmlCursor
SELECT * FROM #tempXML
DROP TABLE #tempXML
/*Create an internal representation of the XML document.
A parsed document is stored in the internal cache of SQL Server.
The MSXML parser uses one-eighth the total memory available for SQL Server.
To avoid running out of memory, run sp_xml_removedocument to free up the memory.
*/
EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlDoc
PRINT @xmlDoc
-- SELECT statement using OPENXML rowset provider
SELECT *
FROM OPENXML (@idoc, '/Root/Input/Record', 1)
WITH (ID varchar(2),
RecordType varchar(30)) AS a
-- free up memory
EXEC sp_xml_removedocument @idoc
END
GO
thanks for the help.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Hello James

Why the detour over the line by line cursor?

DECLARE @xml xml

SELECT @xml = BulkColumn FROM OPENROWSET(

BULK 'D:\Temp\Test\test.xml',

SINGLE_BLOB) AS x

SELECT @xml.query('/root/test')

Greets

Flo

James Tech Right there with Babe Points: 762 More actions · Answer 2

florian.reischl (3/5/2009)
Hello James
Why the detour over the line by line cursor?
DECLARE @xml xml
SELECT @xml = BulkColumn FROM OPENROWSET(
BULK 'D:\Temp\Test\test.xml',
SINGLE_BLOB) AS x
SELECT @xml.query('/root/test')
Greets
Flo

Hi Flo,

Thanks for the reply, actually I just got the code from this link and test it:

http://www.sqlservercentral.com/articles/Basics/handlingsimplexmlusingtsql/1482/

I just need to look-up for a table name and table code inside our xml configuration. In your solution, is it possible to load the result on a sp_xml_preparedocument? or any alternatives to on what I am suppose to achieve in a speedy manner?

Thanks for the help.