SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


XML import from a cross-tab Report


XML import from a cross-tab Report

Author
Message
waynef
waynef
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 44
Comments posted to this topic are about the item XML import from a cross-tab Report
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (337K reputation)SSC Guru (337K reputation)SSC Guru (337K reputation)SSC Guru (337K reputation)SSC Guru (337K reputation)SSC Guru (337K reputation)SSC Guru (337K reputation)SSC Guru (337K reputation)

Group: General Forum Members
Points: 337222 Visits: 42591
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62851 Visits: 19796
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.
Cool
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
Ed Wagner
SSC Guru
SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)

Group: General Forum Members
Points: 93598 Visits: 11140
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
waynef
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 44
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
Steve Jones
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: Administrators
Points: 222638 Visits: 19618
Resources added to the article at the bottom

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search