Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Basics of XML and SQL Server, Part 2: Shredding XML Expand / Collapse
Author
Message
Posted Thursday, March 15, 2012 7:43 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 17, 2014 7:32 AM
Points: 412, Visits: 1,412
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?
Post #1267487
Posted Thursday, March 15, 2012 9:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 16, 2012 2:59 PM
Points: 3, 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?
Post #1267578
Posted Thursday, March 15, 2012 9:20 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 6:49 PM
Points: 130, Visits: 942
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.
Post #1267587
Posted Thursday, March 15, 2012 9:32 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 6:49 PM
Points: 130, Visits: 942
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/
Post #1267605
Posted Thursday, March 15, 2012 10:05 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, October 27, 2014 1:58 PM
Points: 309, Visits: 78
Perhaps then the publishing entity could add such links when publishing. I've seen them before on other articles from SSC.
Post #1267627
Posted Thursday, March 15, 2012 10:58 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 11:07 AM
Points: 83, Visits: 757
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.
Post #1267680
Posted Thursday, March 15, 2012 2:12 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 24, 2013 12:02 PM
Points: 42, 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.
Post #1267845
Posted Thursday, March 15, 2012 2:29 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 6:49 PM
Points: 130, Visits: 942
I got the data file from EODDATA.

http://www.eoddata.com/

You will have to ask them.
Post #1267860
Posted Thursday, March 15, 2012 3:00 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 11:07 AM
Points: 83, Visits: 757
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.
Post #1267881
Posted Thursday, March 15, 2012 3:23 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 6:49 PM
Points: 130, Visits: 942
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.
Post #1267897
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse