InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 26 May 2013 02:14:47 GMT20http://www.sqlservercentral.com/Forums/Topic1266498-2674-1.aspx** 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, @DocSo 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, @docOPENXML (@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.Mon, 31 Dec 2012 18:22:38 GMTsupport-986502http://www.sqlservercentral.com/Forums/Topic1266498-2674-1.aspxThis 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!Mon, 16 Jul 2012 13:23:05 GMTOnan Saladhttp://www.sqlservercentral.com/Forums/Topic1266498-2674-1.aspx[quote][b]Stan Kulp-439977 (3/18/2012)[/b][hr]...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...[/quote]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 :)/RyanSun, 18 Mar 2012 18:57:00 GMTRyan C. Pricehttp://www.sqlservercentral.com/Forums/Topic1266498-2674-1.aspxI'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.Sun, 18 Mar 2012 18:30:33 GMTStan Kulp-439977http://www.sqlservercentral.com/Forums/Topic1266498-2674-1.aspxIt 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[code="sql"]SELECT DISTINCT * FROM [dbo].[sp500hst] AS StockData WHERE [Date] = '20090821' ORDER BY [Ticker]FOR XML PATH('StockData'),ROOT('StockMarketData')[/code]instead of[code="sql"]SELECT DISTINCT * FROM [dbo].[sp500hst] AS StockData WHERE [Date] = '20090821' ORDER BY [Ticker]FOR XML AUTO,ROOT('StockMarketData')[/code]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.Sun, 18 Mar 2012 16:27:28 GMTRyan C. Pricehttp://www.sqlservercentral.com/Forums/Topic1266498-2674-1.aspxI think this is one of the best XML primers I've seen. Thank you for such a great article!Sat, 17 Mar 2012 00:24:04 GMTmishalubahttp://www.sqlservercentral.com/Forums/Topic1266498-2674-1.aspxI'll look for the article..Fri, 16 Mar 2012 15:00:55 GMTCharles Daringerhttp://www.sqlservercentral.com/Forums/Topic1266498-2674-1.aspx[quote][b]Stan Kulp-439977 (3/15/2012)[/b][hr]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.[/quote]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.Fri, 16 Mar 2012 08:28:37 GMTthisisfutilehttp://www.sqlservercentral.com/Forums/Topic1266498-2674-1.aspxMy 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.Thu, 15 Mar 2012 15:23:34 GMTStan Kulp-439977http://www.sqlservercentral.com/Forums/Topic1266498-2674-1.aspx[quote][b]Stan Kulp-439977 (3/15/2012)[/b][hr]I got the data file from EODDATA.http://www.eoddata.com/You will have to ask them.[/quote]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. :-)Thu, 15 Mar 2012 15:00:20 GMTthisisfutilehttp://www.sqlservercentral.com/Forums/Topic1266498-2674-1.aspxI got the data file from EODDATA.http://www.eoddata.com/You will have to ask them.Thu, 15 Mar 2012 14:29:06 GMTStan Kulp-439977http://www.sqlservercentral.com/Forums/Topic1266498-2674-1.aspx[quote]XQuery is usually faster than OpenXML when shredding small XML documents, but slower when shredding large documents.[/quote]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.Thu, 15 Mar 2012 14:12:10 GMTwbrianwhitehttp://www.sqlservercentral.com/Forums/Topic1266498-2674-1.aspxThis 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.Thu, 15 Mar 2012 10:58:13 GMTthisisfutilehttp://www.sqlservercentral.com/Forums/Topic1266498-2674-1.aspxPerhaps then the publishing entity could add such links when publishing. I've seen them before on other articles from SSC.Thu, 15 Mar 2012 10:05:52 GMTaschafferhttp://www.sqlservercentral.com/Forums/Topic1266498-2674-1.aspxThe 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/Thu, 15 Mar 2012 09:32:44 GMTStan Kulp-439977http://www.sqlservercentral.com/Forums/Topic1266498-2674-1.aspxThe next article in this series will show you how to write an SSIS package that performs XSLT transformations.It will be published next Wednesday.Thu, 15 Mar 2012 09:20:19 GMTStan Kulp-439977http://www.sqlservercentral.com/Forums/Topic1266498-2674-1.aspxI 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?Thu, 15 Mar 2012 09:13:26 GMTCharles Daringerhttp://www.sqlservercentral.com/Forums/Topic1266498-2674-1.aspxExcellent! Looking forward for it. And thanks a lot for the series.Thu, 15 Mar 2012 07:43:21 GMTR.P.Rozemahttp://www.sqlservercentral.com/Forums/Topic1266498-2674-1.aspx"1 - the solution shown does not use SQL server to solve the problem, nor can it be automated, so it is often not usable for your target audience, sql server admins/developers."In Part 3 I show how to automate it.Thu, 15 Mar 2012 07:14:44 GMTStan Kulp-439977http://www.sqlservercentral.com/Forums/Topic1266498-2674-1.aspx[quote]- to change the n-th element's value where n is not known before hand (i.e. avoid putting some constant like [1] in the xpath),[/quote]you should use sql:variable function like this:[code="sql"]declare @xml xml='&lt;root&gt;&lt;element&gt;1&lt;/element&gt;&lt;element&gt;2&lt;/element&gt;&lt;/root&gt;'declare @n int=2SET @xml.modify('replace value of (//root/element[position()=sql:variable("@n")]/text())[1] with "12"')select @xml[/code]Wed, 14 Mar 2012 14:41:36 GMTdalexmailboxhttp://www.sqlservercentral.com/Forums/Topic1266498-2674-1.aspx[quote][b]aschaffer (3/14/2012)[/b][hr]Sorry if I missed it, but a request that when offering a part X of Y series of articles, to please include links to the previous articles in the series...just makes it easier to find. I didn't see part 1 and would like to start there before reading part 2. Thanks.[/quote]Here you are : [url=http://www.sqlservercentral.com/articles/XML/87539/]Part 1[/url]Wed, 14 Mar 2012 07:40:34 GMTR.P.Rozemahttp://www.sqlservercentral.com/Forums/Topic1266498-2674-1.aspxSorry if I missed it, but a request that when offering a part X of Y series of articles, to please include links to the previous articles in the series...just makes it easier to find. I didn't see part 1 and would like to start there before reading part 2. Thanks.Wed, 14 Mar 2012 07:11:52 GMTaschafferhttp://www.sqlservercentral.com/Forums/Topic1266498-2674-1.aspxLooking forward for your article on XML DML! I've tried many things, but so far I failed to find any useful patterns to use the .modify() method.My attempts so far with .modify() have been unfruitfull in applying it for anything else than trivial example code. So if you can include some examples on how to use it for example:- to change the n-th element's value where n is not known before hand (i.e. avoid putting some constant like [1] in the xpath), or- to change the value of an element by a value, depending on the current value in that element (again, dynamically, not by specifying something like [@e = "value1"] in the xpath.), or- to change a value in an element in a location in a document that is is not statically known. For example when in the document the elements can be nested to any depth to form a tree and I need to modify only nodes in this tree that match specific criteria, or- to apply .modify() repeatably on a single document to update a set of element's values in the &#100;ocument.Wed, 14 Mar 2012 04:56:36 GMTR.P.Rozemahttp://www.sqlservercentral.com/Forums/Topic1266498-2674-1.aspxYet another tip: the use of OPENROWSET requires permissions many admins will not allow end-users to have on production machines as it opens up many security holes. There is however a way for the admin to setup sql server such that any user can only request the server to open a document the user can read himself, using his own credentials. Using kerberos constrained delegation any user can make the server read any data that he/she can read him/herself. This even works for having the server read the documents from a network share!. Using kerberos contrained delegation the server will never allow to open a document the user does not have access to already via 'normal' file access methods.[url=http://msdn.microsoft.com/en-us/library/ee191523.aspx]This article[/url] describes how to set up [url=http://msdn.microsoft.com/en-us/library/ee191523.aspx]kerberos constrained delegation[/url] on SQL server 2008.Wed, 14 Mar 2012 04:16:31 GMTR.P.Rozemahttp://www.sqlservercentral.com/Forums/Topic1266498-2674-1.aspxNice sum up of the various possibilities indeed.A remark: though I understand you had to keep each section brief because you wanted to cover all topics, I think you could have emphasized a little more on the use of xquery over the openrowset method, as xquery is mostly far superior in performance and memory use over the openrowset method. I found that most articles on the sql & xml subject tend to first list the 'outdated' methods and only after that get to the newer, easier methods. This results in many people giving up reading before they have actually gotten to the part where the easier methods are described. Your's is no exception, sadly.Then, why is the section on converting attributes into elements included in your article? I've got 2 reasons for this question:1 - the solution shown does not use SQL server to solve the problem, nor can it be automated, so it is often not usable for your target audience, sql server admins/developers, and2 - SQL server processes data from attributes a lot faster than it processes elements. Even though I do agree that many xml documents use attributes for the wrong purposes (but that is for different reasons), if the document already has its data in attributes, then why not take advantage of this fact and have SQL server process the data from the attributes directly instead of doing all the pre-processing to convert them into elements and then have a bigger document that processes slower?And a tip: You don't need an external tool to 'tidy' xml data: formatting xml data can easily be done from SSMS: While inside an opened xml document window, the 'Edit' menu contains the 'advanced' -&gt; 'Format document (Ctrl-K, Ctrl-D)' option. This formats the entire xml document, also an option to format just the selected text is available.A second tip: to put xml output by SSMS into a file many people copy from the Results tab and paste the xml text into some other editor, to save it from there. It is far more easy to right-click the column header (output into grid must be on for this, but that is required for almost anything when working with xml) and select 'Save Results As...'. In the file dialog that opens, first change 'Save as type' from the default 'CSV (Comma delimited)' into 'All files', then enter a file name (including the .xml extension) and press Save. This works well for much larger result files than can be copied using copy-paste.Wed, 14 Mar 2012 04:01:21 GMTR.P.Rozemahttp://www.sqlservercentral.com/Forums/Topic1266498-2674-1.aspxI dont understand the point about the doc not being loaded into ram for xpathtaken from your xpath sample,DECLARE @Doc XMLSET @Doc = (SELECT * FROM OPENROWSET(BULK 'C:\xml\StockMarketDataAsElements.xml',SINGLE_BLOB) AS x)has this not loaded the doc into ram?Wed, 14 Mar 2012 03:16:23 GMTadam.everetthttp://www.sqlservercentral.com/Forums/Topic1266498-2674-1.aspxNice article. Please consider the surface well and truly scratched!Wed, 14 Mar 2012 02:10:17 GMTggarethhttp://www.sqlservercentral.com/Forums/Topic1266498-2674-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/XML/87685/">Basics of XML and SQL Server, Part 2: Shredding XML</A>[/B]Tue, 13 Mar 2012 23:47:42 GMTStan Kulp-439977