August 3, 2010 at 6:58 am
tom 29037 (8/2/2010)
I have to admit that I don't quite get his explaination, but I also haven't got a chance to play with that code yet. I'll be using the example on my real and much more complex tables, so I'm sure it will be a bit of a challege for me to incorporate this solution.
If you don't understand, don't be afraid to ask. IMO, if you're planning on using this code (or any code that you didn't write yourself), then you must be able to explain what it's doing. Don't use it until you get your questions answered. If you're trying to understand, you won't get attacked. (But, if you're not trying....)
Edit: you might want to check out this article[/url] that I wrote a while back - it goes through using FOR XML PATH with some examples that might help you understand a bit faster.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 3, 2010 at 9:30 am
I'll try an example that you can follow in your SQL window 🙂
First, set your results to text and execute this: -
SELECT TOP 5 t1.[name]
FROM sys.columns t1
INNER JOIN sys.columns t2 on t1.[column_id] = t2.[column_id]
WHERE t1.[column_id] = t2.[column_id]
FOR XML PATH
You'll get back something like this: -
<row><name>addr</name></row><row><name>addr</name></row><row><name>addr</name></row><row><name>addr</name></row><row><name>addr</name></row>
Next, we'll elimate the row tags, which is done simply by adding ('')
SELECT TOP 5 t1.[name]
FROM sys.columns t1
INNER JOIN sys.columns t2 on t1.[column_id] = t2.[column_id]
WHERE t1.[column_id] = t2.[column_id]
FOR XML PATH('')
<name>addr</name><name>addr</name><name>addr</name><name>addr</name><name>addr</name>
Now, we'll add a comma that comes before the result. This makes the result into a string.
SELECT TOP 5 ',' + t1.[name]
FROM sys.columns t1
INNER JOIN sys.columns t2 on t1.[column_id] = t2.[column_id]
WHERE t1.[column_id] = t2.[column_id]
FOR XML PATH('')
,addr,addr,addr,addr,addr
STUFF allows us to get rid of the comma at the beginning, giving us a list.
SELECT Stuff((SELECT TOP 5 ',' + t1.[name]
FROM sys.columns t1
INNER JOIN sys.columns t2
ON t1.[column_id] = t2.[column_id]
WHERE t1.[column_id] = t2.[column_id]
FOR XML PATH('')), 1, 1, '')
addr,addr,addr,addr,addr
August 3, 2010 at 9:51 am
Wow again! I'll defnitely be using that. Thanks for breaking it down so I can see exactly what that statement is doing. I'm a 1 man band here where I'm at, and it's nice to be able to ask someone for pointers.
A side note and off this particular topic (maybe I should start a new thread) but I just upgraded our SQL Server from 2008 Express R2 to 2008 Enterprise R2 and now my Intellisense stopped working (from my workstation.) I found some info on it on Google. Most everyone said to reinstall at the workstation, which I know is a shot in the dark. I did try it, and it didn't work. It's not critical and I can live without it, but it sure is annoying.
August 3, 2010 at 10:08 am
tom 29037 (8/3/2010)
A side note and off this particular topic (maybe I should start a new thread) but I just upgraded our SQL Server from 2008 Express R2 to 2008 Enterprise R2 and now my Intellisense stopped working (from my workstation.) I found some info on it on Google. Most everyone said to reinstall at the workstation, which I know is a shot in the dark. I did try it, and it didn't work. It's not critical and I can live without it, but it sure is annoying.
Try this
August 3, 2010 at 10:12 am
ya... that's all the stuff I found on Google. No go. I'll sort it out later. No big deal. Thanks again.
August 3, 2010 at 2:19 pm
Funny thing happened to me today...
This afternoon I installed a couple of updates on our SQL Server and after it rebooted... lo and behold.... IntelliSense is working. I guess it makes a bit of sense. That was the first reboot since the upgrade from SQL Express.
August 3, 2010 at 8:27 pm
WayneS (8/3/2010)
ColdCoffee (8/2/2010)
Wayne, awesome job in that explanation , mate! Thanks.. Hope the OP now and the other poster got what the code exactly does.Also i remember a thread where RBarryYoung takes a dig at explaining this; I lost that thread details.. that was one awesome long thread analysing in great depths on types of row concatenation methods..
Once again, thanks for lucid explanation!
Thanks. I've found that once you do understand what it's doing, then it all makes sense. And, most importantly, you can explain how it works to others.
Which brings up a pet peeve of mine... DO NOT just copy code from here (ANYWHERE), and put it into your systems without understanding what it does. If you don't understand it, ask.
Hey Wayne, i knew what the code was doing , mate. If not for your wonderful explanation i would have given the explanation. But in any case, your explanantion is surely better than what i had in my mind for explaining...
@skcadavre, awesome job man.. that was what exactly RBarryYoung used to explain in the other thread.. Thanks..
@tom, hope u are clear with what that code doesl; You know, that code, which uses XML, i used it to make a SP run for few seconds which actually took me hours! Hmmm...
August 3, 2010 at 8:45 pm
ColdCoffee (8/3/2010)
WayneS (8/3/2010)
ColdCoffee (8/2/2010)
Wayne, awesome job in that explanation , mate! Thanks.. Hope the OP now and the other poster got what the code exactly does.Also i remember a thread where RBarryYoung takes a dig at explaining this; I lost that thread details.. that was one awesome long thread analysing in great depths on types of row concatenation methods..
Once again, thanks for lucid explanation!
Thanks. I've found that once you do understand what it's doing, then it all makes sense. And, most importantly, you can explain how it works to others.
Which brings up a pet peeve of mine... DO NOT just copy code from here (ANYWHERE), and put it into your systems without understanding what it does. If you don't understand it, ask.
Hey Wayne, i knew what the code was doing , mate. If not for your wonderful explanation i would have given the explanation. But in any case, your explanantion is surely better than what i had in my mind for explaining...
@cc - somewhere along the line, I guess I got confused and thought I was talking to the OP. I know that you knew what your code was doing. Sorry about that!
Edit: BTW, I really like the .value('.','VARCHAR(MAX)') part!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 3, 2010 at 9:31 pm
WayneS (8/3/2010)
ColdCoffee (8/3/2010)
WayneS (8/3/2010)
ColdCoffee (8/2/2010)
Wayne, awesome job in that explanation , mate! Thanks.. Hope the OP now and the other poster got what the code exactly does.Also i remember a thread where RBarryYoung takes a dig at explaining this; I lost that thread details.. that was one awesome long thread analysing in great depths on types of row concatenation methods..
Once again, thanks for lucid explanation!
Thanks. I've found that once you do understand what it's doing, then it all makes sense. And, most importantly, you can explain how it works to others.
Which brings up a pet peeve of mine... DO NOT just copy code from here (ANYWHERE), and put it into your systems without understanding what it does. If you don't understand it, ask.
Hey Wayne, i knew what the code was doing , mate. If not for your wonderful explanation i would have given the explanation. But in any case, your explanantion is surely better than what i had in my mind for explaining...
@cc - somewhere along the line, I guess I got confused and thought I was talking to the OP. I know that you knew what your code was doing. Sorry about that!
Edit: BTW, I really like the .value('.','VARCHAR(MAX)') part!
Ah no Wayne, pls dont be sorry.. u are one of my heros in SQL.. i would consider any interactions with you as a real treat for myself. Thanks for this, mate !
That VARCHAR was a trick i learnt here.. my memory isnt serving me better on who introduced it, but i got what VARCHAR(MAX) did as soon as i saw it and i was also amazed by how that negates the special characters during XML-ing! Nice thought!
August 4, 2010 at 7:10 am
ColdCoffee (8/3/2010)
Ah no Wayne, pls dont be sorry.. u are one of my heros in SQL.. i would consider any interactions with you as a real treat for myself. Thanks for this, mate !That VARCHAR was a trick i learnt here.. my memory isnt serving me better on who introduced it, but i got what VARCHAR(MAX) did as soon as i saw it and i was also amazed by how that negates the special characters during XML-ing! Nice thought!
:blush: I hope that I can live up to this...I've still got a lot to learn myself!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply