August 20, 2008 at 8:39 am
WayneS (8/20/2008)
Paul DB (8/20/2008)
anil_mootha (8/20/2008)
Hi,Why would you prefer to build XML strings manually when you can do it with FOR XML statement.
The very purpose of FOR XML statement would be void otherwise.
Thanks and regards
Anil
I'm also interested in knowing the answer to Anil's question. Have a great day. 😎
Pure and simple laziness. In the article, I mentioned I was troubleshooting a performance issue. I was checking to see if the problem was in the processing of the xml string (not the creating of it). I just wanted something quick and dirty to build a string so that I could test it. (Turned out to not be so quick... ) I don't use the FOR XML clause that frequently, so when I do I usually need to check it's syntax to get it right.
I am also not familiar with FOR XML. 🙂 Could anyone show us how one of Wayne's queries would look using FOR XML instead? I'd offer 1 point, if I could. 😉
Paul DB
August 20, 2008 at 12:31 pm
Paul DB (8/20/2008)
WayneS (8/20/2008)
Paul DB (8/20/2008)
anil_mootha (8/20/2008)
Hi,Why would you prefer to build XML strings manually when you can do it with FOR XML statement.
The very purpose of FOR XML statement would be void otherwise.
Thanks and regards
Anil
I'm also interested in knowing the answer to Anil's question. Have a great day. 😎
Pure and simple laziness. In the article, I mentioned I was troubleshooting a performance issue. I was checking to see if the problem was in the processing of the xml string (not the creating of it). I just wanted something quick and dirty to build a string so that I could test it. (Turned out to not be so quick... ) I don't use the FOR XML clause that frequently, so when I do I usually need to check it's syntax to get it right.
I am also not familiar with FOR XML. 🙂 Could anyone show us how one of Wayne's queries would look using FOR XML instead? I'd offer 1 point, if I could. 😉
Paul,
Re-read the article. The first two examples show an XML method that use the FOR XML clause.
Wayne
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 20, 2008 at 10:17 pm
Hi Paul,
It looks like nobodys bothered to answer our question. To me till I dont get a satisfactory answer, this topic doesnt make any sense!
Have a wonderful day!
August 21, 2008 at 12:35 am
I really good performance improvement on string concatenation. I guess these problems weren't present in SQL 2000 so much as we didn't have 2GB varchar variables to work with and had to find other solutions 🙁
However there are still merits in looking into other options of rowset manipulation as string concatenation and csv strings should really be a last resort and not part of the main toolset as we've had programmers utilise csv strings to have their code break when commas are included in the data.
As a note I think it should be stated that this code is for SQL 2005 and up.
Thanks for a good article.
August 21, 2008 at 1:18 am
Very useful.
I use it to process a comma delimited list of keys or any other list of values that is passed as a parameter to a stored proc.
Now I do not have to worry about the string being too long, Mine will never be as big as 31k list of values/keys.
My function to split the string up and return a table(that can be used in joins etc) now looks like this :
:angry: See attachment cos I cannot get xml tags to display correctly :blush:
August 21, 2008 at 5:58 am
anil_mootha (8/20/2008)
Hi Paul,It looks like nobodys bothered to answer our question. To me till I dont get a satisfactory answer, this topic doesnt make any sense!
Have a wonderful day!
I thought I had answered the question... I'm sorry if I haven't.
I had seen two questions:
1. Why would anyone build an XML string manually instead of using FOR XML?
2. Could anyone show us how one of Wayne's queries would look using FOR XML instead?
My answers:
1. FOR XML is definitely better to use, as the article demonstrates. I had only used the manual method to build an XML string so that I wouldn't have to read the help for how to use FOR XML for all of the optional parameters. Also, note that if using SQL 2000, I don't think that all of the parameters in the FOR XML can be used, so to get the string in that format I believe that you have to build it manually.
2. In the article, the queries that build the strings for making a comma-delimited list, or the XML string for the 30,000+ items DO use the FOR XML clause. Look at those for the example.
If you still have an unanswered question, can you please restate it so that it can be addressed?
Thanks,
Wayne
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 21, 2008 at 6:08 am
nvanesch (8/21/2008)
I really good performance improvement on string concatenation. I guess these problems weren't present in SQL 2000 so much as we didn't have 2GB varchar variables to work with and had to find other solutions 🙁However there are still merits in looking into other options of rowset manipulation as string concatenation and csv strings should really be a last resort and not part of the main toolset as we've had programmers utilise csv strings to have their code break when commas are included in the data.
As a note I think it should be stated that this code is for SQL 2005 and up.
Thanks for a good article.
I agree with you about being careful when using csv strings. However, sometimes you still have to. For instance, if you are trying to pass a reporting services multi-valued parameter to a stored procedure, you need to do so either by converting it into XML (see article http://www.sqlservercentral.com/articles/Reporting+Services/62731/ ), or by a delimited string. A delimited string should use as the delimiter something that wouldn't appear in the data, such as char(255). What I've done in the article can easily be modified to handle whatever delimiter you desire to use. I just used csv as an example that all would easily understand.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 21, 2008 at 6:18 am
lucian (8/21/2008)
Very useful.I use it to process a comma delimited list of keys or any other list of values that is passed as a parameter to a stored proc.
Now I do not have to worry about the string being too long, Mine will never be as big as 31k list of values/keys.
My function to split the string up and return a table(that can be used in joins etc) now looks like this :
:angry: See attachment cos I cannot get xml tags to display correctly :blush:
If you change ItemVal to StrVal and remove the insert into the temp table, you should be able to just return the data directly from the select clause. This would avoid having to go through an intermediate temp table.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 21, 2008 at 6:19 am
WayneS (8/21/2008)
anil_mootha (8/20/2008)
Hi Paul,It looks like nobody's bothered to answer our question. To me till I don't get a satisfactory answer, this topic doesn't make any sense!
Have a wonderful day!
I thought I had answered the question... I'm sorry if I haven't.
I had seen two questions:
1. Why would anyone build an XML string manually instead of using FOR XML?
2. Could anyone show us how one of Wayne's queries would look using FOR XML instead?
My answers:
1. FOR XML is definitely better to use, as the article demonstrates. I had only used the manual method to build an XML string so that I wouldn't have to read the help for how to use FOR XML for all of the optional parameters. Also, note that if using SQL 2000, I don't think that all of the parameters in the FOR XML can be used, so to get the string in that format I believe that you have to build it manually.
2. In the article, the queries that build the strings for making a comma-delimited list, or the XML string for the 30,000+ items DO use the FOR XML clause. Look at those for the example.
If you still have an unanswered question, can you please restate it so that it can be addressed?
Thanks,
Wayne
I am so much of a noob at using XML in queries that I did not recognize the FOR XML in the statements you posted. :blush: Sorry about that. Glad for your explanations. 🙂
Paul DB
August 21, 2008 at 6:36 am
WayneS (8/21/2008)
lucian (8/21/2008)
Very useful.I use it to process a comma delimited list of keys or any other list of values that is passed as a parameter to a stored proc.
Now I do not have to worry about the string being too long, Mine will never be as big as 31k list of values/keys.
My function to split the string up and return a table(that can be used in joins etc) now looks like this :
:angry: See attachment cos I cannot get xml tags to display correctly :blush:
If you change ItemVal to StrVal and remove the insert into the temp table, you should be able to just return the data directly from the select clause. This would avoid having to go through an intermediate temp table.
But I want to return a table that I can use in a inner join with other tables, or in my where clause.
Ex
--somewhere in my sp
select * from tblCustomer C inner join dbo.delimstrtoTable(@SelctedCustIdList) T on C.Customerid = T.StrVal
or alt.
select * from tblCustomer where CustomerId in(select strval from dbo.DelimStrToTable(@SelctedCustIdList))
August 21, 2008 at 6:49 am
Hi,
Here is a simple example of how I implement XML in sql2005:
declare @XmlList XML
Declare @Property varchar(50)
Declare @ObjectName varchar(50)
Declare @ColumnName varchar(50)
Declare @Value varchar(50)
Declare @rowcount int
Declare @count int
Declare @XmlCount int
set @Count=1
select @XmlList=
'
'
select @XmlCount=CONVERT(varchar,@XmlList.query('count(//Rows/Properties/.)'))
set @rowcount=CONVERT(int,@XmlCount)
WHILE(@count<=@rowcount)
BEGIN
Select
@ObjectName=data.value('@ObjectName[1]','varchar(50)'),
@ColumnName=data.value('@ColumnName[1]','varchar(50)'),
@Property=data.value('@Property[1]','varchar(50)'),
@Value=data.value('@Value[1]','varchar(50)')
from @XmlList.nodes('//Rows/Properties[position()=sql:variable("@count")]') XmlList(data)
set @count=@count+1
select @ObjectName as Object
select @ColumnName as [Column]
select @Property as [Property]
Select @Value as [Value]
END
select @rowcount as count
I usually pass the XML object @XmlList as parameter from app that builds up the XML string. In this case .NET.
Hope this helps,
Argneka
PS: I have not tested on huge data sets, but it works very well for what I use it for.
August 21, 2008 at 6:56 am
Hi,
The XML string is not properly displaying on my previous post. If you would like full example, just send me an email.
Thanks,
Argneka
August 21, 2008 at 10:13 am
lucian (8/21/2008)
But I want to return a table that I can use in a inner join with other tables, or in my where clause.
I understand. I mistakenly thought that you could just return the output from the select statement directly without having to go through the @table variable. (For a very large delimited string, this may cause additional disk activity.)
Wayne
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 21, 2008 at 2:58 pm
Just to answer the rhetorical questions about building xml by wrapping tag constants around data, rather than use a "For XML" statement, we should remember that a lot of code was generated before SQL Server had native XML support. Code that isn't broken isn't revisted every time a new capability is introduced. That said, I just had to test out "For XML" against simple string concatenation to see the performance difference. I was stunned by the difference in CPU efficiency.
Using the following:
select @CSV = (select ',' + cast(n as varchar(6)) from dbo.Tally where N <=20000 FOR XML PATH('') )
CPU Time was 20 seconds
Using the "traditional"
set @csv = ''
select @CSV = @csv+',' + cast(n as varchar(6))
from dbo.Tally where N <=20000
CPU Time was 1219 seconds.
set @csv = ''
select @CSV = ',' + cast(n as varchar(6)) +@css
from dbo.Tally where N <=20000
CPU time was 580 MS, but the numbers are backwards
I'm sold. A couple of our core functions used to generate parsed strings are about to be modified. Good article. Good to know.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 21, 2008 at 3:24 pm
bhovious (8/21/2008)
Just to answer the rhetorical questions about building xml by wrapping tag constants around data, rather than use a "For XML" statement, we should remember that a lot of code was generated before SQL Server had native XML support. Code that isn't broken isn't revisted every time a new capability is introduced.
Excellent point.
That said, I just had to test out "For XML" against simple string concatenation to see the performance difference. I was stunned by the difference in CPU efficiency.
Using the following:
select @CSV = (select ',' + cast(n as varchar(6)) from dbo.Tally where N <=20000 FOR XML PATH('') )
CPU Time was 20 seconds
Using the "traditional"
set @csv = ''
select @CSV = @csv+',' + cast(n as varchar(6))
from dbo.Tally where N <=20000
CPU Time was 1219 seconds.
A whole 20 minutes faster! Fantastic! (well, 1 second short of 20 minutes...)
set @csv = ''
select @CSV = ',' + cast(n as varchar(6)) +@css
from dbo.Tally where N <=20000
CPU time was 580 MS, but the numbers are backwards
I assume the @css is @csv...
Is your cpu time so short the second time around because you had just cached it from the first time? 20,000 numbers aren't going to take up a lot of memory.
I'm sold. A couple of our core functions used to generate parsed strings are about to be modified. Good article. Good to know.
I'm glad it helped someone out.
Wayne
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply