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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Stan Kulp » Basics of XML and SQL Server, Part 2:...

28 posts, Page 2 of 3

««1 2 3 »»

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

Rate Topic

Display Mode

Topic Options

Author

Message

R.P.Rozema

Posted Thursday, March 15, 2012 7:43 AM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, June 13, 2013 12:42 AM
Points: 406, Visits: 1,367

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

Charles Daringer

Posted Thursday, March 15, 2012 9:13 AM

Forum 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

Stan Kulp-439977

Posted Thursday, March 15, 2012 9:20 AM

SSC Journeyman

Group: General Forum Members
Last Login: Today @ 3:24 PM
Points: 80, Visits: 627

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

Stan Kulp-439977

Posted Thursday, March 15, 2012 9:32 AM

SSC Journeyman

Group: General Forum Members
Last Login: Today @ 3:24 PM
Points: 80, Visits: 627

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

aschaffer

Posted Thursday, March 15, 2012 10:05 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, January 28, 2013 3:28 PM
Points: 296, Visits: 58

Perhaps then the publishing entity could add such links when publishing. I've seen them before on other articles from SSC.

Post #1267627

thisisfutile

Posted Thursday, March 15, 2012 10:58 AM

Valued Member

Group: General Forum Members
Last Login: Friday, June 14, 2013 10:11 AM
Points: 56, Visits: 492

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

wbrianwhite

Posted Thursday, March 15, 2012 2:12 PM

SSC 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

Stan Kulp-439977

Posted Thursday, March 15, 2012 2:29 PM

SSC Journeyman

Group: General Forum Members
Last Login: Today @ 3:24 PM
Points: 80, Visits: 627

I got the data file from EODDATA.

http://www.eoddata.com/

You will have to ask them.

Post #1267860

thisisfutile

Posted Thursday, March 15, 2012 3:00 PM

Valued Member

Group: General Forum Members
Last Login: Friday, June 14, 2013 10:11 AM
Points: 56, Visits: 492

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. Smile

Post #1267881

Stan Kulp-439977

Posted Thursday, March 15, 2012 3:23 PM

SSC Journeyman

Group: General Forum Members
Last Login: Today @ 3:24 PM
Points: 80, Visits: 627

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 »

28 posts, Page 2 of 3

««1 2 3 »»

Permissions