XML import from a cross-tab Report

Question

XML import from a cross-tab Report

waynef

Old Hand

Points: 364
More actions
June 2, 2017 at 3:01 pm

#338065

Comments posted to this topic are about the item XML import from a cross-tab Report

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 1

Nice article. It would, however, be helpful if you would attach sample data in the "Resources" section so that people can "play" with your good code for additional learning through experimentation. I'm a little surprised that the editors didn't ask for such a thing especially since the source data is quite complex. Fire off a note via the same channels that you submitted the article through with the attachment and they can add it for you.

--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)

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 2

First of all, thank you for this article but with due respect, there are several flaws in it and the result leaves quite a large room for improvements, suggest you follow Jeff's advice and post fully consumable examples in the resource section. Without a tangible example of the data, one cannot but speculate on it and that in itself is not a good practice.
😎
Just as a starter, the reversed engineered XSDs from the SSIS data source are appallingly bad and should never be used but for the simplest of naive XML schema.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 3

The article looks like it could be interesting, but without the actual XML to play with, it really doesn't give me anything to test or try. A picture of part of the text doesn't give us anywhere to go. Also, you mention that your method might not scale well, but don't expand on it. Given the people who already posted on this thread, they may be able to help refine the technique so it will scale well.

BTW, I am your target audience. I'm very fluent in T-SQL, but not in parsing XML. I'm also the type of person to tear something down to its wires to figure out how it works under the hood. Your approach looks intriguing. Could you post the full XML?

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

waynef Old Hand Points: 364 More actions · Answer 4

I've emailed a zip of the xml to the mods, and hope that it can thus be added for your further exploration.

Ed - the point about scalability is simply a caveat: I have no idea if this technique would cope with (say) a For-Each SSIS container with this source query used within it, pointed at inherently large XML files. I have not explored this because - well - it's Spackle. It works, the source files I am using are inherently small, and I have no reason to go any further. YMMV is the best advice.

Eirikur - I have absolutely no doubt that this crude little script could be optimised much further, or substituted for by other techniques. But my client is not interested in paying for any such work, and I am too busy to be interested on my own account. It works, end of story for me. However, the beauty of fora such as this is that others are quite free to go the extra mile and post their improvements.

Steve Jones - SSC Editor SSC Guru Points: 736291 More actions · Answer 5

Steve Jones - SSC Editor

SSC Guru

Points: 736291

June 5, 2017 at 7:54 pm

#1945513

Resources added to the article at the bottom