Creating nested xml tree

Question

Creating nested xml tree

Viewing 6 posts - 16 through 20 (of 20 total)

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

one way is to get rid of the Insert @myXML line of your command and then follow the tips on this link:

http://blogs.msdn.com/b/jenss/archive/2009/11/06/an-secret-ssis-xml-destination-provider-you-might-not-found-yet.aspx

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

sauraviit-689328 Old Hand Points: 346 More actions · Answer 2

I am using the same logic......dt_ntext has limit of 2gb

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

sauraviit-689328 (8/15/2011)
I am using the same logic......dt_ntext has limit of 2gb

Then your next best option would be to use a procedural language to represent the data hierarchy in a big object and use serialization to output. Preferrably you'd use Streaming (StreamWriter in C#) to do so so that you don't have to represent the whole thing in memory before outputting it.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Actually - there's one more SSIS option. Create the select as a stored procedure, and call it from an Execute SQL parameter. Capture the output as an OBJECT, then output the object to a raw output file, which should give you the XML.

Objects have no size limit so this will be memory-bound.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 5

I've gotta say it again... ya just gotta love XML tag bloat. All of this data would likely fit in a single well formed, properly delimited, single character delimited file. 😉 Even Hierarchical data would fit easily and without duplication of information.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

Well - to some degree.

I don't care for the bloat either, but you're paying for the portability and the self-describing aspects of the data. I do know a fair amount of techniques to pass hierarchical data using delimiters, but it either requires the recieving system to be intimiately familiar with the incoming file format AND the the data structures, or you're stuck building the same types of constructs XML is providing: entity and level delimiters, "container" markers, strongly typed elements, relationship markers, etc...

The size itself keeps becoming a smaller problem as the toolsets to read, interpret and interact this stuff improve. We have some very good performance out of our XML based data interchanges with some partners, and the self-describing aspect is a help, not a hindrance.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?