How can I parse XML from a text column using sp_xml

Tuesday, June 18, 2013

How can I parse XML from a text column using sp_xml_preparedocument? (55809 Requests)

This answer was provided by Umachandar Jayachandran.

I use this technique quite often when I work with dynamic SQL. Here is one sample that I posted a while back in the newsgroups.


DECLARE @xml_he int
DECLARE @xml_0 varchar( 8000 ), @xml_1 varchar( 8000 ),
        @xml_2 varchar( 8000 ), @xml_3 varchar( 8000 )

-- Strip the text value into several varchar strings:
SELECT @xml_0 = SUBSTRING( xml_text, ( 0*8000 ) + 1, 8000 ),
       @xml_1 = SUBSTRING( xml_text, ( 1*8000 ) + 1, 8000 ),
       @xml_2 = SUBSTRING( xml_text, ( 2*8000 ) + 1, 8000 ),
       @xml_3 = SUBSTRING( xml_text, ( 3*8000 ) + 1, 8000 )
  FROM tbl

-- Use dynamic SQL to build the prepare doc call:
EXEC(
'DECLARE @Handle int
 EXEC sp_xml_preparedocument @Handle OUT, ''' +
 @xml_0 + @xml_1 + @xml_2 + @xml_3 + '''
 DECLARE he_cur CURSOR FOR SELECT @Handle'
)
OPEN he_cur
FETCH he_cur INTO @xml_he
DEALLOCATE he_cur

IF @xml_he IS NULL
    RAISERROR( 'Invalid Handle!', 16, 1 )

-- Now use OPENXML with the handle in @xml_he

EXEC sp_xml_removedocument @xml_he

You need to have some realistic estimate of the length of the text data though. Otherwise you cannot use this technique. I do not know the limit for the number of variables, I think it is limited by the amount of memory or some percentage of it.

With any solution involving dynamic SQL, you will be limited by how many variables you can declare & use.

I do not know of any other methods. You can't use automation objects/extended procedures with loop back connections to prepare the XML since the handle is only valid for the connection. You will have other transactional issues if you go this route too.

Lastly, I think trying to do all this on the server-side itself is overkill. It is easy to write components (wsf, wsc, dll) that can pre-process the XML & feed it to the server. But I guess there is a place for every technique!

Feedback

# Re: How can I parse XML from a text column using sp_xml_preparedocument? 7/1/2002 10:53 PM Dan Sketcher

I recommend you replace the substring lines with the following to escape any stray single quotes, saving some headaches. SELECT @xml_0 = REPLACE(SUBSTRING( Itinerary, ( 0*8000 ) + 1, 8000 ), CHAR(39), CHAR(39)+CHAR(39) ) ...

# Re: How can I parse XML from a text column using sp_xml_preparedocument? 7/2/2002 12:04 AM Dan Sketcher

Actually, I forgot to mention that as this replacement will increase the size of the varchar data, you probably should change the 8000's to 7500 or something.

# error while executing 1/29/2003 12:55 AM amit

i am getting error when i execute - 'saying --A cursor with the name 'he_cur' does not exist.

# Re: How can I parse XML from a text column using sp_xml_preparedocument? 4/21/2003 6:25 PM Alex Drougov

Thank you so much. This Idea helped me a lot. But I have a couple comments: Unfortunately the solution with REPLACE In Select statement doesn't work very well ;( -- We need to select smaller strings SELECT @xml_0 = SUBSTRING( xml_text, ( 0*6000 ) + 1, 6000 ), @xml_1 = SUBSTRING( xml_text, ( 1*6000 ) + 1, 6000 ), @xml_2 = SUBSTRING( xml_text, ( 2*6000 ) + 1, 6000 ), @xml_3 = SUBSTRING( xml_text, ( 3*6000 ) + 1, 6000 ) FROM tbl --- and then replace escape as separate statement SET @xml_0 = REPLACE(@xml_0, CHAR(39), CHAR(39)+CHAR(39) ) SET @xml_1 = REPLACE(@xml_1, CHAR(39), CHAR(39)+CHAR(39) ) SET @xml_2 = REPLACE(@xml_2, CHAR(39), CHAR(39)+CHAR(39) ) SET @xml_3 = REPLACE(@xml_3, CHAR(39), CHAR(39)+CHAR(39) )

# are you kidding? 6/27/2003 2:12 PM Mauricio

A dont beliave that is not possible to get text columns directlly from sql server. sp_xml_preparedocument suport text file, but i cant store text in a local variable. If i decide to use this sugestion (8000 char), i will need 22 steps in my smaller xml doc and 6000 steps int the greater.

# Is CONCAT_NULL_YIELDS_NULL an issue? 9/10/2003 9:27 PM John Peterson

I was wondering -- if there are "chunks" that remain unset or NULL, will the entire expression be NULL (potentially) in the EXECUTE statement when the various chunks are concatenated? If so, it's probably best to wrap an ISNULL(..., '') around the chunks in the SELECT statement (in conjunction with Dan Sketcher's suggestions).

# A full dynamic version 9/21/2003 5:00 AM yair

With this version you don't need to estimate, limits etc. The secreat is 2-level synamic sql. ----------------------------------------------------------------------------------- DECLARE @datalen int DECLARE @sql varchar(8000) DECLARE @sql1 varchar(8000) DECLARE @cnt int DECLARE @xml_he int -- get the length SELECT @datalen = DATALENGTH (load_text) / 8000 + 1 FROM tbl WHERE xml_id = '0123' -- phase 1 collect into @sql declarations of @str1, @str2,...@strn SET @cnt = 1 SET @sql='DECLARE ' SET @sql1 = '' WHILE (@cnt <= @datalen) BEGIN SELECT @sql = @sql + CASE @cnt WHEN 1 THEN '' ELSE ', ' + CHAR(13) END + ' @str'+CONVERT(varchar(10),@cnt)+' VARCHAR(8000)' SET @cnt = @cnt + 1 END -- phase 2 collect into @sql selection of chunks (takng care of length) SET @cnt = 1 WHILE (@cnt <= @datalen) BEGIN IF LEN(@sql) < 7900 SELECT @sql = @sql + CHAR (13) + 'SELECT @str' + CONVERT(VARCHAR(10), @cnt) + ' = SUBSTRING(load_text, ' + CONVERT(VARCHAR(30), (@cnt-1)*8000+1) + ', 8000) FROM tbl ' + 'WHERE xml_id=''0123''' ELSE SELECT @sql1 = @sql1 + CHAR (13) + 'SELECT @str' + CONVERT(VARCHAR(10), @cnt) + ' = SUBSTRING(load_text, ' + CONVERT(VARCHAR(30), (@cnt-1)*8000+1) + ', 8000) FROM tbl ' + 'WHERE xml_id=''0123''' SET @cnt = @cnt + 1 END -- phase 3 preparing the 2nd level dynamic sql SELECT @sql1 = @sql1 + CHAR(13) + 'EXEC ('+ CHAR(13) + '''DECLARE @hnd int'+ CHAR(13) + 'EXEC @err = sp_xml_preparedocument @hnd OUT, '''''' + ' SET @cnt = 1 WHILE (@cnt <= @datalen) BEGIN SELECT @sql1 = @sql1 + CHAR (13) + '@str' + CONVERT (varchar(10), @cnt) + ' +' SET @cnt = @cnt + 1 END SET @sql1 = @sql1 + ' '''''' ' SET @sql1 = @sql1 + CHAR(13) + 'DECLARE he_cur CURSOR FOR SELECT @hnd''' +CHAR(13) + ')' /* --some debugging Print @sql PRINT '--<<<< sql len=' +convert(varchar(5),datalength(@sql)) Print @sql1 PRINT '--<<<< sql1 len=' +co

# re: How can I parse XML from a text column using sp_xml_preparedocument? 12/30/2003 5:33 AM Paul

Hate to be a bother but can you post the rest of the code, Yair? Thanks, :)!

# re: How can I parse XML from a text column using sp_xml_preparedocument? 1/14/2004 6:49 AM Angel Only

Please Yair post rest of code!! :)

# re: How can I parse XML from a text column using sp_xml_preparedocument? 3/17/2004 2:34 PM Rob Boek

/*PART 1*/
DECLARE @id int
DECLARE @idoc int
SET @id = 101 -- or whatever the id

DECLARE @datalen int
DECLARE @sql varchar(8000)
DECLARE @sql1 varchar(8000)
DECLARE @cnt int
-- get the length
SELECT @datalen = DATALENGTH (xmlcol) / 4000 + 1 FROM xmltable WHERE id = @id

-- phase 1 collect into @sql declarations of @str1, @str2,...@strn
SET @cnt = 1
SET @sql='DECLARE '
SET @sql1 = ''
WHILE (@cnt <= @datalen)
BEGIN
SELECT
@sql = @sql + CASE @cnt
WHEN 1 THEN ''
ELSE ', ' + CHAR(13)
END
+ ' @str'+CONVERT(varchar(10),@cnt)+' NVARCHAR(4000)'
SET @cnt = @cnt + 1
END
-- phase 2 collect into @sql selection of chunks (takng care of length)
SET @cnt = 1
WHILE (@cnt <= @datalen)
BEGIN
IF LEN(@sql) < 7850
SELECT @sql = @sql + CHAR (13) +
'SELECT @str' + CONVERT(VARCHAR(10), @cnt) + ' = REPLACE(SUBSTRING(xmlcol, ' +
CONVERT(VARCHAR(30), (@cnt-1)*4000+1) + ', 4000),CHAR(39), CHAR(39)+CHAR(39) ) ' +
'FROM xmltable ' +
'WHERE id = ''' + @id + ''''
ELSE
SELECT @sql1 = @sql1 + CHAR (13) +
'SELECT @str' + CONVERT(VARCHAR(10), @cnt) + ' = REPLACE(SUBSTRING(xmlcol, ' +
CONVERT(VARCHAR(30), (@cnt-1)*4000+1) + ', 4000),CHAR(39), CHAR(39)+CHAR(39) ) ' +
'FROM xmltable ' +
'WHERE id = ''' + @id + ''''
SET @cnt = @cnt + 1
END

# re: How can I parse XML from a text column using sp_xml_preparedocument? 3/17/2004 3:44 PM Rob Boek

/*PART 2*/
-- phase 3 preparing the 2nd level dynamic sql
SELECT @sql1 = @sql1 + CHAR(13) + 'EXEC ('+ CHAR(13) + '''DECLARE @idoc int'+ CHAR(13) +
'EXEC sp_xml_preparedocument @idoc OUT, '''''' + '
SET @cnt = 1
WHILE (@cnt <= @datalen)
BEGIN
SELECT @sql1 = @sql1 + CHAR (13) + '@str' + CONVERT (varchar(10), @cnt) + ' +'
SET @cnt = @cnt + 1
END
SET @sql1 = @sql1 + ' '''''' '
SET @sql1 = @sql1 + CHAR(13) + 'DECLARE idoc_cur CURSOR FOR SELECT @idoc''' +CHAR(13) + ')'
--debug code
/*
PRINT @sql
PRINT '@sql length=' +convert(varchar(5),datalength(@sql))
PRINT '----------'
PRINT @sql1
PRINT '@sql1 length=' +convert(varchar(5),datalength(@sql1))
*/
EXEC (@sql + @sql1)
OPEN idoc_cur
FETCH NEXT FROM idoc_cur into @idoc
DEALLOCATE idoc_cur

--When Complete
/*
exec sp_xml_removedocument @idoc
*/

# re: How can I parse XML from a text column using sp_xml_preparedocument? 4/27/2004 8:54 AM Vijayakumar

Hi,

I tried this logic.It's working fine if my text column has up to 24000 chars .but my scenario is the text column which will have 2 laks and above chars.

If i want to implement the sol for my situation i have declare 50 and above variables.It is looking hard. is any other way to proceed this?

-Vijayakumar

# special chars and nvarchar 7/8/2004 5:11 AM Rickard Nordström

I have some data that contains special chars like åäö. Normally I use nvarchar(4000) to feed the sp_prepxml. But with larger chunks of data abow 4000 it just does not work. Even this charmful solution...

I tried to change the varchar(8000) to nvarchar(4000) but the dynamic bulding of the sql seems to disregard that it is nvarchar and feed it with varchar.

Does anyone got a clue?

Regards
Rickard Nordtröm

# re: How can I parse XML from a text column using sp_xml_preparedocument? 8/2/2004 4:30 AM Oliver Bryant

It was a nightmare searching for a solution to this - thanks for the original static solution and cheers to Rob Boek for providing the fully dynamic solution!

# re: How can I parse XML from a text column using sp_xml_preparedocument? 10/8/2004 6:52 AM Bytter

Is this still the only technique arround? sp_xml_preparedocument accepts text! I got the text in the database! It seems rather stupid to introduce all this overhead only because SQLServer does not allow local variables to be Text (or NText). I can't understand why it has this limitation. It only leads me to conclude that, for example, when fetching results from a cursor, SQLServer is indeed copying the values instead of mantaining some kind of pointer to them. It s*cks!

# re: How can I parse XML from a text column using sp_xml_preparedocument? 10/19/2004 6:16 AM Eric Hood

Too bad there's not a version of sp_xml_preparedocument that takes a pointer to a text value, then this'd be real simple.

SELECT @ptr = TEXTPTR(...)

sp_xml_preparedocument(@ptr)

# re: How can I parse XML from a text column using sp_xml_preparedocument? 10/27/2004 10:10 PM Kathy Meagher

Thanks for the dynamic sql solution - this helped me a lot! Got another question if someone's following this thread. I've noticed that I have to be in the context of the Master database in order to run the Select from OPENXML command, after you've run the SQL from Rob Boek above. I need a trigger in the user DB to copy the XML-parsed data to another table, but am forced to first create the procedure in the Master database, then call the procedure from the trigger. Is it possible to call OPENXML from a user database?

# re: How can I parse XML from a text column using sp_xml_preparedocument? 10/29/2004 4:47 PM Kathy Meagher

Disregard my comment above - although my SQL instance is SQL 2000 sp3, turns out my user db has compatibility level of 7.0, and OPENXML was not available in that version.

# How use sp_xml_preparedocument to parse an XML file? 11/3/2004 10:44 PM Alvin Asiimwe

I have XML data that resides in an .xml file. I would like to parse it using sp_xml_preparedocument so that I can save the contents in a database. How can I do that?

# How can I get proper XML tree when using FOR XML AUTO 11/11/2004 2:23 PM Surya

Can any one help me how to send the result set to XML using FOR XML AUTO in SQL, when we have two nodes at the same level.
For example : --> denotes one to Many relations and the relations are like :
Table2 <-- Table1 --> Table3 --> Table4
XML tree should be :
Table1(reslut set)
|
___________________|__________________
| |
Table2(reslut set) Table3(reslut set)
|
Table4(reslut set)

# re: How can I parse XML from a text column using sp_xml_preparedocument? 4/14/2005 2:23 PM Peter W

This is profoundly dopey state of affairs. I notice I'd be able to do this in a function, but unfortunately you can't exec sp_xml_preparedoc in a function since its not an extended procedure.

Sigh.

# re: How can I parse XML from a text column using sp_xml_preparedocument? 10/3/2005 1:15 AM Krithi

Same as Richard's query,I also have a problem. have some data that contains special( non-ascii) chars like åäö or ~ upon 'N' .Sp_xmlPreparedocument procedure returns error on parsing this content.

Please help me with a solution.

# re: How can I parse XML from a text column using sp_xml_preparedocument? 12/14/2005 4:02 PM fdf

fgdf

# re: How can I parse XML from a text column using sp_xml_preparedocument? 3/8/2006 12:52 PM Kerem

Have anybody find a way for the dynamic Nvarchar case?

# re: How can I parse XML from a text column using sp_xml_preparedocument? 10/10/2006 11:55 PM harry

how to pass an xml file itself as a paremeter to a stored procedure

# how to pass an xml file itself as a paremeter to a stored procedure 10/10/2006 11:55 PM harry

how to pass an xml file itself as a paremeter to a stored procedure

# re: How can I parse XML from a text column using sp_xml_preparedocument? 10/27/2006 11:20 AM Prakash Thondepu

-- code to find and replace data in xml

---Code to update a piece of data in xml in the Sql server field. Will be handy when you fix any data related forms bugs.

declare @otxt varchar(1000)

set @otxt = '&JENNIFERGREEN' -- string you want to find

declare curs cursor local fast_forward

for

select

id,

textptr(xmldata),

charindex(@otxt, xmldata)-1

from

tablename

where

XMLDATA -- colum name

like

'%' + @otxt +'%'

and id = 52318 --some criteria

declare @ntxt varchar(1000)

set @ntxt = 'JENNIFERGREEN' ---- string you want to replace with

declare @txtlen int

set @txtlen = len(@otxt)

declare @ptr binary(16)

declare @pos int

declare @id int

--Finally we can do our search and replace:

open curs

fetch next from curs into @id, @ptr, @pos

while @@fetch_status = 0

begin

updatetext tablename.xmldata @ptr @pos @txtlen @ntxt

-- print(@ptr)

-- print(@pos)

-- print( @txtlen)

-- print(@ntxt )

fetch next from curs into @id, @ptr, @pos

end

close curs

deallocate curs

# re: special chars and nvarchar 10/30/2006 3:06 AM Stephen Turner

Rickard, you need to add a N before the string i.e. "EXEC sp_xml_preparedocument @Handle OUT, N''' + @xml_0 +..." to force the string literal to be treated as an nvarchar

# re: How can I parse XML from a text column using sp_xml_preparedocument? 12/15/2006 9:14 PM Rohini

I need to parse XML.
I use text datatype and my XML data IS
<ROOT> <Ord CompID="BM" FYr="2006-2007" GrpID="0186" UGrpID="0028" GrpName="MURALI PARTIES" LID="0409" PtyName="ARUL & JOTHI MILLS" InvNo="BM/LS/1205/0607" InvDt="11/17/2006" JID="LS" ODueDys="28" AsOn="12/15/2006" Bal="135265" BLRefNo="0" RecType="FIFO"/>
</ROOT>'

The following error comes
Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Line 97
XML parsing error: Whitespace is not allowed at this location.

And if i replace the & symbol in the party it works fine/
ANy help appreciated

# re: How can I parse XML from a text column using sp_xml_preparedocument? 3/8/2007 12:31 PM Gagan Khurana

Hi Rohini
This is due to the fact that "All special characters need to be convert to their equivalent code".
For example :
Change '&' to '&'
Change'<' to '<' etc.
Only after that xml data is parsed properly and we can store it into the database.
Happy Programming!!!

# re: How can I parse XML from a text column using sp_xml_preparedocument? 3/22/2007 12:47 PM Sherry

What I need is to populate multiple rows to a table with columns like this:

contentName varchar(50), content NTEXT

The value of ’content’(NTEXT) in the table has to be stored as a XML string, e.g.,

<?xml version="1.0" encoding="UTF-8"?> <email template="102091_100005_250968" emailAddr="xxx.yyy@verizon.net"><FirstName>xxx</FirstName><LastName>yyy</LastName></email>

I was able to insert records via SQL:

INSERT INTO testTbl (contentName, content)
VALUES ('57600_2_297738',
'<?xml version="1.0" encoding="UTF-8"?> <email emailAddr="sherry.yuan@postfuture.com"> <FirstName>Sherry</FirstName> <LastName>Yuan</LastName> </email>')

In order to do bulk inserts, I was thinking to pack a chunk of records into a XML doc and then write a stored procedure using sp_xml_preparedocument.

My test code looks like this:

declare @idoc int
DECLARE @doc varchar(2000)
SET @doc ='
<mydata>
<test contentName="57600_2_297738" content=”<?xml version="1.0" encoding="UTF-8"?><email emailAddr="xxx.yyy@verizon.net"><FirstName>xxx</FirstName><LastName>yyy</LastName></email>”/>
<test contentName="67600_3_297738" content=”<?xml version="1.0" encoding="UTF-8"?><email emailAddr=" xxx1.yyy1@verizon.net"><FirstName>xxx1</FirstName><LastName>yyy1</LastName></email>”/>
</mydata>'
EXEC sp_xml_preparedocument @idoc OUTPUT,@doc
SELECT contentName, content
FROM OPENXML(@idoc, '/mydata/test', 1)
WITH (contentName varchar(50), content NTEXT )
EXEC sp_xml_removedocument @idoc

I got XML parse error about '<' in the first place, th

# re: How can I parse XML from a text column using sp_xml_preparedocument? 3/22/2007 12:48 PM Sherry

I got XML parse error about '<' in the first place, then I replaced all the special characters with valid XML string as follows:

Content=”&lt?xml version=&quot1.0&quot encoding=&quotUTF-8&quot?&gt &ltemail emailAddr= &quot xxx.yyy@verizon.com &quot &gt&ltFirstName&gt &lt xxx/FirstName&gt &lt LastName&gt yyy&lt /LastName&gt &lt /emai

However I still encountered the error:

Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1
The error description is 'A semi colon character was expected.'.
Msg 8179, Level 16, State 5, Line 9
Could not find prepared statement with handle 0.

Could anyone help me with this problem? Thanks very much in advance.

# re: How can I parse XML from a text column using sp_xml_preparedocument? 3/22/2007 12:49 PM Sherry

# re: How can I parse XML from a text column using sp_xml_preparedocument? 3/22/2007 9:02 PM Sherry

# re: How can I parse XML from a text column using sp_xml_preparedocument? 4/19/2007 11:31 AM bzeng

I have an example to read xml file on live read data from HP WebService and load the data into our database:
here is the example coding:

EXECUTE sp_xml_preparedocument @iDocumentHandle OUTPUT, @xml

INSERT INTO ConfigGroup (ConfigGroupKey, ShortDesc, Description, Image, ProductClassFlag, Brand, DoNotDisplay)
(SELECT [Key],
dbo.fn_DecodeXml(dbo.fn_CheckForNullString(ShortDesc)),
dbo.fn_DecodeXml(dbo.fn_CheckForNullString(Description)),
dbo.fn_DecodeXml(dbo.fn_CheckForNullString(Image)),
dbo.fn_CheckForNullNumber(ProductClassFlag),
@sBrand,
0
FROM
OpenXML(@iDocumentHandle, '/productCatalog/ConfigGroup', 2)
WITH ( [Key] varchar(100) '@Key',
ShortDesc VARCHAR(100) '@DescriptionShort',
Description VARCHAR(500) '@Description',
Image VARCHAR(100) '@Image',
ProductClassFlag VARCHAR(50) '@ProductClassFlag'
) ConfigGroupXml
WHERE ConfigGroupXml.[Key] NOT IN (SELECT ConfigGroupKey FROM ConfigGroup)
)

INSERT INTO Product (ProductKey,
RetailerProdId,
GlobalProdId,
ProprietaryProductIdentifierKey,
PrimaryTextDesc,
DetailTextDesc,
ProductImage,
LanguageKey,
LeadTime,
ProductSize,
ProductAction,
ProductClassType,
EffectiveDate,
ExpiryDate)
(SELECT
dbo.fn_DecodeXml(ProductKey),
dbo.fn_DecodeXml(dbo.fn_CheckForNullString(RetailerProdId)),
dbo.fn_DecodeXml(dbo.fn_CheckForNullString(GlobalProdId)),
dbo.fn_DecodeXml(ProprietaryProductIdentifierKey),
dbo.fn_DecodeXml(dbo.fn_CheckForNullString(Primary_text_desc)),
dbo.fn_DecodeXml(dbo.fn_CheckForNullString(Detail_Text_Description)),
dbo.fn_DecodeXml(dbo.fn_CheckForNullString(Image)),
dbo.fn_DecodeXml(dbo.fn_CheckForNullString(LanguageKey)),

# Update to this code to support Large XML documents 6/6/2007 3:48 PM Kevin Rogers

This code should load *MUCH* larger XML Documents. There is still a limit but I have tested this up to 5Mb and I expect that it can cope with documents larger.

CREATE PROCEDURE ParseAndLoadXML
@XML_ID INT,
@XML_DOC_ID INT OUTPUT
AS
BEGIN
DECLARE
@cr CHAR(1),
@codelength INT,
@dataLength INT,
@writeIndex INT,
@swriteIndex VARCHAR(10),
@Code_Declarations VARCHAR(8000),
@Code_VarInitilization VARCHAR(8000),
@Code_Conditionals VARCHAR(8000),
@Code_ExecutionBlock VARCHAR(8000),
@CodeToExecute VARCHAR(8000)

SELECT @dataLength = DATALENGTH (xmlcol) / 3000 + 1 FROM xmltable WHERE id = @XML_id
SET @CodeLength = @dataLength / 30
SET @writeIndex = 1
SET @cr = CHAR(13)

SET @Code_Declarations = 'DECLARE @datalen int,' + @cr +
'@sql varchar(8000),'
SET @Code_VarInitilization = 'SET @cnt = 1 ' + @cr +
'set @codeCount = 1' + @cr +
'SET @codeOver = 0' + @cr +
'SET @sql=''DECLARE '''
SET @Code_ExecutionBlock = 'EXEC('

SET @Code_Conditionals = ''

WHILE @writeIndex < @CodeLength
BEGIN
SET @swriteIndex = CONVERT(varchar(10),@writeIndex)
SET @Code_Declarations = @Code_Declarations + @cr +
'@sql' + @swriteIndex + ' varchar(8000),'
SET @Code_VarInitilization = @Code_VarInitilization + @cr +
'SET @sql' + @swriteIndex + ' = '''''
SET @Code_Conditionals = @Code_Conditionals + @cr +
'if @codeCount = ' + @swriteIndex + @cr +
' SET @sql' + @swriteIndex + ' = @sql'
SET @Code_ExecutionBlock = @Code_ExecutionBlock + @cr +
'@sql' + @swriteIndex + ' + '
SET @writeIndex = @writeIndex + 1
END
SET @Code_ExecutionBlock = @Code_ExecutionBlock + @cr +
'@sqlEnd )'

SET @Code_Declarations = @Code_Declarations +
'@sqlEnd varchar(8000),' + @cr +
'@cnt int,'+ @cr +
'@codeCount INT,'+ @cr +
'

# re: How can I parse XML from a text column using sp_xml_preparedocument? 6/6/2007 3:51 PM Kevin Rogers

CREATE PROCEDURE ParseAndLoadXML
@XML_ID INT,
@XML_DOC_ID INT OUTPUT
AS
BEGIN
DECLARE
@cr CHAR(1),
@codelength INT,
@dataLength INT,
@writeIndex INT,
@swriteIndex VARCHAR(10),
@Code_Declarations VARCHAR(8000),
@Code_VarInitilization VARCHAR(8000),
@Code_Conditionals VARCHAR(8000),
@Code_ExecutionBlock VARCHAR(8000),
@CodeToExecute VARCHAR(8000)

SELECT @dataLength = DATALENGTH (xmlcol) / 3000 + 1 FROM xmltable WHERE id = @XML_id
SET @CodeLength = @dataLength / 30
SET @writeIndex = 1
SET @cr = CHAR(13)

SET @Code_Declarations = 'DECLARE @datalen int,' + @cr +
'@sql varchar(8000),'
SET @Code_VarInitilization = 'SET @cnt = 1 ' + @cr +
'set @codeCount = 1' + @cr +
'SET @codeOver = 0' + @cr +
'SET @sql=''DECLARE '''
SET @Code_ExecutionBlock = 'EXEC('

SET @Code_Conditionals = ''

# re: How can I parse XML from a text column using sp_xml_preparedocument? 6/6/2007 3:52 PM Kevin Rogers

WHILE @writeIndex < @CodeLength
BEGIN
SET @swriteIndex = CONVERT(varchar(10),@writeIndex)
SET @Code_Declarations = @Code_Declarations + @cr +
'@sql' + @swriteIndex + ' varchar(8000),'
SET @Code_VarInitilization = @Code_VarInitilization + @cr +
'SET @sql' + @swriteIndex + ' = '''''
SET @Code_Conditionals = @Code_Conditionals + @cr +
'if @codeCount = ' + @swriteIndex + @cr +
' SET @sql' + @swriteIndex + ' = @sql'
SET @Code_ExecutionBlock = @Code_ExecutionBlock + @cr +
'@sql' + @swriteIndex + ' + '
SET @writeIndex = @writeIndex + 1
END
SET @Code_ExecutionBlock = @Code_ExecutionBlock + @cr +
'@sqlEnd )'

SET @Code_Declarations = @Code_Declarations +
'@sqlEnd varchar(8000),' + @cr +
'@cnt int,'+ @cr +
'@codeCount INT,'+ @cr +
'@codeOver INT,'+ @cr +
'@idoc INT,'+ @cr +
'@id INT,'+ @cr +
'@XML_id INT,'+ @cr +
'@XML_DOC_id INT'+ @cr

# re: How can I parse XML from a text column using sp_xml_preparedocument? 6/6/2007 3:53 PM Kevin Rogers

SET @Code_VarInitilization =
'SET @id = ' + CONVERT(varchar(10),@XML_id) + @cr +
'' +
'SET @datalen = ' +CONVERT(varchar(10),@dataLength) + @cr +
@Code_VarInitilization

EXEC(
@Code_Declarations + @cr +
@Code_VarInitilization + @cr +
'SET @sqlEnd = ''''' + @cr +
'WHILE (@cnt <= @datalen) ' + @cr +
'BEGIN ' + @cr +
' IF LEN(@sql) < 7950 ' + @cr +
' SELECT ' + @cr +
' @sql = @sql + ' + @cr +
' CHAR(13) + '' @str''+CONVERT(varchar(10),@cnt)+'' NVARCHAR(4000),'' ' + @cr +
' ELSE' + @cr +
' Begin' + @cr +
@Code_Conditionals + @cr +
' SELECT @sql = ' + @cr +
' ''@str''+CONVERT(varchar(10),@cnt)+'' NVARCHAR(4000),'' ' + @cr +
' SET @codeCount = @codeCount + 1' + @cr +
' End' + @cr +
' SET @cnt = @cnt + 1 ' + @cr +
'END ' + @cr + @cr +
'SET @sql = @sql + + CHAR(13) + ''@dummy INT''+ CHAR(13) ' + @cr +
'-- phase 2 collect into @sql selection of chunks (taking care of length) ' + @cr +
'SET @cnt = 1 ' + @cr +

# re: How can I parse XML from a text column using sp_xml_preparedocument? 6/6/2007 3:53 PM Kevin Rogers

'WHILE (@cnt <= @datalen) ' + @cr +
'BEGIN ' + @cr +
' IF LEN(@sql) < 7850 ' + @cr +
' SELECT @sql = @sql + CHAR (13) + ' + @cr +
' ''SELECT @str'' + CONVERT(VARCHAR(10), @cnt) + '' = REPLACE(SUBSTRING(xmlcol, '' + ' + @cr +
' CONVERT(VARCHAR(30), (@cnt-1)*3000+1) + '', 3000),CHAR(39), CHAR(39)+CHAR(39) ) '' + ' + @cr +
' ''FROM xmltable '' + ' + @cr +
' ''WHERE id = '' + cast(@id as varchar(10))' + @cr +
' ELSE ' + @cr +
' Begin' + @cr +
@Code_Conditionals + @cr +
' SELECT @sql = CHAR (13) + ' + @cr +
' ''SELECT @str'' + CONVERT(VARCHAR(10), @cnt) + '' = REPLACE(SUBSTRING(xmlcol, '' + ' + @cr +
' CONVERT(VARCHAR(30), (@cnt-1)*3000+1) + '', 3000),CHAR(39), CHAR(39)+CHAR(39) ) '' + ' + @cr +
' ''FROM xmltable '' + ' + @cr +
' ''WHERE id = '' + cast(@id as varchar(10))' + @cr +
' SET @codeCount = @codeCount + 1' + @cr +
' End' + @cr +
' SET @cnt = @cnt + 1' + @cr +
'END ' + @cr +
'' + @cr +
'/*PART 2*/ ' + @cr +
'-- phase 3 preparing the 2nd level dynamic sql ' + @cr +
'SELECT @sql = @sql + CHAR(13) + ''EXEC (''+ CHAR(13) + ''''''DECLARE @idoc int''+ CHAR(13) + ' + @cr +
'''EXEC sp_xml_preparedocument @idoc OUT, N'''''''''''' + '' ' + @cr +
'SET @cnt = 1 ' + @cr +

# re: How can I parse XML from a text column using sp_xml_preparedocument? 6/6/2007 3:54 PM Kevin Rogers

'WHILE (@cnt <= @datalen) ' + @cr +
' BEGIN ' + @cr +
' IF LEN(@sql) < 7850 ' + @cr +
' SELECT @sql = @sql + CHAR (13) + ''@str'' + CONVERT (varchar(10), @cnt) + '' +'' ' + @cr +
' ELSE' + @cr +
' BEGIN' + @cr +
@Code_Conditionals + @cr +
' SELECT @sql = CHAR (13) + ''@str'' + CONVERT (varchar(10), @cnt) + '' +'' ' + @cr +
' SET @codeCount = @codeCount + 1' + @cr +
' END' + @cr +
' SET @cnt = @cnt + 1 ' + @cr +
' END ' + @cr +
'SET @sqlEnd = @sqlEnd + '' '''''''''''' '' ' + @cr +
'SET @sqlEnd = @sqlEnd + CHAR(13) + ''DECLARE idoc_cur CURSOR FOR SELECT @idoc'''''' + CHAR(13) + '')''' + @cr +
@Code_Conditionals + @cr +
@Code_ExecutionBlock + @cr
)
OPEN idoc_cur
FETCH NEXT FROM idoc_cur into @XML_DOC_ID
DEALLOCATE idoc_cur
END

# How to use the above code... 6/6/2007 3:55 PM Kevin Rogers

The last 5 posts above should be extracted and joined together to create the SP. Have fun. This code was very useful but this limitation is quite tedious to work around.

# re: How can I parse XML from a text column using sp_xml_preparedocument? 5/15/2008 10:08 AM Fred

I joined the last 5 posts and I am very, very, very frustrated because it doesn't work : I am the error message "idoc_cur cursor doesn't exist"

May you send me the good one please because you procedure is very nice ?

# re: How can I parse XML from a text column using sp_xml_preparedocument? 6/30/2008 11:46 PM Diego

May you post the complete sp,still error "idoc_cur cursor doesn't exist

# re: How can I parse XML from a text column using sp_xml_preparedocument? 7/1/2008 7:54 AM Diego

May you post the complete sp,still error "idoc_cur cursor doesn't exist

# This should do it 8/19/2008 9:53 AM Kevin Rogers

The code didn't do SMALL XML values well (not at all).
Add the following IF conditional and ELSE clause to the above SP. The insertion points are at the beginning of the first while loop and at the end of the SP.

if @CodeLength > 0
Begin
WHILE @writeIndex < @CodeLength
BEGIN

...
OPEN idoc_cur
FETCH NEXT FROM idoc_cur into @XML_DOC_ID
DEALLOCATE idoc_cur
End
Else
Begin
select @CodeToExecute = xmlcol from xmltable where id = @XML_id
EXEC sp_xml_preparedocument @XML_DOC_ID OUT, @CodeToExecute
end

# miJAzTxPxzOxagqhLtS 3/21/2012 10:58 PM http://plusgigs.com/

olN8VI Very neat blog article.Really looking forward to read more. Much obliged.

# gcKOqX I think this is a real great article post.Really looking forward to read more. Fantastic. 9/19/2012 3:17 PM crork

gcKOqX I think this is a real great article post.Really looking forward to read more. Fantastic.

# Syb1iC I really liked your blog.Really thank you! Awesome. 9/20/2012 4:43 AM cheap seo services

Syb1iC I really liked your blog.Really thank you! Awesome.

# ajryZLaIcejRDoWuKJ 10/20/2012 3:40 PM http://crorkservice.com/

CjKaX5 Im thankful for the article.Much thanks again. Really Cool.

# zaVqCEuZeJE 11/4/2012 4:41 PM http://fiver.com/users/crorkse

je5yqV I loved your post.Much thanks again. Really Cool.

# nfgTwaLcepHi 1/18/2013 6:46 AM http://crork.com/

nkMHQo Really enjoyed this article post.Much thanks again. Want more.

# xcasuuxvFWEBuv 2/1/2013 2:22 AM http://slim-fast.us/

eRbZ5s I cannot thank you enough for the article.Much thanks again. Really Cool.

# YBrDMhOasPziAYyEQXL 2/27/2013 10:39 PM http://clomidnoprescription.be

QDFM0P Im obliged for the post.Really thank you! Really Cool.

# CUXWVrMMaim 4/19/2013 6:35 PM http://crork.com/

doDOMV Really appreciate you sharing this blog.Really thank you! Will read on...

# CUXWVrMMaim 4/19/2013 6:36 PM http://crork.com/

doDOMV Really appreciate you sharing this blog.Really thank you! Will read on...

# BWzfPfnmyjT 4/23/2013 6:25 PM http://crork.com/

xhwqFG Im obliged for the blog.Much thanks again. Will read on...

# tEpQoAvRPDn 5/4/2013 3:59 PM http://crork.com/

RfcMWM Very good article.Really thank you! Really Cool.

# QNIuMHOkNvr 5/14/2013 5:40 AM http://www.insomniacgames.com/

SzDrQF Appreciate you sharing, great post.Much thanks again. Keep writing.

# eEiNMytLRlDifkV 5/24/2013 9:35 PM http://crork.com

2WCKVG I am so grateful for your article.Thanks Again. Awesome.

# CaMXbvLnlT 5/25/2013 6:05 AM http://crork.com

gYVihS Appreciate you sharing, great post.Much thanks again. Keep writing.

# HNLzlzLTyQTDknXeXRF 5/25/2013 3:59 PM http://crork.com

qSoiJW Very good article post.Thanks Again. Keep writing.

# JlFHlFDvfWPq 5/27/2013 4:35 AM http://crork.com/

T08qzm Really appreciate you sharing this blog.Thanks Again. Awesome.

# zuuRvRNmWctjYLFZKOY 5/27/2013 2:25 PM http://crork.com/

H1TyCz Fantastic article post. Really Cool.

# pNHLoVazdTiWXTdUrq 5/27/2013 9:18 PM http://crork.com/

OxD69V I really enjoy the article post.Really looking forward to read more. Cool.

# PmALAJVpfuv 5/29/2013 5:10 AM http://crork.com/

nuve5d Muchos Gracias for your blog article.Thanks Again. Awesome.

# FIgJhYAQFZsVuvuW 5/29/2013 9:08 AM http://crork.com/

e92Ysc Thanks for the blog article.Really thank you! Cool.

Title
Name
Url
Comments
Remember Me?

FAQ #42

last updated:
9/8/2001

Did the information in this faq help answer your question?

Yes
No
Did Not Apply

Survey Results: 163

Yes

N/A


© 2001, 2002, 2003 sqlxml.org