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


I agree with that. I just wanted to confirm that someone else was also getting the 499 result set. Thanks for the follow-up and very nice article by the way. I don't usually walk through a lot of the steps in these articles but I followed this one all the way through (which is why I was concerned about the 499 results).

Again, thanks for the follow-up.
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'll look for the article..
Misha_SQL
Misha_SQL
SSChasing Mays
SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)

Group: General Forum Members
Points: 607 Visits: 983
I think this is one of the best XML primers I've seen. Thank you for such a great article!



Ryan C. Price
Ryan C. Price
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 1173
It took me a while before I could read the article past where we generate XML with attributes and then transform it using a style sheet.

Maybe I'm missing something, but wouldn't it have been easier to use

SELECT DISTINCT * FROM [dbo].[sp500hst] AS StockData WHERE [Date] = '20090821' ORDER BY [Ticker]
FOR XML PATH('StockData'),ROOT('StockMarketData')



instead of

SELECT DISTINCT * FROM [dbo].[sp500hst] AS StockData WHERE [Date] = '20090821' ORDER BY [Ticker]
FOR XML AUTO,ROOT('StockMarketData')



followed by a transform using external processes, which, while interesting, doesn't really add much to the discussion about XML in SQL?


All my XML work uses elements not attributes (not my choice, if that matters to anyone), which may explain why this jumped out at me.
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'm sure your way is more efficient, but I was not really focused on the most efficient way to do things when writing this tutorial.

I was interested in demonstrating how to do things in as illustrative and understandable a manner as possible.

I broke it into stages just to illustrate it better, and to prepare for the next article in which I automate the transforms in SSIS.

For me, learning how to do something new is the hard part. Once I understand it, I can figure out how to improve it on my own.
Ryan C. Price
Ryan C. Price
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 1173
Stan Kulp-439977 (3/18/2012)
...I was interested in demonstrating how to do things in as illustrative and understandable a manner as possible.

I broke it into stages just to illustrate it better...


That's exactly why I found it a bit distracting to have a whole section called 'Transform the XML document's attributes into elements' in the middle of an otherwise interesting article; While the transform stuff is interesting in its own right; it's a *bit* off-topic and didn't really help explain 'XML Shredding', which was the core of your article.

I'm looking forward to the next installment... I have some XML transforming stuff I do at a particular customer using Task Manager, I'm interested to see if there'll be any merit in using SSIS instead Smile

/Ryan
Onan Salad
Onan Salad
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 93
This article was a great help.

My relational mindset is stubbornly resisting the hierarchical nature of xml and I was having a rough time with even the simplest operations. This article helped me shred a fairly complex xml document that I've been having issues with. My only problem now is that it only seems to be bring in the first instance of a node sequence (ie - if a productID can have multiple itemIDs, my shred only brings in the first ItemID). I suspect this is where the "cross apply" operator comes into play and I'm sure I'll figure it out with some practice.

I'll never be an xml guru, but with the information in this article I no longer consider it an achilles heel. Thanks!
support-986502
support-986502
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 24
** Update**
I went through the example as presented and even though it worked it generated error messages (SQL 2012).
I changed the script to what I thought it should be and the errors went away.
Thanks for taking the time to write the article. I understand the material better than I did yesterday after reading about this topic in one of my books. :-)
** End Update **

Did you make a mistake in the openxml example? w00t
I ask this because you have the following:

EXEC sp_xml_prepareDocument @hdoc OUTPUT, @Doc

So now I have to access the xml data using @hdoc right?
But you do it through @idoc in the following statement.

FROM Openxml (@iDoc,'//StockMarketData/StockData ')

You refer the reader to a Microsoft example which is:

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

OPENXML (@idoc, '/ROOT/Customer',1)

So it would seem that your openxml statement should be:

FROM Openxml (@hdoc, '//StockMarketData/StockData' @iDoc)

Am I correct or am I missing something because I do not understand how you can access the xml data using
@idoc which you initialize to a value of 1? Struggling a little with this.
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