Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Basics of XML and SQL Server, Part 2: Shredding XML


Basics of XML and SQL Server, Part 2: Shredding XML

Author
Message
R.P.Rozema
R.P.Rozema
Mr or Mrs. 500
Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)

Group: General Forum Members
Points: 517 Visits: 1678
Excellent! Looking forward for it. And thanks a lot for the series.



Posting Data Etiquette - Jeff Moden
Posting Performance Based Questions - Gail Shaw
Hidden RBAR - Jeff Moden
Cross Tabs and Pivots - Jeff Moden
Catch-all queries - Gail Shaw


If you don't have time to do it right, when will you have time to do it over?
Charles Daringer
Charles Daringer
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 20
I have a question, if you loaded the sql initially via stream not from the file system and wanted to perform a XSLT2.0 transformation within SQL Server from a procedure and then grab the result of that procedure via a stream could you do this?

Can the XSLT transformation be accomplished within SQL Server?
Stan Kulp-439977
Stan Kulp-439977
SSC Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

Group: General Forum Members
Points: 254 Visits: 1093
The next article in this series will show you how to write an SSIS package that performs XSLT transformations.

It will be published next Wednesday.
Stan Kulp-439977
Stan Kulp-439977
SSC Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

Group: General Forum Members
Points: 254 Visits: 1093
The first five articles in this series were accepted before any of them were published. I don't know the URL of a published article until it is actually published. Once an article is accepted, I can make no further edits, so I can't add the URL to the previous article.

What you can do is click on the author's name under the title of an article to see all the articles written by that author.

http://www.sqlservercentral.com/Authors/Articles/Stan_Kulp/439977/
aschaffer
aschaffer
Old Hand
Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)

Group: General Forum Members
Points: 314 Visits: 86
Perhaps then the publishing entity could add such links when publishing. I've seen them before on other articles from SSC.
thisisfutile
thisisfutile
SSC-Enthusiastic
SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)

Group: General Forum Members
Points: 199 Visits: 973
This is a silly question but curiousity is getting the best of me. Am I the only one that got 499 results? Since we're talking about the S&P 500, I'm assuming that 500 records would be present but my result set was 499.
wbrianwhite
wbrianwhite
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 152
XQuery is usually faster than OpenXML when shredding small XML documents, but slower when shredding large documents.


In some recent testing I found that with a document only about 30 columns wide and 150 rows long, that if I ran the same shred in both XQuery and OpenXml in one query, the XQuery took 100% of the query cost. I was blown away by the performance hit. The query plan clearly shows that sql server creates a table per XQuery operation, and then has to inner join all 30 tables to get you your result table. After reading up on it I was able to spend hours adding some complicated middle steps to the XQuery version that dramatically sped it up. Now it only took 96% of the query cost! Needless to say, I will just use openXml in almost all situations now. The only time XQuery is worth it is when you're dealing with messy user data where you might have ampersands and other non-allowable characters in the text, the xml datatype happily swallows a whole slew of errors that make openXml blow up.
Stan Kulp-439977
Stan Kulp-439977
SSC Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

Group: General Forum Members
Points: 254 Visits: 1093
I got the data file from EODDATA.

http://www.eoddata.com/

You will have to ask them.
thisisfutile
thisisfutile
SSC-Enthusiastic
SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)

Group: General Forum Members
Points: 199 Visits: 973
Stan Kulp-439977 (3/15/2012)
I got the data file from EODDATA.

http://www.eoddata.com/

You will have to ask them.


As far as I know the S&P may only track 499 and use "500" because it just sounds better. I wanted to make sure your outlined process wasn't truncating the import for some strange reason. :-)
Stan Kulp-439977
Stan Kulp-439977
SSC Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

Group: General Forum Members
Points: 254 Visits: 1093
My bet is that during the course of a year they lose some through mergers, bankruptcies, etc. and have to replace them. It may take a while to find suitable replacements.

I played with that data quite a bit, and I have no reason to believe that the 499 count is wrong.
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