Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!
Learn how Concerto can help you.
Solved
Concatenating with FOR XML PATH('') while dealing with special XML characters
Posted on 2009-05-12
Hello fellow experts. I figured it was time to ask a question so here it goes.
The goal is that for each A in table #t, there will be a single returned value which is the concatenation of all values in column b. You will see that I am using a correlated sub-select to get the values, and everything works well for #t.a=2. But since #t.a=1 has the & < and carriage return line feed in the data, the FOR XML PATH('') is converting them into their XML hex values respectively.
Adding a group by will ensure that each #t.a is only returned once, that's not my concern.
So my question is this. Come up with a creative, and efficient, way to return a single value for each #t.a that HOPEFULLY does not use a nested replace like I am doing in the third column below.
Points for effort, creativity and participation WILL be given.
Please do NOT suggest a cursor, while loop, or other RBAR based solution. This HAS to be a single statement and PREFERABLY without the use of a UDF.
Thanks fellow experts and I look forward to your responses.
The goal is that for each A in table #t, there will be a single returned value which is the concatenation of all values in column b. You will see that I am using a correlated sub-select to get the values, and everything works well for #t.a=2. But since #t.a=1 has the & < and carriage return line feed in the data, the FOR XML PATH('') is converting them into their XML hex values respectively.
Adding a group by will ensure that each #t.a is only returned once, that's not my concern.
So my question is this. Come up with a creative, and efficient, way to return a single value for each #t.a that HOPEFULLY does not use a nested replace like I am doing in the third column below.
Points for effort, creativity and participation WILL be given.
Please do NOT suggest a cursor, while loop, or other RBAR based solution. This HAS to be a single statement and PREFERABLY without the use of a UDF.
Thanks fellow experts and I look forward to your responses.
create table #t
(a int
,b nvarchar(20)
)
go
insert into #t values(1,'a&b')
insert into #t values(1,'c<
d')
insert into #t values(2,'wx')
insert into #t values(2,'yz')
go
select a,b,
(select b+N''
from #t
where a=t.a
for xml path('')
) 'WITH xml CHARACTERS'
,replace(
replace(
replace(
replace(
replace(
replace(
replace(
(select b+N''
from #t
where a=t.a
for xml path('')
)
,' ',char(20))
,'&','&')
,'<','<')
,'>','>')
,'
',char(10))
,'
',char(13))
,'	',char(9)) 'With XML Replaced'
from #t t
go
drop table #t
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
10
8
5- +2
26 Comments
you can solve that by casting the string into varbinary format, and then after the select statement, cast it back
look for my comment here
http://sqlservernation.com/blogs/147.aspx
it has an example of it
look for my comment here
http://sqlservernation.com/blogs/147.aspx
it has an example of it
If you could, show me as a single select returning the following results. Because for me it doesn't seem to work without assigning it to an XML variable, and using VARIABLE.value which can't be processed as a single statement select.
The desired output should be:
a
---------- ------------
1 a&bc<
d
1 a&bc<
d
2 wxyz
2 wxyz
The desired output should be:
a
---------- ------------
1 a&bc<
d
1 a&bc<
d
2 wxyz
2 wxyz
Active 1 day ago
Sad but true... Ideally need to replace first (put place holders there), and after. Or maybe resort to casting as varbinary first and after.
What you are currently doing is converting to string, so that XML is now varchar effectively.
One of the other subtleties / challenge with replace is that it will insert NULL characters into the string which can sometimes cause a few problems. For example replacing 4 char with 1 means three NULLS - it is doing a replace, not rewriting the string.
Will have a think of something to replace the replace...
What you are currently doing is converting to string, so that XML is now varchar effectively.
One of the other subtleties / challenge with replace is that it will insert NULL characters into the string which can sometimes cause a few problems. For example replacing 4 char with 1 means three NULLS - it is doing a replace, not rewriting the string.
Will have a think of something to replace the replace...
perhaps this will explain it better with something that is easier to grasp than some fictional test data.
The "requirement":
List all objects in tempdb whose names start with "BG_Objects" and include a comma delimited list of their column names. Do so in a single statement (CTE's OK) without looping through records to build the concatenated field.
The results for the second column should be "[a],[b],[a & b]," and not "[a],[b],[a & b],".
BTW, I am perfectly capable of accepting the "you can't do that" answer if that is the answer. Heck, I can't think of a way.
FYI... I will be leaving this question open for 3 days, or until a working solution is found. At which time I will be awarding points for valid participation in the discussion with more going to the first working solution provider.
The "requirement":
List all objects in tempdb whose names start with "BG_Objects" and include a comma delimited list of their column names. Do so in a single statement (CTE's OK) without looping through records to build the concatenated field.
The results for the second column should be "[a],[b],[a & b]," and not "[a],[b],[a & b],".
BTW, I am perfectly capable of accepting the "you can't do that" answer if that is the answer. Heck, I can't think of a way.
FYI... I will be leaving this question open for 3 days, or until a working solution is found. At which time I will be awarding points for valid participation in the discussion with more going to the first working solution provider.
use tempdb
go
create table #BG_Objects1 (a int,b int,c int)
create table #BG_Objects2 (a int,b int,[a & b] as a+b)
go
select o.name as oname,(select c.name + ',' from sys.columns c where o.object_id=c.object_id for xml path('')) cnames
from sys.objects o
where o.name like '#BG_Objects%'
go
drop table #BG_Objects1
drop table #BG_Objects2
Active 1 day ago
well, a bit shirty, no xml (and who said I am a fan ? ) BUT, it is a single statement, it does the concat...
use tempdb
go
create table #BG_Objects1 (a int,b int,c int)
create table #BG_Objects2 (a int,b int,[a & b] as a+b)
go
;with cte_concat as
(
select (select max(ordinal_position) from information_schema.columns c where c.table_name =i.table_name) max_num,
ordinal_position, convert(varchar(1000),column_name) as column_name, table_name
from information_schema.columns i where table_name like '#bg_objects%' and ordinal_position = 1
union all
select max_num,i.ordinal_position, convert(varchar(1000),rtrim(c.column_name) + ',' + rtrim(i.column_name)), c.table_name
from cte_concat c
inner join information_schema.columns i on c.ordinal_position + 1 = i.ordinal_position and c.table_name = i.table_name
)
select table_name,column_name from cte_concat where ordinal_position = max_num
go
drop table #BG_Objects1
drop table #BG_Objects2
Active 1 day ago
Bugga, forgot the []...
use tempdb
go
create table #BG_Objects1 (a int,b int,c int)
create table #BG_Objects2 (a int,b int,[a & b] as a+b)
go
;with cte_concat as
(
select (select max(ordinal_position) from information_schema.columns c where c.table_name =i.table_name) max_num,
ordinal_position, convert(varchar(1000),'['+column_name+']') as column_name, table_name
from information_schema.columns i where table_name like '#bg_objects%' and ordinal_position = 1
union all
select max_num,i.ordinal_position, convert(varchar(1000),rtrim(c.column_name) + ',[' + rtrim(i.column_name)+']'), c.table_name
from cte_concat c
inner join information_schema.columns i on c.ordinal_position + 1 = i.ordinal_position and c.table_name = i.table_name
)
select table_name,column_name from cte_concat where ordinal_position = max_num
go
drop table #BG_Objects1
drop table #BG_Objects2
Good deal mark. I didn't have a incrementing value like the ordinal position in my data set, so I had to fake it by first running a partitioned row_number, then a nested CTE to build the strings.
Any other suggestions from anyone?
Any other suggestions from anyone?
Just use gloves: http://thedailywtf.com/Articles/The_Complicator_0x27_s_Gloves.aspx
T-SQL has limits, you can trick it into doing some amazing things, however, that isn't it's strength. IE: http://thedailywtf.com/articles/stupid-coding-tricks-the-tsql-madlebrot.aspx
Assuming this data is being consumed by a data access layer why don't you concatenate the data there instead of in the database?
T-SQL has limits, you can trick it into doing some amazing things, however, that isn't it's strength. IE: http://thedailywtf.com/articles/stupid-coding-tricks-the-tsql-madlebrot.aspx
Assuming this data is being consumed by a data access layer why don't you concatenate the data there instead of in the database?
Active 1 day ago
Well tedbilly, that certainly is very very interesting. If T-SQL can do fractals (or pretend to), then surely a simple concatenation isn't beyond the realms, just needs more thinking....
Love the gloves story. So very true, so very often...
Love the gloves story. So very true, so very often...
What I tell the team I manage (we build n-tier applications) is to make sure all SQL statements are as short as possible with minimal CPU cost. Only do the bare minimum in the database to protect the data and move all complex programing out to the middle tiers. The reason being is practical for a number of reasons, for one, it minimizes the risk of locking, but the big key is that it's easier to add more non-SQL hardware.
MS SQL doesn't have load balancing clusters, you are stuck with one instance of SQL, however I can add as many web or application servers as I like with a solid design. Therefore I move as much CPU processing as I can off the SQL server onto the other servers.
Cheers
MS SQL doesn't have load balancing clusters, you are stuck with one instance of SQL, however I can add as many web or application servers as I like with a solid design. Therefore I move as much CPU processing as I can off the SQL server onto the other servers.
Cheers
tedbilly:
Thanks, but I think you missed the point of this exercise. I guess it wasn't enough to say "Please do NOT suggest a cursor, while loop, or other RBAR based solution. This HAS to be a single statement and PREFERABLY without the use of a UDF.". I should have also said not to suggest pulling it out of the database.
You are making an incorrect assumption that there is a front end to this component. This is a database task that has to build a single value from a one-to-many value pair, for numerous items at one time. I don't want to go through each item and use a variable to do the FOR XML PATH('') statement because the potential exists for there to be a high number of top level items, each having 0-X number of values to be concatenated into a single value per item.
Locking is not a concern as this is processed in indexed (non-#) temp tables on 16x servers with 96GB of RAM sitting on a multi-petabyte SAN.
B
Thanks, but I think you missed the point of this exercise. I guess it wasn't enough to say "Please do NOT suggest a cursor, while loop, or other RBAR based solution. This HAS to be a single statement and PREFERABLY without the use of a UDF.". I should have also said not to suggest pulling it out of the database.
You are making an incorrect assumption that there is a front end to this component. This is a database task that has to build a single value from a one-to-many value pair, for numerous items at one time. I don't want to go through each item and use a variable to do the FOR XML PATH('') statement because the potential exists for there to be a high number of top level items, each having 0-X number of values to be concatenated into a single value per item.
Locking is not a concern as this is processed in indexed (non-#) temp tables on 16x servers with 96GB of RAM sitting on a multi-petabyte SAN.
B
Based on your requirements I think you need to use a UDF. I've seen articles using the XML PATH as a mechanism to do what you are attempting and they all have the same design flaw. Calling REPLACE to fix the strings.
XPATH is doing exactly what it was designed to do. Just for the fun of it, try the UDF and compare the performance on a large table against using the XML PATH without using the replace.
I am surprised that a database application like this exists without any other tiers. The data must come from somewhere and be used somewhere else outside of the database. I'll have to trust you on that one.
XPATH is doing exactly what it was designed to do. Just for the fun of it, try the UDF and compare the performance on a large table against using the XML PATH without using the replace.
I am surprised that a database application like this exists without any other tiers. The data must come from somewhere and be used somewhere else outside of the database. I'll have to trust you on that one.
Active 1 day ago
And I was expecting a bit of a debate - sheesh...
Question, are the resulting output columns predictable and consistant ? Not meaning the input, or the source data, but the final result set ?
Question, are the resulting output columns predictable and consistant ? Not meaning the input, or the source data, but the final result set ?
"I am surprised that a database application like this exists without any other tiers. The data must come from somewhere and be used somewhere else outside of the database. I'll have to trust you on that one."
Here's more of an explanation.
The process is invoked on the set of data for which a user has chosen to perform certain actions. The action being taken, is the generation of NEW data from existing data.
So the user is presented with a list of the various data components that have been defined in their system. For this example, "Field A" is a one-to-one field to the base objects, "Field B" is a one to many field to the base objects, "Field C" is a one to many object to the base objects and "Date A" is a one-to-many field to the base objects.
Now, you have Base object 1 and 2.
Base Object 1:
Field Value
Field A: "Ted"
Field B: "ABC"
"XYZ"
Field C: "Dell"
"HP"
"IBM"
Date A: "2009-05-15"
"2009-05-14"
"2009-05-13"
"2009-05-12"
"2009-05-11"
Base Object 2:
Field Value
Field A: "Brandon"
Field B: "Hello"
Field C: "Dell"
"IBM"
Date A: "2009-04-12"
"2009-04-11"
"2009-04-10"
"2009-04-09"
Base Object 3:
Field Value
Field A: "Mark"
Field B: "Experts-exchange.com"
"hire-an-expert.com"
"sqlservernation.com"
Field C: "Dell"
Date A: "2009-04-11"
Now let's say the user wants to create some new field. That field is a composite field. I will use ++ to show concatenation and "" to indicate free text that they can supply
Field A++" - "++Field B(MIN)++" - "++Field C(ALL, Text qualifier ", Delimiter ;)++" - "++Date A(max)
So the value would be:
Object 1.
Ted - ABC - "Dell";"HP";"IBM" - 2009-05-15
Object 2.
Brandon - Hello - "Dell";"IBM" - 2009-04-12
Object 3.
Mark - Experts-exchange.com - Dell - 2009-04-11
Now let's pretend that there are 230K base objects, that some of the fields have 30-40 values, and that their composite field is FAR more complex with formatting options on date fields and other fields.
Here's more of an explanation.
The process is invoked on the set of data for which a user has chosen to perform certain actions. The action being taken, is the generation of NEW data from existing data.
So the user is presented with a list of the various data components that have been defined in their system. For this example, "Field A" is a one-to-one field to the base objects, "Field B" is a one to many field to the base objects, "Field C" is a one to many object to the base objects and "Date A" is a one-to-many field to the base objects.
Now, you have Base object 1 and 2.
Base Object 1:
Field Value
Field A: "Ted"
Field B: "ABC"
"XYZ"
Field C: "Dell"
"HP"
"IBM"
Date A: "2009-05-15"
"2009-05-14"
"2009-05-13"
"2009-05-12"
"2009-05-11"
Base Object 2:
Field Value
Field A: "Brandon"
Field B: "Hello"
Field C: "Dell"
"IBM"
Date A: "2009-04-12"
"2009-04-11"
"2009-04-10"
"2009-04-09"
Base Object 3:
Field Value
Field A: "Mark"
Field B: "Experts-exchange.com"
"hire-an-expert.com"
"sqlservernation.com"
Field C: "Dell"
Date A: "2009-04-11"
Now let's say the user wants to create some new field. That field is a composite field. I will use ++ to show concatenation and "" to indicate free text that they can supply
Field A++" - "++Field B(MIN)++" - "++Field C(ALL, Text qualifier ", Delimiter ;)++" - "++Date A(max)
So the value would be:
Object 1.
Ted - ABC - "Dell";"HP";"IBM" - 2009-05-15
Object 2.
Brandon - Hello - "Dell";"IBM" - 2009-04-12
Object 3.
Mark - Experts-exchange.com - Dell - 2009-04-11
Now let's pretend that there are 230K base objects, that some of the fields have 30-40 values, and that their composite field is FAR more complex with formatting options on date fields and other fields.
How does the user interact with the database? Is there nothing inbetween or do they fire a stored procedure directly?
They click a button on an asp.net page, then they log out and walk away. The entire process that is initiated from the time they click that button can take anywhere from 30 seconds to several hours. Depending upon the number of base objects they have chosen to include in this action. The complexity of the "new data" generation also is a factor, but the database processing won't even be the long part of the process. The long part will be that each base object will, after database processing has been completed, have one or more files generated in the file system for it.
The files may be named after one of these "new data" values. They will also contain up to 6, user defined "new data" values that must be prepared prior to the file operation starting.
Our system is a VERY database heavy application. We do database caching of searches so that searches are available when a user gets bounced to a different web server in a web farm. Like I said our db servers are each usually 16 cores with 96GB of ram. A typical database is about 40GB, with them being as large as 150GB.
So again, I will ask that if you want to participate in this fun little experiment, that you abide by my requests and stop telling me what I should and should not be doing and focus on what I have asked.
Mark has already set me in the right direction and I have developed a working solution that does not use xml concatenation. I am leaving this open for now in hopes of getting some of the great DB minds at EE to throw in their input.
Thanks,
BrandonGalderisi
The files may be named after one of these "new data" values. They will also contain up to 6, user defined "new data" values that must be prepared prior to the file operation starting.
Our system is a VERY database heavy application. We do database caching of searches so that searches are available when a user gets bounced to a different web server in a web farm. Like I said our db servers are each usually 16 cores with 96GB of ram. A typical database is about 40GB, with them being as large as 150GB.
So again, I will ask that if you want to participate in this fun little experiment, that you abide by my requests and stop telling me what I should and should not be doing and focus on what I have asked.
Mark has already set me in the right direction and I have developed a working solution that does not use xml concatenation. I am leaving this open for now in hopes of getting some of the great DB minds at EE to throw in their input.
Thanks,
BrandonGalderisi
Well you are missing the point. You see, in my mind the experiment is picking the right tool and technology for the job. I don't want to sound arrogant I simply want you to understand my point of view. My father was a System Analyst for IBM. He taught me how to write code using punch cards on an IBM 360 Series in 1973. I've been programming ever since. That's 36 years. I grew up talking with my father not only about what technology can do, but how to do it, about the design decision making process et cetera.
So from a high level point of view, you are using XML PATH in a way that it wasn't intended, to get a SQL database to do something it's not good at. Isn't that a red flag? SQL wasn't originally designed for recursion and because it's not a compiled language, code execution time will never match Java, .NET or C++.
I work at a company with over 7,000 full-time developers and am concerned a go to guy when it comes to analyzing application performance. One SQL heavy database application was running into some scalability issues with hierarchical data. As you know, as a RDBMS, MS SQL is not a hierarchical database. The performance problems were due to the attempt to add recursion and reentrant functionality to SQL. So, I talked them out of using SQL.
The've rewritten the functionality in C# and it is literally thousands of times faster.
Would you be willing to guarantee 100% that your business process could not be written using another language? Your profile says you are "open to criticism", so I hope that is still true.
So from a high level point of view, you are using XML PATH in a way that it wasn't intended, to get a SQL database to do something it's not good at. Isn't that a red flag? SQL wasn't originally designed for recursion and because it's not a compiled language, code execution time will never match Java, .NET or C++.
I work at a company with over 7,000 full-time developers and am concerned a go to guy when it comes to analyzing application performance. One SQL heavy database application was running into some scalability issues with hierarchical data. As you know, as a RDBMS, MS SQL is not a hierarchical database. The performance problems were due to the attempt to add recursion and reentrant functionality to SQL. So, I talked them out of using SQL.
The've rewritten the functionality in C# and it is literally thousands of times faster.
Would you be willing to guarantee 100% that your business process could not be written using another language? Your profile says you are "open to criticism", so I hope that is still true.
Active 1 day ago
>>you are using XML PATH in a way that it wasn't intended<<
Maybe, it is one of my favourite little uses, so let me attempt to defend (?) it's use, or maybe even convert you (hehehe) ? .
If we examine the reason why For XML exists, then we quickly realise it is first and formost reason to live is to string concatenate individual rowsets. Addmittedly with tags, but also allows us not to use tags. So, is perfect as a mean of being able to convert multi-rows into a streaming string of characters... And it is very, very fast, it has been optimised beyond any recognisable code that we could create to concatenate data elements (heart of the engine type stuff).
Yes, it is a matter of correct tool for the job, and bending data is not unreasonably achieved within the database. Other databases do have facilities more suited to this type of requirement, and could reasonably argue that so does MS by using the XML function. It just needs management of special characters - there are only 5. That in itself is not neccessarily a failure of any toolset as much as a definite problem with converting information into a new format. And really if we wanted to we could export the XML with a style sheet, but then that really does become a matter of bending XML to be something else.
Reality check... Doing the replace on the special characters to ensure it formats as a string properly is likely an issue regardless of tool. Here it happens to include a few printable characters like ampersand, lessthan, greaterthan, quote and also (technically) apostrophe. There are in fact hundreds of them depending on style sheet. And then there are the unprintable characters that can also compromise a string structure - things like "bel" won't but a "htab" will (see attached).
If you look at W3.org you can see that there are entire chapters devoted to wasy of dealing with it - it is part of the XML standard : http://www.w3.org/TR/REC-xml/
If you look at MS then they too suggest a fix for escape characters : http://support.microsoft.com/kb/308060
And in reality, the list of "esccaped" characters is really significant : http://en.wikipedia.org/wiki/List_of_XML_and_HTML_character_entity_references
So, it really isn't a problem with the use of For XML as a technique for concatenation as much as it is a recognised problem as to how XML manages special characters.
In that regard, really, the replace() function does work a treat (in terms of functionality).
And really someone should look at the range of XML replacement characters and build a function to either encode or decode that range. The trouble with a UDF in that case is that it will become a multi-step UDF, a scaler with a BEGIN and END and as soon as you say that, then performance gets hit big time - not so much in the execution plan, but in actual execution, the UDF suddenly becomes runtim line by line interpretative. In that regard, better to write a non-scaler CLR function... Now, if it is a table UDF, could 'join' the string as a tabletype to the table of xml characters and export an approriate replacement character - a file driven UDF does perform very well (go figure - just having a formal list of column names in the "returns" clause).
But also concede the point that sometimes a bit of code at the correct place can achieve wonders - but is that really any different to using replace ?
So, whaddya reckon ? Hopefully at least converted (or a bit of doubt maybe) to the extent that it might not be considered a corruption of the "for XML" function... ? Maybe ?
Character-Codes.pdf
Maybe, it is one of my favourite little uses, so let me attempt to defend (?) it's use, or maybe even convert you (hehehe) ? .
If we examine the reason why For XML exists, then we quickly realise it is first and formost reason to live is to string concatenate individual rowsets. Addmittedly with tags, but also allows us not to use tags. So, is perfect as a mean of being able to convert multi-rows into a streaming string of characters... And it is very, very fast, it has been optimised beyond any recognisable code that we could create to concatenate data elements (heart of the engine type stuff).
Yes, it is a matter of correct tool for the job, and bending data is not unreasonably achieved within the database. Other databases do have facilities more suited to this type of requirement, and could reasonably argue that so does MS by using the XML function. It just needs management of special characters - there are only 5. That in itself is not neccessarily a failure of any toolset as much as a definite problem with converting information into a new format. And really if we wanted to we could export the XML with a style sheet, but then that really does become a matter of bending XML to be something else.
Reality check... Doing the replace on the special characters to ensure it formats as a string properly is likely an issue regardless of tool. Here it happens to include a few printable characters like ampersand, lessthan, greaterthan, quote and also (technically) apostrophe. There are in fact hundreds of them depending on style sheet. And then there are the unprintable characters that can also compromise a string structure - things like "bel" won't but a "htab" will (see attached).
If you look at W3.org you can see that there are entire chapters devoted to wasy of dealing with it - it is part of the XML standard : http://www.w3.org/TR/REC-xml/
If you look at MS then they too suggest a fix for escape characters : http://support.microsoft.com/kb/308060
And in reality, the list of "esccaped" characters is really significant : http://en.wikipedia.org/wiki/List_of_XML_and_HTML_character_entity_references
So, it really isn't a problem with the use of For XML as a technique for concatenation as much as it is a recognised problem as to how XML manages special characters.
In that regard, really, the replace() function does work a treat (in terms of functionality).
And really someone should look at the range of XML replacement characters and build a function to either encode or decode that range. The trouble with a UDF in that case is that it will become a multi-step UDF, a scaler with a BEGIN and END and as soon as you say that, then performance gets hit big time - not so much in the execution plan, but in actual execution, the UDF suddenly becomes runtim line by line interpretative. In that regard, better to write a non-scaler CLR function... Now, if it is a table UDF, could 'join' the string as a tabletype to the table of xml characters and export an approriate replacement character - a file driven UDF does perform very well (go figure - just having a formal list of column names in the "returns" clause).
But also concede the point that sometimes a bit of code at the correct place can achieve wonders - but is that really any different to using replace ?
So, whaddya reckon ? Hopefully at least converted (or a bit of doubt maybe) to the extent that it might not be considered a corruption of the "for XML" function... ? Maybe ?
Character-Codes.pdf
Active 1 day ago
Oh, just one more little thing... As a developer, I do agree that generating purpose built compiled code will outperform any interpretive environment. As a DBA, I also see the wisdom of getting the engine to do the hard work in reducing the rowset through purpose built "engine" functionality, then it will likely perform that function more efficiently.
OK, first things first, here is the solution to the problem. The .value portion of the xml data type is the key.
select
ComeGetSome.value('.', 'varchar(1000)')
from
(
select
cast((select b+N''
from #t r
where r.a=t.a
for xml path('SQLServerNation')
) as xml)'ComeGetSome', ranking = DENSE_RANK() over(partition by a order by newid())
from #t t
) x
where ranking = 1
As for the comment: "you are using XML PATH in a way that it wasn't intended"
How on earth do you know? XML PATH, like any other tool, is designed to solve problems. I will use it at any chance I get. It works great for this solution. In fact, I'll put it up against any RBAR solution you want to come up with.
Love,
Tim
select
ComeGetSome.value('.', 'varchar(1000)')
from
(
select
cast((select b+N''
from #t r
where r.a=t.a
for xml path('SQLServerNation')
) as xml)'ComeGetSome', ranking = DENSE_RANK() over(partition by a order by newid())
from #t t
) x
where ranking = 1
As for the comment: "you are using XML PATH in a way that it wasn't intended"
How on earth do you know? XML PATH, like any other tool, is designed to solve problems. I will use it at any chance I get. It works great for this solution. In fact, I'll put it up against any RBAR solution you want to come up with.
Love,
Tim
Thanks chap and Mark. Both solutions provided me with the desired outcome within the scope of the requirements. I will be doing some various testing within my system to determine which is the best.
For various reasons, I will be unsubscribing from this thread once I have accepted the solutions. The two of you, and the mods/admins, are aware of how to get in touch with me.
Thanks again!
For various reasons, I will be unsubscribing from this thread once I have accepted the solutions. The two of you, and the mods/admins, are aware of how to get in touch with me.
Thanks again!
Featured Post
Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
So every once in a while at work I am asked to export data from one table and insert it into another on a different server. I hate doing this. There's so many different tables and data types. Some column data needs quoted and some doesn't. What …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers.
Hopes this gives you ideas on visualizing your data in new ways ~
Create a calculated field in a query:
…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen.
Visualize your data! ... really see it
To use the code to create a calendar from a q…
Suggested Courses
Course of the Month8 days, 8 hours left to enroll
721 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.