Cursor Fetch into XML Variable

Question

Cursor Fetch into XML Variable

mhilligas

Ten Centuries

Points: 1354
More actions
November 9, 2009 at 11:03 am

#206322

I have a query run by a CURSOR.
i need to interate thru the rows and assign each row to a XML Variable.
how can I code the Fetch Next instruction to assign the row to an XML Variable.
I tried this, but to no Avail. Generates an error at FOR
fetch next from HeaderCursor into @XML_Header FOR XML RAW('item'), ROOT('items'),ELEMENTS , TYPE)
Any help would be great!
Regards
Michael Hilligas

Viewing 8 posts - 1 through 7 (of 7 total)

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

How about posting your cursor? That would be a big help. Also, the DDL for the tables involved and some sample data would be great. Follow the instructions in this thread for formatting your sample data: http://www.sqlservercentral.com/articles/Best+Practices/61537/"> http://www.sqlservercentral.com/articles/Best+Practices/61537/

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

mhilligas Ten Centuries Points: 1354 More actions · Answer 2

here is my cursor code. Sample data is harder as it is from a view.

Normally I would Fetch into variables but I need to Fetch into an XML Variable

declare HeaderCursor cursor for select distinct BATCH_NUMBER,BATCH_ID,PRODUCT_NUMBER,SAMPLE_GROUP_ID,SAMPLE_GROUP_TS,[USER_NAME],

SAMPLE_ID,SAMPLE_TS,TEST_ID,SAMPLE_VALUE,COMMENT FROM DBO.vwGetLineCheckDataForORACLE where BATCH_NUMBER = @BatchNumber

open HeaderCursor

fetch next from HeaderCursor into @XML_Header FOR XML RAW('item'), ROOT('items'),ELEMENTS , TYPE)

while @@fetch_status = 0

begin

--Convert XML to String and Send String to ORACLE HERE

fetch next from HeaderCursor into @XML_Header FOR XML RAW('item'), ROOT('items'),ELEMENTS , TYPE)

set @XML_Header = NULL

end

close HeaderCursor

deallocate HeaderCursor

mhilligas Ten Centuries Points: 1354 More actions · Answer 3

OOPS...My order is a little screwed up.

I NULL out the XML Variable before I fetch Next

mhilligas Ten Centuries Points: 1354 More actions · Answer 4

I believe we are barking up the wrong tree.

The fetch into does not support Table Variable so I am assuming it will not support XML variable.

If you find something different please let me know.

Thanks

John Rowan SSC Guru Points: 56440 More actions · Answer 5

Why do you need a cursor?

What's wrong with this:

DECLARE @XML_Header xml

SET @XML_Header = (

select distinct

BATCH_NUMBER,

BATCH_ID,

PRODUCT_NUMBER,

SAMPLE_GROUP_ID,

SAMPLE_GROUP_TS,

[USER_NAME],

SAMPLE_ID,

SAMPLE_TS,

TEST_ID,

SAMPLE_VALUE,

COMMENT

FROM DBO.vwGetLineCheckDataForORACLE

FOR XML RAW('item'), ROOT('items'),ELEMENTS , TYPE

)

SELECT @XML_Header

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

LutzM SSC Guru Points: 107049 More actions · Answer 6

What is the reason to send one xml node at a time?

Wouldn't it be easier to convert all rows per batch into one xml variable (matching the node structure, of course) and send this to oracle?

Should be easier than the cursor stuff...

If you could provide some sample data to work on it would be a lot easier... (please see the first link in my signature on how to post sample data.)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

mhilligas Ten Centuries Points: 1354 More actions · Answer 7

Yes That was the original plan.

To send the XML directly to ORACLE.

PROBLEM 1:

The OLEDB providers for ORACLE from Microsoft and ORACLE will not allow an XML variable as a PARAMETER.

Solution was to convert to string:

set @XML_HeaderChar = convert(nvarchar(max),@XML_Header)

This worked very well until......

PROBLEM 2:

To many samples to send and string became over 32,000 characters. ORACLE HATES THAT!!!

Solution was to iterate thru the Samples.

And this is where I am today.

I have solved it by getting a unique identifier from each row with the cursor and requerying using the identifier to assign to the XML variable then convert to string and send to ORACLE.

If i were using 2008 i may be able to send table var as parameter which ORACLE supports, but am unsure if OLEDB providers allow it since it is so new.

Regards

Michael Hilligas